Table of Contents
InnoDBStartup Options and System Variables
14.2.5. Creating the
14.2.6. Creating and Using
14.2.7. Adding and Removing
InnoDBData and Log Files
14.2.8. Backing Up and Recovering an
14.2.9. Moving an
InnoDBDatabase to Another Machine
InnoDBTransaction Model and Locking
InnoDBPerformance Tuning Tips
- 14.2.12. Implementation of Multi-Versioning
InnoDBTable and Index Structures
InnoDBFile Space Management and Disk I/O
14.2.16. Restrictions on
HEAP) Storage Engine
BerkeleyDB) Storage Engine
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:
MyISAMmanages non-transactional tables. It provides high-speed storage and retrieval, as well as fulltext searching capabilities.
MyISAMis supported in all MySQL configurations, and is the default storage engine unless you have configured MySQL to use a different one by default.
MEMORYstorage engine provides in-memory tables. The
MERGEstorage engine allows a collection of identical
MyISAMtables to be handled as a single table. Like
MERGEstorage engines handle non-transactional tables, and both are also included in MySQL by default.
MEMORYstorage engine formerly was known as the
BDBstorage engines provide transaction-safe tables.
BDBis included in MySQL-Max binary distributions on those operating systems that support it.
InnoDBis also included by default in all MySQL 5.0 binary distributions. In source distributions, you can enable or disable either engine by configuring MySQL as you like.
EXAMPLEstorage engine is a “stub” engine that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them. The purpose of this engine is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. As such, it is primarily of interest to developers.
NDB Clusteris the storage engine used by MySQL Cluster to implement tables that are partitioned over many computers. It is available in MySQL-Max 5.0 binary distributions. This storage engine is currently supported on Linux, Solaris, and Mac OS X only. We intend to add support for this engine on other platforms, including Windows, in future MySQL releases.
ARCHIVEstorage engine is used for storing large amounts of data without indexes with a very small footprint.
CSVstorage engine stores data in text files using comma-separated values format.
BLACKHOLEstorage engine accepts but does not store data and retrievals always return an empty set.
FEDERATEDstorage engine was added in MySQL 5.0.3. This engine stores data in a remote database. Currently, it works with MySQL only, using the MySQL C Client API. In future releases, we intend to enable it to connect to other data sources using other drivers or client connection methods.
This chapter describes each of the MySQL storage engines except for
NDB Cluster, which is covered in
Chapter 15, MySQL Cluster.
When you create a new table, you can specify which storage engine to
use by adding an
TYPE table option to the
CREATE TABLE t (i INT) ENGINE = INNODB; CREATE TABLE t (i INT) TYPE = MEMORY;
The older term
TYPE is supported as a synonym for
ENGINE for backward compatibility, but
ENGINE is the preferred term and
TYPE is deprecated.
If you omit the
option, the default storage engine is used. Normally, this is
MyISAM, but you can change it by using the
--default-table-type server startup option, or by
default-table-type option in the
my.cnf configuration file.
You can set the default storage engine to be used during the current
session by setting the
SET storage_engine=MYISAM; SET table_type=BDB;
When MySQL is installed on Windows using the MySQL Configuration
InnoDB storage engine can be selected
as the default instead of
Section 22.214.171.124, “The Database Usage Dialog”.
To convert a table from one storage engine to another, use an
ALTER TABLE statement that indicates the new
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 using
the default storage engine, usually
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 default storage engine for unavailable engines can be confusing for new MySQL users. A warning is generated whenever a storage engine is automatically changed.
For new tables, MySQL always creates an
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
storage engine. The server creates the
file above the storage engine level. Individual storage engines
create any additional files required for the tables that they
A database may contain tables of different types. That is, tables need not all be created with the same storage engine.
Transaction-safe tables (TSTs) have several advantages over non-transaction-safe tables (NTSTs):
They are 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
COMMITstatement (if autocommit is disabled).
You can execute
ROLLBACKto ignore your changes (if autocommit is disabled).
If an update fails, all of your changes are reverted. (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.
You can combine transaction-safe and non-transaction-safe tables in
the same statements to get the best of both worlds. However,
although MySQL supports several transaction-safe storage engines,
for best results, you should not mix different storage engines
within a transaction with autocommit disabled. For example, if you
do this, changes to non-transaction-safe tables still are committed
immediately and cannot be rolled back. For information about this
and other problems that can occur in transactions that use mixed
storage engines, see Section 13.4.1, “
Non-transaction-safe tables have several advantages of their own, all of which occur because there is no transaction overhead:
Lower disk space requirements
Less memory required to perform updates
MyISAM is the default storage engine. It is
based on the older
ISAM code but has many
useful extensions. (Note that MySQL 5.0 does
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 format. The data
file has an
extension. The index file has an
To specify explicitly that you want a
table, indicate that with an
CREATE TABLE t (i INT) ENGINE = MYISAM;
The older term
TYPE is supported as a synonym
ENGINE for backward compatibility, but
ENGINE is the preferred term and
TYPE is deprecated.
Normally, it is unnecesary to use
MyISAM storage engine.
MyISAM is the default engine unless the default
has been changed. To ensure that
MyISAM is used
in situations where the default might have been changed, include
ENGINE option explicitly.
You can check or repair
MyISAM tables with the
mysqlcheck client or
myisamchk utility. You can also compress
MyISAM tables with
myisampack to take up much less space. See
Section 8.11, “mysqlcheck — A Table Maintenance and Repair Program”, Section 126.96.36.199, “Using myisamchk for Crash Recovery”,
and Section 8.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.
MyISAM tables have the following
All data values are stored with the low byte first. This makes the data machine and operating system independent. The only requirements for binary portability are that the machine uses two's-complement signed integers and IEEE floating-point format. These requirements are widely used among mainstream machines. Binary compatibility might not be applicable to embedded systems, which sometimes have peculiar processors.
There is no significant speed penalty for storing data low byte first; the bytes in a table row normally are unaligned and it takes little more processing 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.
All numeric key values are stored with the high byte first to allow better index compression.
Large files (up to 63-bit file length) are supported on filesystems and operating systems that support large files.
There is a limit of 232 (~4.295E+09) rows in a
MyISAMtable. You can increase this limitation if you build MySQL with the
--with-big-tablesoption then the row limitation is increased to (232)2 (1.844E+19) rows. See Section 2.9.2, “Typical configure Options”. Beginning with MySQL 5.0.4 all standard binaries are built with this option.
The maximum number of indexes per
MyISAMtable is 64. This can be changed by recompiling. Beginning with MySQL 5.0.18, you can configure the build by invoking configure with the
Nis the maximum number of indexes to permit per
Nmust be less thann or equal to 128. Before MySQL 5.0.18, you must change the source.
The maximum number of columns per index is 16.
The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.
When rows are inserted in sorted order (as when you are using an
AUTO_INCREMENTcolumn), 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_INCREMENTcolumn per table is supported.
MyISAMautomatically updates this column for
UPDATEoperations. This makes
AUTO_INCREMENTcolumns faster (at least 10%). Values at the top of the sequence are not reused after being deleted. (When an
AUTO_INCREMENTcolumn is defined as the last column of a multiple-column index, reuse of values deleted from the top of a sequence does occur.) The
AUTO_INCREMENTvalue can be reset with
ALTER TABLEor myisamchk.
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.
If a table has no free blocks in the middle of the data file, you can
INSERTnew 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. See Section 7.3.3, “Concurrent Inserts”.
You can put the data file and index file on different directories to get more speed with the
INDEX DIRECTORYtable options to
CREATE TABLE. See Section 13.1.5, “
TEXTcolumns can be indexed.
NULLvalues are allowed in indexed columns. This takes 0–1 bytes per key.
Each character column can have a different character set. See Chapter 10, Character Set Support.
There is a flag in the
MyISAMindex file that indicates whether the table was closed correctly. If mysqld is started with the
MyISAMtables are automatically checked when opened, and are repaired if the table wasn't closed properly.
myisamchk marks tables as checked if you run it with the
--update-stateoption. myisamchk --fast checks only those tables that don't have this mark.
myisamchk --analyze stores statistics for portions of keys, as well as for entire keys.
myisampack can pack
MyISAM also supports the following features:
Support for a true
VARCHARcolumn starts with a length stored in one or two bytes.
VARCHARcolumns may have fixed or dynamic row length.
The sum of the lengths of the
CHARcolumns in a table may be up to 64KB.
A hashed computed index can be used for
UNIQUE. This allows you to have
UNIQUEon any combination of columns in a table. (However, you cannot search on a
A forum dedicated to the
MyISAMstorage engine is available at http://forums.mysql.com/list.php?21.
The following options to mysqld can be used
to change the behavior of
MyISAM tables. For
additional information, see Section 5.2.1, “mysqld Command Options”.
Set the mode for automatic recovery of crashed
Don't flush key buffers between writes for any
Note: If you do this, you should not access
MyISAMtables from another program (such as from another MySQL server or with myisamchk) when the tables are in use. Doing so risks index corruption. Using
--external-lockingdoes not eliminate this risk.
The following system variables affect the behavior of
MyISAM tables. For additional information,
see Section 5.2.2, “Server System Variables”.
The size of the tree cache used in bulk insert optimization. Note: This is a limit per thread!
Used to help MySQL to decide when to use the slow but safe key cache index creation method. Note: This parameter was given in bytes before MySQL 5.0.6, when it was removed.
The maximum size of the temporary file that MySQL is allowed to use while re-creating a
ALTER TABLE, or
LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes.
Set the size of the buffer used when recovering tables.
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 external locking disabled. If either of these conditions is
true, the following happens:
The server checks the table 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
previously completed statementas 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
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
You should have a cron script that
automatically moves these files from the database directories to
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 is also prefix compressed. Space compression makes
the index file smaller than the worst-case figure if a 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.
MyISAM tables, you can also prefix
compress numbers by specifying the
PACK_KEYS=1 table option when you create the
table. Numbers are stored with the high byte first, so this
helps when you have many integer keys that have an identical
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
When you use
CREATE TABLE or
TABLE for a table that has no
TEXT columns, you can force the table
ROW_FORMAT table option. This causes
You can decompress tables by specifying
See Section 13.1.5, “
CREATE TABLE Syntax”, for information about
Static format is the default for
tables. It is used when the table contains no variable-length
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 due
to the ease with which rows in the data file can be found on
disk: To look up a row based on a row number in the index,
multiply the row number by the row length to calculate the row
position. Also, when scanning a table, it is very easy to read
a constant number of rows 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 rows except
the partially written one. Note that
table indexes can always be reconstructed based on the data
Static-format tables have these characteristics:
CHARcolumns are space-padded to the column width. This is also true for
DECIMALcolumns created before MySQL 5.0.3.
BINARYcolumns are space-padded to the column width before MySQL 5.0.15. As of 5.0.15,
BINARYcolumns are padded with
Easy to cache.
Easy to reconstruct after a crash, because rows are located in fixed positions.
Reorganization is unnecessary unless you delete a huge number of rows and want to return free disk space to the operating system. To do this, use
OPTIMIZE TABLEor myisamchk -r.
Usually require more disk space than dynamic-format tables.
Dynamic storage format is used if a
table contains any variable-length columns
TEXT), or if
the table was created with the
ROW_FORMAT=DYNAMIC table option.
Dynamic format is a little more complex than static format because each row has a header that indicates how long it is. A row can become fragmented (stored in non-contiguous pieces) when it is made longer as a result of an update.
You can use
OPTIMIZE TABLE or
myisamchk -r 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.
Dynamic-format tables have these characteristics:
All string columns are dynamic except those with a length less than four.
Each row 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
NULLvalues. 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 row uses only as much space as is required. However, if a row becomes larger, it is split into as many pieces as are required, resulting in row fragmentation. For example, if you update a row with information that extends the row length, the row becomes fragmented. In this case, you may have to run
OPTIMIZE TABLEor myisamchk -r from time to time to improve performance. Use myisamchk -ei to obtain table statistics.
More difficult than static-format tables to reconstruct after a crash, because rows may be fragmented into many pieces and links (fragments) may be missing.
The expected row length for dynamic-sized rows 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 row is linked whenever an update causes an enlargement of the row. Each new link is at least 20 bytes, so the next enlargement probably goes in the same link. If not, another link is created. You can find the number of links using myisamchk -ed. All links may be removed with
OPTIMIZE TABLEor myisamchk -r.
Compressed storage format is a read-only format that is generated with the myisampack tool. Compressed tables can be uncompressed with myisamchk.
Compressed tables have the following characteristics:
Compressed tables take very little disk space. This minimizes disk usage, which is helpful when using slow disks (such as CD-ROMs).
Each row is compressed separately, so there is very little access overhead. The header for a row takes up one to three bytes depending on the biggest row 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
BIGINTcolumn (eight bytes) can be stored as a
TINYINTcolumn (one byte) if all its values are in the range from
If a column has only a small set of possible values, the data type is converted to
A column may use any combination of the preceding compression types.
Can be used for fixed-length or dynamic-length rows.
While a compressed table is read-only, and you cannot
therefore update or add rows in the table, DDL (Data
Definition Language) operations are still valid. For
example, you may still use
DROP to drop
the table, and
TRUNCATE to empty the
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. The following discussion describes how this can happen and how to handle it.
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 any of the following events occur:
The mysqld process is killed in the middle of a write.
An unexpected computer shutdown occurs (for example, the computer is turned off).
You are using an external program (such as myisamchk) to modify a table that is being modified by the server at the same time.
A software bug in the MySQL or
Typical symptoms of 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 results.
You can check the health of a
CHECK TABLE statement, and repair
MyISAM table with
REPAIR TABLE. When
mysqld is not running, you can also check
or repair a table with the myisamchk
command. See Section 188.8.131.52, “
CHECK TABLE Syntax”,
Section 184.108.40.206, “
REPAIR TABLE Syntax”, and
Section 8.3, “myisamchk — MyISAM Table-Maintenance Utility”.
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
restarted mysqld message in the
error log. If there is such a message, it is likely that table
corruption is a result of the server dying. Otherwise,
corruption may have occurred during normal operation. This 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”, and
Section E.1.6, “Making a Test Case If You Experience Table Corruption”.
MyISAM index file
.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
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.
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 a
FLUSH TABLESoperation was performed or because there is no 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 become incorrect only under these conditions:
MyISAMtable is copied without first issuing
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.
Multiple mysqld servers are using the table and one server performed a
CHECK TABLEon 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 TABLEshould be avoided because when one server replaces the data file with a new one, this is not known to the other servers.
In general, it is a bad idea to share a data directory among multiple servers. See Section 5.13, “Running Multiple MySQL Servers on the Same Machine”, for additional discussion.