Go to the first, previous, next, last section, table of contents.
InnoDB provides MySQL with a transaction-safe (ACID compliant)
storage engine with commit, rollback, and crash recovery capabilities.
InnoDB does locking on the row level and also provides an Oracle-style
consistent
non-locking read in SELECT statements. These features increase
multi-user concurrency and performance. There is no need for
lock escalation in InnoDB,
because row level locks in InnoDB fit in very small space.
InnoDB also supports FOREIGN KEY constraints.
In SQL queries you can freely mix InnoDB type tables with other
table types of MySQL, even within the same query.
InnoDB has been designed for maximum performance when processing
large data volumes. Its CPU efficiency is probably not
matched by any other disk-based relational database engine.
Fully integrated with MySQL Server, the InnoDB storage engine maintains
its own buffer pool for caching data and indexes in main memory.
InnoDB stores its tables and indexes in a tablespace, which
may consist of several files (or raw disk partitions).
This is different from, for example,
MyISAM tables where each table is stored using separate files.
InnoDB tables can be of any size even on operating
systems where file size is limited to 2GB.
InnoDB is included in binary distributions by default as of MySQL 4.0.
For information about InnoDB support in MySQL 3.23, see
section 16.3 InnoDB in MySQL 3.23.
InnoDB is used in production at numerous
large database sites requiring high performance.
The famous Internet news site Slashdot.org runs on InnoDB.
Mytrix, Inc. stores over 1TB of data in InnoDB,
and another site handles an average
load of 800 inserts/updates per second in InnoDB.
InnoDB is published under the same GNU GPL License Version 2
(of June 1991) as MySQL. If you distribute MySQL/InnoDB, and your application
does not satisfy the provisions of the GPL license, you have to purchase a
commercial
MySQL Pro license from https://order.mysql.com/?sub=pg&pg_no=1.
Contact information for Innobase Oy, producer of the InnoDB engine:
Web site: http://www.innodb.com/
Email: sales@innodb.com
Phone: +358-9-6969 3250 (office)
+358-40-5617367 (mobile)
Innobase Oy Inc.
World Trade Center Helsinki
Aleksanterinkatu 17
P.O.Box 800
00101 Helsinki
Finland
Beginning with MySQL 4.0, InnoDB is enabled by default.
The following information applies only to MySQL 3.23.
InnoDB tables are included in the MySQL source distribution
starting from 3.23.34a and are activated in the MySQL-Max
binaries of the 3.23 series.
For Windows, the MySQL-Max binaries are included in the
standard distribution.
If you have downloaded a binary version of MySQL that includes support for
InnoDB, simply follow the instructions of the MySQL manual for
installing a binary version of MySQL. If you already have MySQL 3.23
installed, the simplest way to install MySQL-Max is to replace the
executable mysqld server with the corresponding executable from the
MySQL-Max distribution. MySQL and MySQL-Max differ only in the server
executable.
See section 2.2.5 Installing MySQL on Other Unix-like Systems and
section 5.1.2 The mysqld-max Extended MySQL Server.
To compile the MySQL source code with InnoDB support,
download MySQL 3.23.34a or newer from
http://www.mysql.com/
and configure MySQL with the
--with-innodb option.
See section 2.3 MySQL Installation Using a Source Distribution.
To use InnoDB tables with MySQL 3.23, you must specify configuration
parameters in the [mysqld] section of the `my.cnf' option file.
On Windows, you can use `my.ini' instead. If you do not configure
InnoDB in the option file, InnoDB will not start. (From MySQL
4.0 on, InnoDB uses default parameters if you do not specify any.
However, it is still recommended that to get best performance, you should use
parameters appropriate for your system, as discussed in
section 16.4 InnoDB Configuration.)
In MySQL 3.23, you must specify at the minimum a innodb_data_file_path
value to configure the InnoDB datafiles. For example, to configure
InnoDB to use a single 10MB auto-extending datafile, place the
following setting in the [mysqld] section of your option file:
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
InnoDB will create the `ibdata1' file in the MySQL data directory
by default. To specify the location explicitly, specify a
innodb_data_home_dir setting.
See section 16.4 InnoDB Configuration.
To enable InnoDB tables in MySQL 3.23, see
section 16.3 InnoDB in MySQL 3.23.
From MySQL 4.0 on, the InnoDB storage engine is enabled
by default. If you don't want to use InnoDB tables, you can
add the skip-innodb option to your MySQL option file.
Two important disk-based resources managed by the InnoDB storage
engine are its tablespace datafiles and its log files.
If you specify no InnoDB configuration options, MySQL 4.0 and
above creates an auto-extending 10MB datafile named `ibdata1' and
two 5MB log files named `ib_logfile0' and `ib_logfile1' in the MySQL data directory.
(In MySQL 4.0.0 and 4.0.1, the datafile is 64MB and not auto-extending.)
In MySQL 3.23, InnoDB will not start if you provide no configuration
options.
Note: To get good performance, you should explicitly provide
InnoDB parameters as discussed in the following examples. Naturally,
you should edit the settings to suit your hardware and requirements.
To set up the InnoDB tablespace files, use the
innodb_data_file_path option in the [mysqld] section of the
`my.cnf' option file. On Windows, you can use `my.ini' instead.
The value of innodb_data_file_path should be a list of one or more
datafile specifications. If you name more than one datafile, separate them
by semicolon (`;') characters:
innodb_data_file_path=datafile_spec1[;datafile_spec2]...
For example, a setting that explicitly creates a tablespace having the
same characteristics as the MySQL 4.0 default is as follows:
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
This setting configures a single 10MB datafile named `ibdata1' that is
auto-extending. No location for the file is given, so the default is the
MySQL data directory.
Sizes are specified using M or G suffix letters to indicate
units of MB or GB.
A tablespace containing a fixed-size 50MB datafile named `ibdata1' and
a 50MB auto-extending file named ibdata2 in the data directory can be
configured like this:
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
The full syntax for a datafile specification includes the filename, its size,
and several optional attributes:
file_name:file_size[:autoextend[:max:max_file_size]]
The autoextend attribute and those following can be used only for the
last datafile in the innodb_data_file_path line. autoextend
is available starting from MySQL 3.23.50 and 4.0.2.
If you specify the autoextend option for the last datafile,
InnoDB extends the datafile if it runs out of free space in the
tablespace. The increment is 8MB at a time.
If the disk becomes full, you might want to add another datafile on
another disk. Instructions for reconfiguring an existing tablespace are
given in section 16.8 Adding and Removing InnoDB Data and Log Files.
InnoDB is not aware of the maximum file size, so
be cautious on filesystems where the maximum file size is 2GB.
To specify a maximum size for an auto-extending datafile, use the max
attribute. The following configuration allows `ibdata1' to grow up to a
limit of 500MB:
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend:max:500M
InnoDB creates tablespace files in the MySQL data directory by default.
To specify a location explicitly, use the innodb_data_home_dir option.
For example, to use two files named `ibdata1' and `ibdata2' but
create them in the `/ibdata' directory, configure InnoDB like this:
[mysqld]
innodb_data_home_dir = /ibdata
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
Note: InnoDB does not create directories, so make sure that
the `/ibdata' directory exists before you start the server. This is
also true of any log file directories that you configure. Use the Unix or
DOS mkdir command to create any necessary directories.
InnoDB forms the directory path for each datafile by textually
concatenating the value of innodb_data_home_dir to the datafile name,
adding a slash or backslash between if needed. If the
innodb_data_home_dir option is not mentioned in `my.cnf' at all,
the default value is the ``dot'' directory `./', which means the MySQL
data directory.
If you specify innodb_data_home_dir as an empty string, you can
specify absolute paths for the datafiles listed in the
innodb_data_file_path value. The following example is equivalent to
the preceding one:
[mysqld]
innodb_data_home_dir =
innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend
A simple `my.cnf' example.
Suppose you have a computer
with 128MB RAM and one hard disk. The following example shows
possible configuration parameters in `my.cnf' or
`my.ini' for InnoDB. The example assumes the use of
MySQL-Max 3.23.50 or later or MySQL 4.0.2 or later because it makes use of the
autoextend attribute.
This example suits most users, both on Unix and Windows,
who do not want to distribute InnoDB datafiles and
log files on several disks. It creates an
auto-extending datafile `ibdata1' and two InnoDB log files
`ib_logfile0' and `ib_logfile1' in the
MySQL data directory. Also, the small archived InnoDB log file
`ib_arch_log_0000000000' that InnoDB creates automatically ends
up in the data directory.
[mysqld]
# You can write your other MySQL server options here
# ...
# Datafile(s) must be able to hold your data and indexes.
# Make sure you have enough free disk space.
innodb_data_file_path = ibdata1:10M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
#
# Set the log file size to about 25% of the buffer pool size
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1
Make sure that the MySQL server has the proper access rights to create files
in the data directory. More generally, the server must have access rights in
any directory where it needs to create datafiles or log files.
Note that datafiles must be less than 2GB in some filesystems.
The combined size of the log files must be less than 4GB. The combined
size of datafiles must be at least 10MB.
When you create an InnoDB tablespace for the first time, it is
best that you start the MySQL server from the command prompt.
InnoDB will then print the information about the database
creation to the screen, so you can see what is happening.
For example, on Windows, if mysqld-max is located in
`C:\mysql\bin', you can start it like this:
C:\> C:\mysql\bin\mysqld-max --console
If you do not send server output to the screen, check the server's error log
to see what InnoDB prints during the startup process.
See section 16.6 Creating the InnoDB Tablespace for an example of what the
information displayed by InnoDB should look like.
Where to specify options on Windows?
The rules for option files on Windows are as follows:
Where to specify options on Unix?
On Unix, mysqld reads options from the following files, if they exist,
in the following order:
- `/etc/my.cnf'
Global options.
- `COMPILATION_DATADIR/my.cnf'
Server-specific options.
- `defaults-extra-file'
The file specified with the
--defaults-extra-file option.
- `~/.my.cnf'
User-specific options.
COMPILATION_DATADIR is the MySQL data directory that was
specified as a ./configure option when mysqld
was compiled
(typically `/usr/local/mysql/data' for a binary installation or `/usr/local/var' for a source installation).
If you want to make sure that mysqld reads options only from a
specific file, you can use the --defaults-option as the first option
on the command line when starting the server:
mysqld --defaults-file=your_path_to_my_cnf
An advanced `my.cnf' example.
Suppose you have a Linux computer
with 2GB RAM and three 60GB hard disks
(at directory paths `/', `/dr2' and
`/dr3'). The following example shows possible
configuration parameters in `my.cnf' for InnoDB.
[mysqld]
# You can write your other MySQL server options here
# ...
innodb_data_home_dir =
#
# Datafiles must be able to hold your data and indexes
innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory,
# but make sure on Linux x86 total memory usage is < 2GB
set-variable = innodb_buffer_pool_size=1G
set-variable = innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
#
# innodb_log_arch_dir must be the same as innodb_log_group_home_dir
# (starting from 4.0.6, you can omit it)
innodb_log_arch_dir = /dr3/iblogs
set-variable = innodb_log_files_in_group=2
#
# Set the log file size to about 25% of the buffer pool size
set-variable = innodb_log_file_size=250M
set-variable = innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
#
# Uncomment the next lines if you want to use them
#innodb_flush_method=fdatasync
#set-variable = innodb_thread_concurrency=5
Note that the example places the two datafiles on different disks.
InnoDB will fill the tablespace beginning with the first datafile.
In some cases, it will
improve the performance of the database if all data is not placed
on the same physical disk. Putting log files on a different disk from
data is very often beneficial for performance.
You can also use raw disk partitions (raw devices)
as InnoDB datafiles, which may speed up I/O. See section 16.15.2 Using Raw Devices for the Tablespace.
Warning: On GNU/Linux x86, you must be careful not to set memory
usage too high. glibc will allow the process heap to grow over
thread stacks, which will crash your server. It is a risk if the value of
the following expression is close to or exceeds 2GB:
innodb_buffer_pool_size + key_buffer_size +
max_connections * (sort_buffer_size+read_buffer_size+binlog_cache_size) +
max_connections * 2MB
Each thread will use a stack (often 2MB, but only 256KB in MySQL AB binaries)
and in the worst case also uses sort_buffer + read_buffer_size
additional memory.
Starting from MySQL 4.1, you can use up to 64GB of physical memory in 32-bit
Windows. See the description for innodb_buffer_pool_awe_mem_mb in
section 16.5 InnoDB Startup Options.
How to tune other mysqld server parameters?
The following values are typical and suit most users:
[mysqld]
skip-locking
set-variable = max_connections=200
set-variable = read_buffer_size=1M
set-variable = sort_buffer=1M
#
# Set key_buffer to 5 - 50% of your RAM depending on how much
# you use MyISAM tables, but keep key_buffer_size + InnoDB
# buffer pool size < 80% of your RAM
set-variable = key_buffer_size=...
This section describes the InnoDB-related server options. In MySQL
4.0 and up, all of them can be specified in --opt_name=value form on
the command line or in option files. Before MySQL 4.0, numeric options
should be specified using --set-variable=opt_name=value or -O
opt_name=value syntax.
innodb_additional_mem_pool_size
-
The size of a memory pool
InnoDB uses to store data dictionary
information and other internal data structures. The more tables you have in
your application, the more memory you will need to allocate here. If
InnoDB runs out of memory in this pool, it will start to allocate
memory from the operating system, and write warning messages to the MySQL
error log. The default value is 1MB.
innodb_buffer_pool_awe_mem_mb
-
The size of the buffer pool in MB, if it is placed in the AWE memory of
32-bit Windows. Available from 4.1.0 and only relevant in 32-bit Windows. If
your 32-bit Windows operating system supports > 4GB memory, so-called Address
Windowing Extensions, you can allocate the
InnoDB buffer pool into the
AWE physical memory using this parameter. The maximum possible value for this
is 64000. If this parameter is specified, innodb_buffer_pool_size
is the window in the 32-bit address space of mysqld where InnoDB
maps that AWE memory. A good value for innodb_buffer_pool_size
is then 500MB.
innodb_buffer_pool_size
-
The size of the memory buffer
InnoDB uses to cache data and indexes
of its tables. The larger you set this value, the less disk I/O is needed
to access data in tables. On a dedicated database server, you may set this
to up to 80% of the machine physical memory size. Do not set it too large
though, because competition for the physical memory might cause paging in
the operating system.
innodb_data_file_path
-
The paths to individual datafiles and their sizes. The full directory path
to each datafile is acquired by concatenating
innodb_data_home_dir
to each path specified here. The file sizes are specified in megabytes
or gigabytes (1024MB) by appending M or G to the size
value. The sum of the sizes of the files must be at least 10MB. On some
operating systems, files must be less than 2GB. If you do not specify
innodb_data_file_path, the default behavior starting from 4.0 is to
create a single 10MB auto-extending datafile named `ibdata1'. Starting
from 3.23.44, you can set the file size bigger than 4GB on those operating
systems that support big files. You can also use raw disk partitions as
datafiles. See section 16.15.2 Using Raw Devices for the Tablespace.
innodb_data_home_dir
-
The common part of the directory path for all
InnoDB datafiles. If
you do not set this value, the default is the MySQL data directory. You can
specify this also as an empty string, in which case you can use absolute
file paths in innodb_data_file_path.
innodb_fast_shutdown
-
By default,
InnoDB does a full purge and an insert buffer merge
before a shutdown. These operations can take minutes, or in extreme
cases even hours. If you set this parameter to 1, InnoDB
skips these operations at shutdown. This option is available starting from
MySQL 3.23.44 and 4.0.1. Its default value is 1 starting from 3.23.50.
innodb_file_io_threads
-
The number of file I/O threads in
InnoDB. Normally this should be
left at the default value of 4, but on Windows, disk I/O may benefit from
a larger number. On Unix, increasing the number has no effect; InnoDB
always uses the default value.
This option is available as of MySQL 3.23.37
innodb_file_per_table
-
This option causes
InnoDB to create each new table
using its own `.ibd' file for storing data and indexes, rather than in
the shared tablespace. See section 16.7.6 Using Per-table Tablespaces.
This option is available as of MySQL 4.1.1.
innodb_flush_log_at_trx_commit
-
Normally you set this to 1, meaning that at a transaction commit, the log
is flushed to disk, and the modifications made by the transaction become
permanent and survive a database crash. If you are willing to compromise
this safety, and you are running small transactions, you may set this to
0 or 2 to reduce disk I/O to the logs. A value of 0 means that the log is
only written to the log file and the log file flushed to disk approximately
once per second. A value of 2 means the log is written to the log file at
each commit, but the log file is only flushed to disk approximately once
per second. The default value is 1 (prior to MySQL 4.0.13, the default 0).
innodb_flush_method
-
This option is only relevant on Unix systems. If set to
fdatasync,
InnoDB uses fsync() to flush both the data and log
files. If set to O_DSYNC, InnoDB uses O_SYNC
to open and flush the log files, but uses fsync() to flush the
datafiles. If O_DIRECT is specified (available on some GNU/Linux
versions starting from MySQL 4.0.14), InnoDB uses O_DIRECT
to open the datafiles, and uses fsync() to flush both the data
and log files. Note that InnoDB does not use fdatasync or
O_DSYNC by default because there have been problems with them on
many Unix flavors. This option is available as of MySQL 3.23.40.
innodb_force_recovery
-
Warning: This option should be defined only in an emergency situation when
you want to dump your tables from a corrupt database! Possible values are
from 1 to 6. The meanings of these values are described in section 16.9.1 Forcing Recovery. As a safety measure,
InnoDB prevents a user from modifying
data when this option is greater than 0. This option is available starting
from MySQL 3.23.44.
innodb_lock_wait_timeout
-
The timeout in seconds an
InnoDB transaction may wait for a lock
before being rolled back. InnoDB automatically detects transaction
deadlocks in its own lock table and rolls back the transaction. If you
use the LOCK TABLES statement, or other transaction-safe storage
engines than InnoDB in the same transaction, a deadlock may arise
that InnoDB cannot notice. In cases like this, the timeout is useful
to resolve the situation. The default is 50 seconds.
innodb_log_arch_dir
-
The directory where fully written log files would be archived if we used log
archiving. The value of this parameter should currently be set the same as
innodb_log_group_home_dir. Starting from MySQL 4.0.6, you may omit
this option.
innodb_log_archive
-
This value should currently be set to 0. Because recovery from a backup
is done by MySQL using its own log files, there is currently no need to
archive
InnoDB log files. The default for this option is 0.
innodb_log_buffer_size
-
The size of the buffer that
InnoDB uses to write to the log files
on disk. Sensible values range from 1MB to 8MB. The default is 1MB. A large
log buffer allows large transactions to run without a need to write the log
to disk before the transactions commit. Thus, if you have big transactions,
making the log buffer larger will save disk I/O.
innodb_log_file_size
-
The size of each log file in a log group in megabytes. The combined size of
log files must be less than 4GB on 32-bit computers. The default is 5MB.
Sensible values range from 1MB to 1/
n-th of the size of the buffer pool,
below, where n is the number of log files in the group. The larger
the value, the less checkpoint flush activity is needed in the buffer
pool, saving disk I/O. But larger log files also mean that recovery will
be slower in case of a crash.
innodb_log_files_in_group
-
The number of log files in the log group.
InnoDB writes to the
files in a circular fashion. The default is 2 (recommended).
innodb_log_group_home_dir
-
The directory path to the
InnoDB log files. It must have the same
value as innodb_log_arch_dir. If you do not specify any InnoDB
log parameters, the default is to create two 5MB files `ib_logfile0'
and `ib_logfile1' in the MySQL data directory.
innodb_max_dirty_pages_pct
-
This is an integer in the range from 0 to 100. The default is 90. The main
thread in
InnoDB tries to flush pages from the buffer pool so that
at most this many percent of pages may not yet flushed been flushed at
any particular time. Available starting from 4.0.13 and 4.1.1. If you have the
SUPER privilege, this percentage can be changed while the server is
running:
SET GLOBAL innodb_max_dirty_pages_pct = value;
innodb_mirrored_log_groups
-
The number of identical copies of log groups we keep for the
database. Currently this should be set to 1.
innodb_open_files
-
This option is relevant only if you use multiple tablespaces in
InnoDB. It specifies the maximum number of `.ibd' files that
InnoDB can keep open at one time. The minimum value is 10. The
default is 300. This option is available as of MySQL 4.1.1.
The file descriptors used for `.ibd' files are for InnoDB only.
They are independent of those specified by the --open-files-limit
server option, and do not affect the operation of the table cache.
innodb_thread_concurrency
-
InnoDB tries to keep the number of operating system threads
concurrently inside InnoDB less than or equal to the limit given
by this parameter. The default value is 8. If you have low performance
and SHOW INNODB STATUS reveals many threads waiting for semaphores,
you may have thread thrashing and should try setting this parameter lower
or higher. If you have a computer with many processors and disks, you
can try setting this value higher to better utilize the resources of you
computer. A recommended value is the sum of the number of processors and
disks your system has. A value of 500 or greater disables the concurrency
checking. This option is available starting from MySQL 3.23.44 and 4.0.1.
Suppose you have installed MySQL and have edited your option file so
that it contains the necessary InnoDB configuration parameters.
Before starting MySQL, you should verify that the directories you have
specified for InnoDB datafiles and log files exist and that the
MySQL server has access rights to those directories. InnoDB cannot
create directories, only files. Check also that you have enough disk space
for the data and log files.
It is best to run the MySQL server mysqld from the command prompt
when you create an InnoDB database, not from the `safe_mysqld'
wrapper or as a Windows service. When you run from a command prompt you
see what mysqld prints and what is happening. On Unix, just invoke
mysqld. On Windows, use the --console option.
When you start the MySQL server after initially configuring InnoDB in
your option file, InnoDB creates your
datafiles and log files. InnoDB will print something like the following:
# mysqld
InnoDB: The first specified datafile /home/heikki/data/ibdata1
did not exist:
InnoDB: a new database to be created!
InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728
InnoDB: Database physically writes the file full: wait...
InnoDB: datafile /home/heikki/data/ibdata2 did not exist:
new to be created
InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000
InnoDB: Database physically writes the file full: wait...
InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist:
new to be created
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size
to 5242880
InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist:
new to be created
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size
to 5242880
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
InnoDB: Started
mysqld: ready for connections
A new InnoDB database has now been created. You can connect to the MySQL
server with the usual MySQL client programs like mysql.
When you shut down the MySQL server with mysqladmin shutdown,
the output will be like the following:
010321 18:33:34 mysqld: Normal shutdown
010321 18:33:34 mysqld: Shutdown Complete
InnoDB: Starting shutdown...
InnoDB: Shutdown completed
You can now look at the datafile and log directories and you
will see the files created. The log directory will also contain
a small file named `ib_arch_log_0000000000'. That file
resulted from the database creation, after which InnoDB switched off
log archiving.
When MySQL is started again, the datafiles and log files will already have
been created, so the output will be much briefer:
# mysqld
InnoDB: Started
mysqld: ready for connections
If InnoDB prints an operating system error in a file operation,
usually the problem is one of the following:
- You did not create the
InnoDB datafile or log directories.
mysqld does not have access rights to create files in those
directories.
mysqld does not read the proper `my.cnf' or `my.ini'
option file, and consequently does not see the options you specified.
- The disk is full or a disk quota is exceeded.
- You have created a subdirectory whose name is equal to a datafile
you specified.
- There is a syntax error in
innodb_data_home_dir
or innodb_data_file_path.
If something goes wrong when InnoDB attempts to initialize
its tablespace or its log files, you should delete all files created
by InnoDB. This means all datafiles, all log files, the small
archived log file. In case you already created some InnoDB tables,
delete the corresponding `.frm' files for these tables (and any
`.ibd' files if you are using multiple tablespaces) from the
MySQL database directories as well. Then you can try the InnoDB
database creation again. It is best to start the MySQL server from a
command prompt so that you see what is happening.
Suppose you have started the MySQL client with the command mysql test.
To create an InnoDB table, you must specify ENGINE = InnoDB
or TYPE = InnoDB in the table creation SQL statement:
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) TYPE=InnoDB;
The SQL statement creates a table and an index on column a in the
InnoDB tablespace that consists of the datafiles you specified in
`my.cnf'. In addition, MySQL creates a file `customers.frm'
in the `test' directory under the MySQL database directory.
Internally, InnoDB adds to its own data dictionary an entry for
table 'test/customers'. This means you can create a table of the same
name customers in some other database, and the table names will
not collide inside InnoDB.
You can query the amount of free space in the InnoDB tablespace
by issuing a SHOW TABLE STATUS statement for any InnoDB table.
The amount of free
space in the tablespace appears in the Comment section in the
output of SHOW TABLE STATUS. An example:
SHOW TABLE STATUS FROM test LIKE 'customers'
Note that the statistics SHOW gives about InnoDB tables
are only approximate. They are used in SQL optimization. Table and index
reserved sizes in bytes are accurate, though.
By default, each client tht connects to the MySQL server begins with autocommit
mode enabled, which automatically commits every SQL statement you run.
To use multiple-statement transactions, you can switch autocommit off
with the SQL statement SET AUTOCOMMIT = 0 and use COMMIT
and ROLLBACK to commit or rollback your transaction.
If you want to leave the autocommit on, you can enclose your transactions
between START TRANSACTION and COMMIT or ROLLBACK.
Before MySQL 4.0.11, you have to use the keyword BEGIN
instead of START TRANSACTION. The following example shows two
transactions. The first is committed and the second is rolled back.
# mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.50-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
-> TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A | B |
+------+--------+
| 10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>
In APIs like PHP, Perl DBI/DBD, JDBC, ODBC, or the standard C call
interface of MySQL, send the transaction control statements like
COMMIT to the MySQL server as strings just like any other
SQL statements such as SELECT or INSERT.
Some APIs also offer separate special transaction commit and rollback functions
or methods.
Important: You should not convert MySQL system tables in the mysql
database (such as user or host) to the InnoDB type. The
system tables must always be of the MyISAM type.
If you want all your (non-system) tables to be created in the InnoDB
type, you can, starting from the MySQL 3.23.43, add the line
default-table-type=innodb to the [mysqld] section of
your `my.cnf' or `my.ini' file.
InnoDB does not have a special optimization for separate index
creation the way the MyISAM storage engine does. Therefore, it does
not pay to export and import the table and create indexes afterward.
The fastest way to alter a table to InnoDB is to do the inserts
directly to an InnoDB table, that is, use ALTER TABLE
... TYPE=INNODB, or create an empty InnoDB table with identical
definitions and insert the rows with INSERT INTO ... SELECT *
FROM ....
If you have UNIQUE constraints on secondary keys, starting from
MySQL 3.23.52, you can speed up a table import by turning off the uniqueness
checks temporarily during the import session: SET UNIQUE_CHECKS=0;
For big tables this saves a lot of disk I/O because InnoDB can then
use its insert buffer to write secondary index records in a batch.
To get better control over the insertion process, it may be good to insert
big tables in pieces:
INSERT INTO newtable SELECT * FROM oldtable
WHERE yourkey > something AND yourkey <= somethingelse;
After all data has been inserted, you can rename the tables.
During the conversion of big tables you should increase the size of the
InnoDB buffer pool to reduce disk I/O. Do not use more than 80% of the
physical memory, though. You can also increase the sizes of the InnoDB
log files and the log files.
Make sure you do not fill up the tablespace: InnoDB tables require
a lot more disk space than MyISAM tables. If an ALTER TABLE
runs out of space, it will start a rollback, and that can take hours if
it is disk-bound. For inserts, InnoDB uses the insert buffer to
merge secondary index records to indexes in batches. That saves a lot of
disk I/O. In rollback, no such mechanism is used, and the rollback can take
30 times longer than the insertion.
In the case of a runaway rollback, if you do not have valuable data in your
database, it may be advisable to kill the database process rather than wait
for millions of disk I/O operations to complete.
For the complete procedure, see section 16.9.1 Forcing Recovery.
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.43b, InnoDB features foreign key constraints.
The syntax of a foreign key constraint definition in InnoDB looks like
this::
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
Both tables must be InnoDB type. In the referencing table, there must
be an index where the foreign key columns are listed as the first
columns in the same order. In the referenced table, there must be an index
where the referenced columns are listed as the first columns in
the same order. Index prefixes on foreign keys are not supported.
InnoDB does not automatically create indexes on foreign keys or
referenced keys: You must create them explicitly. The indexes are needed
so that foreign key checks can be fast and not require a table scan.
Corresponding columns in the foreign key and the referenced key must
have similar internal datatypes inside InnoDB so that they can be
compared without a type conversion.
The size and the signedness of integer types has to be the same.
The length of string types need not be the same.
If you specify a SET NULL action, make sure you
have not declared the columns in the child table as
NOT NULL.
If MySQL reports an error number 1005 from a CREATE TABLE statement,
and the error message string refers to errno 150, this means that the table
creation failed because a foreign key constraint was not correctly formed.
Similarly, if an ALTER TABLE fails and it refers to errno 150,
that means a foreign key definition would be incorrectly formed for the
altered table. Starting from MySQL 4.0.13, you can use SHOW INNODB
STATUS to display a detailed explanation of the latest InnoDB
foreign key error in the server.
Starting from MySQL 3.23.50, InnoDB does not check foreign key
constraints on those foreign key or referenced key values
that contain a NULL column.
A deviation from SQL standards: If in the parent table
there are several rows that have the same referenced key value,
then InnoDB acts in foreign key checks as if the other parent
rows with the same key value do not exist. For example,
if you have defined a RESTRICT type constraint, and there
is a child row with several parent rows, InnoDB does not allow
the deletion of any of those parent rows.
Starting from MySQL 3.23.50, you can also associate the
ON DELETE CASCADE or ON DELETE SET NULL clause with
the foreign key constraint. Corresponding ON UPDATE options
are available starting from 4.0.8. If ON DELETE CASCADE is
specified, and a row in the parent table is deleted, InnoDB
automatically deletes also all those rows in the child table
whose foreign key values are equal to the referenced key value in
the parent row. If ON DELETE SET NULL is specified, the
child rows are automatically updated so that the columns in the
foreign key are set to the SQL NULL value.
InnoDB performs cascading operations through a depth-first algorithm,
based on records in the indexes corresponding to the foreign key
constraints.
A deviation from SQL standards: If
ON UPDATE CASCADE or ON UPDATE SET NULL recurses to
update the SAME TABLE it has already updated during the cascade,
it acts like RESTRICT. This means that you cannot use
self-referential ON UPDATE CASCADE or
ON UPDATE SET NULL operations.
This is to prevent infinite loops resulting from cascaded updates.
A self-referential ON DELETE SET NULL, on the other hand,
is possible from 4.0.13.
A self-referential ON DELETE CASCADE has been possible since ON
DELETE was implemented.
A simple example that relates parent and child tables through a
single-column foreign key:
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;
A more complex example in which a product_order table has foreign keys
for two other tables. One foreign key references a two-column index in the
product table. The other references a single-column index in the
customer table:
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id)) ENGINE=INNODB;
Starting from MySQL 3.23.50, InnoDB allows you to add a new
foreign key constraint to a table by using ALTER TABLE:
ALTER TABLE yourtablename
ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
Remember to create the required indexes first, though.
You can also add a self-referential foreign key constraint to a
table using ALTER TABLE.
Starting from MySQL 4.0.13, InnoDB supports the use of ALTER
TABLE to drop foreign keys:
ALTER TABLE yourtablename
DROP FOREIGN KEY fk_symbol
If the FOREIGN KEY clause included a CONSTRAINT name when you
created the foreign key, you can refer to that name to drop the foreign key.
(A constraint name can be given as of MySQL 4.0.18.) Otherwise, the
fk_symbol value is internally generated by InnoDB when the
foreign key is created. To find out the symbol when you want to drop a foreign
key, use the SHOW CREATE TABLE statement.
An example:
mysql> show create table ibtest11c\G
*************************** 1. row ***************************
Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
`A` int(11) NOT NULL auto_increment,
`D` int(11) NOT NULL default '0',
`B` varchar(200) NOT NULL default '',
`C` varchar(175) default NULL,
PRIMARY KEY (`A`,`D`,`B`),
KEY `B` (`B`,`C`),
KEY `C` (`C`),
CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB CHARSET=latin1
1 row in set (0.01 sec)
mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;
Starting from MySQL 3.23.50, the InnoDB parser allows you to use
also backticks around table and column names in a
FOREIGN KEY ... REFERENCES ... clause.
Starting from MySQL 4.0.5, the InnoDB parser also takes into account
the lower_case_table_names system variable setting.
Before MySQL 3.23.50, ALTER TABLE or CREATE INDEX
should not be used in connection with tables that have foreign
key constraints or that are referenced in foreign key constraints:
Any ALTER TABLE removes all foreign key
constraints defined for the table. You should not use
ALTER TABLE with the referenced table, either. Instead,
use DROP TABLE and CREATE TABLE to modify the
schema. When MySQL does an ALTER TABLE it may internally
use RENAME TABLE, and that will confuse the
foreign key constraints that refer to the table.
In MySQL, a CREATE INDEX statement is processed as an
ALTER TABLE, so the same considerations apply.
Starting from MySQL 3.23.50, InnoDB returns the foreign key
definitions of a table as part of the output of the SHOW CREATE TABLE
statement:
SHOW CREATE TABLE tbl_name;
From this version, also mysqldump produces correct definitions
of tables to the dump file, and does not forget about the foreign keys.
You can also display the foreign key constraints for a table like this:
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name'
The foreign key constraints are listed in the Comment column of
the output.
When performing foreign key checks, InnoDB sets shared row
level locks on child or parent records it has to look at.
InnoDB checks foreign key constraints immediately: The check
is not deferred to transaction commit.
To make it easier to reload dump files for tables that have foreign key
relationships, mysqldump automatically includes a statement in the
dump output to set FOREIGN_KEY_CHECKS to 0 as of MySQL 4.1.1. This
avoids problems with tables having to be reloaded in a particular order
when the dump is reloaded. For earlier versions, you can disable the
variable manually within mysql when loading the dump file like this:
mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;
This allows you to import the tables in any order if the dump file contains
tables that are not correctly ordered for foreign keys. It also speeds
up the import operation. FOREIGN_KEY_CHECKS is available starting
from MySQL 3.23.52 and 4.0.3.
Setting FOREIGN_KEY_CHECKS to 0 can also be useful for ignoring
foreign key constraints during LOAD DATA operations.
InnoDB allows you to drop any table, even though that
would break the foreign key constraints that reference
the table. When you drop a table, the constraints that
were defined in its create statement are also dropped.
If you re-create a table that was dropped, it must have
a definition that conforms to the foreign key constraints
referencing it. It must have the right column names and types,
and it must have indexes on the referenced keys, as stated earlier.
If these are not satisfied, MySQL returns error number 1005
and refers to errno 150 in the error message string.
The MySQL replication feature works on InnoDB tables like it does
on MyISAM type tables. It is also possible to use replication in
a way where the table type on the slave is not the same as the original
table type on the master. For example, you can replicate modifications to
an InnoDB table on the master to a MyISAM table on the slave.
To set up a new slave for a master you have to make a copy of the
InnoDB tablespace and the log files, as well as the `.frm' files
of the InnoDB tables, and move the copies to the slave.
For the proper procedure to do this, see See section 16.10 Moving an InnoDB Database to Another Machine.
If you can shut down the master or an existing slave, you can take a
cold backup of the InnoDB tablespace and log files and use that to
set up a slave.
To make a new slave without taking down any server you can also use
the non-free InnoDB Hot Backup tool.
There are minor limitations in InnoDB replication:
-
LOAD TABLE FROM MASTER does not work for InnoDB type
tables. There are workarounds:
1) dump the table on the master and import the dumpfile into the slave, or
2) use ALTER TABLE tbl_name TYPE=MyISAM on the master before setting up
replication with LOAD TABLE tbl_name FROM MASTER, then use ALTER
TABLE to alter the master table
back to the InnoDB type afterward.
-
Before MySQL 4.0.6,
SLAVE STOP did not
respect the boundary of a multiple-statement transaction.
An incomplete transaction would be rolled back, and the next
SLAVE START would only execute the remaining part of the half
transaction. That would make replication to fail.
-
Before MySQL 4.0.6, a slave crash in the middle of a
multiple-statement transaction would cause the same problem as
SLAVE STOP.
-
Before MySQL 4.0.11, replication of the
SET FOREIGN_KEY_CHECKS=0 statement does not work properly.
Most of these limitations can be eliminated by using more recent server
versions for which the limitations do not apply.
Transactions that fail on the master do not affect replication at all.
MySQL replication is based on the binary log where MySQL writes SQL
statements that modify data. A slave reads the binary log of the master and
executes the same SQL statements. However, statements that occur within a
transaction are not written to the binary log until the transaction commits,
at which point all statements in the transaction are written at once.
If a statement fails, for example, because of a foreign key violation,
or if a transaction is rolled back, no SQL statements are written to the
binary log, and the transaction is not executed on the slave at all.
Starting from MySQL 4.1.1, you can store each InnoDB table and its
indexes into its own file. This feature is called ``multiple tablespaces'',
because in effect each table has its own tablespace.
Important note: If you upgrade to InnoDB-4.1.1 or higher, it is
difficult to downgrade back to 4.0 or 4.1.0! That is because earlier
versions of InnoDB are not aware of multiple tablespaces.
If you need to downgrade to 4.0, you have to take table dumps and
re-create the whole InnoDB tablespace. If you have not created new
InnoDB tables under MySQL 4.1.1 or above, and need to downgrade
quickly, you can also do a direct downgrade to the MySQL
4.0.18 or later in the 4.0 series.
Before doing the direct downgrade to 4.0.x, you have to end all
connections to >= 4.1.1, and let mysqld run purge and the
insert buffer merge to completion, so that
SHOW INNODB STATUS shows the Main thread in the state
waiting for server activity. Then you can shut down
mysqld and start 4.0.18 or later in the 4.0 series.
A direct downgrade is not recommended, however, because it has not
been extensively tested.
You can enable multiple tablespaces by adding a line to the
[mysqld] section of `my.cnf':
[mysqld]
innodb_file_per_table
After restarting the server, InnoDB will store each newly created
table into its own file `tbl_name.ibd' in the database directory
where the table belongs. This is similar to what the MyISAM does,
but MyISAM divides the table into a datafile `tbl_name.MYD'
and the index file `tbl_name.MYI'. For InnoDB, both the data
and the indexes are in the `.ibd' file. The `tbl_name.frm'
file is still created as usual.
If you remove the innodb_file_per_table line from `my.cnf' and
restart the server, InnoDB creates tables inside the shared tablespace
files again. The old tables you had in the shared tablespace files before an
upgrade to 4.1.1 remain there; they are not converted into `.ibd' files.
innodb_file_per_table affects only table creation. If you start the
server with this option, you can still access tables that in the shared
tablespace. If you remove the option, you can still access any tables that
were created using multiple tablespaces.
InnoDB always needs the shared ``system tablespace.'' The `.ibd'
files are not sufficient for InnoDB to operate. The system tablespace
consists of the familiar `ibdata' files where InnoDB puts its
internal data dictionary and undo logs.
You CANNOT FREELY MOVE .ibd files around the way you can with
MyISAM table files. This is because the table definition is stored
in the InnoDB system tablespace, and also because InnoDB
must preserve the consistency of transaction IDs and log sequence numbers.
You can move an `.ibd' file and the associated table from a database
to another within a given MySQL installation with the familiar RENAME
TABLE statement:
RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;
If you have a ``clean'' backup of an `.ibd' file,
you can restore it to the MySQL installation from which it originated as
follows:
-
Issue this
ALTER TABLE statement:
ALTER TABLE tbl_name DISCARD TABLESPACE;
Caution: This deletes the current `.ibd' file.
-
Put the backup `.ibd' file back in the proper database directory.
-
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:
-
Stop all activity from the
mysqld server and commit all transactions.
-
Wait that
SHOW INNODB STATUS\G shows that there are no active
transactions in the database, and the main thread of InnoDB is
Waiting for server activity. Then you can make a copy of the
`.ibd' file.
Another method for making a clean copy of an `.ibd' file is to
use the commercial InnoDB Hot Backup tool:
-
Use
InnoDB Hot Backup to back up the InnoDB installation.
-
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
datafile in the tablespace definition. Then InnoDB will increase the
size of that file automatically in 8MB increments when it runs out of space.
Alternatively, you can increase the size of your tablespace by adding an
additional datafile. To do this, you have to shut down the MySQL server,
edit the `my.cnf' file to add a new datafile to the end of
innodb_data_file_path, and start the server again.
If your last datafile already was defined with the keyword autoextend,
then the procedure to edit`my.cnf' must take into account the size to
which the last datafile has grown. You have to
look at the size of the datafile, round the size downward to the
closest multiple of 1024 * 1024 bytes (= 1MB), and specify the rounded
size explicitly in innodb_data_file_path. Then you can add
another datafile. Remember that only the last datafile in the
innodb_data_file_path can be specified as auto-extending.
As an example, assume the talespace had just one auto-extending datafile
`ibdata1':
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
Suppose that this datafile, over time, grew to 988MB. Below is the
configuration line after adding another auto-extending datafile.
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
When you add a new file to the tablespace, make sure that it does not exist
so that when you restart the server, InnoDB will create it and
initialize it.
Currently, you cannot remove a datafile from the tablespace. To decrease the
size of your tablespace, use this procedure:
-
Use
mysqldump to dump all your InnoDB tables.
-
Stop the server.
-
Remove all the existing tablespace files.
-
Configure a new tablespace.
-
Restart the server.
-
Import the dump files.
If you want to change the number or the size of your InnoDB log
files, you have to stop the MySQL server and make sure that it shuts down
without errors. Then copy the old log files into a safe place just in case
something went wrong in the shutdown and you will need them to recover the
tablespace. Delete the old log files from the log file directory, edit
`my.cnf' to change the log file configuration, and start the MySQL
server again. mysqld will see that the no log files exist at startup
and tell you that it is creating new ones.
The key to safe database management is taking regular backups.
InnoDB Hot Backup is an online backup tool you can use to backup
your InnoDB database while it is running. InnoDB Hot Backup does not
require you to shut down your database and it does not set any locks or
disturb your normal database processing. InnoDB Hot Backup
is a non-free additional tool whose annual license fee is
390 euros per computer where the MySQL server is run.
See the InnoDB Hot Backup homepage
for detailed information and screenshots.
If you are able to shut down your MySQL server, you can make
a ``binary'' backup that consists of all files used by InnoDB to manage
its tables. Use the following procedure:
-
Shut down your MySQL database and make sure it shuts down without errors.
-
Copy all your datafiles into a safe place.
-
Copy all your
InnoDB log files to a safe place.
-
Copy your `my.cnf' configuration file or files to a safe place.
-
Copy all the `.frm' files for your
InnoDB tables to a safe place.
Replication works with InnoDB type tables, so you can use MySQL
replication capabilities to keep a copy of your database at database sites
requiring high availability.
In addition to taking binary backups as just described,
you should also regularly take dumps of your tables with
mysqldump. The reason for this is that a binary file
might be corrupted without you noticing it. Dumped tables are stored
into text files that are human-readable, so spotting table corruption
becomes easier. Also, since the format is simpler, the chance for
serious data corruption is smaller.
mysqldump also has a --single-transaction option that
you can use to take a consistent snapshot without locking out other
clients.
To be able to recover your InnoDB database to the present from the
binary backup described above, you have to run your MySQL server
with binary logging turned on. Then you can apply the binary log to the
backup database to achieve point-in-time recovery:
mysqlbinlog yourhostname-bin.123 | mysql
To recover from a crash of your MySQL server process, the only thing
you have to do is to restart it. InnoDB will automatically check the
logs and perform a roll-forward of the database to the present.
InnoDB will automatically roll back uncommitted transactions that were
present at the time of the crash. During recovery, mysqld will
print out something like the following:
# mysqld
InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections
If your database gets corrupted or your disk fails, you have
to do the recovery from a backup. In the case of corruption, you should
first find a backup that is not corrupted. After restoring the base
backup, do the recovery from the binary log files.
In some cases of database corruption it is enough just to dump, drop,
and re-create one or a few corrupt tables. You can use the
CHECK TABLE SQL statement to check whether a table is corrupt, though
CHECK TABLE naturally cannot detect all kinds of corruption.
You can use innodb_tablespace_monitor to check the integrity of
the file space management inside the tablespace files.
In some cases, apparent database page corruption is actually due to the
operating system which has corrupted its own file cache, and the data
on disk may be okay. It is best first to try restarting your computer.
It may remove the errors that appeared as database page corruption.
If there is database page corruption, you may want to dump
your tables from the database with SELECT INTO OUTFILE,
and usually most of the data
is intact and correct. But the corruption may cause
SELECT * FROM tbl_name or InnoDB
background operations to crash or assert, or even the InnoDB
roll-forward recovery to crash. Starting from MySQL
3.23.44, there is an InnoDB variable that you can use to
force the InnoDB storage engine to start up, and you can also
prevent background operations from running, so that
you will be able to dump your tables. For example, you can add the following
line to the [mysqld] section of your option file before restarting the
server:
[mysqld]
innodb_force_recovery = 4
Before MySQL 4.0, use this syntax instead:
[mysqld]
set-variable = innodb_force_recovery = 4
The allowable non-zero values for innodb_force_recovery follow.
A larger number includes all precautions of lower numbers. If you are
able to dump your tables with an option value of at most 4, then you are
relatively safe that only some data on corrupt individual pages
is lost. A value of 6 is more dramatic, because database pages are left
in an obsolete state, which in turn may introduce more corruption
into B-trees and other database structures.
1 (SRV_FORCE_IGNORE_CORRUPT)
Let the server run even if it detects a corrupt page; try to make
SELECT * FROM table jump over corrupt index records and pages,
which helps in dumping tables;
2 (SRV_FORCE_NO_BACKGROUND)
Prevent the main thread from running. If a crash would occur
in purge, this prevents it;
3 (SRV_FORCE_NO_TRX_UNDO)
Do not run transaction rollbacks after recovery;
4 (SRV_FORCE_NO_IBUF_MERGE)
Prevent also insert buffer merge operations. If they would cause a
crash, better not do them; do not calculate table statistics;
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
Do not look at undo logs when starting the database: InnoDB will
treat even incomplete transactions as committed;
6 (SRV_FORCE_NO_LOG_REDO)
Do not do the log roll-forward in connection with recovery.
The database must not otherwise be used with these options! As a safety
measure, InnoDB prevents a user from doing INSERT,
UPDATE, or DELETE when innodb_force_recovery is set to
a value greater than 0.
Starting from MySQL 3.23.53 and 4.0.4, you are allowed to DROP or
CREATE a table even if forced recovery is used. If you know that a
certain table is causing a crash in rollback, you can drop it. You can use
this also to stop a runaway rollback caused by a failing mass import or
ALTER TABLE. You can kill the mysqld process and set
innodb_force_recovery to 3 to bring your database up without
the rollback. Then DROP the table that is causing the runaway
rollback.
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 datafiles, InnoDB data and log files are
binary-compatible on all platforms if the floating-point number format on
the machines is the same. You can move an InnoDB database simply by
copying all the relevant files, which were listed in section 16.9 Backing Up and Recovering an InnoDB Database. If
the floating-point formats on the machines are different but you have not
used FLOAT or DOUBLE datatypes in your tables then the
procedure is the same: Just copy the relevant files. If the formats are
different and your tables contain floating-point data, you have to use
mysqldump to dump your tables on one machine and then import the dump
files on the other machine.
A performance tip is to switch off autocommit mode when you import
data into your database, assuming your tablespace has enough space for
the big rollback segment the big import transaction will generate.
Do the commit only after importing a whole table or a segment of
a table.
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 think that a user always has a transaction open. An
SQL COMMIT or ROLLBACK statement ends the
current transaction and a new one starts. Both statements will release
all InnoDB locks that were set during the current transaction.
A COMMIT means that the changes made in the current transaction
are made permanent and become visible to other users.
A ROLLBACK statement, on the other hand, cancels all
modifications made by the current transaction.
If the connection has autocommit enabled, the user can still
perform a multiple-statement transaction by starting it with an
explicit START TRANSACTION or BEGIN statement and ending it with
COMMIT or ROLLBACK.
In terms of the SQL:1992 transaction isolation levels,
the InnoDB default is REPEATABLE READ.
Starting from MySQL 4.0.5, InnoDB offers all four different
transaction isolation levels described by the SQL standard.
You can set the default isolation level for all connections by using the
--transaction-isolation option on the command line or in option files.
For example, you can set the option
in the [mysqld] section of `my.cnf' like this:
[mysqld]
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
| REPEATABLE-READ | SERIALIZABLE}
A user can change the isolation level of a single session or
all new incoming connections with the SET TRANSACTION
statement. Its syntax is as follows:
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED
| REPEATABLE READ | SERIALIZABLE}
Note that there are hyphens in the level names for the
--transaction-isolation option, but not for the SET
TRANSACTION statement.
The default behavior is to set the isolation level for the next (not
started) transaction. If you use the GLOBAL keyword, the statement
sets the default transaction level globally for all new connections
created from that point on (but not existing connections).
You need the SUPER privilege to do this. Using the SESSION
keyword sets the default transaction level for all future transactions
performed on the current connection.
Any client is free to change the session isolation level (even in the
middle of a transaction), or the isolation level for the next
transaction.
Before MySQL 3.23.50, SET TRANSACTION had no effect
on InnoDB tables. Before 4.0.5, only REPEATABLE READ
and SERIALIZABLE were available.
You can query the global and session transaction isolation levels with these
statements:
SELECT @@global.tx_isolation;
SELECT @@tx_isolation;
In row level locking, InnoDB uses so-called next-key locking.
That means that besides index records, InnoDB can also lock
the ``gap'' before an index record to block insertions by other users
immediately before the index record. A next-key lock refers to
a lock that locks an index record and the gap before it.
A gap lock refers to a lock that only locks a gap before some
index record.
A detailed description of each isolation level in InnoDB:
READ UNCOMMITTED
SELECT statements are performed in a non-locking fashion, but a
possible earlier version of a record might be used. Thus, using this
isolation level, such reads are not ``consistent.'' This is also called
``dirty read''. Other than that, this isolation level works like READ
COMMITTED.
READ COMMITTED
A somewhat Oracle-like isolation level. All SELECT ... FOR UPDATE
and SELECT ... LOCK IN SHARE MODE statements lock only the index
records, not the gaps before them, and thus allow free inserting of new
records next to locked records. UPDATE and DELETE statements
that use a unique index with a unique search condition lock only the index
record found, not the gap before it. But still in range type UPDATE
and DELETE statements, InnoDB must set next-key or gap locks
and block insertions by other users to the gaps covered by the range. This
is necessary because ``phantom rows'' must be blocked for MySQL replication
and recovery to work.
Consistent reads behave as in Oracle: Each consistent read,
even within the same transaction, sets and reads its own fresh
snapshot.
See section 16.11.3 Consistent Non-Locking Read.
REPEATABLE READ
This is the default isolation level of InnoDB.
SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE,
UPDATE, and DELETE statements that use a unique index with a
unique search condition lock only the index record found, not the gap
before it.
With other search conditions, these operations employ next-key locking,
locking the index range scanned with next-key or gap locks, and block
new insertions by other users.
In consistent reads, there is an important difference from the
previous isolation level: In this level all consistent reads within
the same transaction read the same snapshot established by the first
read. This convention means that if you issue several plain
SELECT statements within the same transaction, these
SELECT statements are consistent also with respect to each
other.
See section 16.11.3 Consistent Non-Locking Read.
SERIALIZABLE
This level is like REPEATABLE READ, but all plain
SELECT statements are implicitly converted to
SELECT ... LOCK IN SHARE MODE.
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.
Suppose you want to add a new row into your table child,
and make sure that the child already has a parent in table
parent. The following example shows how to implement referential
integrity in your application code.
Suppose you use a consistent read to read the table parent
and indeed see the parent of the child in the table. Can you now safely
add the child row to table child? No, because it may
happen that meanwhile some other user deletes the parent row
from the table parent, without you being aware of it.
The solution is to perform the SELECT in a locking
mode using LOCK IN SHARE MODE:
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
Performing a read in share mode means that we read the latest
available data, and set a shared mode lock on the rows we read.
A shared mode lock prevents others from updating or deleting
the row we have read. Also,
if the latest data belongs to a yet uncommitted transaction of another
client connection, we will wait until that transaction commits.
After we see that the preceding query returns
the parent 'Jones', we can safely add the child record
to the child table and commit our transaction.
Let us look at another example: We have an integer counter field in
a table child_codes that we use to assign
a unique identifier to each child added to table child.
Obviously, using a consistent read or a shared mode read
to read the present value of the counter is not a good idea, since
then two users of the database may see the same value for the
counter, and duplicate-key error will occur if two users attempt to add
children with the same identifier to the table.
Here, LOCK IN SHARE MODE is not a good solution because if two
users read the counter at the same time, at least one of them
will end up in deadlock when attempting to update the counter.
In this case, there are two good ways to implement the reading and
incrementing of the counter:
(1) update the counter first by incrementing it by 1 and only after
that read it, or
(2) read the counter first with a lock mode FOR UPDATE, and
increment after that. The latter approach can be implemented as follows:
SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;
A SELECT ... FOR UPDATE reads the latest
available data, setting exclusive locks on each row it reads.
Thus it sets the same locks a searched SQL UPDATE would set
on the rows.
Please note that the above is merely an example of how SELECT ... FOR
UPDATE works. In MySQL, the specific task of generating a unique identifier
actually can be accomplished using only a single access to the table:
UPDATE child_codes
SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();
The SELECT statement merely retrieves the identifier information
(specific to the current connection). It does not access any table.
In row level locking, InnoDB uses an algorithm called next-key locking.
InnoDB does the row level locking in such a way that when it searches
or scans an index of a table, it sets shared or exclusive locks
on the index records it encounters. Thus the row level locks are
actually index record locks.
The locks InnoDB sets on index records also affect the ``gap'' before
that index record. If a user has a shared or exclusive lock on record
R in an index, another user cannot insert a new index record
immediately before R in the index order. This locking of gaps is
done to prevent the so-called phantom problem. Suppose you want to read and
lock all children from the child table with an identifier value
larger than 100, with the intent of updating some column in the selected
rows later:
SELECT * FROM child WHERE id > 100 FOR UPDATE;
Suppose there is an index on the id column. The query will scan that
index starting from the first record where id is bigger than 100.
Now, if the locks set on the index records would not lock out inserts made
in the gaps, a new row might meanwhile be inserted to the table. If you now
execute the same SELECT within the same transaction, you would see a
new row in the result set returned by the query. This is contrary the
isolation principle of transactions: A transaction should be able to run so
that the data it has read does not change during the transaction. If we
regard a set of rows as a data item, the new ``phantom'' child would violate
this isolation principle.
When InnoDB scans an index, it can also lock the gap
after the last record in the index. Just that happens in the previous
example: The locks set by InnoDB prevent any insert to
the table where id would be bigger than 100.
You can use next-key locking to implement a uniqueness
check in your application: If you read your data in share mode
and do not see a duplicate for a row you are going to insert,
then you can safely insert your row and know that the next-key
lock set on the successor of your row during the read will prevent
anyone meanwhile inserting a duplicate for your row. Thus the next-key
locking allows you to ``lock'' the non-existence of something in your
table.
Suppose you are running on the default REPEATABLE READ isolation level.
When you issue a consistent read, that is, an ordinary SELECT
statement, InnoDB will give your transaction a timepoint according
to which your query sees the database. If another transaction deletes
a row and commits after your timepoint was assigned, you will
not see the row as having been deleted. Inserts and updates are treated
similarly.
You can advance your timepoint by committing your transaction
and then doing another SELECT.
This is called multi-versioned concurrency control.
User A User B
SET AUTOCOMMIT=0; SET AUTOCOMMIT=0;
time
| SELECT * FROM t;
| empty set
| INSERT INTO t VALUES (1, 2);
|
v SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
---------------------
| 1 | 2 |
---------------------
1 row in set
In this example,
user A sees the row inserted by B only when B has committed the
insert and A has committed as well, so that the timepoint
is advanced past the commit of B.
If you want to see the ``freshest'' state of the database, you should
use either the READ COMMITTED isolation level or a locking read:
SELECT * FROM t LOCK IN SHARE MODE;
A locking read, an UPDATE, or a DELETE
generally set record locks on every index record that is scanned in
the processing of the SQL query. It does not matter if there are
WHERE conditions in the query that would exclude the row
from the result set of the query. InnoDB does not remember the exact
WHERE condition, but only knows which index ranges were
scanned. The record locks are normally next-key locks that also block
inserts to the ``gap'' immediately before the record.
If the locks to be set are exclusive,
then InnoDB always retrieves also the clustered index record and sets
a lock on it.
If you do not have indexes suitable for your query and
MySQL has to scan the whole table to process the query,
every row of the table will become locked, which in turn blocks all
inserts by other users to the table. It is important to create good indexes
so that your queries do not unnecessarily need to scan many rows.
-
SELECT ... FROM is a consistent read, reading a snapshot of the
database and setting no locks unless the transaction isolation level is set
to SERIALIZABLE. For SERIALIZABLE level, this sets shared
next-key locks on the index records it encounters.
-
SELECT ... FROM ... LOCK IN SHARE MODE
sets shared next-key locks on all index records the read encounters.
-
SELECT ... FROM ... FOR UPDATE
sets exclusive next-key locks on all index records the read
encounters.
-
INSERT INTO ... VALUES (...)
sets an exclusive lock on the inserted row. Note that this lock is not
a next-key lock and does not prevent other users from inserting to the
gap before the inserted row. If a duplicate-key error occurs, a shared
lock on the duplicate index record is set.
-
While initializing a previously specified
AUTO_INCREMENT column
on a table, InnoDB sets an exclusive lock on the end of the index
associated with the AUTO_INCREMENT column.
In accessing the auto-increment counter, InnoDB uses a specific table
lock mode AUTO-INC where the lock lasts only to the end of the
current SQL statement, instead of to the end of the whole transaction.
See section 16.11.1 InnoDB and AUTOCOMMIT.
Before MySQL 3.23.50, SHOW TABLE STATUS applied to a table
with an AUTO_INCREMENT column sets an exclusive row level lock
to the high end of the AUTO_INCREMENT index. This means
also that SHOW TABLE STATUS could cause a deadlock of transactions,
something that may surprise users. Starting from MySQL 3.23.50,
InnoDB fetches the value of a previously initialized
AUTO_INCREMENT column without setting any locks.
-
INSERT INTO T SELECT ... FROM S WHERE ...
sets an exclusive (non-next-key) lock on each row inserted into
T. It does the search on S as a consistent read, but
sets shared next-key locks on S if MySQL binary logging is turned
on. InnoDB has to set locks in the latter case: In
roll-forward recovery from a backup, every SQL statement has to be
executed in exactly the same way it was done originally.
-
CREATE TABLE ... SELECT ...
performs the SELECT as a consistent read or with shared locks,
as in the previous item.
-
REPLACE
is done like an insert if there is no collision on a unique key.
Otherwise, an exclusive next-key lock is placed on the row that has
to be updated.
-
UPDATE ... WHERE ...
sets an exclusive next-key lock on every record the search encounters.
-
DELETE FROM ... WHERE ...
sets an exclusive next-key lock on every record the search encounters.
-
If a
FOREIGN KEY constraint is defined on a table, any insert,
update, or delete that requires checking of the constraint condition
sets shared record level locks on the records it looks at to check the
constraint. InnoDB also sets these locks
in the case where the constraint fails.
-
LOCK TABLES sets table locks, but it is the higher MySQL layer above
the InnoDB layer that sets these locks. The automatic deadlock
detection of InnoDB cannot detect deadlocks where such table locks
are involved.
See section 16.11.9 Deadlock Detection and Rollback.
Also, since the higher MySQL layer does know about row level locks, it
is possible that you get a table lock on a table where another user
currently has row level locks. But that does not put transaction
integrity in danger.
See section 16.17 Restrictions on InnoDB Tables.
-
MySQL begins each client connection with autocommit mode enabled by default.
When autocommit enabled, MySQL does a commit after each SQL statement if
that statement did not return an error.
-
If an error is returned by an SQL statement, the commit/rollback
behavior depends on the error.
See section 16.16 Error Handling.
-
The following SQL statements cause an implicit commit of the
current transaction in MySQL:
-
CREATE TABLE (commits only if before MySQL 4.0.13 and MySQL
binary logging is used).
-
ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE,
DROP INDEX, DROP TABLE, LOAD MASTER DATA, LOCK
TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START
TRANSACTION, TRUNCATE, UNLOCK TABLES.
-
The
CREATE TABLE statement in InnoDB is processed as a single
transaction. It means that a ROLLBACK from the user does not
undo CREATE TABLE statements the user made during that
transaction.
-
If you have the
AUTOCOMMIT mode off and close a connection
without calling an explicit COMMIT of your transaction,
then MySQL will roll back your transaction.
InnoDB automatically detects a deadlock of transactions and rolls back a
transaction or transactions to prevent the deadlock. Starting from
MySQL 4.0.5, InnoDB tries to pick small transactions to roll
back. The size of a transaction is determined by the number of rows
it has inserted, updated, or deleted. Prior to 4.0.5, InnoDB
always rolled back the transaction whose lock request was the last
one to build a deadlock, that is, a cycle in the ``waits-for'' graph
of transactions.
InnoDB cannot detect deadlocks where a table lock set by a MySQL
LOCK TABLES statement is involved, or if a lock set
by another storage engine than InnoDB is involved. You have to resolve
these situations by setting the value of the innodb_lock_wait_timeout
system variable.
When InnoDB performs a complete rollback of a transaction, all the
locks of the transaction are released. However, if just a single SQL
statement is rolled back as a result of an error, some of the locks
set by the SQL statement may be preserved. This is because InnoDB
stores row locks in a format where it cannot afterward know which was
set by which SQL statement.
Deadlocks are a classic problem in transactional databases, but they
are not dangerous unless they are so frequent that you cannot run
certain transactions at all. Normally, you must write your
applications so that they are always prepared to re-issue a
transaction if it gets rolled back because of a deadlock.
InnoDB uses automatic row level locking. You can get deadlocks
even in the case of transactions that just insert or delete a
single row. That is because these operations are not really ``atomic;''
they automatically set locks on the (possibly several) index
records of the row inserted or deleted.
You can cope with deadlocks and reduce the likelihood of their occurrance
with the following techniques:
-
If the Unix `top' tool or the Windows `Task Manager' shows
that the CPU usage percentage with your workload is less than 70%,
your workload is probably
disk-bound. Maybe you are making too many transaction commits, or the
buffer pool is too small.
Making the buffer pool bigger can help, but do not set
it bigger than 80% of physical memory.
-
Wrap several modifications into one transaction.
InnoDB must
flush the log to disk at each transaction commit, if that transaction
made modifications to the database. Since the rotation speed of a disk
is typically
at most 167 revolutions/second, that constrains the number of commits
to the same 167/second if the disk does not fool the operating system.
-
If you can afford the loss of some latest committed transactions, you can
set the `my.cnf' parameter
innodb_flush_log_at_trx_commit
to 0. InnoDB tries to flush the log once per second anyway,
though the flush is not guaranteed.
-
Make your log files big, even as big as the buffer pool. When
InnoDB
has written the log files full, it has to write the modified contents
of the buffer pool to disk in a checkpoint. Small log files will cause many
unnecessary disk writes. The drawback of big log files is that recovery
time will be longer.
-
Make the log buffer quite big as well (say, 8MB).
-
Use the
VARCHAR column type instead of CHAR if you are
storing variable-length strings or if the column may contain many
NULL values. A CHAR(N) column always takes N bytes to
store data, even if the string is shorter, or its value is
NULL. Smaller tables fit better in the buffer pool and reduce
disk I/O.
-
(Relevant from 3.23.39 up.)
In some versions of GNU/Linux and Unix, flushing files to disk with the
Unix
fsync() and other similar methods is surprisingly slow.
The default method InnoDB uses is the fsync() function.
If you are not satisfied with the database write performance, you might
try setting innodb_flush_method in `my.cnf' to
O_DSYNC, though O_DSYNC seems to be slower on most
systems.
-
When importing data into
InnoDB, make sure that MySQL does not have
autocommit mode enabled. Then every insert requires a log flush to disk.
To disable autocommit during your import operation, surround it with SET
AUTOCOMMIT and COMMIT statements:
SET AUTOCOMMIT=0;
/* SQL import statements ... */
COMMIT;
If you use the mysqldump option --opt, you will get dump
files that are fast to import into to an InnoDB table, even without
wrapping them with the SET AUTOCOMMIT and COMMIT statements.
-
Beware of big rollbacks of mass inserts:
InnoDB uses the insert
buffer to save disk I/O in inserts, but no such mechanism is used in a
corresponding rollback. A disk-bound rollback can take 30 times the time
of the corresponding insert. Killing the database process will not help
because the rollback will start again at the server startup. The only
way to get rid of a runaway rollback is to increase the buffer pool so that
the rollback becomes CPU-bound and runs fast, or to use a special procedure.
See section 16.9.1 Forcing Recovery.
-
Beware also of other big disk-bound operations.
Use
DROP TABLE or TRUNCATE TABLE (from MySQL 4.0 up) to empty a
table, not DELETE FROM tbl_name.
-
Use the multiple-row
INSERT syntax to reduce
communication overhead between the client and the server if you need
to insert many rows:
INSERT INTO yourtable VALUES (1,2), (5,5), ...;
This tip is valid for inserts into any table type, not just InnoDB.
-
If you have
UNIQUE constraints on
secondary keys, starting from MySQL 3.23.52 and 4.0.3, you
can speed up table imports by temporarily turning off the uniqueness
checks during the import session:
SET UNIQUE_CHECKS=0;
For big tables, this saves a lot of disk I/O because InnoDB can then
use its insert buffer to write secondary index records in a batch.
-
If you have
FOREIGN KEY constraints in your tables, starting
from MySQL 3.23.52 and 4.0.3 you can speed up table imports by turning the
foreign key checks off for a while in the import session:
SET FOREIGN_KEY_CHECKS=0;
For big tables, this can save a lot of disk I/O.
-
If you often have recurring queries to tables that are not
updated frequently, use the query cache available as of MySQL 4.0:
[mysqld]
query_cache_type = ON
query_cache_size = 10M
In MySQL 4.0, the query cache works only with autocommit enabled. This
restriction is removed in MySQL 4.1.1 and up.
Starting from MySQL 3.23.42, InnoDB includes InnoDB Monitors
that print information about the InnoDB internal state. Starting from
MySQL 3.23.52 and 4.0.3, you can use the SQL statement SHOW INNODB
STATUS to fetch the output of the standard InnoDB Monitor to your
SQL client. The information is useful in performance tuning. If you are
using the mysql interactive SQL client, the output is more readable
if you replace the usual semicolon statement terminator by \G:
mysql> SHOW INNODB STATUS\G
| 
