|< Day Day Up >|
2.5 The Storage Engines
Now it's time to look at each of MySQL's storage engines in more detail. Table 2-5 summarizes some of the high-level characteristics of the handlers. The following sections provide some basic highlights and background about each table handler as well as any unusual characteristics and interesting features.
Before going further, it's worth noting that this isn't an exhaustive discussion of MySQL's storage engines. We assume that you've read (or at least know where to find) the information in the MySQL Reference Manual.
Most of MySQL's disk-based tables have some basic things in common. Each database in MySQL is simply a subdirectory of MySQL's data directory in the underlying filesystem. Whenever you create a table, MySQL stores the table definition in a .frm file with the same name as the table. Thus, when you create a table named MyTable, MySQL stores the table definition in MyTable.frm.
mysql> SHOW TABLE STATUS LIKE 'user' \G *************************** 1. row *************************** Name: user Type: MyISAM Row_format: Dynamic Rows: 6 Avg_row_length: 59 Data_length: 356 Max_data_length: 4294967295 Index_length: 2048 Data_free: 0 Auto_increment: NULL Create_time: 2002-01-24 18:07:17 Update_time: 2002-01-24 21:56:29 Check_time: NULL Create_options: Comment: Users and global privileges 1 row in set (0.06 sec)
Notice that it's a MyISAM table. You might also notice a lot of other information and statistics in the output. Let's briefly look at what each line means:
2.5.1 MyISAM Tables
As MySQL's default storage engine, MyISAM provides a good compromise between performance and useful features. Versions of MySQL prior to 3.23 used the Index Sequential Access Method (ISAM) table format. In Version 3.23, ISAM tables were deprecated in favor of MyISAM, an enhanced ISAM format. MyISAM tables don't provide transactions or a very granular locking model, but they do have full-text indexing (see Chapter 4), compression, and more.
In MyISAM storage, there are typically two files: a data file and an index file. The two files bear .MYD and .MYI extensions, respectively. The MyISAM format is platform-neutral, meaning you can copy the data and index files from an Intel-based server to a Macintosh PowerBook or Sun SPARC without any trouble.
MyISAM tables can contain either dynamic or static (fixed-length) rows. MySQL decides which format to use based on the table definition. The number of rows a MyISAM table can hold is limited primarily by the available disk space on your database server and the largest file your operating system will let you create. Some (mostly older) operating systems have been known to cut you off at 2 GB, so check your local documentation.
However, MyISAM files with variable-length rows, are set up by default to handle only 4 GB of data, mainly for efficiency. The index uses 32-bit pointers to the data records. To create a MyISAM table that can hold more than 4 GB, you must specify values for the MAX_ROWS and AVG_ROW_LENGTH options that represent ballpark figures for the amount of space you need:
CREATE TABLE mytable ( a INTEGER NOT NULL PRIMARY KEY, b CHAR(18) NOT NULL ) MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 32;
In the example, we've told MySQL to be prepared to store at least 32 GB of data in the table. To find out what MySQL decided to do, simply ask for the table status:
mysql> SHOW TABLE STATUS LIKE 'mytable' \G *************************** 1. row *************************** Name: mytable Type: MyISAM Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 98784247807 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2002-02-24 17:36:57 Update_time: 2002-02-24 17:36:57 Check_time: NULL Create_options: max_rows=1000000000 avg_row_length=32 Comment: 1 row in set (0.05 sec)
As you can see, MySQL remembers the create options exactly as specified. And it chose a representation capable of holding 91 GB of data!
188.8.131.52 Other stuff
2.5.2 Compressed MyISAM Tables
For circumstances in which the data never changes, such as CD-ROM- or DVD-ROM-based applications, or in some embedded environments, MyISAM tables can be compressed (or packed) using the myisampack utility. Compressed tables can't be modified, but they generally take far less space and are faster as a result. Having smaller tables means fewer disk seeks are required to find records.
On relatively modern hardware, the overhead involved in decompressing the data is insignificant for most applications. The individual rows are compressed, so MySQL doesn't need to unpack an entire table (or even a page) just to fetch a single row.
2.5.3 RAID MyISAM Tables
While they're not really a separate table type, MyISAM RAID tables do serve a particular niche. To use them, you need to compile your own copy of MySQL from source or use the MySQL-Max package. RAID tables are just like MyISAM tables except that the data file is split into several data files. Despite the reference to RAID in the name, these data files don't have to be stored on separate disks, although it is easy to do so. Writes to the table are striped across the data files, much like RAID-0 would do across physical disks. This can be helpful in two circumstances. If you have an operating system that limits file sizes to 2 or 4 GB but you need larger tables, using RAID will get you past the limit. If you're have an I/O bound table that is read from and written to very frequently, you might achieve better performance by storing each of the RAID files on a separate physical disk.
To create a RAID table, you must supply some additional options at table-creation time:
CREATE TABLE mytable ( a INTEGER NOT NULL PRIMARY KEY, b CHAR(18) NOT NULL ) RAID_TYPE = STRIPED RAID_CHUNKS = 4 RAID_CHUNKSIZE = 16;
The RAID_TYPE option, while required, must be STRIPED or RAID0, which are synonymous. No other RAID algorithms are available. The RAID_CHUNKS parameter tells MySQL how many data files to break the table into. The RAID_CHUNKSIZE option specifies how many kilobytes of data MySQL will write in each file before moving to the next.
In the previous example, MySQL would create four subdirectories named 00, 01, 02, and 03 in which it would store a file named mytable.MYD. When writing data to the table, it would write 16 KB of data to one file and then move to the next one. Once created, RAID tables are transparent. You can use them just as you would normal MyISAM tables.
With the availability of inexpensive RAID controllers and the software RAID features of some operating systems, there isn't much need for using RAID tables in MySQL. Also, it's important to realize that RAID tables split only the data file, not the indexes. If you're trying to overcome file size limits, keep an eye on the size of your index files.
2.5.4 MyISAM Merge Tables
Merge tables are the final variation of MyISAM tables that MySQL provides. Where a RAID table is a single table split into smaller pieces, a Merge table is the combination of several similar tables into one virtual table.
This is particularly useful when MySQL is used in logging applications. Imagine you store web server logs in MySQL. For ease of management, you might create a table for each month. However, when it comes time to generate annual statistics, it would be easier if all the records were in a single table. Using Merge tables, that's possible. You can create 12 normal MyISAM tables, log_2004_01, log_2004_02, ... log_2004_12, and then a Merge table named log_2004.
Queries for a particular month can be run against the specific table that holds the data. But queries that may need to cross month boundaries can be run against the Merge table log_2004 as if it was a table that contained all the data in the underlying twelve tables.
The requirements for a Merge table are that the underlying tables must:
Interestingly, it's possible for some underlying tables to be compressed MyISAM tables. That means you can compress tables as they get old (since they're no longer being written to anyway), but still use them as part of a Merge table. Just make sure to remove the table from the Merge table before compressing it, then re-add it after it has been compressed.
Using the example table from earlier, let's create several identical tables and a Merge table that aggregates them:
CREATE TABLE mytable0 ( a INTEGER NOT NULL PRIMARY KEY, b CHAR(18) NOT NULL ); CREATE TABLE mytable1 ( a INTEGER NOT NULL PRIMARY KEY, b CHAR(18) NOT NULL ); CREATE TABLE mytable2 ( a INTEGER NOT NULL PRIMARY KEY, b CHAR(18) NOT NULL ); CREATE TABLE mytable ( a INTEGER NOT NULL PRIMARY KEY, b CHAR(18) NOT NULL ) TYPE = MERGE UNION = (mytable0, mytable1, mytable2) INSERT_METHOD = LAST;
The only difference between the Merge table and the underlying tables is that it has a few extra options set at creation time. The type, of course, is MERGE. The UNION option specifies the tables that make up the Merge table. Order is important if you plan to insert into the Merge table rather than the underlying tables. The INSERT_METHOD option, which can be NO, FIRST, or LAST, tells MySQL how to handle inserts to the Merge table. If the method is NO, inserts aren't allowed. Otherwise, inserts will always go to either the first or last of the underlying tables based on the value of INSERT_METHOD.
The order of the tables is also important for unique-key lookups because as soon as the record is found, MySQL stops looking. Thus, the earlier in the list the table is, the better. In most logging applications where you'll be doing searches on the Merge table, it might make sense to put the tables in reverse chronological order. The order is also important for making ORDER BY as fast as possible because the required merge-sort will be faster when the rows are nearly in order already. If you don't specify INSERT_METHOD, the default is NO.
As with other tables, you can use SHOW TABLE STATUS to get information about a Merge table:
mysql> SHOW TABLE STATUS LIKE 'mytable' \G *************************** 1. row *************************** Name: mytable Type: MRG_MyISAM Row_format: Fixed Rows: 2 Avg_row_length: 23 Data_length: 46 Max_data_length: NULL Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Create_options: Comment: 1 row in set (0.01 sec)
Not all of the data is available. MySQL doesn't keep track of the creation, update, and check times for merge tables. It also doesn't store the create options that you might expect. However, you can retrieve that information using SHOW CREATE TABLE:
mysql> SHOW CREATE TABLE mytable \G *************************** 1. row *************************** Table: mytable Create Table: CREATE TABLE `mytable` ( `a` int(11) NOT NULL default '0', `b` char(18) NOT NULL default '', PRIMARY KEY (`a`) ) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(mytable0,mytable1,mytable2) 1 row in set (0.00 sec)
This demonstrates that Merge tables really aren't full-fledged tables. In fact, Merge tables have some important limitations and surprising behavior:
2.5.5 InnoDB Tables
The InnoDB table handler is the newest addition to the MySQL family. Developed by Heikki Tuuri of Innobase Oy in Helsinki, Finland, InnoDB was designed with transaction processing in mind and modeled largely after Oracle.
The InnoDB table handler breaks from MySQL tradition and stores all its data in a series of one or more data files that are collectively known as a tablespace. A tablespace is essentially a black box that is completely managed by InnoDB. If a tablespace if composed of several underlying files, you can't choose or influence which of the underlying files will contain the data for any particular database or table.
InnoDB can also use raw disk partitions in building its tablespace, but that's not very common. Using disk partitions makes it more difficult to back up InnoDB's data, and the resulting performance boost is on the order of a few percent on most operating systems.
As of MySQL 4.1, you have the option of slightly more MyISAM-like storage for InnoDB. You can enable multiple tablespace support by adding innodb_file_per_table to my.cnf; this makes InnoDB create one tablespace file per newly created InnoDB table. The filename will be of the form tablename.ibd. In all other respects, they're simply dynamically sized InnoDB tablespace files. Each one just happens to contain data for only one specific table.
184.108.40.206 Locking and concurrency
InnoDB uses MVCC to achieve very high concurrency. InnoDB defaults to the repeatable read isolation level, and as of MySQL Version 4.0.5, it implements all four levels: read uncommitted, read committed, repeatable read, and serializable.
In an InnoDB transaction, You may explicitly obtain either exclusive or shared locks on rows using the MySQL statements: SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE.
220.127.116.11 Special features
CREATE TABLE master ( id INTEGER NOT NULL PRIMARY KEY, stuff TEXT NOT NULL ) TYPE = InnoDB; CREATE TABLE detail ( master_id INTEGER NOT NULL, detail1 VARCHAR(80) NOT NULL, detail2 VARCHAR(20) NOT NULL, INDEX master_idx (master_id), FOREIGN KEY (master_id) REFERENCES master(id) ) TYPE = InnoDB;
InnoDB doesn't allow you to insert add records to the detail table until there is a corresponding record in the master table. Attempting to do so yields an error:
mysql> INSERT INTO detail VALUES (10, 'blah', 'blah'); ERROR 1216: Cannot add a child row: a foreign key constraint fails
InnoDB also provides lightning fast record lookups for queries that use a primary key. Its clustered index system (described in more detail in Chapter 4) explains how it works.
2.5.6 Heap (In-Memory) Tables
MySQL provides in-memory Heap tables for applications in which you need incredibly fast access to data that either never changes or doesn't need to persist after a restart. Using a Heap table means that a query can complete without even waiting for disk I/O. This makes sense for lookup or mapping tables, such as area code to city/state name, or for caching the results of periodically aggregated data.
While Heap tables are very fast, they often don't work well as replacements for disk-based tables. Until MySQL Version 4.1, Heap tables used only hash-based indexes rather than B-tree indexes (which MyISAM uses). Hash indexes are suited to only a subset of queries. Section 4.3.2 in Chapter 4 covers this in more detail.
2.5.7 Berkeley DB (BDB) Tables
MySQL's first transaction-safe storage engine, BDB is built on top of the Berkeley DB database library, which is now maintained and developed by Sleepycat Software. In fact, the original work to integrate the Berkeley DB technology with MySQL was performed jointly by MySQL AB and Sleepycat Software. Other than transactions, the BDB table handler's other main feature is that it uses page-level locking to achieve higher concurrency than MyISAM tables.
Though BDB tables have been available in MySQL since Version 3.23, they haven't proven very popular among users. Many users looking for transactions in MySQL were also looking for row-level locking or MVCC. Further dampening interest in BDB, by the time the BDB code had stabilized, word of InnoDB began to circulate. This prompted many users to hold out for the real thing and use MyISAM tables a bit longer.
If nothing else, the inclusion of BDB tables in MySQL served as a stepping stone in many ways. It prompted the MySQL developers to put the transaction-handling infrastructure into MySQL, while at the same time proving to the skeptics that MySQL wasn't a toy.
|< Day Day Up >|