Web Directory: mysql docs, manual InnoDB.php
Search:  
MySQL Reference Manual for version 5.0.0-alpha. - 16 The InnoDB Storage Engine Go to the first, previous, next, last section, table of contents.


16 The InnoDB Storage Engine

16.1 InnoDB Tables Overview

InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine with commit, rollback, and crash recovery capabilities. InnoDB does locking on the row level and also provides an Oracle-style consistent non-locking read in SELECT statements. These features increase multi-user concurrency and performance. There is no need for lock escalation in InnoDB, because row level locks in InnoDB fit in very small space. InnoDB also supports FOREIGN KEY constraints. In SQL queries you can freely mix InnoDB type tables with other table types of MySQL, even within the same query.

InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.

Fully integrated with MySQL Server, the InnoDB storage engine maintains 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 (or raw disk partitions). This is different from, for example, MyISAM tables where each table is stored using separate files. InnoDB tables can be of any size even on operating systems where file size is limited to 2GB.

InnoDB is included in binary distributions by default as of MySQL 4.0. For information about InnoDB support in MySQL 3.23, see section 16.3 InnoDB in MySQL 3.23.

InnoDB is used in production at numerous large database sites requiring high performance. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. stores over 1TB of data in InnoDB, and another site handles an average load of 800 inserts/updates per second in InnoDB.

InnoDB is published under the same GNU GPL License Version 2 (of June 1991) as MySQL. If you distribute MySQL/InnoDB, and your application does not satisfy the provisions of the GPL license, you have to purchase a commercial MySQL Pro license from https://order.mysql.com/?sub=pg&pg_no=1.

16.2 InnoDB Contact Information

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

Web site: http://www.innodb.com/
Email: sales@innodb.com
Phone: +358-9-6969 3250 (office)
       +358-40-5617367 (mobile)

Innobase Oy Inc.
World Trade Center Helsinki
Aleksanterinkatu 17
P.O.Box 800
00101 Helsinki
Finland

16.3 InnoDB in MySQL 3.23

Beginning with MySQL 4.0, InnoDB is enabled by default. The following information applies only to MySQL 3.23.

InnoDB tables are included in the MySQL source distribution starting from 3.23.34a and are activated in the MySQL-Max binaries of the 3.23 series. For Windows, the MySQL-Max binaries are included in the standard distribution.

If you have downloaded a binary version of MySQL that includes support for InnoDB, simply follow the instructions of the MySQL manual for installing a binary version of MySQL. If you already have MySQL 3.23 installed, the simplest way to install MySQL-Max is to replace the executable mysqld server with the corresponding executable from the MySQL-Max distribution. MySQL and MySQL-Max differ only in the server executable. See section 2.2.5 Installing MySQL on Other Unix-like Systems and section 5.1.2 The mysqld-max Extended MySQL Server.

To compile the MySQL source code with InnoDB support, download MySQL 3.23.34a or newer from http://www.mysql.com/ and configure MySQL with the --with-innodb option. See section 2.3 MySQL Installation Using a Source Distribution.

To use InnoDB tables with MySQL 3.23, you must specify configuration parameters in the [mysqld] section of the `my.cnf' option file. On Windows, you can use `my.ini' instead. If you do not configure InnoDB in the option file, InnoDB will not start. (From MySQL 4.0 on, InnoDB uses default parameters if you do not specify any. However, it is still recommended that to get best performance, you should use parameters appropriate for your system, as discussed in section 16.4 InnoDB Configuration.)

In MySQL 3.23, you must specify at the minimum a innodb_data_file_path value to configure the InnoDB datafiles. For example, to configure InnoDB to use a single 10MB auto-extending datafile, place the following setting in the [mysqld] section of your option file:

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend

InnoDB will create the `ibdata1' file in the MySQL data directory by default. To specify the location explicitly, specify a innodb_data_home_dir setting. See section 16.4 InnoDB Configuration.

16.4 InnoDB Configuration

To enable InnoDB tables in MySQL 3.23, see section 16.3 InnoDB in MySQL 3.23.

From MySQL 4.0 on, the InnoDB storage engine is enabled by default. If you don't want to use InnoDB tables, you can add the skip-innodb option to your MySQL option file.

Two important disk-based resources managed by the InnoDB storage engine are its tablespace datafiles and its log files.

If you specify no InnoDB configuration options, MySQL 4.0 and above creates an auto-extending 10MB datafile named `ibdata1' and two 5MB log files named `ib_logfile0' and `ib_logfile1' in the MySQL data directory. (In MySQL 4.0.0 and 4.0.1, the datafile is 64MB and not auto-extending.) In MySQL 3.23, InnoDB will not start if you provide no configuration options.

Note: To get good performance, you should explicitly provide InnoDB parameters as discussed in the following examples. Naturally, you should edit the settings to suit your hardware and requirements.

To set up the InnoDB tablespace files, use the innodb_data_file_path option in the [mysqld] section of the `my.cnf' option file. On Windows, you can use `my.ini' instead. The value of innodb_data_file_path should be a list of one or more datafile specifications. If you name more than one datafile, separate them by semicolon (`;') characters:

innodb_data_file_path=datafile_spec1[;datafile_spec2]...

For example, a setting that explicitly creates a tablespace having the same characteristics as the MySQL 4.0 default is as follows:

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend

This setting configures a single 10MB datafile named `ibdata1' that is auto-extending. No location for the file is given, so the default is the MySQL data directory.

Sizes are specified using M or G suffix letters to indicate units of MB or GB.

A tablespace containing a fixed-size 50MB datafile named `ibdata1' and a 50MB auto-extending file named ibdata2 in the data directory can be configured like this:

[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

The full syntax for a datafile specification includes the filename, its size, and several optional attributes:

file_name:file_size[:autoextend[:max:max_file_size]]

The autoextend attribute and those following can be used only for the last datafile in the innodb_data_file_path line. autoextend is available starting from MySQL 3.23.50 and 4.0.2.

If you specify the autoextend option for the last datafile, InnoDB extends the datafile if it runs out of free space in the tablespace. The increment is 8MB at a time.

If the disk becomes full, you might want to add another datafile on another disk. Instructions for reconfiguring an existing tablespace are given in section 16.8 Adding and Removing InnoDB Data and Log Files.

InnoDB is not aware of the maximum file size, so be cautious on filesystems where the maximum file size is 2GB. To specify a maximum size for an auto-extending datafile, use the max attribute. The following configuration allows `ibdata1' to grow up to a limit of 500MB:

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend:max:500M

InnoDB creates tablespace files in the MySQL data directory by default. To specify a location explicitly, use the innodb_data_home_dir option. For example, to use two files named `ibdata1' and `ibdata2' but create them in the `/ibdata' directory, configure InnoDB like this:

[mysqld]
innodb_data_home_dir = /ibdata
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

Note: InnoDB does not create directories, so make sure that the `/ibdata' directory exists before you start the server. This is also true of any log file directories that you configure. Use the Unix or DOS mkdir command to create any necessary directories.

InnoDB forms the directory path for each datafile by textually concatenating the value of innodb_data_home_dir to the datafile name, adding a slash or backslash between if needed. If the innodb_data_home_dir option is not mentioned in `my.cnf' at all, the default value is the ``dot'' directory `./', which means the MySQL data directory.

If you specify innodb_data_home_dir as an empty string, you can specify absolute paths for the datafiles listed in the innodb_data_file_path value. The following example is equivalent to the preceding one:

[mysqld]
innodb_data_home_dir =
innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend

A simple `my.cnf' example. Suppose you have a computer with 128MB RAM and one hard disk. The following example shows possible configuration parameters in `my.cnf' or `my.ini' for InnoDB. The example assumes the use of MySQL-Max 3.23.50 or later or MySQL 4.0.2 or later because it makes use of the autoextend attribute.

This example suits most users, both on Unix and Windows, who do not want to distribute InnoDB datafiles and log files on several disks. It creates an auto-extending datafile `ibdata1' and two InnoDB log files `ib_logfile0' and `ib_logfile1' in the MySQL data directory. Also, the small archived InnoDB log file `ib_arch_log_0000000000' that InnoDB creates automatically ends up in the data directory.

[mysqld]
# You can write your other MySQL server options here
# ...
# Datafile(s) must be able to hold your data and indexes.
# Make sure you have enough free disk space.
innodb_data_file_path = ibdata1:10M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
#
# Set the log file size to about 25% of the buffer pool size
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1

Make sure that the MySQL server has the proper access rights to create files in the data directory. More generally, the server must have access rights in any directory where it needs to create datafiles or log files.

Note that datafiles must be less than 2GB in some filesystems. The combined size of the log files must be less than 4GB. The combined size of datafiles must be at least 10MB.

When you create an InnoDB tablespace for the first time, it is best that you start the MySQL server from the command prompt. InnoDB will then print the information about the database creation to the screen, so you can see what is happening. For example, on Windows, if mysqld-max is located in `C:\mysql\bin', you can start it like this:

C:\> C:\mysql\bin\mysqld-max --console

If you do not send server output to the screen, check the server's error log to see what InnoDB prints during the startup process.

See section 16.6 Creating the InnoDB Tablespace for an example of what the information displayed by InnoDB should look like.

Where to specify options on Windows? The rules for option files on Windows are as follows:

  • Only one of `my.cnf' or `my.ini' should be created.
  • The `my.cnf' file should be placed in the root directory of the `C:' drive.
  • The `my.ini' file should be placed in the WINDIR directory; for example, `C:\WINDOWS' or `C:\WINNT'. You can use the SET command at the command prompt in a console window to print the value of WINDIR:
    C:\> SET WINDIR
    windir=C:\WINNT
    
  • If your PC uses a boot loader where the `C:' drive is not the boot drive, your only option is to use the `my.ini' file.

Where to specify options on Unix? On Unix, mysqld reads options from the following files, if they exist, in the following order:

  • `/etc/my.cnf' Global options.
  • `COMPILATION_DATADIR/my.cnf' Server-specific options.
  • `defaults-extra-file' The file specified with the --defaults-extra-file option.
  • `~/.my.cnf' User-specific options.

COMPILATION_DATADIR is the MySQL data directory that was specified as a ./configure option when mysqld was compiled (typically `/usr/local/mysql/data' for a binary installation or `/usr/local/var' for a source installation).

If you want to make sure that mysqld reads options only from a specific file, you can use the --defaults-option as the first option on the command line when starting the server:

mysqld --defaults-file=your_path_to_my_cnf

An advanced `my.cnf' example. Suppose you have a Linux computer with 2GB RAM and three 60GB hard disks (at directory paths `/', `/dr2' and `/dr3'). The following example shows possible configuration parameters in `my.cnf' for InnoDB.

[mysqld]
# You can write your other MySQL server options here
# ...
innodb_data_home_dir =
#
# Datafiles must be able to hold your data and indexes
innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory,
# but make sure on Linux x86 total memory usage is < 2GB
set-variable = innodb_buffer_pool_size=1G
set-variable = innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
#
# innodb_log_arch_dir must be the same as innodb_log_group_home_dir
# (starting from 4.0.6, you can omit it)
innodb_log_arch_dir = /dr3/iblogs
set-variable = innodb_log_files_in_group=2
#
# Set the log file size to about 25% of the buffer pool size
set-variable = innodb_log_file_size=250M
set-variable = innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
#
# Uncomment the next lines if you want to use them
#innodb_flush_method=fdatasync
#set-variable = innodb_thread_concurrency=5

Note that the example places the two datafiles on different disks. InnoDB will fill the tablespace beginning with the first datafile. 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. You can also use raw disk partitions (raw devices) as InnoDB datafiles, which may speed up I/O. See section 16.15.2 Using Raw Devices for the Tablespace.

Warning: On GNU/Linux x86, you must be careful not to set memory usage too high. glibc will allow the process heap to grow over thread stacks, which will crash your server. It is a risk if the value of the following expression is close to or exceeds 2GB:

innodb_buffer_pool_size + key_buffer_size +
max_connections * (sort_buffer_size+read_buffer_size+binlog_cache_size) +
max_connections * 2MB

Each thread will use a stack (often 2MB, but only 256KB in MySQL AB binaries) and in the worst case also uses sort_buffer + read_buffer_size additional memory.

Starting from MySQL 4.1, you can use up to 64GB of physical memory in 32-bit Windows. See the description for innodb_buffer_pool_awe_mem_mb in section 16.5 InnoDB Startup Options.

How to tune other mysqld server parameters? The following values are typical and suit most users:

[mysqld]
skip-locking
set-variable = max_connections=200
set-variable = read_buffer_size=1M
set-variable = sort_buffer=1M
#
# Set key_buffer to 5 - 50% of your RAM depending on how much
# you use MyISAM tables, but keep key_buffer_size + InnoDB
# buffer pool size < 80% of your RAM
set-variable = key_buffer_size=...

16.5 InnoDB Startup Options

This section describes the InnoDB-related server options. In MySQL 4.0 and up, all of them can be specified in --opt_name=value form on the command line or in option files. Before MySQL 4.0, numeric options should be specified using --set-variable=opt_name=value or -O opt_name=value syntax.

innodb_additional_mem_pool_size
The size of a memory pool InnoDB uses to store data dictionary information and other internal data structures. The more tables you have in your application, the more memory 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. The default value is 1MB.
innodb_buffer_pool_awe_mem_mb
The size of the buffer pool in MB, if it is placed in the AWE memory of 32-bit Windows. Available from 4.1.0 and only relevant in 32-bit Windows. If your 32-bit Windows operating system supports > 4GB memory, so-called Address Windowing Extensions, you can allocate the InnoDB buffer pool into the AWE physical memory using this parameter. The maximum possible value for this is 64000. If this parameter is specified, innodb_buffer_pool_size is the window in the 32-bit address space of mysqld where InnoDB maps that AWE memory. A good value for innodb_buffer_pool_size is then 500MB.
innodb_buffer_pool_size
The size of the memory buffer InnoDB uses to cache data and indexes of its tables. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. Do not set it too large though, because competition for the physical memory might cause paging in the operating system.
innodb_data_file_path
The paths to individual datafiles and their sizes. The full directory path to each datafile is acquired by concatenating innodb_data_home_dir to each path specified here. The file sizes are specified in megabytes or gigabytes (1024MB) by appending M or G to the size value. The sum of the sizes of the files must be at least 10MB. On some operating systems, files must be less than 2GB. If you do not specify innodb_data_file_path, the default behavior starting from 4.0 is to create a single 10MB auto-extending datafile named `ibdata1'. Starting from 3.23.44, you can set the file size bigger than 4GB on those operating systems that support big files. You can also use raw disk partitions as datafiles. See section 16.15.2 Using Raw Devices for the Tablespace.
innodb_data_home_dir
The common part of the directory path for all InnoDB datafiles. If you do not set this value, the default is the MySQL data directory. You can specify this also as an empty string, in which case you can use absolute file paths in innodb_data_file_path.
innodb_fast_shutdown
By default, InnoDB does a full purge and an insert buffer merge before a shutdown. These operations can take minutes, or in extreme cases even hours. If you set this parameter to 1, InnoDB skips these operations at shutdown. This option is available starting from MySQL 3.23.44 and 4.0.1. Its default value is 1 starting from 3.23.50.
innodb_file_io_threads
The number of file I/O threads in InnoDB. Normally this should be left at the default value of 4, but on Windows, disk I/O may benefit from a larger number. On Unix, increasing the number has no effect; InnoDB always uses the default value. This option is available as of MySQL 3.23.37
innodb_file_per_table
This option causes InnoDB to create each new table using its own `.ibd' file for storing data and indexes, rather than in the shared tablespace. See section 16.7.6 Using Per-table Tablespaces. This option is available as of MySQL 4.1.1.
innodb_flush_log_at_trx_commit
Normally you set this 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 or 2 to reduce disk I/O to the logs. A value of 0 means that the log is only written to the log file and the log file flushed to disk approximately once per second. A value of 2 means the log is written to the log file at each commit, but the log file is only flushed to disk approximately once per second. The default value is 1 (prior to MySQL 4.0.13, the default 0).
innodb_flush_method
This option is only relevant on Unix systems. If set to fdatasync, InnoDB uses fsync() to flush both the data and log files. If set to O_DSYNC, InnoDB uses O_SYNC to open and flush the log files, but uses fsync() to flush the datafiles. If O_DIRECT is specified (available on some GNU/Linux versions starting from MySQL 4.0.14), InnoDB uses O_DIRECT to open the datafiles, and uses fsync() to flush both the data and log files. Note that InnoDB does not use fdatasync or O_DSYNC by default because there have been problems with them on many Unix flavors. This option is available as of MySQL 3.23.40.
innodb_force_recovery
Warning: This option should be defined only in an emergency situation when you want to dump your tables from a corrupt database! Possible values are from 1 to 6. The meanings of these values are described in section 16.9.1 Forcing Recovery. As a safety measure, InnoDB prevents a user from modifying data when this option is greater than 0. This option is available starting from MySQL 3.23.44.
innodb_lock_wait_timeout
The 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 the LOCK TABLES statement, or other transaction-safe storage engines than InnoDB in the same transaction, a deadlock may arise that InnoDB cannot notice. In cases like this, the timeout is useful to resolve the situation. The default is 50 seconds.
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. Starting from MySQL 4.0.6, you may omit this option.
innodb_log_archive
This value should currently be set to 0. Because recovery from a backup is done by MySQL using its own log files, there is currently no need to archive InnoDB log files. The default for this option is 0.
innodb_log_buffer_size
The size of the buffer that InnoDB uses to write to the log files on disk. Sensible values range from 1MB to 8MB. The default is 1MB. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger will save disk I/O.
innodb_log_file_size
The size of each log file in a log group in megabytes. The combined size of log files must be less than 4GB on 32-bit computers. The default is 5MB. Sensible values range from 1MB to 1/n-th of the size of the buffer pool, below, where n is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery will be slower in case of a crash.
innodb_log_files_in_group
The number of log files in the log group. InnoDB writes to the files in a circular fashion. The default is 2 (recommended).
innodb_log_group_home_dir
The directory path to the InnoDB log files. It must have the same value as innodb_log_arch_dir. If you do not specify any InnoDB log parameters, the default is to create two 5MB files `ib_logfile0' and `ib_logfile1' in the MySQL data directory.
innodb_max_dirty_pages_pct
This is an integer in the range from 0 to 100. The default is 90. The main thread in InnoDB tries to flush pages from the buffer pool so that at most this many percent of pages may not yet flushed been flushed at any particular time. Available starting from 4.0.13 and 4.1.1. If you have the SUPER privilege, this percentage can be changed while the server is running:
SET GLOBAL innodb_max_dirty_pages_pct = value;
innodb_mirrored_log_groups
The number of identical copies of log groups we keep for the database. Currently this should be set to 1.
innodb_open_files
This option is relevant only if you use multiple tablespaces in InnoDB. It specifies the maximum number of `.ibd' files that InnoDB can keep open at one time. The minimum value is 10. The default is 300. This option is available as of MySQL 4.1.1. The file descriptors used for `.ibd' files are for InnoDB only. They are independent of those specified by the --open-files-limit server option, and do not affect the operation of the table cache.
innodb_thread_concurrency
InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to the limit given by this parameter. The default value is 8. If you have low performance and SHOW INNODB STATUS reveals many threads waiting for semaphores, you may have thread thrashing and should try setting this parameter lower or higher. If you have a computer with many processors and disks, you can try setting this value higher to better utilize the resources of you computer. A recommended value is the sum of the number of processors and disks your system has. A value of 500 or greater disables the concurrency checking. This option is available starting from MySQL 3.23.44 and 4.0.1.

16.6 Creating the InnoDB Tablespace

Suppose you have installed MySQL and have edited your option file so that it contains the necessary InnoDB configuration parameters. Before starting MySQL, you should verify that the directories you have specified for InnoDB datafiles and log files exist and that the MySQL server has access rights to those directories. InnoDB cannot create directories, only files. Check also that you have enough disk space for the data and log files.

It is best to run the MySQL server mysqld from the command prompt when you create an InnoDB database, not from the `safe_mysqld' wrapper or as a Windows service. When you run from a command prompt you see what mysqld prints and what is happening. On Unix, just invoke mysqld. On Windows, use the --console option.

When you start the MySQL server after initially configuring InnoDB in your option file, InnoDB creates your datafiles and log files. InnoDB will print something like the following:

# mysqld
InnoDB: The first specified datafile /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: datafile /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 created
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 created
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size
to 5242880
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
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, the 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 datafile and log 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 started again, the datafiles and log files will already have been created, so the output will be much briefer:

# mysqld
InnoDB: Started
mysqld: ready for connections

16.6.1 Dealing with InnoDB Initialization Problems

If InnoDB prints an operating system error in a file operation, usually the problem is one of the following:

  • You did not create the InnoDB datafile or log directories.
  • mysqld does not have access rights to create files in those directories.
  • mysqld does not read the proper `my.cnf' or `my.ini' option file, and consequently does not see the options you specified.
  • The disk is full or a disk quota is exceeded.
  • You have created a subdirectory whose name is equal to a datafile you specified.
  • There is a syntax error in innodb_data_home_dir or innodb_data_file_path.

If something goes wrong when InnoDB attempts to initialize its tablespace or its log files, you should delete all files created by InnoDB. This means all datafiles, all log files, the small archived log file. In case you already created some InnoDB tables, delete the corresponding `.frm' files for these tables (and any `.ibd' files if you are using multiple tablespaces) from the MySQL database directories as well. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening.

16.7 Creating InnoDB Tables

Suppose you have started the MySQL client with the command mysql test. To create an InnoDB table, you must specify ENGINE = InnoDB or TYPE = InnoDB in the table creation SQL statement:

CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) TYPE=InnoDB;

The SQL statement creates a table and an index on column a in the InnoDB tablespace that consists of the datafiles you specified in `my.cnf'. In addition, MySQL creates a file `customers.frm' in the `test' directory under the MySQL database directory. Internally, InnoDB adds to its own data dictionary an entry for table 'test/customers'. This means you can create a table of the same name customers in some other database, and the table names will not collide inside InnoDB.

You can query the amount of free space in the InnoDB tablespace by issuing a SHOW TABLE STATUS statement for any InnoDB table. The amount of free space in the tablespace appears in the Comment section in the output of SHOW TABLE STATUS. An example:

SHOW TABLE STATUS FROM test LIKE 'customers'

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.

16.7.1 How to Use Transactions in InnoDB with Different APIs

By default, each client tht connects to the MySQL server begins with autocommit mode enabled, which automatically commits every SQL statement you run. To use multiple-statement transactions, you can switch autocommit off with the SQL statement SET AUTOCOMMIT = 0 and use COMMIT and ROLLBACK to commit or rollback your transaction. If you want to leave the autocommit on, you can enclose your transactions between START TRANSACTION and COMMIT or ROLLBACK. Before MySQL 4.0.11, you have to use the keyword BEGIN instead of START TRANSACTION. The following example shows two transactions. The first is committed and the second is rolled back.

# mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.50-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
    -> TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A    | B      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>

In APIs like PHP, Perl DBI/DBD, JDBC, ODBC, or the standard C call interface of MySQL, send the transaction control statements like COMMIT to the MySQL server as strings just like any other SQL statements such as SELECT or INSERT. Some APIs also offer separate special transaction commit and rollback functions or methods.

16.7.2 Converting MyISAM Tables to InnoDB

Important: You should not convert MySQL system tables in the mysql database (such as user or host) to the InnoDB type. The system tables must always be of the MyISAM type.

If you want all your (non-system) tables to be created in the InnoDB type, you can, starting from the MySQL 3.23.43, add the line default-table-type=innodb to the [mysqld] section of your `my.cnf' or `my.ini' file.

InnoDB does not have a special optimization for separate index creation the way the MyISAM storage engine does. Therefore, it does not pay to export and import the table and create indexes afterward. The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table, that is, use ALTER TABLE ... TYPE=INNODB, or create an empty InnoDB table with identical definitions and insert the rows with INSERT INTO ... SELECT * FROM ....

If you have UNIQUE constraints on secondary keys, starting from MySQL 3.23.52, you can speed up a table import by turning off the uniqueness checks temporarily during the import session: SET UNIQUE_CHECKS=0; For big tables this saves a lot of disk I/O because InnoDB can then use its insert buffer to write secondary index records in a batch.

To get better control over the insertion process, it may be good to insert big tables in pieces:

INSERT INTO newtable SELECT * FROM oldtable
   WHERE yourkey > something AND yourkey <= somethingelse;

After all data has been inserted, you can rename the tables.

During the conversion of big tables you should increase the size of the InnoDB buffer pool to reduce disk I/O. Do not use more than 80% of the physical memory, though. You can also increase the sizes of the InnoDB log files and the log files.

Make sure you do not fill up the tablespace: InnoDB tables require a lot more disk space than MyISAM tables. If an ALTER TABLE runs out of space, it will start a rollback, and that can take hours if it is disk-bound. For inserts, InnoDB uses the insert buffer to merge secondary index records to indexes in batches. That saves a lot of disk I/O. In rollback, no such mechanism is used, and the rollback can take 30 times longer than the insertion.

In the case of a runaway rollback, if you do not have valuable data in your database, it may be advisable to kill the database process rather than wait for millions of disk I/O operations to complete. For the complete procedure, see section 16.9.1 Forcing Recovery.

16.7.3 How an AUTO_INCREMENT Column Works in InnoDB

If you specify an AUTO_INCREMENT column for a table, the InnoDB table handle in the data dictionary will contain a special counter called the auto-increment counter that is used in assigning new values for the column. The auto-increment counter is stored only in main memory, not on disk.

InnoDB uses the following algorithm to initialize the auto-increment counter for a table T that contains an AUTO_INCREMENT column named ai_col: After a server startup, when a user first does an insert to a table T, InnoDB executes the equivalent of this statement:

SELECT MAX(ai_col) FROM T FOR UPDATE

The value retrieved by the statement is incremented by one and assigned to the column and the auto-increment counter of the table. If the table is empty, the value 1 is assigned. If the auto-increment counter is not initialized and the user invokes a SHOW TABLE STATUS statement that displays output for the table T, the counter is initialized (but not incremented) and stored for use by later inserts. Note that in this initialization we do a normal exclusive-locking read on the table and the lock lasts to the end of the transaction.

InnoDB follows the same procedure for initializing the auto-increment counter for a freshly created table.

Note that if the user specifies NULL or 0 for the AUTO_INCREMENT column in an INSERT, InnoDB treats the row as if the value had not been specified and generates a new value for it.

After the auto-increment counter has been initialized, if a user inserts a row that explicitly specifies the column value, and the value is bigger than the current counter value, the counter is set to the specified column value. If the user does not explicitly specify a value, InnoDB increments the counter by one and assigns the new value to the column.

When accessing the auto-increment counter InnoDB uses a special table level AUTO-INC lock that it keeps to the end of the current SQL statement, not to the end of the transaction. The special lock release strategy was introduced to improve concurrency for inserts into a table containing an AUTO_INCREMENT column. Two transactions cannot have the AUTO-INC lock on the same table simultaneously.

Note that you may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have gotten numbers from the counter.

The behavior of the auto-increment mechanism is not defined if a user assigns a negative value to the column or if the value becomes bigger than the maximum integer that can be stored in the specified integer type.

16.7.4 FOREIGN KEY Constraints

Starting from MySQL 3.23.43b, InnoDB features foreign key constraints.

The syntax of a foreign key constraint definition in InnoDB looks like this::

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

Both tables must be InnoDB type. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order. Index prefixes on foreign keys are not supported.

InnoDB does not automatically create indexes on foreign keys or referenced keys: You must create them explicitly. The indexes are needed so that foreign key checks can be fast and not require a table scan.

Corresponding columns in the foreign key and the referenced key must have similar internal datatypes inside InnoDB so that they can be compared without a type conversion. The size and the signedness of integer types has to be the same. The length of string types need not be the same. If you specify a SET NULL action, make sure you have not declared the columns in the child table as NOT NULL.

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message string refers to errno 150, this means that the table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to errno 150, that means a foreign key definition would be incorrectly formed for the altered table. Starting from MySQL 4.0.13, you can use SHOW INNODB STATUS to display a detailed explanation of the latest InnoDB foreign key error in the server.

Starting from MySQL 3.23.50, InnoDB does not check foreign key constraints on those foreign key or referenced key values that contain a NULL column.

A deviation from SQL standards: If in the parent table there are several rows that have the same referenced key value, then InnoDB acts in foreign key checks as if the other parent rows with the same key value do not exist. For example, if you have defined a RESTRICT type constraint, and there is a child row with several parent rows, InnoDB does not allow the deletion of any of those parent rows.

Starting from MySQL 3.23.50, you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. Corresponding ON UPDATE options are available starting from 4.0.8. If ON DELETE CASCADE is specified, and a row in the parent table is deleted, InnoDB automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row. If ON DELETE SET NULL is specified, the child rows are automatically updated so that the columns in the foreign key are set to the SQL NULL value.

InnoDB performs cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints.

A deviation from SQL standards: If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the SAME TABLE it has already updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible from 4.0.13. A self-referential ON DELETE CASCADE has been possible since ON DELETE was implemented.

A simple example that relates parent and child tables through a single-column foreign key:

CREATE TABLE parent(id INT NOT NULL,
                    PRIMARY KEY (id)
) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT,
                   INDEX par_ind (parent_id),
                   FOREIGN KEY (parent_id) REFERENCES parent(id)
                     ON DELETE CASCADE
) ENGINE=INNODB;

A more complex example in which a product_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table. The other references a single-column index in the customer table:

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                      product_category INT NOT NULL,
                      product_id INT NOT NULL,
                      customer_id INT NOT NULL,
                      PRIMARY KEY(no),
                      INDEX (product_category, product_id),
                      FOREIGN KEY (product_category, product_id)
                        REFERENCES product(category, id)
                        ON UPDATE CASCADE ON DELETE RESTRICT,
                      INDEX (customer_id),
                      FOREIGN KEY (customer_id)
                        REFERENCES customer(id)) ENGINE=INNODB;

Starting from MySQL 3.23.50, InnoDB allows you to add a new foreign key constraint to a table by using ALTER TABLE:

ALTER TABLE yourtablename
    ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

Remember to create the required indexes first, though. You can also add a self-referential foreign key constraint to a table using ALTER TABLE.

Starting from MySQL 4.0.13, InnoDB supports the use of ALTER TABLE to drop foreign keys:

ALTER TABLE yourtablename
    DROP FOREIGN KEY fk_symbol

If the FOREIGN KEY clause included a CONSTRAINT name when you created the foreign key, you can refer to that name to drop the foreign key. (A constraint name can be given as of MySQL 4.0.18.) Otherwise, the fk_symbol value is internally generated by InnoDB when the foreign key is created. To find out the symbol when you want to drop a foreign key, use the SHOW CREATE TABLE statement. An example:

mysql> show create table ibtest11c\G
*************************** 1. row ***************************
       Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
  `A` int(11) NOT NULL auto_increment,
  `D` int(11) NOT NULL default '0',
  `B` varchar(200) NOT NULL default '',
  `C` varchar(175) default NULL,
  PRIMARY KEY  (`A`,`D`,`B`),
  KEY `B` (`B`,`C`),
  KEY `C` (`C`),
  CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB CHARSET=latin1
1 row in set (0.01 sec)
mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;

Starting from MySQL 3.23.50, the InnoDB parser allows you to use also backticks around table and column names in a FOREIGN KEY ... REFERENCES ... clause. Starting from MySQL 4.0.5, the InnoDB parser also takes into account the lower_case_table_names system variable setting.

Before MySQL 3.23.50, ALTER TABLE or CREATE INDEX should not be used in connection with tables that have foreign key constraints or that are referenced in foreign key constraints: Any ALTER TABLE removes all foreign key constraints defined for the table. You should not use ALTER TABLE with the referenced table, either. Instead, use DROP TABLE and CREATE TABLE to modify the schema. When MySQL does an ALTER TABLE it may internally use RENAME TABLE, and that will confuse the foreign key constraints that refer to the table. In MySQL, a CREATE INDEX statement is processed as an ALTER TABLE, so the same considerations apply.

Starting from MySQL 3.23.50, InnoDB returns the foreign key definitions of a table as part of the output of the SHOW CREATE TABLE statement:

SHOW CREATE TABLE tbl_name;

From this version, also mysqldump produces correct definitions of tables to the dump file, and does not forget about the foreign keys.

You can also display the foreign key constraints for a table like this:

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name'

The foreign key constraints are listed in the Comment column of the output.

When performing foreign key checks, InnoDB sets shared row level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately: The check is not deferred to transaction commit.

To make it easier to reload dump files for tables that have foreign key relationships, mysqldump automatically includes a statement in the dump output to set FOREIGN_KEY_CHECKS to 0 as of MySQL 4.1.1. This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded. For earlier versions, you can disable the variable manually within mysql when loading the dump file like this:

mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;

This allows you to import the tables in any order if the dump file contains tables that are not correctly ordered for foreign keys. It also speeds up the import operation. FOREIGN_KEY_CHECKS is available starting from MySQL 3.23.52 and 4.0.3.

Setting FOREIGN_KEY_CHECKS to 0 can also be useful for ignoring foreign key constraints during LOAD DATA operations.

InnoDB allows you to drop any table, even though that would break the foreign key constraints that reference the table. When you drop a table, the constraints that were defined in its create statement are also dropped.

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to errno 150 in the error message string.

16.7.5 InnoDB and MySQL Replication

The MySQL replication feature works on InnoDB tables like it does on MyISAM type tables. It is also possible to use replication in a way where the table type on the slave is not the same as the original table type on the master. For example, you can replicate modifications to an InnoDB table on the master to a MyISAM table on the slave.

To set up a new slave for a master you have to make a copy of the InnoDB tablespace and the log files, as well as the `.frm' files of the InnoDB tables, and move the copies to the slave. For the proper procedure to do this, see See section 16.10 Moving an InnoDB Database to Another Machine.

If you can shut down the master or an existing slave, you can take a cold backup of the InnoDB tablespace and log files and use that to set up a slave. To make a new slave without taking down any server you can also use the non-free InnoDB Hot Backup tool.

There are minor limitations in InnoDB replication:

  • LOAD TABLE FROM MASTER does not work for InnoDB type tables. There are workarounds: 1) dump the table on the master and import the dumpfile into the slave, or 2) use ALTER TABLE tbl_name TYPE=MyISAM on the master before setting up replication with LOAD TABLE tbl_name FROM MASTER, then use ALTER TABLE to alter the master table back to the InnoDB type afterward.
  • Before MySQL 4.0.6, SLAVE STOP did not respect the boundary of a multiple-statement transaction. An incomplete transaction would be rolled back, and the next SLAVE START would only execute the remaining part of the half transaction. That would make replication to fail.
  • Before MySQL 4.0.6, a slave crash in the middle of a multiple-statement transaction would cause the same problem as SLAVE STOP.
  • Before MySQL 4.0.11, replication of the SET FOREIGN_KEY_CHECKS=0 statement does not work properly.

Most of these limitations can be eliminated by using more recent server versions for which the limitations do not apply.

Transactions that fail on the master do not affect replication at all. MySQL replication is based on the binary log where MySQL writes SQL statements that modify data. A slave reads the binary log of the master and executes the same SQL statements. However, statements that occur within a transaction are not written to the binary log until the transaction commits, at which point all statements in the transaction are written at once. If a statement fails, for example, because of a foreign key violation, or if a transaction is rolled back, no SQL statements are written to the binary log, and the transaction is not executed on the slave at all.

16.7.6 Using Per-table Tablespaces

Starting from MySQL 4.1.1, you can store each InnoDB table and its indexes into its own file. This feature is called ``multiple tablespaces'', because in effect each table has its own tablespace.

Important note: If you upgrade to InnoDB-4.1.1 or higher, it is difficult to downgrade back to 4.0 or 4.1.0! That is because earlier versions of InnoDB are not aware of multiple tablespaces.

If you need to downgrade to 4.0, you have to take table dumps and re-create the whole InnoDB tablespace. If you have not created new InnoDB tables under MySQL 4.1.1 or above, and need to downgrade quickly, you can also do a direct downgrade to the MySQL 4.0.18 or later in the 4.0 series. Before doing the direct downgrade to 4.0.x, you have to end all connections to >= 4.1.1, and let mysqld run purge and the insert buffer merge to completion, so that SHOW INNODB STATUS shows the Main thread in the state waiting for server activity. Then you can shut down mysqld and start 4.0.18 or later in the 4.0 series. A direct downgrade is not recommended, however, because it has not been extensively tested.

You can enable multiple tablespaces by adding a line to the [mysqld] section of `my.cnf':

[mysqld]
innodb_file_per_table

After restarting the server, InnoDB will store each newly created table into its own file `tbl_name.ibd' in the database directory where the table belongs. This is similar to what the MyISAM does, but MyISAM divides the table into a datafile `tbl_name.MYD' and the index file `tbl_name.MYI'. For InnoDB, both the data and the indexes are in the `.ibd' file. The `tbl_name.frm' file is still created as usual.

If you remove the innodb_file_per_table line from `my.cnf' and restart the server, InnoDB creates tables inside the shared tablespace files again. The old tables you had in the shared tablespace files before an upgrade to 4.1.1 remain there; they are not converted into `.ibd' files.

innodb_file_per_table affects only table creation. If you start the server with this option, you can still access tables that in the shared tablespace. If you remove the option, you can still access any tables that were created using multiple tablespaces.

InnoDB always needs the shared ``system tablespace.'' The `.ibd' files are not sufficient for InnoDB to operate. The system tablespace consists of the familiar `ibdata' files where InnoDB puts its internal data dictionary and undo logs.

You CANNOT FREELY MOVE .ibd files around the way you can with MyISAM table files. This is because the table definition is stored in the InnoDB system tablespace, and also because InnoDB must preserve the consistency of transaction IDs and log sequence numbers.

You can move an `.ibd' file and the associated table from a database to another within a given MySQL installation with the familiar RENAME TABLE statement:

RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;

If you have a ``clean'' backup of an `.ibd' file, you can restore it to the MySQL installation from which it originated as follows:

  1. Issue this ALTER TABLE statement:
    ALTER TABLE tbl_name DISCARD TABLESPACE;
    
    Caution: This deletes the current `.ibd' file.
  2. Put the backup `.ibd' file back in the proper database directory.
  3. Issue this ALTER TABLE statement:
    ALTER TABLE tbl_name IMPORT TABLESPACE;
    

In this context, a ``clean'' `.ibd' file backup means:

  • There are no uncommitted modifications by transactions in the `.ibd' file.
  • There are no unmerged insert buffer entries in the `.ibd' file.
  • Purge has removed all delete-marked index records from the `.ibd' file.
  • mysqld has flushed all modified pages of the `.ibd' file from the buffer pool to the file.

You can make such a clean backup `.ibd' file with the following method:

  1. Stop all activity from the mysqld server and commit all transactions.
  2. Wait that SHOW INNODB STATUS\G shows that there are no active transactions in the database, and the main thread of InnoDB is Waiting for server activity. Then you can make a copy of the `.ibd' file.

Another method for making a clean copy of an `.ibd' file is to use the commercial InnoDB Hot Backup tool:

  1. Use InnoDB Hot Backup to back up the InnoDB installation.
  2. Start a second mysqld server on the backup and let it clean up the `.ibd' files in the backup.

It is in the TODO to also allow moving clean `.ibd' files to another MySQL installation. This requires resetting of transaction IDs and log sequence numbers in the `.ibd' file.

16.8 Adding and Removing InnoDB Data and Log Files

This section describes what you can do when your InnoDB tablespace runs out of room or when you want to change the size of the log files.

From MySQL 3.23.50 and 4.0.2, the easiest way to increase the size of the InnoDB tablespace is to configure it from the beginning to be auto-extending. Specify the autoextend attribute for the last datafile in the tablespace definition. Then InnoDB will increase the size of that file automatically in 8MB increments when it runs out of space.

Alternatively, you can increase the size of your tablespace by adding an additional datafile. To do this, you have to shut down the MySQL server, edit the `my.cnf' file to add a new datafile to the end of innodb_data_file_path, and start the server again.

If your last datafile already was defined with the keyword autoextend, then the procedure to edit`my.cnf' must take into account the size to which the last datafile has grown. You have to look at the size of the datafile, round the size downward to the closest multiple of 1024 * 1024 bytes (= 1MB), and specify the rounded size explicitly in innodb_data_file_path. Then you can add another datafile. Remember that only the last datafile in the innodb_data_file_path can be specified as auto-extending.

As an example, assume the talespace had just one auto-extending datafile `ibdata1':

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

Suppose that this datafile, over time, grew to 988MB. Below is the configuration line after adding another auto-extending datafile.

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

When you add a new file to the tablespace, make sure that it does not exist so that when you restart the server, InnoDB will create it and initialize it.

Currently, you cannot remove a datafile from the tablespace. To decrease the size of your tablespace, use this procedure:

  1. Use mysqldump to dump all your InnoDB tables.
  2. Stop the server.
  3. Remove all the existing tablespace files.
  4. Configure a new tablespace.
  5. Restart the server.
  6. Import the dump files.

If you want to change the number or the size of your InnoDB log files, you have to stop the MySQL server 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 tablespace. Delete the old log files from the log file directory, edit `my.cnf' to change the log file configuration, and start the MySQL server again. mysqld will see that the no log files exist at startup and tell you that it is creating new ones.

16.9 Backing Up and Recovering an InnoDB Database

The key to safe database management is taking regular backups.

InnoDB Hot Backup is an online backup tool you can use to backup your InnoDB database while it is running. InnoDB Hot Backup does not require you to shut down your database and it does not set any locks or disturb your normal database processing. InnoDB Hot Backup is a non-free additional tool whose annual license fee is 390 euros per computer where the MySQL server is run. See the InnoDB Hot Backup homepage for detailed information and screenshots.

If you are able to shut down your MySQL server, you can make a ``binary'' backup that consists of all files used by InnoDB to manage its tables. Use the following procedure:

  1. Shut down your MySQL database and make sure it shuts down without errors.
  2. Copy all your datafiles into a safe place.
  3. Copy all your InnoDB log files to a safe place.
  4. Copy your `my.cnf' configuration file or files to a safe place.
  5. Copy all the `.frm' files for your InnoDB tables to a safe place.

Replication works with InnoDB type tables, so you can use MySQL replication capabilities to keep a copy of your database at database sites requiring high availability.

In addition to taking binary backups as just described, you should also regularly take dumps of your tables with mysqldump. The reason for this is that a binary file might be corrupted without you noticing it. Dumped tables are stored into text files that are human-readable, so spotting table corruption becomes easier. Also, since the format is simpler, the chance for serious data corruption is smaller. mysqldump also has a --single-transaction option that you can use to take a consistent snapshot without locking out other clients.

To be able to recover your InnoDB database to the present from the binary backup described above, you have to run your MySQL server with binary logging turned on. Then you can apply the binary log to the backup database to achieve point-in-time recovery:

mysqlbinlog yourhostname-bin.123 | mysql

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 that were present at the time of the crash. During recovery, mysqld will print out something like the following:

# 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 that is not corrupted. After restoring the base backup, do the recovery from the binary log files.

In some cases of database corruption it is enough just to dump, drop, and re-create one or a few corrupt tables. You can use the CHECK TABLE SQL statement to check whether a table is corrupt, though CHECK TABLE naturally cannot detect all kinds of corruption. You can use innodb_tablespace_monitor to check the integrity of the file space management inside the tablespace files.

In some cases, apparent database page corruption is actually due to the operating system which has corrupted its own file cache, and the data on disk may be okay. It is best first to try restarting your computer. It may remove the errors that appeared as database page corruption.

16.9.1 Forcing Recovery

If there is database page corruption, you may want to dump your tables from the database with SELECT INTO OUTFILE, and usually most of the data is intact and correct. But the corruption may cause SELECT * FROM tbl_name or InnoDB background operations to crash or assert, or even the InnoDB roll-forward recovery to crash. Starting from MySQL 3.23.44, there is an InnoDB variable that you can use to force the InnoDB storage engine to start up, and you can also prevent background operations from running, so that you will be able to dump your tables. For example, you can add the following line to the [mysqld] section of your option file before restarting the server:

[mysqld]
innodb_force_recovery = 4

Before MySQL 4.0, use this syntax instead:

[mysqld]
set-variable = innodb_force_recovery = 4

The allowable non-zero values for innodb_force_recovery follow. A larger number includes all precautions of lower numbers. If you are able to dump your tables with an option value of at most 4, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 6 is more dramatic, because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

  • 1 (SRV_FORCE_IGNORE_CORRUPT) Let the server run even if it detects a corrupt page; try to make SELECT * FROM table jump over corrupt index records and pages, which helps in dumping tables;
  • 2 (SRV_FORCE_NO_BACKGROUND) Prevent the main thread from running. If a crash would occur in purge, this prevents it;
  • 3 (SRV_FORCE_NO_TRX_UNDO) Do not run transaction rollbacks after recovery;
  • 4 (SRV_FORCE_NO_IBUF_MERGE) Prevent also insert buffer merge operations. If they would cause a crash, better not do them; do not calculate table statistics;
  • 5 (SRV_FORCE_NO_UNDO_LOG_SCAN) Do not look at undo logs when starting the database: InnoDB will treat even incomplete transactions as committed;
  • 6 (SRV_FORCE_NO_LOG_REDO) Do not do the log roll-forward in connection with recovery.

The database must not otherwise be used with these options! As a safety measure, InnoDB prevents a user from doing INSERT, UPDATE, or DELETE when innodb_force_recovery is set to a value greater than 0.

Starting from MySQL 3.23.53 and 4.0.4, you are allowed to DROP or CREATE a table even if forced recovery is used. If you know that a certain table is causing a crash in rollback, you can drop it. You can use this also to stop a runaway rollback caused by a failing mass import or ALTER TABLE. You can kill the mysqld process and set innodb_force_recovery to 3 to bring your database up without the rollback. Then DROP the table that is causing the runaway rollback.

16.9.2 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 in 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 that 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 preceding description 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 of big log files is that crash recovery can take longer because there will be more logged information to apply to the database.

16.10 Moving an InnoDB Database to Another Machine

On Windows, InnoDB internally always stores database and table names in lowercase. To move databases in a binary format from Unix to Windows or from Windows to Unix, you should have all table and database names in lowercase. A convenient way to accomplish this on Unix is to add the following line to the [mysqld] section of your `my.cnf' before you start creating your databases and tables:

[mysqld]
set-variable = lower_case_table_names = 1

On Windows, lower_case_table_names is set to 1 by default.

Like MyISAM datafiles, 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 were listed in section 16.9 Backing Up and Recovering an InnoDB Database. If the floating-point formats on the machines are different but you have not used FLOAT or DOUBLE datatypes 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 to dump your tables on one machine and then import the dump files on the other machine.

A performance tip is to switch off autocommit mode 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.

16.11 InnoDB Transaction Model and Locking

In the InnoDB transaction model, the goal has been to combine the best properties of a multi-versioning database with traditional two-phase locking. InnoDB does locking on the row level and runs queries as non-locking consistent reads by default, 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.

16.11.1 InnoDB and AUTOCOMMIT

In InnoDB, all user activity occurs inside a transaction. If the autocommit mode is enabled, each SQL statement forms a single transaction on its own. MySQL always starts a new connection with autocommit enabled.

If the autocommit mode is switched off with SET AUTOCOMMIT = 0, then we can think that a user always has a transaction open. An SQL COMMIT or ROLLBACK statement ends the current transaction and a new one 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 statement, on the other hand, cancels all modifications made by the current transaction.

If the connection has autocommit enabled, the user can still perform a multiple-statement transaction by starting it with an explicit START TRANSACTION or BEGIN statement and ending it with COMMIT or ROLLBACK.

16.11.2 InnoDB and TRANSACTION ISOLATION LEVEL

In terms of the SQL:1992 transaction isolation levels, the InnoDB default is REPEATABLE READ. Starting from MySQL 4.0.5, InnoDB offers all four different transaction isolation levels described by the SQL standard. You can set the default isolation level for all connections by using the --transaction-isolation option on the command line or in option files. For example, you can set the option in the [mysqld] section of `my.cnf' like this:

[mysqld]
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
                         | REPEATABLE-READ | SERIALIZABLE}

A user can change the isolation level of a single session or all new incoming connections with the SET TRANSACTION statement. Its syntax is as follows:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                       {READ UNCOMMITTED | READ COMMITTED
                        | REPEATABLE READ | SERIALIZABLE}

Note that there are hyphens in the level names for the --transaction-isolation option, but not for the SET TRANSACTION statement.

The default behavior is to set the isolation level for the next (not started) transaction. If you use the GLOBAL keyword, the statement sets the default transaction level globally for all new connections created from that point on (but not existing connections). You need the SUPER privilege to do this. Using the SESSION keyword sets the default transaction level for all future transactions performed on the current connection.

Any client is free to change the session isolation level (even in the middle of a transaction), or the isolation level for the next transaction.

Before MySQL 3.23.50, SET TRANSACTION had no effect on InnoDB tables. Before 4.0.5, only REPEATABLE READ and SERIALIZABLE were available.

You can query the global and session transaction isolation levels with these statements:

SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

In row level locking, InnoDB uses so-called next-key locking. That means that besides index records, InnoDB can also lock the ``gap'' before an index record to block insertions by other users immediately before the index record. A next-key lock refers to a lock that locks an index record and the gap before it. A gap lock refers to a lock that only locks a gap before some index record.

A detailed description of each isolation level in InnoDB:

  • READ UNCOMMITTED SELECT statements are performed in a non-locking fashion, but a possible earlier version of a record might be used. Thus, using this isolation level, such reads are not ``consistent.'' This is also called ``dirty read''. Other than that, this isolation level works like READ COMMITTED.
  • READ COMMITTED A somewhat Oracle-like isolation level. All SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE statements lock only the index records, not the gaps before them, and thus allow free inserting of new records next to locked records. UPDATE and DELETE statements that use a unique index with a unique search condition lock only the index record found, not the gap before it. But still in range type UPDATE and DELETE statements, InnoDB must set next-key or gap locks and block insertions by other users to the gaps covered by the range. This is necessary because ``phantom rows'' must be blocked for MySQL replication and recovery to work. Consistent reads behave as in Oracle: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See section 16.11.3 Consistent Non-Locking Read.
  • REPEATABLE READ This is the default isolation level of InnoDB. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, and DELETE statements that use a unique index with a unique search condition lock only the index record found, not the gap before it. With other search conditions, these operations employ next-key locking, locking the index range scanned with next-key or gap locks, and block new insertions by other users. In consistent reads, there is an important difference from the previous isolation level: In this level all consistent reads within the same transaction read the same snapshot established by the first read. This convention means that if you issue several plain SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See section 16.11.3 Consistent Non-Locking Read.
  • SERIALIZABLE This level is like REPEATABLE READ, but all plain SELECT statements are implicitly converted to SELECT ... LOCK IN SHARE MODE.

16.11.3 Consistent Non-Locking Read

A consistent read means that InnoDB uses its multi-versioning 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 that issues the query.

If you are running with the default REPEATABLE READ isolation level, then all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. 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 in READ COMMITTED and REPEATABLE READ isolation levels. 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.

16.11.4 Locking Reads SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE

In some circumstances a consistent read is not convenient. 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. The following example shows how to implement referential integrity in your application code.

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 deletes the parent row from the table parent, without you being aware of it.

The solution is to perform the SELECT in a locking mode using LOCK IN SHARE MODE:

SELECT * FROM parent WHERE NAME = 'Jones' 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. A shared mode lock prevents others from updating or deleting the row we have read. Also, if the latest data belongs to a yet uncommitted transaction of another client connection, we will wait until that transaction commits. After we see that the preceding query returns the parent 'Jones', we can safely add the child record to the child table and commit our transaction.

Let us look at another example: We have an integer counter field in a table child_codes that we use to assign a unique identifier to each child added 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 duplicate-key error will occur if two users attempt to add children with the same identifier to the table.

Here, LOCK IN SHARE MODE is not a good solution because if two users read the counter at the same time, at least one of them will end up in deadlock when attempting to update the counter.

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. The latter approach can be implemented as follows:

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

A SELECT ... FOR UPDATE reads 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.

Please note that the above is merely an example of how SELECT ... FOR UPDATE works. In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:

UPDATE child_codes
       SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

The SELECT statement merely retrieves the identifier information (specific to the current connection). It does not access any table.

16.11.5 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 in such a way that when it searches or scans an index of a table, it sets shared or exclusive locks on the index records it encounters. Thus the row level locks are actually 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, 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 you want to read and lock all children from the child table with an identifier value larger than 100, with the intent of updating some column in the selected rows later:

SELECT * FROM child WHERE id > 100 FOR UPDATE;

Suppose there is an index on the id column. The 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 row might meanwhile be inserted to the table. If you now execute the same SELECT within the same transaction, you would see a new row in the result set returned by the query. This is contrary 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, the new ``phantom'' child would violate 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 prevent any insert to the table where id would be bigger than 100.

You can use 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.

16.11.6 An Example of How the Consistent Read Works in InnoDB

Suppose you are running on the default REPEATABLE READ isolation level. When you issue a consistent read, that is, an ordinary SELECT statement, InnoDB will give your transaction a timepoint according to which your query sees the database. If another transaction deletes a row and commits after your timepoint was assigned, you will not see the row as having been deleted. Inserts and updates are treated similarly.

You can advance your timepoint by committing your transaction and then doing another SELECT.

This is called multi-versioned concurrency control.

                  User A                 User B

              SET AUTOCOMMIT=0;      SET AUTOCOMMIT=0;
time
|             SELECT * FROM t;
|             empty set
|                                    INSERT INTO t VALUES (1, 2);
|
v             SELECT * FROM t;
              empty set
                                     COMMIT;

              SELECT * FROM t;
              empty set

              COMMIT;

              SELECT * FROM t;
              ---------------------
              |    1    |    2    |
              ---------------------
              1 row in set

In this example, user A sees the row inserted by B only when B has committed the insert and A has committed as well, so that the timepoint is advanced past the commit of B.

If you want to see the ``freshest'' state of the database, you should use either the READ COMMITTED isolation level or a locking read:

SELECT * FROM t LOCK IN SHARE MODE;

16.11.7 Locks Set by Different SQL Statements in InnoDB

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL query. It does not matter if there are WHERE conditions in the query that would exclude the row from the result set of the query. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. The record locks are normally next-key locks that also block inserts to the ``gap'' immediately before the record.

If the locks to be set are exclusive, then InnoDB always retrieves also the clustered index record and sets a lock on it.

If you do not have indexes suitable for your query and MySQL has to scan the whole table to process the query, every row of the table will become locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily need to scan many rows.

  • SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, this sets shared next-key locks on the index records it encounters.
  • SELECT ... FROM ... LOCK IN SHARE MODE sets shared next-key locks on all index records the read encounters.
  • SELECT ... FROM ... FOR UPDATE sets exclusive next-key locks on all index records the read encounters.
  • INSERT INTO ... VALUES (...) sets an exclusive lock on the inserted row. Note that this lock is not a next-key lock and does not prevent other users from inserting to the gap before the inserted row. If a duplicate-key error occurs, a shared lock on the duplicate index record is set.
  • While initializing a previously specified AUTO_INCREMENT column on a table, InnoDB sets an exclusive lock on the end of the index associated with the AUTO_INCREMENT column. In accessing the auto-increment counter, InnoDB uses a specific table lock mode AUTO-INC where the lock lasts only to the end of the current SQL statement, instead of to the end of the whole transaction. See section 16.11.1 InnoDB and AUTOCOMMIT. Before MySQL 3.23.50, SHOW TABLE STATUS applied to a table with an AUTO_INCREMENT column sets an exclusive row level lock to the high end of the AUTO_INCREMENT index. This means also that SHOW TABLE STATUS could cause a deadlock of transactions, something that may surprise users. Starting from MySQL 3.23.50, InnoDB fetches the value of a previously initialized AUTO_INCREMENT column without setting any locks.
  • INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive (non-next-key) lock on each row inserted into T. It does the search on S as a consistent read, but sets shared next-key locks on S if MySQL binary logging is turned on. InnoDB has to set locks in the latter case: In roll-forward recovery from a backup, every SQL statement has to be executed in exactly the same way it was done originally.
  • CREATE TABLE ... SELECT ... performs the SELECT as a consistent read or with shared locks, as in the previous item.
  • REPLACE is done like an insert if there is no collision on a unique key. Otherwise, an exclusive next-key lock is placed on the row that has to be updated.
  • UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters.
  • DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.
  • If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires checking of the constraint condition sets shared record level locks on the records it looks at to check the constraint. InnoDB also sets these locks in the case where the constraint fails.
  • LOCK TABLES sets table locks, but it is the higher MySQL layer above the InnoDB layer that sets these locks. The automatic deadlock detection of InnoDB cannot detect deadlocks where such table locks are involved. See section 16.11.9 Deadlock Detection and Rollback. Also, since the higher MySQL layer does know about row level locks, it is possible that you get a table lock on a table where another user currently has row level locks. But that does not put transaction integrity in danger. See section 16.17 Restrictions on InnoDB Tables.

16.11.8 When Does MySQL Implicitly Commit or Rollback a Transaction?

  • MySQL begins each client connection with autocommit mode enabled by default. When autocommit enabled, MySQL does a commit after each SQL statement if that statement did not return an error.
  • If an error is returned by an SQL statement, the commit/rollback behavior depends on the error. See section 16.16 Error Handling.
  • The following SQL statements cause an implicit commit of the current transaction in MySQL:
    • CREATE TABLE (commits only if before MySQL 4.0.13 and MySQL binary logging is used).
    • ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE, DROP INDEX, DROP TABLE, LOAD MASTER DATA, LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE, UNLOCK TABLES.
    • The CREATE TABLE statement in InnoDB is processed as a single transaction. It means that a ROLLBACK from the user does not undo CREATE TABLE statements the user made during that transaction.
  • If you have the AUTOCOMMIT mode off and close a connection without calling an explicit COMMIT of your transaction, then MySQL will roll back your transaction.

16.11.9 Deadlock Detection and Rollback

InnoDB automatically detects a deadlock of transactions and rolls back a transaction or transactions to prevent the deadlock. Starting from MySQL 4.0.5, InnoDB tries to pick small transactions to roll back. The size of a transaction is determined by the number of rows it has inserted, updated, or deleted. Prior to 4.0.5, InnoDB always rolled 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 table lock set by a MySQL LOCK TABLES statement is involved, or if a lock set by another storage engine than InnoDB is involved. You have to resolve these situations by setting the value of the innodb_lock_wait_timeout system variable.

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 afterward know which was set by which SQL statement.

16.11.10 How to Cope With Deadlocks

Deadlocks are a classic problem in transactional databases, but they are not dangerous unless they are so frequent that you cannot run certain transactions at all. Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.

InnoDB uses automatic row level locking. You can get deadlocks even in the case of transactions that just insert or delete a single row. That is because these operations are not really ``atomic;'' they automatically set locks on the (possibly several) index records of the row inserted or deleted.

You can cope with deadlocks and reduce the likelihood of their occurrance with the following techniques:

  • Use SHOW INNODB STATUS to determine the cause of the latest deadlock. That can help you to tune your application to avoid deadlocks. This strategy can be used as of MySQL 3.23.52 and 4.0.3, depending on your MySQL series.
  • Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.
  • Commit your transactions often. Small transactions are less prone to collide.
  • If you are using locking reads (SELECT ... FOR UPDATE or ... LOCK IN SHARE MODE), try using a lower isolation level such as READ COMMITTED.
  • Access your tables and rows in a fixed order. Then transactions form nice queues and do not deadlock.
  • Add well-chosen indexes to your tables. Then your queries need to scan fewer index records and consequently set fewer locks. Use EXPLAIN SELECT to determine which indexes the MySQL server regards as the most appropriate for your queries.
  • Use less locking. If you can afford to allow a SELECT to return data from an old snapshot, do not add the clause FOR UPDATE or LOCK IN SHARE MODE to it. Using READ COMMITTED isolation level is good here, because each consistent read within the same transaction reads from its own fresh snapshot.
  • If nothing helps, serialize your transactions with table level locks. For example, if you need to write table t1 and read table t2, you can do this:
    LOCK TABLES t1 WRITE, t2 READ, ...;
    [do something with tables t1 and t2 here];
    UNLOCK TABLES;
    
    Table level locks make your transactions queue nicely, and deadlocks are avoided. Note that LOCK TABLES implicitly starts a transaction, just like the statement BEGIN, and UNLOCK TABLES implicitly ends the transaction in a COMMIT.
  • Another way to serialize transactions is to create an auxiliary ``semaphore'' table that contains just a single row. Have each transaction update that row before accessing other tables. In that way, all transactions happen in a serial fashion. Note that the InnoDB instant deadlock detection algorithm also works in this case, because the serializing lock is a row level lock. With MySQL table level locks, the timeout method must be used to resolve deadlocks.

16.12 InnoDB Performance Tuning Tips

  • If the Unix `top' tool or the Windows `Task Manager' shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound. Maybe you are making too many transaction commits, or the buffer pool is too small. Making the buffer pool bigger can help, but do not set it bigger than 80% of physical memory.
  • Wrap several modifications into one transaction. InnoDB must flush the log to disk at each transaction commit, if that transaction made modifications to the database. Since the rotation speed of a disk is typically at most 167 revolutions/second, that constrains the number of commits to the same 167/second if the disk does not fool the operating system.
  • If you can afford the loss of some latest committed transactions, you can set the `my.cnf' parameter innodb_flush_log_at_trx_commit to 0. InnoDB tries to flush the log once per second anyway, though the flush is not guaranteed.
  • Make your log files big, even as big as the buffer pool. When InnoDB has written the log files full, it has to write the modified contents of the buffer pool to disk in a checkpoint. Small log files will cause many unnecessary disk writes. The drawback of big log files is that recovery time will be longer.
  • Make the log buffer quite big as well (say, 8MB).
  • Use the VARCHAR column type instead of CHAR if you are storing variable-length strings or if the column may contain many NULL values. A CHAR(N) column always takes N bytes to store data, even if the string is shorter, or its value is NULL. Smaller tables fit better in the buffer pool and reduce disk I/O.
  • (Relevant from 3.23.39 up.) In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() and other similar methods is surprisingly slow. The default method InnoDB uses is the fsync() function. If you are not satisfied with the database write performance, you might try setting innodb_flush_method in `my.cnf' to O_DSYNC, though O_DSYNC seems to be slower on most systems.
  • When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled. Then every insert requires a log flush to disk. To disable autocommit during your import operation, surround it with SET AUTOCOMMIT and COMMIT statements:
    SET AUTOCOMMIT=0;
    /* SQL import statements ... */
    COMMIT;
    
    If you use the mysqldump option --opt, you will get dump files that are fast to import into to an InnoDB table, even without wrapping them with the SET AUTOCOMMIT and COMMIT statements.
  • Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer to save disk I/O in inserts, but no such mechanism is used in a corresponding rollback. A disk-bound rollback can take 30 times the time of the corresponding insert. Killing the database process will not help because the rollback will start again at the server startup. The only way to get rid of a runaway rollback is to increase the buffer pool so that the rollback becomes CPU-bound and runs fast, or to use a special procedure. See section 16.9.1 Forcing Recovery.
  • Beware also of other big disk-bound operations. Use DROP TABLE or TRUNCATE TABLE (from MySQL 4.0 up) to empty a table, not DELETE FROM tbl_name.
  • Use the multiple-row INSERT syntax to reduce communication overhead between the client and the server if you need to insert many rows:
    INSERT INTO yourtable VALUES (1,2), (5,5), ...;
    
    This tip is valid for inserts into any table type, not just InnoDB.
  • If you have UNIQUE constraints on secondary keys, starting from MySQL 3.23.52 and 4.0.3, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:
    SET UNIQUE_CHECKS=0;
    
    For big tables, this saves a lot of disk I/O because InnoDB can then use its insert buffer to write secondary index records in a batch.
  • If you have FOREIGN KEY constraints in your tables, starting from MySQL 3.23.52 and 4.0.3 you can speed up table imports by turning the foreign key checks off for a while in the import session:
    SET FOREIGN_KEY_CHECKS=0;
    
    For big tables, this can save a lot of disk I/O.
  • If you often have recurring queries to tables that are not updated frequently, use the query cache available as of MySQL 4.0:
    [mysqld]
    query_cache_type = ON
    query_cache_size = 10M
    
    In MySQL 4.0, the query cache works only with autocommit enabled. This restriction is removed in MySQL 4.1.1 and up.

16.12.1 SHOW INNODB STATUS and the InnoDB Monitors

Starting from MySQL 3.23.42, InnoDB includes InnoDB Monitors that print information about the InnoDB internal state. Starting from MySQL 3.23.52 and 4.0.3, you can use the SQL statement SHOW INNODB STATUS to fetch the output of the standard InnoDB Monitor to your SQL client. The information is useful in performance tuning. If you are using the mysql interactive SQL client, the output is more readable if you replace the usual semicolon statement terminator by \G:

mysql> SHOW INNODB STATUS\G

Another way to use InnoDB Monitors is to let them continuously write data to the standard output of the server mysqld. In this case, no output is sent to clients. When switched on, InnoDB Monitors print data about once every 15 seconds. Server output usually is directed to the `.err' log in the MySQL data directory. This data is useful in performance tuning. On Windows, you must start the server from a command prompt in a console window with the --console option if you want to direct the output to the window rather than to the error log.

Monitor information includes data of the following types:

  • Table and record locks held by each active transaction
  • Lock waits of a transactions
  • Semaphore waits of threads
  • Pending file I/O requests
  • Buffer pool statistics
  • Purge and insert buffer merge activity of the main InnoDB thread

To cause the standard InnoDB Monitor to write to the standard output of mysqld, use the following SQL statement:

CREATE TABLE innodb_monitor(a INT) ENGINE=InnoDB;

The monitor can be stopped by issuing the following statement:

DROP TABLE innodb_monitor;

The CREATE TABLE syntax is just a way to pass a command to the InnoDB engine through the MySQL SQL parser: The only things that matter are the table name innodb_monitor and that it be an InnoDB table. The structure of the table is not relevant at all for InnoDB Monitor. If you shut down the database when the monitor is running, and you want to start the monitor again, you have to drop the table before you can issue a new CREATE TABLE to start the monitor. This syntax may change in a future release.

In a similar way, you can start innodb_lock_monitor, which is otherwise the same as innodb_monitor but also prints a lot of lock information. A separate innodb_tablespace_monitor prints a list of created file segments existing in the tablespace and also validates the tablespace allocation data structures. Starting from 3.23.44, there is innodb_table_monitor with which you can print the contents of the internal data dictionary of InnoDB.

A sample of InnoDB Monitor output:

mysql> SHOW INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
030709 13:00:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 413452, signal count 378357
--Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the semaphore:
X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135
a writer (thread id 32782) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file btr0sea.c line 731
Last time write locked in file btr0sea.c line 1347
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits 375485
------------------------
LATEST FOREIGN KEY ERROR
------------------------
030709 13:00:59 Transaction:
TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 inser
ting
15 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
Foreign key constraint fails for table test/ibtest11a:
,
  CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`, `D`)
 ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index PRIMARY tuple:
 0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2: len 4;
 hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4: len 7; hex
 00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;;
But in parent table test/ibtest11b, in index PRIMARY,
the closest match we can find is record:
RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex 80000005; a
sc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex 0000111ef3eb; asc ......
;; 4: len 7; hex 800001001e0084; asc .......;; 5: len 3; hex 6b6864; asc khd;;
------------------------
LATEST DETECTED DEADLOCK
------------------------
030709 12:59:58
*** (1) TRANSACTION:
TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733 inser
ting
LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146
MySQL thread id 21, query id 4553379 localhost heikki update
INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t','e187358f'
,'g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),7
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290252780 lock mode S waiting
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a
a35818;; 1:
*** (2) TRANSACTION:
TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782 inser
ting
130 lock struct(s), heap size 11584, undo log entries 437
MySQL thread id 23, query id 4554396 localhost heikki update
REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','', NULL,'
h396', NULL, NULL, 7.31,7.31,7.31,200)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290251546 lock_mode X locks rec but not gap
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a
a35818;; 1:
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290251546 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230; asc aa
35720;; 1:
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 290328385
Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
Total number of lock structs in row lock hash table 70
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002
MySQL thread id 32, query id 4668737 localhost heikki
show innodb status
---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id 38929 in
serting
1 lock struct(s), heap size 320
MySQL thread id 29, query id 4668736 localhost heikki update
insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlhh
gghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh
---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id 28684 co
mmitting
1 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 19, query id 4668734 localhost heikki update
insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlh
hgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf
---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id 36880 st
arting index read
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 27, query id 4668644 localhost heikki Searching rows for update
update ibtest11a set B = 'kHdkkkk' where A = 89572
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index PRIM
ARY trx id 0 290328327 lock_mode X waiting
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc
 supremum.;;
------------------
---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 ro
llback of SQL statement
ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id 32782
58 lock struct(s), heap size 5504, undo log entries 159
MySQL thread id 23, query id 4668732 localhost heikki update
REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t','e200498f
','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),
---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id 30733 in
serting
4 lock struct(s), heap size 1024, undo log entries 165
MySQL thread id 21, query id 4668735 localhost heikki update
INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','', NULL,
'h321', NULL, NULL, 7.31,7.31,7.31,200)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs
25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 19, seg size 21,
85004 inserts, 85004 merged recs, 26669 merges
Hash table size 207619, used cells 14461, node heap has 16 buffer(s)
1877.67 hash searches/s, 5121.10 non-hash searches/s
---
LOG
---
Log sequence number 18 1212842764
Log flushed up to   18 1212665295
Last checkpoint at  18 1135877290
0 pending log writes, 0 pending chkp writes
4341 log i/o's done, 1.22 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 84966343; in additional pool allocated 1402624
Buffer pool size   3200
Free buffers       110
Database pages     3074
Modified db pages  2674
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 171380, created 51968, written 194688
28.72 reads/s, 20.72 creates/s, 47.55 writes/s
Buffer pool hit rate 999 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 3004, id 7176, state: purging
Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779
1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.05 sec)

Some notes on the output:

  • If the TRANSACTIONS section reports lock waits, your application may have lock contention. The output can also help to trace the reasons for transaction deadlocks.
  • The SEMAPHORES section reports threads waiting for a semaphore and statistics on how many times threads have needed a spin or a wait on a mutex or a rw-lock semaphore. A large number of threads waiting for semaphores may be a result of disk I/O, or contention problems inside InnoDB. Contention can be due to heavy parallelism of queries, or problems in operating system thread scheduling. Setting innodb_thread_concurrency smaller than the default value of 8 can help in such situations.
  • The BUFFER POOL AND MEMORY section gives you statistics on pages read and written. You can calculate from these numbers how many data file I/O operations your queries currently are doing.

16.13 Implementation of Multi-Versioning

Because InnoDB is a multi-versioned database, it must keep information about old versions of rows in the tablespace. This information is stored in a data structure called a rollback segment after an analogous data structure in Oracle.

Internally, InnoDB adds two fields to each row stored in the database. A 6-byte field tells the transaction identifier for the last transaction that inserted or updated the row. Also, a deletion is treated internally 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, 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, including those transactions that only issue consistent reads. 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 the InnoDB multi-versioning 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 can it 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 that did the deletion.

16.14 Table and Index Structures

MySQL stores its data dictionary information for tables in `.frm' files in database directories. This is true for all MySQL storage engines. But every InnoDB table also has its own entry in InnoDB internal data dictionaries inside the tablespace. When MySQL drops a table or a database, it has to delete both a `.frm' file or files, and the corresponding entries inside the InnoDB data dictionary. This is the reason why you cannot move InnoDB tables between databases simply by moving the `.frm' files. It is also the reason why DROP DATABASE did not work for InnoDB type tables before MySQL 3.23.44.

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, the index of the primary key will be the clustered index.

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no such index in the table, InnoDB internally generates a clustered index where the rows are ordered by the row ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that 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. If a table is large, the clustered index architecture often saves a disk I/O when compared to the traditional solution. (In many databases, the data is traditionally stored on a different page from the index record.)

In InnoDB, the records in non-clustered indexes (also called secondary indexes) 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 use more space.

InnoDB compares CHAR and VARCHAR strings of different lengths such that the remaining length in the shorter string is treated as if padded with spaces.

16.14.1 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 16KB. 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 order (ascending or descending), the resulting index pages will be about 15/16 full. If records are inserted in a random order, the pages will be from 1/2 to 15/16 full. If the fillfactor of an index page drops below 1/2, InnoDB tries to contract the index tree to free the page.

16.14.2 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 into secondary indexes happen in a relatively random order. This would cause a lot of random disk I/O operations without a special mechanism used in InnoDB.

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

Periodically, the insert buffer is merged into the secondary index trees in the database. Often it is possible to merge several insertions to the same page of the index tree, saving disk I/O operations. It has been measured that the insert buffer can speed up insertions into a table up to 15 times.

16.14.3 Adaptive Hash Indexes

If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes. InnoDB has an automatic mechanism that 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 built automatically.

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 the pattern of searches that InnoDB observes for 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 for those pages of the index that are often accessed.

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

16.14.4 Physical Record Structure

Records in InnoDB tables have the following characteristics:

  • Each index record in InnoDB contains a header of six bytes. The header is used to link consecutive records together, and also in row level locking.
  • Records in the clustered index contain fields for all user-defined columns. In addition, there is a six-byte field for the transaction ID and a seven-byte field for the roll pointer.
  • If no primary key was defined for a table, each clustered index record also contains a six-byte row ID field.
  • Each secondary index record contains also all the fields defined for the clustered index key.
  • A record contains also a pointer to each field of the record. If the total length of the fields in a record is less than 128 bytes, the pointer is one byte, else two bytes.
  • Internally, InnoDB stores fixed length character columns such as CHAR(10) in a fixed length format. InnoDB truncates trailing spaces from VARCHAR columns. Note that MySQL may internally convert CHAR columns to VARCHAR. See section 14.2.5.1 Silent Column Specification Changes.
  • An SQL NULL value reserves zero bytes if stored in a variable length column. In a fixed length column it reserves the fixed length of the column. The motivation behind reserving the fixed space for NULL values is that then an update of the column from NULL to a non-NULL value can be done in place and does not cause fragmentation of the index page.

16.15 File Space Management and Disk I/O

16.15.1 Disk I/O

InnoDB uses simulated asynchronous disk I/O: InnoDB creates a number of threads to take care of I/O operations, such as read-ahead.

There are two read-ahead heuristics in InnoDB:

  • In sequential read-ahead, if InnoDB notices that the access pattern to a segment in the tablespace is sequential, it posts in advance a batch of reads of database pages to the I/O system.
  • In random read-ahead, if InnoDB notices that some area in a tablespace seems to be in the process of being fully read into the buffer pool, it posts the remaining reads to the I/O system.

Starting from MySQL 3.23.40b, InnoDB uses a novel file flush technique called doublewrite. It adds safety to crash recovery after an operating system crash or a power outage, and improves performance on most Unix flavors by reducing the need for fsync() operations.

Doublewrite means that before writing pages to a datafile, InnoDB first writes them to a contiguous tablespace area called the doublewrite buffer. Only after the write and the flush to the doublewrite buffer has completed does InnoDB write the pages to their proper positions in the datafile. If the operating system crashes in the middle of a page write, InnoDB later will find a good copy of the page from the doublewrite buffer during recovery.

16.15.2 Using Raw Devices for the Tablespace

Starting from MySQL 3.23.41, you can use a raw disk partitions as tablespace datafiles. By using a raw disk you can perform non-buffered I/O on Windows and on some Unix systems without filesystem overhead, which might improve performance.

When you create a new datafile, you must put the keyword newraw immediately after the datafile size in innodb_data_file_path. The partition must be at least as large as the size that you specify. Note that 1MB in InnoDB is 1024 * 1024 bytes, whereas in disk specifications, 1MB usually means 1,000,000 bytes.

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

The next time you start the server, InnoDB notices the newraw keyword and initializes the new partition. However, do not create or change any InnoDB tables yet. Otherwise, when you next restart the server, InnoDB will reinitialize the partition and your changes will be lost. (Starting from 3.23.44, as a safety measure InnoDB prevents users from modifying data when any partition with newraw is specified.)

After InnoDB has initialized the new partition, stop the server, change newraw in the datafile specification to raw:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw

Then restart the server and InnoDB will allow changes to be made.

On Windows, starting from 4.1.1, you can allocate a disk partition as a datafile like this:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=//./D::10Gnewraw

When you use raw disk partitions, be sure they have permissions that allow read and write access by the account used for running the MySQL server.

16.15.3 File Space Management

The datafiles you define in the configuration file form the tablespace of InnoDB. The files are simply concatenated to form the tablespace. There is no striping in use. Currently you cannot define where in the tablespace your tables will be allocated. However, in a newly created tablespace, InnoDB will allocate space starting from the first datafile.

The tablespace consists of database pages with a default size of 16KB. 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 confusing because it actually contains many segments in the tablespace.

Two segments are allocated for each index in InnoDB. 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 ask for available free space in the tablespace by issuing a SHOW TABLE STATUS, InnoDB reports the extents that are definitely free in 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 whether that frees individual pages or extents to the tablespace, so that the freed space becomes 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 will be physically removed only in an (automatic) purge operation after they are no longer needed in transaction rollback or consistent read.

16.15.4 Defragmenting a Table

If there are random insertions into or deletions from the indexes of a table, the indexes may become fragmented. Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages, or that there are many unused pages in the 64-page blocks that were allocated to the index.

It can speed up index scans if you periodically perform a ``null'' ALTER TABLE operation:

ALTER TABLE tbl_name TYPE=InnoDB

That causes MySQL to rebuild the table. Another way to perform a defragmention operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file.

If the insertions to an index are always ascending and records are deleted only from the end, the InnoDB file space management algorithm guarantees that fragmentation in the index will not occur.

16.16 Error Handling

Error handling in InnoDB is not always the same as specified in the SQL standard. According to the standard, any error during an SQL statement should cause the rollback of that statement. InnoDB sometimes rolls back only part of the statement, or the whole transaction. The following items describe how InnoDB performs error handling:

  • If you run out of file space in the tablespace, you will get the MySQL Table is full error and InnoDB rolls back the SQL statement.
  • A transaction deadlock or a timeout in a lock wait causes InnoDB to roll back the whole transaction.
  • A duplicate-key error rolls back only the insert of that particular row, even in a statement like INSERT INTO ... SELECT. This will probably change so that the SQL statement will be rolled back if you have not specified the IGNORE option in your statement.
  • A ``row too long'' error rolls back the SQL statement.
  • Other errors are mostly detected by the MySQL layer of code (above the InnoDB storage engine level), and they roll back the corresponding SQL statement.

16.16.1 InnoDB Error Codes

The following is a non-exhaustive list about common InnoDB-specific errors that you may encounter, with information about why they occur and how to resolve the problem.

1005 (ER_CANT_CREATE_TABLE)
Cannot create table. If the error message string refers to errno 150, then table creation failed because a foreign key constraint was not correctly formed.
1016 (ER_CANT_OPEN_FILE)
Cannot find the InnoDB table from the InnoDB datafiles though the `.frm' file for the table exists. See section 16.18.1 Troubleshooting InnoDB Data Dictionary Operations.
1114 (ER_RECORD_FILE_FULL)
InnoDB has run out of free space in the tablespace. You should reconfigure the tablespace to add a new datafile.
1205 (ER_LOCK_WAIT_TIMEOUT)
Lock wait timeout expired. Transaction was rolled back.
1213 (ER_LOCK_DEADLOCK)
Transaction deadlock. You should rerun the transaction.
1216 (ER_NO_REFERENCED_ROW)
You are trying to add a row but there is no parent row, and a foreign key constraint fails. You should add the parent row first.
1217 (ER_ROW_IS_REFERENCED)
You are trying to delete a parent row that has children, and a foreign key constraint fails. You should delete the children first.

16.16.2 Operating System Error Codes

In Unix, to print the meaning of an operating system error number, use the perror program that comes with the MySQL distribution.

The following table provides a list of some common Linux system error codes. For a more complete list, see Linux source code.

1 (EPERM)
Operation not permitted
2 (ENOENT)
No such file or directory
3 (ESRCH)
No such process
4 (EINTR)
Interrupted system call
5 (EIO)
I/O error
6 (ENXIO)
No such device or address
7 (E2BIG)
Arg list too long
8 (ENOEXEC)
Exec format error
9 (EBADF)
Bad file number
10 (ECHILD)
No child processes
11 (EAGAIN)
Try again
12 (ENOMEM)
Out of memory
13 (EACCES)
Permission denied
14 (EFAULT)
Bad address
15 (ENOTBLK)
Block device required
16 (EBUSY)
Device or resource busy
17 (EEXIST)
File exists
18 (EXDEV)
Cross-device link
19 (ENODEV)
No such device
20 (ENOTDIR)
Not a directory
21 (EISDIR)
Is a directory
22 (EINVAL)
Invalid argument
23 (ENFILE)
File table overflow
24 (EMFILE)
Too many open files
25 (ENOTTY)
Inappropriate ioctl for device
26 (ETXTBSY)
Text file busy
27 (EFBIG)
File too large
28 (ENOSPC)
No space left on device
29 (ESPIPE)
Illegal seek
30 (EROFS)
Read-only file system
31 (EMLINK)
Too many links

The following table provides a list of some common Windows system error codes. For a complete list see the Microsoft website.

1 (ERROR_INVALID_FUNCTION)
Incorrect function.
2 (ERROR_FILE_NOT_FOUND)
The system cannot find the file specified.
3 (ERROR_PATH_NOT_FOUND)
The system cannot find the path specified.
4 (ERROR_TOO_MANY_OPEN_FILES)
The system cannot open the file.
5 (ERROR_ACCESS_DENIED)
Access is denied.
6 (ERROR_INVALID_HANDLE)
The handle is invalid.
7 (ERROR_ARENA_TRASHED)
The storage control blocks were destroyed.
8 (ERROR_NOT_ENOUGH_MEMORY)
Not enough storage is available to process this command.
9 (ERROR_INVALID_BLOCK)
The storage control block address is invalid.
10 (ERROR_BAD_ENVIRONMENT)
The environment is incorrect.
11 (ERROR_BAD_FORMAT)
An attempt was made to load a program with an incorrect format.
12 (ERROR_INVALID_ACCESS)
The access code is invalid.
13 (ERROR_INVALID_DATA)
The data is invalid.
14 (ERROR_OUTOFMEMORY)
Not enough storage is available to complete this operation.
15 (ERROR_INVALID_DRIVE)
The system cannot find the drive specified.
16 (ERROR_CURRENT_DIRECTORY)
The directory cannot be removed.
17 (ERROR_NOT_SAME_DEVICE)
The system cannot move the file to a different disk drive.
18 (ERROR_NO_MORE_FILES)
There are no more files.
19 (ERROR_WRITE_PROTECT)
The media is write protected.
20 (ERROR_BAD_UNIT)
The system cannot find the device specified.
21 (ERROR_NOT_READY)
The device is not ready.
22 (ERROR_BAD_COMMAND)
The device does not recognize the command.
23 (ERROR_CRC)
Data error (cyclic redundancy check).
24 (ERROR_BAD_LENGTH)
The program issued a command but the command length is incorrect.
25 (ERROR_SEEK)
The drive cannot locate a specific area or track on the disk.
26 (ERROR_NOT_DOS_DISK)
The specified disk or diskette cannot be accessed.
27 (ERROR_SECTOR_NOT_FOUND)
The drive cannot find the sector requested.
28 (ERROR_OUT_OF_PAPER)
The printer is out of paper.
29 (ERROR_WRITE_FAULT)
The system cannot write to the specified device.
30 (ERROR_READ_FAULT)
The system cannot read from the specified device.
31 (ERROR_GEN_FAILURE)
A device attached to the system is not functioning.
32 (ERROR_SHARING_VIOLATION)
The process cannot access the file because it is being used by another process.
33 (ERROR_LOCK_VIOLATION)
The process cannot access the file because another process has locked a portion of the file.
34 (ERROR_WRONG_DISK)
The wrong diskette is in the drive. Insert %2 (Volume Serial Number: %3) into drive %1.
36 (ERROR_SHARING_BUFFER_EXCEEDED)
Too many files opened for sharing.
38 (ERROR_HANDLE_EOF)
Reached the end of the file.
39 (ERROR_HANDLE_DISK_FULL)
The disk is full.
112 (ERROR_DISK_FULL)
The disk is full.
123 (ERROR_INVALID_NAME)
The filename, directory name, or volume label syntax is incorrect.
1450 (ERROR_NO_SYSTEM_RESOURCES)
Insufficient system resources exist to complete the requested service.

16.17 Restrictions on InnoDB Tables

  • A table cannot contain more than 1000 columns.
  • The maximum key length is 1024 bytes.
  • The maximum row length, except for BLOB and TEXT columns, is slightly less than half of a database page, that is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including also BLOB and TEXT columns, must be less than 4GB. InnoDB stores the first 512 bytes of a BLOB or TEXT column in the row, and the rest into separate pages.
  • On some operating systems, datafiles must be less than 2GB. The combined size of the InnoDB log files must be less than 4GB.
  • The maximum tablespace size is four billion database pages (64TB). This is also the maximum size for a table. The minimum tablespace size is 10MB.
  • InnoDB tables do not support FULLTEXT indexes.
  • On Windows, InnoDB always stores database and table names internally in lowercase. To move databases in binary format from Unix to Windows or from Windows to Unix, you should have all database and table names in lowercase.
  • Warning: Do not convert MySQL system tables from MyISAM to InnoDB tables! This is an unsupported operation. If you do this, MySQL will not restart until you restore the old system tables from a backup or re-generate them with the mysql_install_db script.
  • InnoDB does not keep an internal count of rows in a table. (This would actually be somewhat complicated because of multi-versioning.) To process a SELECT COUNT(*) FROM T statement, InnoDB must scan an index of the table, which will take some time if the table is not entirely in the buffer pool. To get a fast count you have to use a counter table you create yourself, and let your application update it according to the inserts and deletes it does. If your table does not change often, using the MySQL query cache is a good solution. SHOW TABLE STATUS also can be used if an approximate row count is sufficient. See section 16.12 InnoDB Performance Tuning Tips.
  • For an AUTO_INCREMENT column, you must always define an index for the table, and that index must contain just the AUTO_INCREMENT column. In MyISAM tables, the AUTO_INCREMENT column may be part of a multi-column index.
  • InnoDB does not support the AUTO_INCREMENT table option for setting the initial sequence value in a CREATE TABLE or ALTER TABLE statement. To set the value with InnoDB, insert a dummy row with a value one less and delete that dummy row, or insert the first row with an explicit value specified.
  • When you restart the MySQL server, InnoDB may reuse an old value for an AUTO_INCREMENT column (that is, a value that was assigned to an old transaction that was rolled back).
  • When an AUTO_INCREMENT column runs out of values, InnoDB wraps a BIGINT to -9223372036854775808 and BIGINT UNSIGNED to 1. However, BIGINT values have 64 bits, so do note that if you were to insert one million rows per second, it would still take about a million years before BIGINT reaches its upper bound. With all other integer type columns, a duplicate-key error will result. This is similar to how MyISAM works, as it is mostly general MySQL behavior and not about any storage engine in particular.
  • DELETE FROM tbl_name does not regenerate the table but instead deletes all rows, one by one.
  • TRUNCATE tbl_name is mapped to DELETE FROM tbl_name for InnoDB and doesn't reset the AUTO_INCREMENT counter.
  • SHOW TABLE STATUS does not give accurate statistics on InnoDB tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization.
  • If you try to create a unique index on a prefix of a column you will get an error:
    CREATE TABLE T (A CHAR(20), B INT, UNIQUE (A(5))) TYPE = InnoDB;
    
    If you create a non-unique index on a prefix of a column, InnoDB will create an index over the whole column. These restrictions are removed starting from 4.0.14 and 4.1.1.
  • INSERT DELAYED is not supported for InnoDB tables.
  • The MySQL LOCK TABLES operation does not know about InnoDB row level locks set by already completed SQL statements. This means that you can get a table lock on a table even if there still exist transactions by other users that have row level locks on the same table. Thus your operations on the table may have to wait if they collide with these locks of other users. Also a deadlock is possible. However, this does not endanger transaction integrity, because the row level locks set by InnoDB will always take care of the integrity. Also, a table lock prevents other transactions from acquiring more row level locks (in a conflicting lock mode) on the table.
  • Before MySQL 3.23.52, replication always ran with autocommit enabled. Therefore consistent reads in the slave would also see partially processed transactions, and thus the read would not be really consistent in the slave. This restriction was removed in MySQL 3.23.52.
  • The LOAD TABLE FROM MASTER statement for setting up replication slave servers does not yet work for InnoDB tables. A workaround is to alter the table to MyISAM in the master, do then the load, and after that alter the master table back to InnoDB.
  • The default database page size in InnoDB is 16KB. By recompiling the code, you can set it to values ranging from 8KB to 64KB. You have to update the values of UNIV_PAGE_SIZE and UNIV_PAGE_SIZE_SHIFT in the `univ.i' source file.

16.18 InnoDB Troubleshooting

  • A general rule is that when an operation fails or you suspect a bug, you should look at the MySQL server error log, which typically has a name something like `hostname.err', or on Windows `mysql.err'.
  • When doing troubleshooting, it is usually best to run the MySQL server from the command prompt, not through the `safe_mysqld' wrapper or as a Windows service. You will then see what mysqld prints to the command prompt window, and you have a better grasp of what is going on. On Windows, you must start the server with the `--console' option to direct the output to the console window.
  • Use the InnoDB Monitors to obtain information about a problem. If the problem is performance-related, or your server appears to be hung, you should use innodb_monitor to print information about the internal state of InnoDB. If the problem is with locks, use innodb_lock_monitor. If the problem is in creation of tables or other data dictionary operations, use innodb_table_monitor to print the contents of the InnoDB internal data dictionary.
  • If you suspect a table is corrupt, run CHECK TABLE on that table.

16.18.1 Troubleshooting InnoDB Data Dictionary Operations

A specific issue with tables is that the MySQL server keeps data dictionary information in `.frm' files it stores in the database directories, while InnoDB also stores the information into its own data dictionary inside the tablespace files. If you move `.frm' files around, or use DROP DATABASE in MySQL versions before 3.23.44, or the server crashes in the middle of a data dictionary operation, the `.frm' files may end up out of sync with the InnoDB internal data dictionary.

A symptom of an out-of-sync data dictionary is that a CREATE TABLE statement fails. If this occurs, you should look in the server's error log. If the log says that the table already exists inside the InnoDB internal data dictionary, you have an orphaned table inside the InnoDB tablespace files that has no corresponding `.frm' file. The error message looks like this:

InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

You can drop the orphaned table by following the instructions given in the error message.

Another symptom of an out-of-sync data dictionary is that MySQL prints an error that it cannot open an `.InnoDB' file:

ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)

In the error log you will find a message like this:

InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?

This means that there is an orphaned `.frm' file without a corresponding table inside InnoDB. You can drop the orphaned `.frm' file by deleting it manually.

If MySQL crashes in the middle of an ALTER TABLE operation, you may end up with an orphaned temporary table inside the InnoDB tablespace. With innodb_table_monitor you see a table whose name is `#sql...', but since MySQL does not allow accessing any table with such a name, you cannot dump or drop it. The solution is to use a special mechanism available starting from version 3.23.48 of InnoDB.

When you have an orphaned table `#sql_id' inside the tablespace, you can cause InnoDB to rename it to `rsql_id_recover_innodb_tmp_table' with the following statement:

CREATE TABLE `rsql_id_recover_innodb_tmp_table`(...) ENGINE=InnoDB;

The backquotes around the table name are needed because a temporary table name contains the character `-'.

The table definition must be similar to that of the temporary table. If you do not know the definition of the temporary table, you can use an arbitrary definition in the preceding CREATE TABLE statement, and after that replace the file `rsql_id.frm' by the file `#sql_id.frm' of the temporary table. Note that to copy or rename a file in the shell, you need to put the file name in double quotes if the file name contains ``#''. Then you can dump and drop the renamed table.


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