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

8 MySQL Table types

As of MySQL Version 3.23.6, you can choose between three basic table formats (ISAM, HEAP and MyISAM. Newer MySQL may support additional table type (BDB, GEMINI or InnoDB), depending on how you compile it. When you create a new table, you can tell MySQL which table type it should use for the table. MySQL will always create a .frm file to hold the table and column definitions. Depending on the table type, the index and data will be stored in other files.

The default table type in MySQL is MyISAM. If you are trying to use a table type that is not compiled-in or activated, MySQL will instead create a table of type MyISAM. This is a very useful feature when you want to copy tables between different SQL servers that supports different table types (like copying tables to a slave that is optimized for speed by not having transactional tables). This automatic table changing can however also be very confusing for new MySQL users. We plan to fix this by introducing warnings in MySQL 4.0 and giving a warning when a table type is automaticly changed.

ALTER TABLE 文を使用すれば、テーブルを違う形式に変更できます。 「7.8 ALTER TABLE構文」節参照.

Note that MySQL supports two different kinds of tables. Transaction-safe tables (BDB, InnoDB or GEMINI) and not transaction-safe tables (HEAP, ISAM, MERGE, and MyISAM).

Advantages of transaction-safe tables (TST):

Advantages of not transaction-safe tables (NTST):

You can combine TST and NTST tables in the same statements to get the best of both worlds.

8.1 MyISAM Tables

MyISAM は、MySQL Version 3.23 でのデフォルトのテーブル形式です. これは ISAM コードを基にし、多くの便利な拡張機能を持っています。

インデックスは .MYI (MYIndex) 拡張子のつくファイルに保存され、 データは、 .MYD (MYData) 拡張子のつくファイルに保存されます。 myisamchk ユーティリティを使用して、 MyISAM テーブルの 検査・修復が可能です。 「16.4 クラッシュからの修復のための myisamchk 使用」節参照. You can compress MyISAM tables with myisampack to take up much less space. 「15.12 MySQL の圧縮された読み込み専用テーブルジェネレータ ( myisampackpack_isam )」節参照.

The following is new in MyISAM:

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

Note that 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 when inserting data into a compressed index.

The following options to mysqld can be used to change the behavior of MyISAM tables. 「7.28.4 SHOW VARIABLES」節参照.

Option Meaning
--myisam-recover=# Automatic recover of crashed tables.
-O myisam_sort_buffer_size=# Buffer used when recovering tables.
--delay-key-write-for-all-tables Don't flush key buffers between writes for any MyISAM table
-O myisam_max_extra_sort_file_size=# Used to help MySQL to decide when to use the slow but safe key cache index create method. NOTE that this parameter is given in megabytes!
-O 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 that this paramter is given in megabytes!

The automatic recovery is activated if you start mysqld with --myisam-recover=#. 「4.16.4 mysqld コマンド行オプション」節参照. 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 with --skip-locking. If either of the above is true the following happens.

If the recover wouldn't be able to recover all rows from a previous completed statement and you didn't specify FORCE as an option to myisam-recover, then the automatic repair will abort with an error message in the error file:

Error: Couldn't repair table: test.g00pages

If you in this case had used the FORCE option you would instead have got a warning in the error file:

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

Note that if you run automatic recover with the BACKUP option, you should have a cron script that automatically moves file with names like `tablename-datetime.BAK' from the database directories to a backup media.

4.16.4 mysqld コマンド行オプション」節参照.

8.1.1 Space Needed for Keys

MySQL can support different index types, but the normal type is ISAM or MyISAM. These use a B-tree index, and 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 we don'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.

8.1.2 MyISAM Table Formats

MyISAM supports 3 different table types. Two of them are chosen automatically depending on the type of columns you are using. The third, compressed tables, can only be created with the myisampack tool. Static (Fixed-length) table characteristics

This is the default format. It's used when the table contains no VARCHAR, BLOB or TEXT columns.

このフォーマットは、最も単純、かつ、安全なフォーマットです。 これは, Disk 上に作られるテーブルの中で、最も速いフォーマットでもあります。 これはディスク上のデータを見つけやすいからです。 When looking up something with an index and static format it is very simple, just multiply the row number with the row length.

Also when scanning a table it is very easy to read a constant number of records with each disk read.

安全、というのは、次の様なことです。 もし仮に、静的(固定長) MyISAM ファイルに書き込み中に、 あなたのコンピュータがクラッシュした場合、 myisamchk は、それぞれのレコードの開始点と終了点を安易に見つけることが出 来ます。 So it can usually reclaim all records except the partially written one. MySQL では、常に、全てのインデックスが再構築できることに注意してください。 Dynamic Table Characteristics

This format is used if the table contains any VARCHAR, BLOB, or TEXT columns or if the table was created with ROW_FORMAT=dynamic.

この形式は少し複雑です。 なぜならそれぞれのレコードが、レコードがどのぐらいの 長さを持っているかを記録するヘッダーを持っているからです。 One record can also end up at more than one location when it is made longer at an update.

OPTIMIZE tablemyisamchk を使用して、テーブルの フラグメンテーションを修正することが可能です。 If you have static data that you access/change a lot in the same table as some VARCHAR or BLOB columns, it might be a good idea to move the dynamic columns to other tables just to avoid fragmentation. Compressed Table Characteristics

This is a read-only type that is generated with the optional これは読み込み専用の型で、オプションツールの myisampack で作成されます。 (pack_isam for ISAM tables).

8.1.3 MyISAM table problems.

Each MyISAM .MYI file has in the header a counter that can be used to check if a table has been closed properly.

If you get the following warning from CHECK TABLE or myisamchk:

# clients is using or hasn't closed the table properly

this means that this counter has come out of sync. This doesn't mean that the table is corrupted, but means that you should at least do a check on the table to verify that it's ok.

The counter works as follows:

In other words, the only ways this can go out of sync are:

8.2 MERGE Tables

MERGE tables are new in MySQL Version 3.23.25. The code is still in gamma, but should be resonable stable.

A MERGE table is a collection of identical MyISAM tables that can be used as one. You can only SELECT, DELETE, and UPDATE from the collection of tables. If you DROP the MERGE table, you are only dropping the MERGE specification.

Note that DELETE FROM merge_table used without a WHERE will only clear the mapping for the table, not delete everything in the mapped tables. (We plan to fix this in 4.0).

With identical tables we mean that all tables are created with identical column and key information. You can't put a MERGE over tables where the columns are packed differently or doesn't have exactly the same columns. Some of the tables can however be compressed with myisampack. 「15.12 MySQL の圧縮された読み込み専用テーブルジェネレータ ( myisampackpack_isam )」節参照.

When you create a MERGE table, you will get a .frm table definition file and a .MRG table list file. The .MRG just contains a list of the index files (.MYI files) that should be used as one.

For the moment you need to have SELECT, UPDATE, and DELETE privileges on the tables you map to a MERGE table.

MERGE tables can help you solve the following problems:

The disadvantages with MERGE tables are:

The following example shows you how to use MERGE tables:

INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
CREATE TABLE total (a INT NOT NULL, message CHAR(20), KEY(a)) TYPE=MERGE UNION=(t1,t2);

Note that we didn't create a UNIQUE or PRIMARY KEY in the total table as the key isn't going to be unique in the total table.

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.MYI t2.MYI > total.MRG
shell> mysqladmin flush-tables

Now you can do things like:

mysql> select * from total;
| a | message |
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |

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

8.3 ISAM Tables

You can also use the deprecated ISAM table type. This will disappear rather soon because MyISAM is a better implementation of the same thing. ISAM uses a B-tree index. The index is stored in a file with the .ISM extension, and the data is stored in a file with the .ISD extension. You can check/repair ISAM tables with the isamchk utility. 「16.4 クラッシュからの修復のための myisamchk 使用」節参照.

ISAM has the following features/properties:

Most of the things true for MyISAM tables are also true for ISAM tables. 「8.1 MyISAM Tables」節参照. The major differences compared to MyISAM tables are:

8.4 HEAP Tables

HEAP tables use a hashed index and are stored in memory. This makes them very fast, but if MySQL crashes you will lose all data stored in them. HEAP is very useful as temporary tables!

MySQL 内部 HEAP テーブルは、100% ダイナッミック・ハッシングを 使用しています(オーバーフローエリア無しに)。 There is no extra space needed for free lists. HEAP tables also don't have problems with delete + inserts, which normally is common with hashed tables:

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

Here are some things you should consider when you use HEAP tables:

The memory needed for one row in a HEAP table is:

SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2)
+ ALIGN(length_of_row+1, sizeof(char*))

sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.

8.5 BDB or Berkeley_DB Tables

8.5.1 Overview of BDB Tables

Support for BDB tables is included in the MySQL source distribution starting from Version 3.23.34 and is activated in the MySQL-Max binary.

BerkeleyDB, available at http://www.sleepycat.com/ has provided MySQL with a transactional table handler. By using BerkeleyDB tables, your tables may have a greater chance of surviving crashes, and also provides COMMIT and ROLLBACK 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.

When it comes to supporting BDB tables, we are committed to help our users to locate the problem and help creating a reproducable test case for any problems involving BDB tables. 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 table handlers. However, as the BerkeleyDB code itself has been used by many other applications than MySQL, we don't envision any big problems with this. 「3.5.6 Support for other table handlers」節参照.

8.5.2 Installing BDB

If you have downloaded a binary version of MySQL that includes support for BerkeleyDB, simply follow the instructions for installing a binary version of MySQL. 「4.6 MySQL バイナリディストリビューションのインストール」節参照. 「15.2 mysqld-max, An extended mysqld server」節参照.

To compile MySQL with Berkeley DB support, download MySQL Version 3.23.34 or newer and configure MySQL with the --with-berkeley-db option. 「4.7 MySQL ソースディストリビューションのインストール」節参照.

cd /path/to/source/of/mysql-3.23.34
./configure --with-berkeley-db

Please refer to the manual provided with the BDB distribution for more updated information.

Even though Berkeley DB is in itself very tested and reliable, the MySQL interface is still considered beta quality. We are actively improving and optimizing it to get it stable very soon.

8.5.3 BDB startup options

If you are running with AUTOCOMMIT=0 then your changes in BDB tables will not be updated until you execute COMMIT. Instead of commit you can execute ROLLBACK to forget your changes. 「7.31 BEGIN/COMMIT/ROLLBACK 構文」節参照.

If you are running with AUTOCOMMIT=1 (the default), your changes will be committed immediately. You can start an extended transaction with the BEGIN WORK SQL command, after which your changes will not be committed until you execute COMMIT (or decide to ROLLBACK the changes).

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

Option Meaning
--bdb-home=directory Base directory for BDB tables. This should be the same directory you use for --datadir.
--bdb-lock-detect=# Berkeley lock detect. One of (DEFAULT, OLDEST, RANDOM, or YOUNGEST).
--bdb-logdir=directory Berkeley DB log file directory.
--bdb-no-sync Don't synchronously flush logs.
--bdb-no-recover Don't start Berkeley DB in recover mode.
--bdb-shared-data Start Berkeley DB in multi-process mode (Don't use DB_PRIVATE when initializing Berkeley DB)
--bdb-tmpdir=directory Berkeley DB tempfile name.
--skip-bdb Don't use berkeley db.
-O bdb_max_lock=1000 Set the maximum number of locks possible. 「7.28.4 SHOW VARIABLES」節参照.

If you use --skip-bdb, MySQL will not initialize the Berkeley DB library and this will save a lot of memory. Of course, you cannot use BDB tables if you are using this option.

Normally you should start mysqld without --bdb-no-recover if you intend to use BDB tables. This may, however, give you problems when you try to start mysqld if the BDB log files are corrupted. 「4.16.2 MySQL サーバー起動時の問題」節参照.

With bdb_max_lock you can specify the maximum number of locks (10000 by default) you can have active on a BDB table. You should increase this if you get errors of type bdb: Lock table is out of available locks or Got error 12 from ... when you have do long transactions or when mysqld has to examine a lot of rows to calculate the query.

You may also want to change binlog_cache_size and max_binlog_cache_size if you are using big multi-line transactions. 「7.31 BEGIN/COMMIT/ROLLBACK 構文」節参照.

8.5.4 Some characteristic of BDB tables:

8.5.5 Some things we need to fix for BDB in the near future:

8.5.6 Operating systems supported by BDB

If you after having built MySQL with support for BDB tables get the following error in the log file when you start mysqld:

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

This means that BDB tables are not supported for your architecture. In this case you have to rebuild MySQL without BDB table support.

NOTE: The following list is not complete; We will update this as we get more information about this.

Currently we know that BDB tables works with the following operating system.

It doesn't work with the following operating systems:

8.5.7 Errors You May Get When Using BDB Tables

8.6 GEMINI Tables

8.6.1 Overview of GEMINI tables

The GEMINI table type is developed and supported by NuSphere Corporation (http://www.nusphere.com). It features row-level locking, transaction support (COMMIT and ROLLBACK), and automatic crash recovery.

GEMINI tables will be included in some future MySQL 3.23.X source distribution.

8.6.2 GEMINI startup options

If you are running with AUTOCOMMIT=0 then your changes in GEMINI tables will not be updated until you execute COMMIT. Instead of commit you can execute ROLLBACK to forget your changes. 「7.31 BEGIN/COMMIT/ROLLBACK 構文」節参照.

If you are running with AUTOCOMMIT=1 (the default), your changes will be committed immediately. You can start an extended transaction with the BEGIN WORK SQL command, after which your changes will not be committed until you execute COMMIT (or decide to ROLLBACK the changes).

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

Option Meaning
--gemini-full-recovery Default.
--gemini-no-recovery Turn off recovery logging. Not recommended.
--gemini-lazy-commit Relaxes the flush log at commit rule.
--gemini-unbuffered-io All database writes bypass OS cache.
--skip-gemini Don't use Gemini.
--O gemini_db_buffers=# Number of database buffers in database cache.
--O gemini_connection_limit=# Maximum number of connections to Gemini.
--O gemini_spin_retries=# Spin lock retries (optimization).
--O gemini_io_threads=# Number of background I/O threads.
--O gemini_lock_table_size=# Set the maximum number of locks. Default 4096.

If you use --skip-gemini, MySQL will not initialize the Gemini table handler, saving memory; you cannot use Gemini tables if you use --skip-gemini.

8.6.3 Features of GEMINI tables:

8.6.4 Current limitations of GEMINI tables:

NuSphere is working on removing these limitations.

8.7 InnoDB Tables

8.7.1 InnoDB tables overview

InnoDB tables are included in the MySQL source distribution starting from 3.23.34a and are activated in the MySQL -max binary.

If you have downloaded a binary version of MySQL that includes support for InnoDB (mysqld-max), simply follow the instructions for installing a binary version of MySQL. 「4.6 MySQL バイナリディストリビューションのインストール」節参照. 「15.2 mysqld-max, An extended mysqld server」節参照.

To compile MySQL with InnoDB support, download MySQL-3.23.37 or newer and configure MySQL with the --with-innodb option. 「4.7 MySQL ソースディストリビューションのインストール」節参照.

cd /path/to/source/of/mysql-3.23.37
./configure --with-innodb

InnoDB provides MySQL with a transaction safe table handler with commit, rollback, and crash recovery capabilities. InnoDB does locking on row level, and also provides an Oracle-style consistent non-locking read in SELECTS, which increases transaction concurrency. There is not need for lock escalation in InnoDB, because row level locks in InnoDB fit in very small space.

Technically, InnoDB is a database backend placed under MySQL. InnoDB has its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files. This is different from, for example, MyISAM tables where each table is stored as a separate file.

InnoDB is distributed under the GNU GPL License Version 2 (of June 1991). In the source distribution of MySQL, InnoDB appears as a subdirectory.

8.7.2 InnoDB startup options

Beginning from MySQL-3.23.37 the prefix of the options is changed from innobase_... to innodb_....

To use InnoDB tables you MUST specify configuration parameters in the MySQL configuration file in the [mysqld] section of the configuration file `my.cnf'. 「4.16.5 オプションファイル ( my.cnf )」節参照.

The only required parameter to use InnoDB is innodb_data_file_path, but you should set others if you want to get a better performance.

Suppose you have a Windows NT machine with 128 MB RAM and a single 10 GB hard disk. Below is an example of possible configuration parameters in `my.cnf' for InnoDB:

innodb_data_file_path = ibdata1:2000M;ibdata2:2000M
innodb_data_home_dir = c:\ibdata
set-variable = innodb_mirrored_log_groups=1
innodb_log_group_home_dir = c:\iblogs
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=30M
set-variable = innodb_log_buffer_size=8M
innodb_log_arch_dir = c:\iblogs
set-variable = innodb_buffer_pool_size=80M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50

Suppose you have a Linux machine with 512 MB RAM and three 20 GB hard disks (at directory paths `/', `/dr2' and `/dr3'). Below is an example of possible configuration parameters in `my.cnf' for InnoDB:

innodb_data_file_path = ibdata/ibdata1:2000M;dr2/ibdata/ibdata2:2000M
innodb_data_home_dir = /
set-variable = innodb_mirrored_log_groups=1
innodb_log_group_home_dir = /dr3
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=50M
set-variable = innodb_log_buffer_size=8M
innodb_log_arch_dir = /dr3/iblogs
set-variable = innodb_buffer_pool_size=400M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50

Note that we have placed the two data files on different disks. The reason for the name innodb_data_file_path is that you can also specify paths to your data files, and innodb_data_home_dir is just textually catenated before your data file paths, adding a possible slash or backslash in between. InnoDB will fill the tablespace formed by the data files from bottom up. In some cases it will improve the performance of the database if all data is not placed on the same physical disk. Putting log files on a different disk from data is very often beneficial for performance.

The meanings of the configuration parameters are the following:

innodb_data_home_dir The common part of the directory path for all innobase data files.
innodb_data_file_path Paths to individual data files and their sizes. The full directory path to each data file is acquired by concatenating innodb_data_home_dir to the paths specified here. The file sizes are specified in megabytes, hence the 'M' after the size specification above. Do not set a file size bigger than 4000M, and on most operating systems not bigger than 2000M. InnoDB also understands the abbreviation 'G', 1G meaning 1024M.
innodb_mirrored_log_groups Number of identical copies of log groups we keep for the database. Currently this should be set to 1.
innodb_log_group_home_dir Directory path to InnoDB log files.
innodb_log_files_in_group Number of log files in the log group. InnoDB writes to the files in a circular fashion. Value 3 is recommended here.
innodb_log_file_size Size of each log file in a log group in megabytes. Sensible values range from 1M to the size of the buffer pool specified below. The bigger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk i/o. But bigger log files also mean that recovery will be slower in case of a crash. File size restriction as for a data file.
innodb_log_buffer_size The size of the buffer which InnoDB uses to write log to the log files on disk. Sensible values range from 1M to half the combined size of log files. A big log buffer allows large transactions to run without a need to write the log to disk until the transaction commit. Thus, if you have big transactions, making the log buffer big will save disk i/o.
innodb_flush_log_at_trx_commit Normally this is set to 1, meaning that at a transaction commit the log is flushed to disk, and the modifications made by the transaction become permanent, and survive a database crash. If you are willing to compromise this safety, and you are running small transactions, you may set this to 0 to reduce disk i/o to the logs.
innodb_log_arch_dir The directory where fully written log files would be archived if we used log archiving. The value of this parameter should currently be set the same as innodb_log_group_home_dir.
innodb_log_archive This value should currently be set to 0. As recovery from a backup is done by MySQL using its own log files, there is currently no need to archive InnoDB log files.
innodb_buffer_pool_size The size of the memory buffer InnoDB uses to cache data and indexes of its tables. The bigger you set this the less disk i/o is needed to access data in tables. On a dedicated database server you may set this parameter up to 90 % of the machine physical memory size. Do not set it too large, though, because competition of the physical memory may cause paging in the operating system.
innodb_additional_mem_pool_size Size of a memory pool InnoDB uses to store data dictionary information and other internal data structures. A sensible value for this might be 2M, but the more tables you have in your application the more you will need to allocate here. If InnoDB runs out of memory in this pool, it will start to allocate memory from the operating system, and write warning messages to the MySQL error log.
innodb_file_io_threads Number of file i/o threads in InnoDB. Normally, this should be 4, but on Windows NT disk i/o may benefit from a larger number.
innodb_lock_wait_timeout Timeout in seconds an InnoDB transaction may wait for a lock before being rolled back. InnoDB automatically detects transaction deadlocks in its own lock table and rolls back the transaction. If you use LOCK TABLES command, or other transaction safe table handlers than InnoDB in the same transaction, then a deadlock may arise which InnoDB cannot notice. In cases like this the timeout is useful to resolve the situation.

8.7.3 Creating an InnoDB database

Suppose you have installed MySQL and have edited `my.cnf' so that it contains the necessary InnoDB configuration parameters. Before starting MySQL you should check that the directories you have specified for InnoDB data files and log files exist and that you have access rights to those directories. InnoDB cannot create directories, only files. Check also you have enough disk space for the data and log files.

When you now start MySQL, InnoDB will start creating your data files and log files. InnoDB will print something like the following:

~/mysqlm/sql > mysqld
InnoDB: The first specified data file /home/heikki/data/ibdata1 did not exist:
InnoDB: a new database to be created!
InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728
InnoDB: Database physically writes the file full: wait...
InnoDB: Data file /home/heikki/data/ibdata2 did not exist: new to be created
InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000
InnoDB: Database physically writes the file full: wait...
InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be c
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880
InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be c
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880
InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist: new to be c
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to 5242880
InnoDB: Started
mysqld: ready for connections

A new InnoDB database has now been created. You can connect to the MySQL server with the usual MySQL client programs like mysql. When you shut down the MySQL server with `mysqladmin shutdown', InnoDB output will be like the following:

010321 18:33:34  mysqld: Normal shutdown
010321 18:33:34  mysqld: Shutdown Complete
InnoDB: Starting shutdown...
InnoDB: Shutdown completed

You can now look at the data files and logs directories and you will see the files created. The log directory will also contain a small file named `ib_arch_log_0000000000'. That file resulted from the database creation, after which InnoDB switched off log archiving. When MySQL is again started, the output will be like the following:

~/mysqlm/sql > mysqld
InnoDB: Started
mysqld: ready for connections If something goes wrong in database creation

If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all data files, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding `.frm' files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again.

8.7.4 Creating InnoDB tables

Suppose you have started the MySQL client with the command mysql test. To create a table in the InnoDB format you must specify TYPE = InnoDB in the table creation SQL command:


This SQL command will create a table and an index on column A into the InnoDB tablespace consisting of the data files you specified in `my.cnf'. In addition MySQL will create a file `CUSTOMER.frm' to the MySQL database directory `test'. Internally, InnoDB will add to its own data dictionary an entry for table 'test/CUSTOMER'. Thus you can create a table of the same name CUSTOMER in another database of MySQL, and the table names will not collide inside InnoDB.

You can query the amount of free space in the InnoDB tablespace by issuing the table status command of MySQL for any table you have created with TYPE = InnoDB. Then the amount of free space in the tablespace appears in the table comment section in the output of SHOW. An example:


Note that the statistics SHOW gives about InnoDB tables are only approximate: they are used in SQL optimization. Table and index reserved sizes in bytes are accurate, though.

NOTE: DROP DATABASE does not currently work for InnoDB tables! You must drop the tables individually. Also take care not to delete or add `.frm' files to your InnoDB database manually: use CREATE TABLE and DROP TABLE commands. InnoDB has its own internal data dictionary, and you will get problems if the MySQL `.frm' files are out of 'sync' with the InnoDB internal data dictionary.

8.7.5 Adding and removing InnoDB data and log files

You cannot increase the size of an InnoDB data file. To add more into your tablespace you have to add a new data file. To do this you have to shut down your MySQL database, edit the `my.cnf' file, adding a new file to innodb_data_file_path, and then start MySQL again.

Currently you cannot remove a data file from InnoDB. To decrease the size of your database you have to use mysqldump to dump all your tables, create a new database, and import your tables to the new database.

If you want to change the number or the size of your InnoDB log files, you have to shut down MySQL and make sure that it shuts down without errors. Then copy the old log files into a safe place just in case something went wrong in the shutdown and you will need them to recover the database. Delete then the old log files from the log file directory, edit `my.cnf', and start MySQL again. InnoDB will tell you at the startup that it is creating new log files.

8.7.6 Backing up and recovering an InnoDB database

The key to safe database management is taking regular backups. To take a 'binary' backup of your database you have to do the following:

There is currently no on-line or incremental backup tool available for InnoDB, though they are in the TODO list.

In addition to taking the binary backups described above, you should also regularly take dumps of your tables with `mysqldump'. The reason to this is that a binary file may be corrupted without you noticing it. Dumped tables are stored into text files which are human-readable and much simpler than database binary files. Seeing table corruption from dumped files is easier, and since their format is simpler, the chance for serious data corruption in them is smaller.

A good idea is to take the dumps at the same time you take a binary backup of your database. You have to shut out all clients from your database to get a consistent snapshot of all your tables into your dumps. Then you can take the binary backup, and you will then have a consistent snapshot of your database in two formats.

To be able to recover your InnoDB database to the present from the binary backup described above, you have to run your MySQL database with the general logging and log archiving of MySQL switched on. Here by the general logging we mean the logging mechanism of the MySQL server which is independent of InnoDB logs.

To recover from a crash of your MySQL server process, the only thing you have to do is to restart it. InnoDB will automatically check the logs and perform a roll-forward of the database to the present. InnoDB will automatically roll back uncommitted transactions which were present at the time of the crash. During recovery, InnoDB will print out something like the following:

~/mysqlm/sql > mysqld
InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections

If your database gets corrupted or your disk fails, you have to do the recovery from a backup. In the case of corruption, you should first find a backup which is not corrupted. From a backup do the recovery from the general log files of MySQL according to instructions in the MySQL manual. Checkpoints

InnoDB implements a checkpoint mechanism called a fuzzy checkpoint. InnoDB will flush modified database pages from the buffer pool in small batches, there is no need to flush the buffer pool in one single batch, which would in practice stop processing of user SQL statements for a while.

In crash recovery InnoDB looks for a checkpoint label written to the log files. It knows that all modifications to the database before the label are already present on the disk image of the database. Then InnoDB scans the log files forward from the place of the checkpoint applying the logged modifications to the database.

InnoDB writes to the log files in a circular fashion. All committed modifications which make the database pages in the buffer pool different from the images on disk must be available in the log files in case InnoDB has to do a recovery. This means that when InnoDB starts to reuse a log file in the circular fashion, it has to make sure that the database page images on disk already contain the modifications logged in the log file InnoDB is going to reuse. In other words, InnoDB has to make a checkpoint and often this involves flushing of modified database pages to disk.

The above explains why making your log files very big may save disk i/o in checkpointing. It can make sense to set the total size of the log files as big as the buffer pool or even bigger. The drawback in big log files is that crash recovery can last longer because there will be more log to apply to the database.

8.7.7 Moving an InnoDB database to another machine

InnoDB data and log files are binary-compatible on all platforms if the floating point number format on the machines is the same. You can move an InnoDB database simply by copying all the relevant files, which we already listed in the previous section on backing up a database. If the floating point formats on the machines are different but you have not used FLOAT or DOUBLE data types in your tables then the procedure is the same: just copy the relevant files. If the formats are different and your tables contain floating point data, you have to use `mysqldump' and `mysqlimport' to move those tables.

A performance tip is to switch off the auto commit when you import data into your database, assuming your tablespace has enough space for the big rollback segment the big import transaction will generate. Do the commit only after importing a whole table or a segment of a table.

8.7.8 InnoDB transaction model

In the InnoDB transaction model the goal has been to combine the best sides of a multiversioning database to traditional two-phase locking. InnoDB does locking on row level and runs queries by default as non-locking consistent reads, in the style of Oracle. The lock table in InnoDB is stored so space-efficiently that lock escalation is not needed: typically several users are allowed to lock every row in the database, or any random subset of the rows, without InnoDB running out of memory.

In InnoDB all user activity happens inside transactions. If the auto commit mode is used in MySQL, then each SQL statement will form a single transaction. If the auto commit mode is switched off, then we can think that a user always has a transaction open. If he issues the SQL COMMIT or ROLLBACK statement, that ends the current transaction, and a new starts. Both statements will release all InnoDB locks that were set during the current transaction. A COMMIT means that the changes made in the current transaction are made permanent and become visible to other users. A ROLLBACK on the other hand cancels all modifications made by the current transaction. Consistent read

A consistent read means that InnoDB uses its multiversioning to present to a query a snapshot of the database at a point in time. The query will see the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query will see the changes made by the transaction itself which issues the query.

When a transaction issues its first consistent read, InnoDB assigns the snapshot, or the point of time, which all consistent reads in the same transaction will use. In the snapshot are all transactions that committed before assigning the snapshot. Thus the consistent reads within the same transaction will also be consistent with respect to each other. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

Consistent read is the default mode in which InnoDB processes SELECT statements. A consistent read does not set any locks on the tables it accesses, and therefore other users are free to modify those tables at the same time a consistent read is being performed on the table. Locking reads

A consistent read is not convenient in some circumstances. Suppose you want to add a new row into your table CHILD, and make sure that the child already has a parent in table PARENT.

Suppose you use a consistent read to read the table PARENT and indeed see the parent of the child in the table. Can you now safely add the child row to table CHILD? No, because it may happen that meanwhile some other user has deleted the parent row from the table PARENT, and you are not aware of that.

The solution is to perform the SELECT in a locking mode, LOCK IN SHARE MODE.


Performing a read in share mode means that we read the latest available data, and set a shared mode lock on the rows we read. If the latest data belongs to a yet uncommitted transaction of another user, we will wait until that transaction commits. A shared mode lock prevents others from updating or deleting the row we have read. After we see that the above query returns the parent 'Jones', we can safely add his child to table CHILD, and commit our transaction. This example shows how to implement referential integrity in your application code.

Let us look at another example: we have an integer counter field in a table CHILD_CODES which we use to assign a unique identifier to each child we add to table CHILD. Obviously, using a consistent read or a shared mode read to read the present value of the counter is not a good idea, since then two users of the database may see the same value for the counter, and we will get a duplicate key error when we add the two children with the same identifier to the table.

In this case there are two good ways to implement the reading and incrementing of the counter: (1) update the counter first by incrementing it by 1 and only after that read it, or (2) read the counter first with a lock mode FOR UPDATE, and increment after that:


A SELECT ... FOR UPDATE will read the latest available data setting exclusive locks on each row it reads. Thus it sets the same locks a searched SQL UPDATE would set on the rows. Next-key locking: avoiding the 'phantom problem'

In row level locking InnoDB uses an algorithm called next-key locking. InnoDB does the row level locking so that when it searches or scans an index of a table, it sets shared or exclusive locks on the index records in encounters. Thus the row level locks are more precisely called index record locks.

The locks InnoDB sets on index records also affect the 'gap' before that index record. If a user has a shared or exclusive lock on record R in an index, then another user cannot insert a new index record immediately before R in the index order. This locking of gaps is done to prevent the so-called phantom problem. Suppose I want to read and lock all children with identifier bigger than 100 from table CHILD, and update some field in the selected rows.


Suppose there is an index on table CHILD on column ID. Our query will scan that index starting from the first record where ID is bigger than 100. Now, if the locks set on the index records would not lock out inserts made in the gaps, a new child might meanwhile be inserted to the table. If now I in my transaction execute


again, I will see a new child in the result set the query returns. This is against the isolation principle of transactions: a transaction should be able to run so that the data it has read does not change during the transaction. If we regard a set of rows as a data item, then the new 'phantom' child would break this isolation principle.

When InnoDB scans an index it can also lock the gap after the last record in the index. Just that happens in the previous example: the locks set by InnoDB will prevent any insert to the table where ID would be bigger than 100.

You can use the next-key locking to implement a uniqueness check in your application: if you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read will prevent anyone meanwhile inserting a duplicate for your row. Thus the next-key locking allows you to 'lock' the non-existence of something in your table. Locks set by different SQL statements in InnoDB Deadlock detection and rollback

InnoDB automatically detects a deadlock of transactions and rolls back the transaction whose lock request was the last one to build a deadlock, that is, a cycle in the waits-for graph of transactions. InnoDB cannot detect deadlocks where a lock set by a MySQL LOCK TABLES statement is involved, or if a lock set in another table handler than InnoDB is involved. You have to resolve these situations using innodb_lock_wait_timeout set in `my.cnf'.

When InnoDB performs a complete rollback of a transaction, all the locks of the transaction are released. However, if just a single SQL statement is rolled back as a result of an error, some of the locks set by the SQL statement may be preserved. This is because InnoDB stores row locks in a format where it cannot afterwards know which was set by which SQL statement.

8.7.9 Implementation of multiversioning

Since InnoDB is a multiversioned database, it must keep information of old versions of rows in the tablespace. This information is stored in a data structure we call a rollback segment after an analogous data structure in Oracle.

InnoDB internally adds two fields to each row stored in the database. A 6-byte field tells the transaction identifier for the last transaction which inserted or updated the row. Also a deletion is internally treated as an update where a special bit in the row is set to mark it as deleted. Each row also contains a 7-byte field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, then the undo log record contains the information necessary to rebuild the content of the row before it was updated.

InnoDB uses the information in the rollback segment to perform the undo operations needed in a transaction rollback. It also uses the information to build earlier versions of a row for a consistent read.

Undo logs in the rollback segment are divided into insert and update undo logs. Insert undo logs are only needed in transaction rollback and can be discarded as soon as the transaction commits. Update undo logs are used also in consistent reads, and they can be discarded only after there is no transaction present for which InnoDB has assigned a snapshot that in a consistent read could need the information in the update undo log to build an earlier version of a database row.

You must remember to commit your transactions regularly. Otherwise InnoDB cannot discard data from the update undo logs, and the rollback segment may grow too big, filling up your tablespace.

The physical size of an undo log record in the rollback segment is typically smaller than the corresponding inserted or updated row. You can use this information to calculate the space need for your rollback segment.

In our multiversioning scheme a row is not physically removed from the database immediately when you delete it with an SQL statement. Only when InnoDB can discard the update undo log record written for the deletion, it can also physically remove the corresponding row and its index records from the database. This removal operation is called a purge, and it is quite fast, usually taking the same order of time as the SQL statement which did the deletion.

8.7.10 Table and index structures

Every InnoDB table has a special index called the clustered index where the data of the rows is stored. If you define a PRIMARY KEY on your table, then the index of the primary key will be the clustered index.

If you do not define a primary key for your table, InnoDB will internally generate a clustered index where the rows are ordered by the row id InnoDB assigns to the rows in such a table. The row id is a 6-byte field which monotonically increases as new rows are inserted. Thus the rows ordered by the row id will be physically in the insertion order.

Accessing a row through the clustered index is fast, because the row data will be on the same page where the index search leads us. In many databases the data is traditionally stored on a different page from the index record. If a table is large, the clustered index architecture often saves a disk i/o when compared to the traditional solution.

The records in non-clustered indexes (we also call them secondary indexes), in InnoDB contain the primary key value for the row. InnoDB uses this primary key value to search for the row from the clustered index. Note that if the primary key is long, the secondary indexes will use more space. Physical structure of an index

All indexes in InnoDB are B-trees where the index records are stored in the leaf pages of the tree. The default size of an index page is 16 kB. When new records are inserted, InnoDB tries to leave 1 / 16 of the page free for future insertions and updates of the index records.

If index records are inserted in a sequential (ascending or descending) order, the resulting index pages will be about 15/16 full. If records are inserted in a random order, then the pages will be 1/2 - 15/16 full. If the fillfactor of an index page drops below 1/4, InnoDB will try to contract the index tree to free the page. Insert buffering

It is a common situation in a database application that the primary key is a unique identifier and new rows are inserted in the ascending order of the primary key. Thus the insertions to the clustered index do not require random reads from a disk.

On the other hand, secondary indexes are usually non-unique and insertions happen in a relatively random order into secondary indexes. This would cause a lot of random disk i/o's without a special mechanism used in InnoDB.

If an index record should be inserted to a non-unique secondary index, InnoDB checks if the secondary index page is already in the buffer pool. If that is the case, InnoDB will do the insertion directly to the index page. But, if the index page is not found from the buffer pool, InnoDB inserts the record to a special insert buffer structure. The insert buffer is kept so small that it entirely fits in the buffer pool, and insertions can be made to it very fast.

The insert buffer is periodically merged to the secondary index trees in the database. Often we can merge several insertions on the same page in of the index tree, and hence save disk i/o's. It has been measured that the insert buffer can speed up insertions to a table up to 15 times. Adaptive hash indexes

If a database fits almost entirely in main memory, then the fastest way to perform queries on it is to use hash indexes. InnoDB has an automatic mechanism which monitors index searches made to the indexes defined for a table, and if InnoDB notices that queries could benefit from building of a hash index, such an index is automatically built.

But note that the hash index is always built based on an existing B-tree index on the table. InnoDB can build a hash index on a prefix of any length of the key defined for the B-tree, depending on what search pattern InnoDB observes on the B-tree index. A hash index can be partial: it is not required that the whole B-tree index is cached in the buffer pool. InnoDB will build hash indexes on demand to those pages of the index which are often accessed.

In a sense, through the adaptive hash index mechanism InnoDB adapts itself to ample main memory, coming closer to the architecture of main memory databases. Physical record structure

8.7.11 File space management and disk i/o Disk i/o

In disk i/o InnoDB uses asynchronous i/o. On Windows NT it uses the native asynchronous i/o provided by the operating system. On Unixes InnoDB uses simulated asynchronous i/o built into InnoDB: InnoDB creates a number of i/o threads to take care of i/o operations, such as read-ahead. In a future version we will add support for simulated aio on Windows NT and native aio on those Unixes which have one.

On Windows NT InnoDB uses non-buffered i/o. That means that the disk pages InnoDB reads or writes are not buffered in the operating system file cache. This saves some memory bandwidth.

You can also use a raw disk in InnoDB, though this has not been tested yet: just define the raw disk in place of a data file in `my.cnf'. You must give the exact size in bytes of the raw disk in `my.cnf', because at startup InnoDB checks that the size of the file is the same as specified in the configuration file. Using a raw disk you can on some Unixes perform non-buffered i/o.

There are two read-ahead heuristics in InnoDB: sequential read-ahead and random read-ahead. In sequential read-ahead InnoDB notices that the access pattern to a segment in the tablespace is sequential. Then InnoDB will post in advance a batch of reads of database pages to the i/o system. In random read-ahead InnoDB notices that some area in a tablespace seems to be in the process of being fully read into the buffer pool. Then InnoDB posts the remaining reads to the i/o system. File space management

The data files you define in the configuration file form the tablespace of InnoDB. The files are simply catenated to form the tablespace, there is no striping in use. Currently you cannot directly instruct where the space is allocated for your tables, except by using the following fact: from a newly created tablespace InnoDB will allocate space starting from the low end.

The tablespace consists of database pages whose default size is 16 kB. The pages are grouped into extents of 64 consecutive pages. The 'files' inside a tablespace are called segments in InnoDB. The name of the rollback segment is somewhat misleading because it actually contains many segments in the tablespace.

For each index in InnoDB we allocate two segments: one is for non-leaf nodes of the B-tree, the other is for the leaf nodes. The idea here is to achieve better sequentiality for the leaf nodes, which contain the data.

When a segment grows inside the tablespace, InnoDB allocates the first 32 pages to it individually. After that InnoDB starts to allocate whole extents to the segment. InnoDB can add to a large segment up to 4 extents at a time to ensure good sequentiality of data.

Some pages in the tablespace contain bitmaps of other pages, and therefore a few extents in an InnoDB tablespace cannot be allocated to segments as a whole, but only as individual pages.

When you issue a query SHOW TABLE STATUS FROM ... LIKE ... to ask for available free space in the tablespace, InnoDB will report you the space which is certainly usable in totally free extents of the tablespace. InnoDB always reserves some extents for clean-up and other internal purposes; these reserved extents are not included in the free space.

When you delete data from a table, InnoDB will contract the corresponding B-tree indexes. It depends on the pattern of deletes if that frees individual pages or extents to the tablespace, so that the freed space is available for other users. Dropping a table or deleting all rows from it is guaranteed to release the space to other users, but remember that deleted rows can be physically removed only in a purge operation after they are no longer needed in transaction rollback or consistent read.

8.7.12 Error handling

The error handling in InnoDB is not always the same as specified in the ANSI SQL standards. According to the ANSI standard, any error during an SQL statement should cause the rollback of that statement. InnoDB sometimes rolls back only part of the statement. The following list specifies the error handling of InnoDB.

8.7.13 Some restrictions on InnoDB tables

8.7.14 InnoDB contact information

Contact information of Innobase Oy, producer of the InnoDB engine:

Website: www.innobase.fi
phone: 358-9-6969 3250 (office) 358-40-5617367 (mobile)
InnoDB Oy Inc.
World Trade Center Helsinki
Aleksanterinkatu 17
P.O.Box 800
00101 Helsinki

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