Web Directory: mysql docs, manual Table types.php
Search:  
MySQL Reference Manual for version 5.0.0-alpha. - 15 MySQL Storage Engines and Table Types Go to the first, previous, next, last section, table of contents.


15 MySQL Storage Engines and Table Types

MySQL supports several storage engines that act as handlers for different table types. MySQL storage engines include both those that handle transaction-safe tables and those that handle non-transaction-safe tables:

  • The original storage engine was ISAM, which managed non-transactional tables. This engine has been replaced by MyISAM and should no longer be used. It is deprecated in MySQL 4.1, and will be removed in MySQL 5.0.
  • In MySQL 3.23.0, the MyISAM and HEAP storage engines were introduced. MyISAM is an improved replacement for ISAM. The HEAP storage engine provides in-memory tables. (HEAP now is known as the MEMORY engine.) The MERGE storage engine was added in MySQL 3.23.25. It allows a collection of identical MyISAM tables to be handled as a single table. All three of these storage engines handle non-transactional tables, and all are included in MySQL by default.
  • The InnoDB and BDB storage engines that handle transaction-safe tables were introduced in later versions of MySQL 3.23. Both are available in source distributions as of MySQL 3.23.34a. BDB is included in MySQL-Max binary distributions on those operating systems that support it. InnoDB also is included in MySQL-Max binary distributions for MySQL 3.23. Beginning with MySQL 4.0, InnoDB is included by default in all MySQL binary distributions. In source distributions, you can enable or disable either engine by configuring MySQL as you like.

This chapter describes each of the MySQL storage engines except for InnoDB, which is covered in section 16 The InnoDB Storage Engine.

When you create a new table, you can tell MySQL what type of table to create by adding an ENGINE or TYPE table option to the CREATE TABLE statement:

CREATE TABLE t (i INT) ENGINE = INNODB;
CREATE TABLE t (i INT) TYPE = MEMORY;

ENGINE is the preferred term, but cannot be used before MySQL 4.0.18. TYPE is available beginning with MySQL 3.23.0 (the first version of MySQL for which multiple storage engines were available).

If you omit the ENGINE or TYPE option, the default table type is usually MyISAM. This can be changed by setting the table_type system variable.

To convert a table from one type to another, use an ALTER TABLE statement that indicates the new type:

ALTER TABLE t ENGINE = MYISAM;
ALTER TABLE t TYPE = BDB;

See section 14.2.5 CREATE TABLE Syntax and section 14.2.2 ALTER TABLE Syntax.

If you try to use a table type that is not compiled in or that is compiled in but deactivated, MySQL instead creates a table of type MyISAM. This behavior is convenient when you want to copy tables between MySQL servers that support different table types. (Perhaps your master server supports transactional storage engines for increased safety, while the slave servers use only non-transactional storage engines for greater speed.)

This automatic change of table types can be confusing for new MySQL users. In MySQL 4.1 and up, a warning is generated when a table type is automatically changed.

MySQL always creates a `.frm' file to hold the table and column definitions. The table's index and data may be stored in one or more other files, depending on the table type.

A database may contain tables of different types.

Transaction-safe tables (TST) have several advantages:

  • Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.
  • You can combine many statements and accept them all at the same time with the COMMIT statement (if autocommit is disabled).
  • You can execute ROLLBACK to ignore your changes (if autocommit is disabled).
  • If an update fails, all your changes will be restored. (With NTST tables, all changes that have taken place are permanent)
  • Can provide better concurrency if the table gets many updates concurrently with reads.

Note that to use the InnoDB storage engine in MySQL 3.23, you must configure at least the innodb_data_file_path startup option. In 4.0 and up, InnoDB uses default configuration values if you specify none. See section 16.4 InnoDB Configuration.

Non-transaction-safe tables (NTST) have several advantages of their own, all of which occur because there is no transaction overhead:

  • Much faster
  • Lower disk space requirements
  • Less memory required to perform updates

You can combine TST and NTST tables in the same statements to get the best of both worlds. However, within a transaction with autocommit disabled, changes to non-transaction-safe tables still are committed immediately and cannot be rolled back.

15.1 The MyISAM Storage Engine

MyISAM is the default table type as of MySQL 3.23. It is based on the ISAM code but has many useful extensions.

Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. A `.frm' file stores the table definition. The datafile has a `.MYD' (MYData) extension. The index file has a `.MYI' (MYIndex) extension,

To specify explicitly that you want a MyISAM table, indicate that with an ENGINE or TYPE table option:

CREATE TABLE t (i INT) ENGINE = MYISAM;
CREATE TABLE t (i INT) TYPE = MYISAM;

Normally, the ENGINE or TYPE option is unnecessary; MyISAM is the default storage engine unless the default has been changed.

You can check or repair MyISAM tables with the myisamchk utility. See section 5.6.2.7 Using myisamchk for Crash Recovery. You can compress MyISAM tables with myisampack to take up much less space. See section 8.2 myisampack, the MySQL Compressed Read-only Table Generator.

The following characteristics of the MyISAM storage engine are improvements over the older ISAM engine::

  • All data values are stored with the low byte first. This makes the data machine and operating system independent. The only requirement for binary portability is that the machine uses two's-complement signed integers (as every machine for the last 20 years has) and IEEE floating-point format (also totally dominant among mainstream machines). The only area of machines that may not support binary compatibility are embedded systems, which sometimes have peculiar processors. There is no big speed penalty for storing data low byte first; the bytes in a table row normally are unaligned and it doesn't take that much more power to read an unaligned byte in order than in reverse order. Also, the code in the server that fetches column values is not time critical compared to other code.
  • Large files (up to 63-bit file length) are supported on filesystems and operating systems that support large files.
  • Dynamic size rows are much less fragmented when mixing deletes with updates and inserts. This is done by automatically combining adjacent deleted blocks and by extending blocks if the next block is deleted.
  • The maximum number of indexes per table is 32 by default. This can be enlarged to 64 without having to recompile myisamchk. The maximum number of columns per index is 16.
  • The maximum key length is 500 bytes by default, and 1000 bytes as of MySQL 4.1.2. This can be changed by recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.
  • BLOB and TEXT columns can be indexed.
  • NULL values are allowed in indexed columns. This takes 0-1 bytes per key.
  • All numeric key values are stored with high byte first to give better index compression.
  • Index files are usually much smaller with MyISAM than with ISAM. This means that MyISAM will normally use less system resources than ISAM, but will need more CPU time when inserting data into a compressed index.
  • When records are inserted in sorted order (as when you are using an AUTO_INCREMENT column) the index tree will be split so that the high node only contains one key. This improves space utilization in the index tree.
  • Internal handling of one AUTO_INCREMENT column per table. MyISAM automatically updates this column for INSERT/UPDATE. This makes AUTO_INCREMENT columns faster (at least 10%). Values at the top of the sequence are not reused after being deleted as they are with ISAM. (When an AUTO_INCREMENT column is defined as the last column of a multiple-column index, reuse of deleted values does occur.) The AUTO_INCREMENT value can be reset with ALTER TABLE or myisamchk.
  • If a table doesn't have free blocks in the middle of the datafile, you can INSERT new rows into it at the same time that other threads are reading from the table. (These are concurrent inserts.) A free block can occur as a result of deleting rows or an update of a dynamic length row with more data than its current contents. When all free blocks are used up (filled in), future inserts become concurrent again.
  • You can use put the datafile and index file on different directories to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options to CREATE TABLE. See section 14.2.5 CREATE TABLE Syntax.
  • As of MySQL 4,1, each character column can have a different character set.
  • There is a flag in the MyISAM index file that indicates whether the table was closed correctly. If mysqld is started with the --myisam-recover option, MyISAM tables are automatically checked (and optionally repaired) when opened if the table wasn't closed properly.
  • myisamchk marks tables as checked if you run it with the --update-state option. myisamchk --fast checks only those tables that don't have this mark.
  • myisamchk --analyze stores statistics for key parts, not only for whole keys as in ISAM.
  • myisampack can pack BLOB and VARCHAR columns; pack_isam cannot.

MyISAM also supports the following features, which MySQL will be able to use in the near future:

  • Support for a true VARCHAR type; a VARCHAR column starts with a length stored in two bytes.
  • Tables with VARCHAR may have fixed or dynamic record length.
  • VARCHAR and CHAR columns may be up to 64KB.
  • A hashed computed index can be used for UNIQUE. This will allow you to have UNIQUE on any combination of columns in a table. (You can't search on a UNIQUE computed index, however.)

15.1.1 MyISAM Startup Options

The following options to mysqld can be used to change the behavior of MyISAM tables:

--myisam-recover=mode
Set the mode for automatic recovery of crashed MyISAM tables.
--delay-key-write=ALL
Don't flush key buffers between writes for any MyISAM table. NOTE: If you do this, you should not use MyISAM tables from another program (like from another MySQL server or with myisamchk) when the table is in use. Doing so, will lead to index corruption. Using --external-lock will not help for tables which uses delay-key-writes.

The following system variables affect the behavior of MyISAM tables:

bulk_insert_buffer_size
The size of the tree cache used in bulk insert optimization. Note: This is a limit per thread!
myisam_max_extra_sort_file_size
Used to help MySQL to decide when to use the slow but safe key cache index creation method. Note: This parameter is given in megabytes before MySQL 4.0.3, and in bytes as of 4.0.3.
myisam_max_sort_file_size
Don't use the fast sort index method to created index if the temporary file would get bigger than this. Note: This parameter is given in megabytes before MySQL 4.0.3, and in bytes as of 4.0.3.
myisam_sort_buffer_size
Set the size of the buffer used when recovering tables.

See section 5.2.3 Server System Variables.

Automatic recovery is activated if you start mysqld with the --myisam-recover option. See section 5.2.1 mysqld Command-line Options. On open, the table is checked if it's marked as crashed or if the open count variable for the table is not 0 and you are running the server with --skip-external-locking. If either of these conditions are true, the following happens:

  • The table is checked for errors.
  • If the server finds an error, it tries to do a fast table repair (with sorting and without re-creating the datafile).
  • If the repair fails because of an error in the datafile (for example, a duplicate-key error), the server tries again, this time re-creating the datafile.
  • If the repair still fails, the server tries once more with the old repair option method (write row by row without sorting), which should be able to repair any type of error and that has low disk space requirements.

If the recovery wouldn't be able to recover all rows from a previous completed statement and you didn't specify FORCE in the value of the --myisam-recover option, automatic repair aborts with an error message in the error log:

Error: Couldn't repair table: test.g00pages

If you specify FORCE, a warning like this is written instead:

Warning: Found 344 of 354 rows when repairing ./test/g00pages

Note that if the automatic recovery value includes BACKUP, the recovery process creates files with names of the form `tbl_name-datetime.BAK'. You should have a cron script that automatically moves these files from the database directories to backup media.

See section 5.2.1 mysqld Command-line Options.

15.1.2 Space Needed for Keys

MyISAM tables use B-tree indexes. You can roughly calculate the size for the index file as (key_length+4)/0.67, summed over all keys. This is for the worst case when all keys are inserted in sorted order and the table doesn't have any compressed keys.

String indexes are space compressed. If the first index part is a string, it will also be prefix compressed. Space compression makes the index file smaller than the above figures if the string column has a lot of trailing space or is a VARCHAR column that is not always used to the full length. Prefix compression is used on keys that start with a string. Prefix compression helps if there are many strings with an identical prefix.

In MyISAM tables, you can also prefix compress numbers by specifying PACK_KEYS=1 when you create the table. This helps when you have many integer keys that have an identical prefix when the numbers are stored high-byte first.

15.1.3 MyISAM Table Storage Formats

MyISAM supports three different storage formats. Two of them (fixed and dynamic format) are chosen automatically depending on the type of columns you are using. The third, compressed format, can be created only with the myisampack utility.

When you CREATE or ALTER a table that doesn't have BLOB or TEXT columns, you can force the table format to FIXED or DYNAMIC with the ROW_FORMAT table option. This causes CHAR and VARCHAR columns to become CHAR for FIXED format or VARCHAR for DYNAMIC format.

In the future, you will be able to compress or decompress tables by specifying ROW_FORMAT={COMPRESSED | DEFAULT} to ALTER TABLE. See section 14.2.5 CREATE TABLE Syntax.

15.1.3.1 Static (Fixed-length) Table Characteristics

Static format is the default for MyISAM tables. It is used when the table contains no variable-length columns (VARCHAR, BLOB, or TEXT). Each row is stored using a fixed number of bytes.

Of the three MyISAM storage formats, static format is the simplest and most secure (least subject to corruption). It is also the fastest of the on-disk formats. The speed comes from the easy way that rows in the datafile can be found on disk: When looking up a row based on a row number in the index, multiply the row number by the row length. Also, when scanning a table, it is very easy to read a constant number of records with each disk read operation.

The security is evidenced if your computer crashes while the MySQL server is writing to a fixed-format MyISAM file. In this case, myisamchk can easily determine where each row starts and ends, so it can usually reclaim all records except the partially written one. Note that in MySQL indexes can always be reconstructed based on the data rows.

General characteristics of static format tables:

  • All CHAR, NUMERIC, and DECIMAL columns are space-padded to the column width.
  • Very quick.
  • Easy to cache.
  • Easy to reconstruct after a crash, because records are located in fixed positions.
  • Reorganization is unnecessary unless you delete a huge number of records and want to return free disk space to the operating system. To do this, use OPTIMIZE TABLE or myisamchk -r.
  • More disk space usually required than for dynamic tables.

15.1.3.2 Dynamic Table Characteristics

Dynamic storage format is used if a MyISAM table contains any variable-length columns (VARCHAR, BLOB, or TEXT), or if the table was created with the ROW_FORMAT=DYNAMIC option.

This format is a little more complex because each row has a header that says how long it is. One record can also end up at more than one location when it is made longer as a result of an update.

You can use OPTIMIZE TABLE or myisamchk to defragment a table. If you have fixed-length columns that you access or change frequently in a table that also contains some variable-length columns, it might be a good idea to move the variable-length columns to other tables just to avoid fragmentation.

General characteristics of dynamic format tables:

  • All string columns are dynamic except those with a length less than four.
  • Each record is preceded by a bitmap that indicates which columns contain the empty string (for string columns) or zero (for numeric columns). Note that this does not include column that contain NULL values. If a string column has a length of zero after trailing space removal, or a numeric column has a value of zero, it is marked in the bit map and not saved to disk. Non-empty strings are saved as a length byte plus the string contents.
  • Much less disk space usually required than for fixed-length tables.
  • Each record uses only as much space as is required. However, if a record becomes larger, it is split into as many pieces as are required, resulting in record fragmentation. For example, if you update a row with information that extends the row length, the row will be fragmented. In this case, you may have to run myisamchk -r from time to time to get better performance. Use myisamchk -ei to obtain table statistics.
  • More difficult to reconstruct after a crash, because a record may be fragmented into many pieces and a link (fragment) may be missing.
  • The expected row length for dynamic sized records is calculated using the following expression:
    3
    + (number of columns + 7) / 8
    + (number of char columns)
    + packed size of numeric columns
    + length of strings
    + (number of NULL columns + 7) / 8
    
    There is a penalty of six bytes for each link. A dynamic record is linked whenever an update causes an enlargement of the record. Each new link will be at least 20 bytes, so the next enlargement will probably go in the same link. If not, there will be another link. You may check how many links there are with myisamchk -ed. All links may be removed with myisamchk -r.

15.1.3.3 Compressed Table Characteristics

Compressed storage format is a read-only format that is generated with the myisampack tool.

All MySQL distributions as of version 3.23.19 include myisampack by default. (This is when MySQL was placed under the GPL.) For earlier versions, myisampack was included only with licenses or support agreements, but the server still can read tables that were compressed with myisampack. Compressed tables can be uncompressed with myisamchk. (For the ISAM storage engine, compressed tables can be created with pack_isam and uncompressed with isamchk.)

Compressed tables have the following characteristics:

  • Compressed tables take very little disk space. This minimizes disk usage, which is very nice when using slow disks (such as CD-ROMs).
  • Each record is compressed separately, so there is very little access overhead. The header for a record is fixed (1-3 bytes) depending on the biggest record in the table. Each column is compressed differently. There is usually a different Huffman tree for each column. Some of the compression types are:
    • Suffix space compression.
    • Prefix space compression.
    • Numbers with a value of zero are stored using one bit.
    • If values in an integer column have a small range, the column is stored using the smallest possible type. For example, a BIGINT column (eight bytes) can be stored as a TINYINT column (one byte) if all its values are in the range from -128 to 127.
    • If a column has only a small set of possible values, the column type is converted to ENUM.
    • A column may use a combination of the preceding compressions.
  • Can handle fixed-length or dynamic-length records.

15.1.4 MyISAM Table Problems

The file format that MySQL uses to store data has been extensively tested, but there are always circumstances that may cause database tables to become corrupted.

15.1.4.1 Corrupted MyISAM Tables

Even though the MyISAM table format is very reliable (all changes to a table made by a SQL statement are written before the statement returns), you can still get corrupted tables if some of the following things happen:

  • The mysqld process is killed in the middle of a write.
  • Unexpected computer shutdown occurs (for example, the computer is turned off).
  • Hardware errors.
  • You are using an external program (such as myisamchk) on a table that is being modified by the server at the same time.
  • A software bug in the MySQL or MyISAM code.

Typical symptoms for a corrupt table are:

  • You get the following error while selecting data from the table:
    Incorrect key file for table: '...'. Try to repair it
    
  • Queries don't find rows in the table or return incomplete data.

You can check whether a MyISAM table is okay with the CHECK TABLE statement. You can repair a corrupted MyISAM table with REPAIR TABLE. When mysqld is not running, you can also check or repair a table with the myisamchk command. See section 14.5.2.3 CHECK TABLE Syntax, section 14.5.2.6 REPAIR TABLE Syntax, and section 5.6.2.1 myisamchk Invocation Syntax.

If your tables become corrupted frequently, you should try to determine why this is happening. The most important thing to know is whether the table became corrupted as a result of a server crash. You can verify this easily by looking for a recent restarted mysqld message in the error log. If there is such a message, it is likely that that table corruption is a result of the server dying. Otherwise, corruption may have occurred during normal operation, which is a bug. You should try to create a reproducible test case that demonstrates the problem. See section A.4.1 What to Do if MySQL Keeps Crashing and section D.1.6 Making a Test Case If You Experience Table Corruption.

15.1.4.2 Problems from Tables Not Being Closed Properly

Each MyISAM index (`.MYI') file has a counter in the header that can be used to check whether a table has been closed properly. If you get the following warning from CHECK TABLE or myisamchk, it means that this counter has gone out of sync:

clients are using or haven't closed the table properly

This warning doesn't necessarily mean that the table is corrupted, but you should at least check the table to verify that it's okay.

The counter works as follows:

  • The first time a table is updated in MySQL, a counter in the header of the index files is incremented.
  • The counter is not changed during further updates.
  • When the last instance of a table is closed (because of a FLUSH TABLES operation or because there isn't room in the table cache) the counter is decremented if the table has been updated at any point.
  • When you repair the table or check the table and it is found to be okay, the counter is reset to zero.
  • To avoid problems with interaction with other processes that might check the table, the counter is not decremented on close if it was zero.

In other words, the counter can go out of sync only under these conditions:

  • The MyISAM tables are copied without a preceding LOCK TABLES and FLUSH TABLES.
  • MySQL has crashed between an update and the final close. (Note that the table may still be okay, as MySQL always issues writes for everything between each statement.)
  • A table was modified by myisamchk --recover or myisamchk --update-state at the same time that it was in use by mysqld.
  • Many mysqld servers are using the table and one server performed a REPAIR TABLE or CHECK TABLE on the table while it was in use by another server. In this setup, it is safe to use CHECK TABLE, although you might get the warning from other servers. However, REPAIR TABLE should be avoided because when one server replaces the datafile with a new one, this is not signaled to the other servers. In general, it's a bad idea to share a data directory among multiple servers. See section 5.9 Running Multiple MySQL Servers on the Same Machine for additional discussion.

15.2 The MERGE Storage Engine

The MERGE storage engine was introduced in MySQL 3.23.25. It is also known as the MRG_MyISAM engine. The code is now reasonably stable.

A MERGE table is a collection of identical MyISAM tables that can be used as one. ``Identical'' means that all tables have identical column and index information. You can't merge tables in which the columns are packed differently, don't have exactly the same columns, or have the indexes in different order. However, any or all of the tables can be compressed with myisampack. See section 8.2 myisampack, the MySQL Compressed Read-only Table Generator.

When you create a MERGE table, MySQL creates two files on disk. The files have names that begin with the table name and have an extension to indicate the file type. A `.frm' file stores the table definition, and a `.MRG' file contains the names of the tables that should be used as one. (Originally, all used tables had to be in the same database as the MERGE table itself. This restriction has been lifted as of MySQL 4.1.1.)

You can use SELECT, DELETE, UPDATE, and (as of MySQL 4.0) INSERT on the collection of tables. For the moment, you must have SELECT, UPDATE, and DELETE privileges on the tables you map to a MERGE table.

If you DROP the MERGE table, you are only dropping the MERGE specification. The underlying tables are not affected.

When you create a MERGE table, you must specify a UNION=(list-of-tables) clause that indicates which tables you want to use as one. You can optionally specify an INSERT_METHOD option if you want inserts for the MERGE table to happen in the first or last table of the UNION list. If you don't specify any INSERT_METHOD option or specify it with a value of NO, attempts to insert records into the MERGE table result in an error.

The following example shows how to use MERGE tables:

mysql> CREATE TABLE t1 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20));
mysql> CREATE TABLE t2 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20));
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
    ->    a INT NOT NULL AUTO_INCREMENT,
    ->    message CHAR(20), INDEX(a))
    ->    TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

Note that the a column is indexed in the MERGE table, but is not declared as a PRIMARY KEY as it is in the underlying MyISAM tables. This is necessary because a MERGE table cannot enforce uniqueness over the set of underlying tables.

After creating the MERGE table, you can do things like this:

mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+

Note that you can also manipulate the `.MRG' file directly from the outside of the MySQL server:

shell> cd /mysql-data-directory/current-database
shell> ls -1 t1 t2 > total.MRG
shell> mysqladmin flush-tables

To remap a MERGE table you can do one of the following:

  • DROP the table and re-create it.
  • Use ALTER TABLE tbl_name UNION=(...) to change the list of underlying tables.
  • Change the `.MRG' file and issue a FLUSH TABLE statement for the MERGE table and all underlying tables to force the storage engine to read the new definition file.

MERGE tables can help you solve the following problems:

  • Easily manage a set of log tables. For example, you can put data from different months into separate files, compress some of them with myisampack, and then create a MERGE table to use them as one.
  • Give you more speed. You can split a big read-only table based on some criteria, and then put individual tables on different disks. A MERGE table on this could be much faster than using the big table. (You can also use a RAID to get the same kind of benefits.)
  • Do more efficient searches. If you know exactly what you are looking after, you can search in just one of the split tables for some queries and use a MERGE table for others. You can even have many different MERGE tables that use overlapping sets of tables.
  • More efficient repairs. It's easier to repair the individual tables that are mapped to a MERGE table than to repair a single really big table.
  • Instant mapping of many files as one. A MERGE table need not maintain an index of its own because it uses the indexes of the individual tables. As a result, MERGE table collections are very fast to create or remap. (Note that you must still specify the index definitions when you create a MERGE table, even though no indexes are created.)
  • If you have a set of tables that you join as a big table on demand or batch, you should instead create a MERGE table on them on demand. This is much faster and will save a lot of disk space.
  • Exceed the file-size limit for the operating system. Each MyISAM table is bound by this limit, but a collection of MyISAM tables is not.
  • You can create an alias or synonym for a MyISAM table by defining a MERGE table that maps to that single table. There should be no really notable performance impact of doing this (only a couple of indirect calls and memcpy() calls for each read).

The disadvantages of MERGE tables are:

  • You can use only identical MyISAM tables for a MERGE table.
  • MERGE tables use more file descriptors. If 10 clients are using a MERGE table that maps to 10 tables, the server uses 10*10 + 10 file descriptors. (10 datafile descriptors for each of the 10 clients, and 10 index file descriptors shared among the clients.)
  • Key reads are slower. When you read a key, the MERGE storage engine needs to issue a read on all underlying tables to check which one most closely matches the given key. If you then do a ``read-next,'' the MERGE storage engine needs to search the read buffers to find the next key. Only when one key buffer is used up, the storage engine will need to read the next key block. This makes MERGE keys much slower on eq_ref searches, but not much slower on ref searches. See section 7.2.1 EXPLAIN Syntax (Get Information About a SELECT) for more information about eq_ref and ref.

15.2.1 MERGE Table Problems

The following are the known problems with MERGE tables:

  • If you use ALTER TABLE to change a MERGE table to another table type, the mapping to the underlying tables is lost. Instead, the rows from the underlying MyISAM tables are copied into the altered table, which then is assigned the new type.
  • Before MySQL 4.1.1, all underlying tables and the MERGE table itself had to be in the same database.
  • REPLACE doesn't work.
  • You can't use DROP TABLE, ALTER TABLE, DELETE FROM without a WHERE clause, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on any of the tables that are mapped into a MERGE table that is ``open.'' If you do this, the MERGE table may still refer to the original table and you will get unexpected results. The easiest way to work around this deficiency is to issue FLUSH TABLES statement to ensure that no MERGE tables remain ``open.''
  • A MERGE table cannot maintain UNIQUE constraints over the whole table. When you perform an INSERT, the data goes into the first or last MyISAM table (depending on the value of the INSERT_METHOD option). MySQL ensures that unique key values remain unique within that MyISAM table, but not across all the tables in the collection.
  • Before MySQL 3.23.49, DELETE FROM merge_table used without a WHERE clause only clears the mapping for the table. That is, it incorrectly empties the `.MRG' file rather than deleting records from the mapped tables.
  • Using RENAME TABLE on an active MERGE table may corrupt the table. This will be fixed in MySQL 4.1.x.
  • When you create a MERGE table, there is no check whether the underlying tables exist and have identical structure. When the MERGE table is used, MySQL does a quick check that the record length for all mapped tables is equal, but this is not foolproof. If you create a MERGE table from dissimilar MyISAM tables, you are very likely to run into strange problems.
  • Index order in the MERGE table and its underlying tables should be the same. If you use ALTER TABLE to add a UNIQUE index to a table used in a MERGE table, and then use ALTER TABLE to add a normal index on the MERGE table, the index order will be different for the tables if there was an old non-unique index in the underlying table. (This is because ALTER TABLE puts UNIQUE indexes before normal indexes to be able to detect duplicate keys as early as possible.) Consequently, queries may return unexpected results.
  • DROP TABLE on a table that is in use by a MERGE table does not work on Windows because the MERGE storage engine does the table mapping hidden from the upper layer of MySQL. Because Windows doesn't allow you to delete files that are open, you first must flush all MERGE tables (with FLUSH TABLES) or drop the MERGE table before dropping the table. We will fix this at the same time we introduce views.

15.3 The MEMORY (HEAP) Storage Engine

The MEMORY storage engine creates tables with contents that are stored in memory. Before MySQL 4.1, MEMORY tables are called HEAP tables. As of 4.1, HEAP is a synonym for MEMORY, and MEMORY is the preferred term.

Each MEMORY table is associated with one disk file. The filename begins with the table name and has an extension of `.frm' to indicate that it stores the table definition.

To specify explicitly that you want a MEMORY table, indicate that with an ENGINE or TYPE table option:

CREATE TABLE t (i INT) ENGINE = MEMORY;
CREATE TABLE t (i INT) TYPE = HEAP;

MEMORY tables are stored in memory and use hash indexes. This makes them very fast, and very useful for creating temporary tables! However, when the server shuts down, all data stored in MEMORY tables is lost. The tables continue to exist because their definitions are stored in the `.frm' files on disk, but their contents will be empty when the server restarts.

Here is an example that shows how you might create, use, and remove a MEMORY table:

mysql> CREATE TABLE test TYPE=MEMORY
    ->     SELECT ip,SUM(downloads) AS down
    ->     FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

MEMORY tables have the following characteristics:

  • Space for MEMORY tables is allocated in small blocks. The tables use 100% dynamic hashing (on inserting). No overflow areas and no extra key space are needed. There is no extra space needed for free lists. Deleted rows are put in a linked list and are reused when you insert new data into the table. MEMORY tables also don't have problems with deletes plus inserts, which normally is common with hashed tables.
  • MEMORY tables allow up to 32 indexes per table, 16 columns per index, and a maximum key length of 500 bytes.
  • Before MySQL 4.1, the MEMORY storage engine implements only hash indexes. From MySQL 4.1 on, you can specify explicitly that a MEMORY table index should be HASH or BTREE by adding a USING clause:
    CREATE TABLE lookup
        (id INT, INDEX USING BTREE (id))
        ENGINE = MEMORY;
    CREATE TABLE lookup
        (id INT, INDEX USING HASH (id))
        ENGINE = MEMORY;
    
    General characteristics of B-tree and hash indexes are described in section 7.4.5 How MySQL Uses Indexes.
  • You can have non-unique keys in a MEMORY table. (This is an uncommon feature for implementations of hash indexes.)
  • If you have a hash index on a MEMORY table that has a high degree of key duplication (many index entries containing the same value), updates to the table that affect key values and all deletes will be significantly slower. The degree of slowdown is proportional to the degree of duplication (or, inversely proportional to the index cardinality). You can use a BTREE index to avoid this problem.
  • MEMORY tables use a fixed record length format.
  • MEMORY doesn't support BLOB or TEXT columns.
  • MEMORY doesn't support AUTO_INCREMENT columns.
  • Prior to MySQL 4.0.2, MEMORY doesn't support indexes on columns that can contain NULL values.
  • MEMORY tables are shared between all clients (just like any other table).
  • The MEMORY table property that table contents are stored in memory is one that is shared with internal tables that the server creates on the fly while processing queries. However, internal tables also have the property that the server converts them to on-disk tables automatically if they become too large. The size limit is determined by the value of the tmp_table_size system variable. MEMORY tables are not converted to disk tables. To ensure that you accidentally don't do anything foolish, you can set the max_heap_table_size system variable to impose a maximum size on MEMORY tables. For individual tables, you can also specify a MAX_ROWS table option in the CREATE TABLE statement
  • The server needs enough extra memory to maintain all MEMORY tables that are in use at the same time.
  • To free memory used by a MEMORY table if you no longer require its contents, you should execute DELETE or TRUNCATE TABLE, or else remove the table with DROP TABLE.
  • If you want to populate a MEMORY table when the MySQL server starts, you can use the --init-file option. For example, you can put statements such as INSERT INTO ... SELECT or LOAD DATA INFILE into the file to load the table from some persistent data source. See section 5.2.1 mysqld Command-line Options.
  • If you are using replication, the master server's MEMORY tables become empty when it is shut down and restarted. However, a slave is not aware that these tables have become empty, so it will return out of date content if you select data from them. Beginning with MySQL 4.0.18, when a MEMORY table is used on master for the first time since the master's startup, a DELETE FROM statement is written to the master's binary log automatically, thus synchronizing the slave to the master again. Note that even with this strategy, the slave still has out of date data in the table during the interval between the master's restart and its first use of the table. But if you use the --init-file option to populate the MEMORY table on the master at startup, it ensures that the failing time interval is zero.
  • The memory needed for one row in a MEMORY table is calculated using the following expression:
    SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2)
    + ALIGN(length_of_row+1, sizeof(char*))
    
    ALIGN() represents a round-up factor to cause the row length to be an exact multiple of the char pointer size. sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.

15.4 The BDB (BerkeleyDB) Storage Engine

Berkeley DB, available at http://www.sleepycat.com/ has provided MySQL with a transactional storage engine. This storage engine is typically called BDB for short. Support for the BDB storage engine is included in the MySQL source distribution starting from version 3.23.34a and is activated in MySQL-Max binary distributions.

BDB tables may have a greater chance of surviving crashes and are also capable of COMMIT and ROLLBACK operations on transactions. The MySQL source distribution comes with a BDB distribution that has a couple of small patches to make it work more smoothly with MySQL. You can't use a non-patched BDB version with MySQL.

We at MySQL AB are working in close cooperation with Sleepycat to keep the quality of the MySQL/BDB interface high. (Even though Berkeley DB is in itself very tested and reliable, the MySQL interface is still considered gamma quality. We are improving and optimizing it.)

When it comes to support for any problems involving BDB tables, we are committed to helping our users locate the problem and create a reproducible test case. Any such test case will be forwarded to Sleepycat who in turn will help us find and fix the problem. As this is a two-stage operation, any problems with BDB tables may take a little longer for us to fix than for other storage engines. However, we anticipate no significant difficulties with this procedure because the Berkeley DB code itself is used in many applications other than MySQL. See section 1.4.1 Support Offered by MySQL AB.

For general information about Berkeley DB, please visit the Sleepcat Web site.

15.4.1 Operating Systems Supported by BDB

Currently, we know that the BDB storage engine works with the following operating systems:

  • Linux 2.x Intel
  • Sun Solaris (SPARC and x86)
  • FreeBSD 4.x/5.x (x86, sparc64)
  • IBM AIX 4.3.x
  • SCO OpenServer
  • SCO UnixWare 7.1.x

BDB does not work with the following operating systems:

  • Linux 2.x Alpha
  • Linux 2.x AMD64
  • Linux 2.x IA-64
  • Linux 2.x s390
  • Mac OS X

Note: The preceding lists are not complete. We will update them as we receive more information.

If you build MySQL from source with support for BDB tables, but the following error occurs when you start mysqld, it means BDB is not supported for your architecture:

bdb: architecture lacks fast mutexes: applications cannot be threaded
Can't init databases

In this case, you must rebuild MySQL without BDB table support or start the server with the --skip-bdb option.

15.4.2 Installing BDB

If you have downloaded a binary version of MySQL that includes support for Berkeley DB, simply follow the usual binary distribution installation instructions. (MySQL-Max distributions include BDB support.)

If you build MySQL from source, you can enable BDB support by running configure with the --with-berkeley-db option in addition to any other options that you normally use. Download a distribution for MySQL 3.23.34 or newer, change location into its top-level directory, and run this command:

shell> ./configure --with-berkeley-db [other-options]

For more information, see section 2.2.5 Installing MySQL on Other Unix-like Systems, section 5.1.2 The mysqld-max Extended MySQL Server, and See section 2.3 MySQL Installation Using a Source Distribution.

15.4.3 BDB Startup Options

The following options to mysqld can be used to change the behavior of the BDB storage engine:

--bdb-home=path
The base directory for BDB tables. This should be the same directory you use for --datadir.
--bdb-lock-detect=method
The BDB lock detection method. The option value should be DEFAULT, OLDEST, RANDOM, or YOUNGEST.
--bdb-logdir=path
The BDB log file directory.
--bdb-no-recover
Don't start Berkeley DB in recover mode.
--bdb-no-sync
Don't synchronously flush the BDB logs.
--bdb-shared-data
Start Berkeley DB in multi-process mode. (Don't use DB_PRIVATE when initializing Berkeley DB.)
--bdb-tmpdir=path
The BDB temporary file directory.
--skip-bdb
Disable the BDB storage engine.

The following system variable affects the behavior of BDB tables:

bdb_max_lock
Set the maximum number of locks possible.

See section 5.2.3 Server System Variables.

If you use --skip-bdb, MySQL will not initialize the Berkeley DB library and this will save a lot of memory. However, if you use this option, you cannot use BDB tables. If you try to create a BDB table, MySQL will create a MyISAM table instead.

Normally, you should start mysqld without the --bdb-no-recover option if you intend to use BDB tables. However, this may give you problems when you try to start mysqld if the BDB log files are corrupted. See section 2.4.2.3 Starting and Troubleshooting the MySQL Server.

With the bdb_max_lock variable, you can specify the maximum number of locks that can be active on a BDB table. (The default is 10,000.) You should increase this if errors such as the following occur when you perform long transactions or when mysqld has to examine many rows to execute a query:

bdb: Lock table is out of available locks
Got error 12 from ...

You may also want to change the binlog_cache_size and max_binlog_cache_size variables if you are using large multiple-statement transactions. See section 5.8.4 The Binary Log.

15.4.4 Characteristics of BDB Tables

Each BDB table is stored on disk in two files. The files have names that begin with the table name and have an extension to indicate the file type. A `.frm' file stores the table definition, and a `.db' file contains the table data and indexes.

To specify explicitly that you want a BDB table, indicate that with an ENGINE or TYPE table option:

CREATE TABLE t (i INT) ENGINE = BDB;
CREATE TABLE t (i INT) TYPE = BDB;

BerkeleyDB is a synonym for BDB in the ENGINE or TYPE option.

The BDB storage engine provides transactional tables. The way you use these tables depends on the autocommit mode:

  • If you are running with autocommit enabled (which is the default), changes to BDB tables are committed immediately and cannot be rolled back.
  • If you are running with autocommit disabled, changes do not become permanent until you execute a COMMIT statement. Instead of committing, you can execute ROLLBACK to forget the changes. You can start a transaction with the BEGIN WORK statement to suspend autocommit, or with SET AUTOCOMMIT=0 to disable autocommit explicitly.

See section 14.4.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax.

The BDB storage engine has the following characteristics:

  • BDB tables can have up to 31 indexes per table, 16 columns per index, and a maximum key size of 1024 bytes (500 bytes before MySQL 4.0).
  • MySQL requires a PRIMARY KEY in each BDB table so that each row can be uniquely identified. If you don't create one explicitly, MySQL creates and maintains a hidden PRIMARY KEY for you. The hidden key has a length of five bytes and is incremented for each insert attempt.
  • The PRIMARY KEY will be faster than any other index, because the PRIMARY KEY is stored together with the row data. The other indexes are stored as the key data + the PRIMARY KEY, so it's important to keep the PRIMARY KEY as short as possible to save disk space and get better speed. This behavior is similar to that of InnoDB, where shorter primary keys save space not only in the primary index but in secondary indexes as well.
  • If all columns you access in a BDB table are part of the same index or part of the primary key, then MySQL can execute the query without having to access the actual row. In a MyISAM table, this can be done only if the columns are part of the same index.
  • Sequential scanning is slower than for MyISAM tables because the data in BDB tables stored in B-trees and not in a separate datafile.
  • Key values are not prefix- or suffix-compressed like key values in MyISAM tables. In other words, key information takes a little more space in BDB tables compared to MyISAM tables.
  • There are often holes in the BDB table to allow you to insert new rows in the middle of the index tree. This makes BDB tables somewhat larger than MyISAM tables.
  • SELECT COUNT(*) FROM tbl_name is slow for BDB tables, because no row count is maintained in the table.
  • The optimizer needs to know the approximate the number of rows in the table. MySQL solves this by counting inserts and maintaining this in a separate segment in each BDB table. If you don't issue a lot of DELETE or ROLLBACK statements, this number should be accurate enough for the MySQL optimizer. However, MySQL only stores the number on close, so it may be incorrect if the server terminates unexpectedly. It should not be fatal even if this number is not 100% correct. You can update the row count by using ANALYZE TABLE or OPTIMIZE TABLE. See section 14.5.2.1 ANALYZE TABLE Syntax and section 14.5.2.5 OPTIMIZE TABLE Syntax.
  • Internal locking in BDB tables is done at the page level.
  • LOCK TABLES works on BDB tables as with other tables. If you don't use LOCK TABLE, MySQL issues an internal multiple-write lock on the table (a lock that doesn't block other writers), to ensure that the table will be properly locked if another thread issues a table lock.
  • To be able to roll back transactions, the BDB storage engine maintains log files. For maximum performance, you can use the --bdb-logdir option to place the BDB logs on a different disk than the one where your databases are located.
  • MySQL performs a checkpoint each time a new BDB log file is started, and removes any BDB log files that are not needed for current transactions. You can also use FLUSH LOGS at any time to checkpoint the Berkeley DB tables. For disaster recovery, you should use table backups plus MySQL's binary log. See section 5.6.1 Database Backups. Warning: If you delete old log files that are still in use, BDB will not be able to do recovery at all and you may lose data if something goes wrong.
  • Applications must always be prepared to handle cases where any change of a BDB table may cause an automatic rollback and any read may fail with a deadlock error.
  • If you get full disk with a BDB table, you will get an error (probably error 28) and the transaction should roll back. This contrasts with MyISAM and ISAM tables, for which mysqld will wait for enough free disk before continuing.

15.4.5 Things We Need to Fix for BDB

  • It's very slow to open many BDB tables at the same time. If you are going to use BDB tables, you should not have a very large table cache (for example, with a size larger than 256) and you should use the --no-auto-rehash option when you use the mysql client. We plan to partly fix this in 4.0.
  • SHOW TABLE STATUS doesn't yet provide that much information for BDB tables.
  • Optimize performance.
  • Change to not use page locks at all for table scanning operations.

15.4.6 Restrictions on BDB Tables

The following list indicates restrictions that you must observe when using BDB tables:

  • Each BDB table stores in the `.db' file the path to the file as it was created. This was done to be able to detect locks in a multi-user environment that supports symlinks. However, the consequence is that BDB table files cannot be moved from one database directory to another.
  • When making backups of BDB tables, you must either use mysqldump or else make a backup that includes the files for each BDB table (the `.frm' and `.db' files) as well as the BDB log files. The BDB storage engine stores unfinished transactions in its log files and requires them to be present when mysqld starts. The BDB logs are the files in the data directory with names of the form `log.XXXXXXXXXX' (ten digits).

15.4.7 Errors That May Occur When Using BDB Tables

  • If the following error occurs when you start mysqld, it means that the new BDB version doesn't support the old log file format:
    bdb:  Ignoring log file: .../log.XXXXXXXXXX:
    unsupported log version #
    
    In this case, you must delete all BDB logs from your data directory (the files with names that have the format `log.XXXXXXXXXX') and restart mysqld. We also recommend that you then use mysqldump --opt to dump your BDB tables, drop the tables, and restore them from the dump file.
  • If autocommit mode is disabled and you drop a BDB table that is referenced in another transaction, you may get error messages of the following form in your MySQL error log:
    001119 23:43:56  bdb:  Missing log fileid entry
    001119 23:43:56  bdb:  txn_abort: Log undo failed for LSN:
                           1 3644744: Invalid
    
    This is not fatal, but until the problem is fixed, we recommend that you not drop BDB tables except while autocommit mode is enabled. (The fix is not trivial.)

15.5 The ISAM Storage Engine

The original storage engine in MySQL was the ISAM engine. It was the only storage engine available until MySQL 3.23, when the improved MyISAM engine was introduced as the default. ISAM now is deprecated. As of MySQL 4.1, it's included in the source but not enabled in binary distributions. It will disappear in MySQL 5.0. Embedded MySQL server versions don't support ISAM tables.

Due to the deprecated status of ISAM, and because MyISAM is an improvement over ISAM, you are advised to convert any remaining ISAM tables to MySAM as soon as possible. To convert an ISAM table to a MyISAM table, use an ALTER TABLE statement:

mysql> ALTER TABLE tbl_name TYPE = MYISAM;

ISAM uses B-tree indexes.

Each ISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. A `.frm' file stores the table definition. The datafile has an `.ISD' extension. The index file has an `.ISM' extension,

You can check or repair ISAM tables with the isamchk utility. See section 5.6.2.7 Using myisamchk for Crash Recovery.

ISAM has the following properties:

  • Compressed and fixed-length keys
  • Fixed and dynamic record length
  • 16 indexes per table, with 16 key parts per key
  • Maximum key length 256 (default)
  • Data values are stored in machine format; this is fast, but machine/OS dependent.

Many of the properties of MyISAM tables are also true for ISAM tables. However, there are also many differences. The following list describes some of the ways that ISAM is distinct from MyISAM:

  • Not binary portable across OS/Platforms.
  • Can't handle tables larger than 4GB.
  • Only supports prefix compression on strings.
  • Smaller (more restrictive) key limits.
  • Dynamic tables become more fragmented.
  • Doesn't support MERGE tables.
  • Tables are checked and repaired with isamchk rather than with myisamchk.
  • Tables are compressed with pack_isam rather than with myisampack.
  • Cannot be used with the BACKUP TABLE or RESTORE TABLE backup-related statements.
  • Cannot be used with the CHECK TABLE, REPAIR TABLE, OPTIMIZE TABLE, or ANALYZE TABLE table maintenance statements.
  • No support for full-text searching or spatial datatypes.
  • No support for multiple character sets per table.
  • Indexes cannot be assigned to specific key caches.

For more information about the MyISAM storage engine, see section 15.1 The MyISAM Storage Engine.


Go to the first, previous, next, last section, table of contents.