InnoDB
Storage EngineInnoDB
OverviewInnoDB
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 little 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 9.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 must purchase a commercial MySQL Pro license from https://order.mysql.com/?sub=pg&pg_no=1.
Contact information for Innobase Oy, producer of the InnoDB
engine:
Web site: http://www.innodb.com/
Email: [email protected]
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
Beginning with MySQL 4.0, InnoDB
is enabled by default, so 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 4.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, to get best performance, it is still recommended that you use parameters appropriate for your system, as discussed in Section 9.4, “InnoDB
Configuration.”)
In MySQL 3.23, you must specify at the minimum an innodb_data_file_path
value to configure the InnoDB
data files. For example, to configure InnoDB
to use a single 10MB auto-extending data file, 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 an innodb_data_home_dir
setting. See Section 9.4, “InnoDB
Configuration.”
To enable InnoDB
tables in MySQL 3.23, see Section 9.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 data files and its log files.
If you specify no InnoDB
configuration options, MySQL 4.0 and above creates an auto-extending 10MB data file 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 data file 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 data file specifications. If you name more than one data file, 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 data file 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 data file 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 data file 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 data file 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 data file, InnoDB
extends the data file 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 data file on another disk. Instructions for reconfiguring an existing tablespace are given in Section 9.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 data file, 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 data file by textually concatenating the value of innodb_data_home_dir
to the data file 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 data files 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 that 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
data files and log files on several disks. It creates an auto-extending data file 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
# ...
# Data files must be able to hold your data and indexes.
# Make sure that 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 data files or log files.
Note that data files must be less than 2GB in some filesystems. The combined size of the log files must be less than 4GB. The combined size of data files 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:mysqlin
, you can start it like this:
C:> C:mysqlinmysqld-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 9.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.
DATADIR
/my.cnf
Server-specific options.
defaults-extra-file
The file specified with the --defaults-extra-file
option.
~/.my.cnf
User-specific options.
DATADIR
represents 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 that 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 =
#
# Data files must be able to hold your data and indexes
# Enter the entire innodb_data_file_path value on a single line
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 data files on different disks. InnoDB
will fill the tablespace beginning with the first data file. 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
data files, which may speed up I/O. See Section 9.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_size + 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 9.5, “InnoDB
Startup Options.”
How to tune other mysqld
server parameters?The following values are typical and suit most users:
[mysqld]
skip-external-locking
set-variable = max_connections=200
set-variable = read_buffer_size=1M
set-variable = sort_buffer_size=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=...
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 MySQL 4.1.0 and relevant only in 32-bit Windows. If your 32-bit Windows operating system supports more than 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 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’s physical memory size. However, do not set it too large because competition for the physical memory might cause paging in the operating system.
The paths to individual data files and their sizes. The full directory path to each data file is acquired by concatenating innodb_data_home_dir
to 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 data file 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 data files. See Section 9.15.2, “Using Raw Devices for the Tablespace.”
innodb_data_home_dir
The common part of the directory path for all InnoDB
data files. 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 even hours in extreme cases. 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 disk I/O on Windows 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 9.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 is 0).
innodb_flush_method
This option is relevant only 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 data files. If O_DIRECT
is specified (available on some GNU/Linux versions starting from MySQL 4.0.14), InnoDB
uses O_DIRECT
to open the data files, 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 9.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. 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.
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 named 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 have 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 the value higher to better utilize the resources of your 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.
Suppose that 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
data files 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 mysqld_safe
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 data files and log files. InnoDB
will print something like the following:
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 data file 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 data files and log files will already have been created, so the output will be much briefer:
InnoDB: Started
mysqld: ready for connections
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
data file 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 data file 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 data files, all log files, and 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.
Suppose that you have started the MySQL client with the command mysql test
. To create an InnoDB
table, you must specify an ENGINE = InnoDB
or TYPE = InnoDB
option 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 data files 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.
By default, each client that 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 roll back your transaction. If you want to leave 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.
shell> 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, you can send transaction control statements such as 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.
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 as InnoDB
tables, 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 might be good to insert big tables in pieces:
INSERT INTO newtable SELECT * FROM oldtable
WHERE yourkey > something AND yourkey <= somethingelse;
After all records have 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.
Make sure that 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 9.9.1, “Forcing Recovery.”
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.
Starting from MySQL 3.23.44b, 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 | SET DEFAULT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT | SET DEFAULT}]
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-prefixed columns 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 data types 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. SET DEFAULT
is parsed but ignored.
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
) TYPE=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)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
PRIMARY KEY (id)) TYPE=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)) TYPE=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 | SET DEFAULT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT | SET DEFAULT}]
Remember to create the required indexes first. 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 ibtest11cG
*************************** 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 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, mysqldump
also produces correct definitions of tables to the dump file, and does not forget about the foreign keys.
You can 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 also can 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.
MySQL replication works for InnoDB
tables as it does for MyISAM
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 Section 9.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 (commercial) InnoDB Hot Backup
tool (order.html
).
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 dump file 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
, and 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 cause 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.
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 MySQL 4.1.1 or higher, it is difficult to downgrade back to 4.0 or 4.1.0! That is because, for earlier versions, InnoDB
is 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 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 client connections to the mysqld
server that is to be downgraded, and let it run the purge and insert buffer merge operations 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
storage engine does, but MyISAM
divides the table into a data file tbl_name
.MYD
and the index file tbl_name
.MYI
. For InnoDB
, the data and the indexes are stored together 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.
innodb_file_per_table
affects only table creation. If you start the server with this option, new tables are created using .idb
files, but you can still access tables that exist in the shared tablespace. If you remove the option, new tables are created in the shared tablespace, but you can still access any tables that were created using multiple tablespaces.
InnoDB
always needs the shared tablespace. The .ibd
files are not sufficient for InnoDB
to operate. The shared tablespace consists of the familiar ibdata
files where InnoDB
puts its internal data dictionary and undo logs.
You cannot freely move .ibd
files around between database directories the way you can with MyISAM
table files. This is because the table definition is stored in the InnoDB
shared tablespace, and also because InnoDB
must preserve the consistency of transaction IDs and log sequence numbers.
Within a given MySQL installation, you can move an .ibd
file and the associated table from one database to another 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 until SHOW INNODB STATUS
shows that there are no active transactions in the database, and the main thread status 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.
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 data file 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 another data file. To do this, you have to shut down the MySQL server, edit the my.cnf
file to add a new data file to the end of innodb_data_file_path
, and start the server again.
If your last data file already was defined with the keyword autoextend
, the procedure to edit my.cnf
must take into account the size to which the last data file has grown. You have to look at the size of the data file, 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 data file. Remember that only the last data file in the innodb_data_file_path
can be specified as auto-extending.
As an example, assume that the tablespace has just one auto-extending data file ibdata1
:
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
Suppose that this data file, over time, has grown to 988MB. Below is the configuration line after adding another auto-extending data file.
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. InnoDB
will create and initialize it when you restart the server,
Currently, you cannot remove a data file 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 no log files exist at startup and tell you that it is creating new ones.
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 (commercial) additional tool whose annual license fee is 390 euros per computer where the MySQL server is run. See the InnoDB Hot Backup
home page (http://www.innodb.com/order.html) 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 server and make sure that it shuts down without errors.
2. Copy all your data files 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 display output something like this:
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 every possible kind 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 corrupting its own file cache, and the data on disk may be okay. It is best first to try restarting your computer. It may eliminate errors that appeared to be database page corruption.
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 will be 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
tbl_name
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 the purge operation, 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 any of these options enabled! As a safety measure, InnoDB
prevents users 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.
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.
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
data files, 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 9.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
data types in your tables, then the procedure is the same: Just copy the relevant files. If the formats are different and your tables contain floating-point data, you have to use mysqldump
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 that 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.
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.
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 consider 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
.
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. 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 9.11.3, “Consistent Non-Locking 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 9.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
.
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.
In some circumstances, a consistent read is not convenient. For example, you might 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 that 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 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 two users of the database may then see the same value for the counter, and a 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.
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 that 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 that 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 to 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.
Suppose that you are running in 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;
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 9.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 9.11.9, “Deadlock Detection and Rollback.”
Also, since the higher MySQL layer does not know about row-level locks, it is possible to 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 9.17, “Restrictions on InnoDB
Tables.”
MySQL begins each client connection with autocommit mode enabled by default. When autocommit is enabled, MySQL does a commit after each SQL statement if that statement did not return an error.
If you have the autocommit mode off and close a connection without performing an explicit commit of your transaction, then MySQL will roll back your transaction.
If an error is returned by an SQL statement, the commit/rollback behavior depends on the error. See Section 9.16, “Error Handling.”
The following SQL statements cause an implicit commit of the current transaction in MySQL:
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
.
CREATE TABLE
(this commits only if before MySQL 4.0.13 and MySQL binary logging is used).
The CREATE TABLE
statement in InnoDB
is processed as a single transaction. This means that a ROLLBACK
from the user does not undo CREATE TABLE
statements the user made during that transaction.
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 such that it cannot know afterward which lock was set by which SQL statement.
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 occurrence 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.
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 of the 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, although 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 because that would require a log flush to disk for every insert. 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 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 9.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 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.
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 STATUSG
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 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 output includes information of the following types:
Table and record locks held by each active transaction
Lock waits of 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) TYPE=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 the InnoDB
Monitor. If you shut down the server 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
statement 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 InnoDB
internal data dictionary.
A sample of InnoDB
Monitor output:
mysql> SHOW INNODB STATUSG
*************************** 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 an 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.
The ROW OPERATIONS
section shows what the main thread is doing.
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 indicates 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 needed only 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.
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 an .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 increases monotonically 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.
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.
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.
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. If InnoDB
notices that queries could benefit from building a hash index, it does so automatically.
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
tailors itself through the adaptive hash index mechanism to ample main memory, coming closer to the architecture of main memory databases.
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; otherwise, 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
.
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.
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 data file, 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 data file. 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.
Starting from MySQL 3.23.41, you can use raw disk partitions as tablespace data files. 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 data file, you must put the keyword newraw
immediately after the data file 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 1MB usually means 1,000,000 bytes in disk specifications.
[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 data file 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 data file like this:
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=//./D::10Gnewraw
The //./
corresponds to the Windows syntax of \.
for accessing physical drives.
When you use raw disk partitions, be sure that they have permissions that allow read and write access by the account used for running the MySQL server.
The data files 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
allocates space starting from the first data file.
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 four 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 cleanup 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.
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 defragmentation 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.
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.
The following is a non-exhaustive list of 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, 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
data files though the .frm
file for the table exists. See Section 9.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 data file.
1205 (ER_LOCK_WAIT_TIMEOUT)
Lock wait timeout expired. Transaction was rolled back.
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.
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 (http://www.iglu.org.il/lxr/source/include/asm-i386/errno.h).
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
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
No space left on device
29 (ESPIPE)
Illegal seek
30 (EROFS)
Read-only filesystem
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 Web site (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/debug/base/system_error_codes.asp).
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.
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.
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.
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, data files must be less than 2GB.
The combined size of the InnoDB
log files must be less than 4GB.
The minimum tablespace size is 10MB. The maximum tablespace size is four billion database pages (64TB). This is also the maximum size for a table.
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 in the mysql
database 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 regenerate 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 9.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
reached 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 MySQL 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
on 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.
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 host_name
.err
, or mysql.err
on Windows.
When doing troubleshooting, it is usually best to run the MySQL server from the command prompt, not through the mysqld_safe
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.
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 MySQL 3.23.48.
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`(...) TYPE=InnoDB;
The backticks 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 filename in double quotes if the filename contains ‘#
’. Then you can dump and drop the renamed table.
18.217.5.86