8 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. 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. Note that the HEAP storage engine now is known as the MEMORY engine.
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 Chapter 9, “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;
If you try to use a storage engine 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 storage engines. (For example, in a replication setup, perhaps your master server supports transactional storage engines for increased safety, but the slave servers use only non-transactional storage engines for greater speed.)
This automatic substitution of the MyISAM table type when an unavailable type is specified 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 an .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. The server creates the .frm file above the storage engine level. Individual storage engines create any additional files required for the tables that they manage.
A database may contain tables of different types.
Transaction-safe tables (TSTs) have several advantages over non-transaction-safe tables (NTSTs):
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 non-transaction-safe tables, all changes that have taken place are permanent.)
Transaction-safe storage engines can provide better concurrency for tables that get 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 9.4, “InnoDB Configuration.”
Non-transaction-safe tables 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 transaction-safe and non-transaction-safe 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.
MyISAM is the default storage engine 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. An .frm file stores the table definition. The data file has an .MYD (MYData) extension. The index file has an .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 4.6.2.7, “Using myisamchk for Crash Recovery.” You can compress MyISAM tables with myisampack to take up much less space. See Section 7.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-sized 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 64 (32 before MySQL 4.1.2). This can be changed by recompiling. The maximum number of columns per index is 16.
The maximum key length is 1000 bytes (500 before 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 the high byte first to allow better index compression.
Index files are usually much smaller with MyISAM than with ISAM. This means that MyISAM normally will 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 is 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 data file, you can INSERT new rows into it at the same time that other threads are reading from the table. (These are known as 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 put the data file and index file on different directories to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options to CREATE TABLE.
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.)
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 (such as from another MySQL server or with myisamchk) when the table is in use. Doing so will lead to index corruption.
Using --external-locking will not help for tables that use --delay-key-write.
See Section 4.2.1, “mysqld Command-Line Options.”
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.
Don’t use the fast sort index method to create an index if the temporary file would become larger 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 4.2.3, “Server System Variables.”
Automatic recovery is activated if you start mysqld with the --myisam-recover option. In this case, when the server opens a MyISAM table, it checks whether the table is marked as crashed or whether 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 is 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 data file).
If the repair fails because of an error in the data file (for example, a duplicate-key error), the server tries again, this time re-creating the data file.
If the repair still fails, the server tries once more with the old repair option method (write row by row without sorting). This method should be able to repair any type of error and 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.
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 worst-case figure 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.
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 has no 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.
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 data file 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 MyISAM table 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.
Usually require more disk space than for dynamic-format tables.
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 indicates 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 columns 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 bitmap and not saved to disk. Non-empty strings are saved as a length byte plus the string contents.
Much less disk space usually is 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 OPTIMIZE TABLE or myisamchk -r from time to time to get better performance. Use myisamchk -ei to obtain table statistics.
More difficult than static-format tables 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 6 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.
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 version 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.
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.
Even though the MyISAM table format is very reliable (all changes to a table made by an 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 4.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.2, “What to Do If MySQL Keeps Crashing.”
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, because 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 data file with a new one, this is not signaled to the other servers.
In general, it is a bad idea to share a data directory among multiple servers. See Section 4.9, “Running Multiple MySQL Servers on the Same Machine,” for additional discussion.
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 7.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. An .frm file stores the table definition, and an .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 that you map to a MERGE table.
If you DROP the MERGE table, you are dropping only 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 create a MERGE table:
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 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 to a different collection of MyISAM tables, 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 tables, compress some of them with myisampack, and then create a MERGE table to use them as one.
Obtain 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 table to get the same kind of benefits.)
Do more efficient searches. If you know exactly what you are looking for, 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.
Do 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.
Instantly map many tables 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 data file 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 6.2.1, “EXPLAIN Syntax (Get Information About a SELECT),” for more information about eq_ref and ref.
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, or 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 a 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 non-unique 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 non-unique 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.
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 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, hash indexes are still the default, but 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 HASH (id))
ENGINE = MEMORY;
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;
General characteristics of B-tree and hash indexes are described in Section 6.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 non-TEMPORARY 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 don’t accidentally 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 4.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 the 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.
Sleepycat Software has provided MySQL with the Berkeley DB transactional storage engine. This storage engine typically is called BDB for short. Support for the BDB storage engine is included in MySQL source distributions 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, which 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 Sleepycat Web site, http://www.sleepycat.com/.
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)
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.
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 4.1.2, “The mysqld-max Extended MySQL Server,” and Section 2.3, “MySQL Installation Using a Source Distribution.”
The following options to mysqld can be used to change the behavior of the BDB storage engine:
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.
See Section 4.2.1, “mysqld Command-Line Options.”
The following system variable affects the behavior of BDB tables:
bdb_max_lock
The maximum number of locks you can have active on a BDB table.
See Section 4.2.3, “Server System Variables.”
If you use the --skip-bdb option, 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.4, “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 4.8.4, “The Binary Log.”
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. An .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.
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, 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 is stored in B-trees and not in a separate data file.
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 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 stores the number only 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.
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 4.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.
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 very much information for BDB tables.
Optimize performance.
Change to not use page locks at all for table scanning operations.
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).
If a column that allows NULL values has a unique index, only a single NULL value is allowed. This differs from other storage engines.
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.)
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 do not support ISAM tables by default.
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;
For more information about MyISAM, see Section 8.1, “The MyISAM Storage Engine.”
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. An .frm file stores the table definition. The data file has an .ISD extension. The index file has an .ISM extension.
ISAM uses B-tree indexes.
You can check or repair ISAM tables with the isamchk utility. See Section 4.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 bytes (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.
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 data types.
No support for multiple character sets per table.
Indexes cannot be assigned to specific key caches.