This section describes the
command options and system variables.
System variables that are true or false can be enabled at
server startup by naming them, or disabled by using a
--skip- prefix. For example, to enable or
InnoDB checksums, you can use
on the command line, or
skip-innodb_checksums in an option file.
System variables that take a numeric value can be specified as
on the command line or as
in option files.
Many system variables can be changed at runtime (see Section 188.8.131.52, “Dynamic System Variables”).
For information about
SESSION variable scope modifiers, refer to
For more information on specifying options and system variables, see Section 4.2.3, “Specifying Program Options”.
|Name||Cmd-Line||Option File||System Var||Status Var||Var Scope||Dynamic|
InnoDB storage engine, if the
server was compiled with
In this case, the server will not start if the default storage
engine is set to
--default-storage-engine to set
the default to some other engine if necessary.
InnoDB creates a file
in the MySQL data directory. If enabled,
InnoDB periodically writes the output of
INNODB STATUS to this file.
InnoDB storage engine. See the
Whether InnoDB adaptive hash indexes are enabled or disabled
(see Section 184.108.40.206, “Adaptive Hash Indexes”). This variable is
enabled by default. Use
--skip-innodb_adaptive_hash_index at server
startup to disable it. This variable was added in MySQL
The size in bytes of a memory pool
uses to store data dictionary information and other internal
data structures. The more tables you have in your application,
the more memory you need to allocate here. If
InnoDB runs out of memory in this pool, it
starts to allocate memory from the operating system and writes
warning messages to the MySQL error log. The default value is
The increment size (in MB) for extending the size of an
auto-extending shared tablespace file when it becomes full.
The default value is 8. This variable does not affect the
per-table tablespace files that are created if you use
Those files are auto-extending regardless of the value of
The initial extensions are by small amounts, after which
extensions occur in increments of 4MB.
|Permitted Values (Windows)||Type|
The size of the buffer pool (in MB), if it is placed in the
AWE memory. If it is greater than 0,
the window in the 32-bit address space of
that AWE memory. A good value for
500MB. The maximum possible value is 63000.
To take advantage of AWE memory, you will need to recompile
MySQL yourself. The current project settings needed for doing
this can be found in the
innobase/os/os0proc.c source file.
This variable is relevant only in 32-bit Windows. If your
32-bit Windows operating system supports more than 4GB memory,
using so-called “Address Windowing Extensions,”
you can allocate the
InnoDB buffer pool
into the AWE physical memory using this variable.
The size in bytes of the memory buffer
InnoDB uses to cache data and indexes of
its tables. The default value is 8MB. 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. However, do not set it
too large because competition for physical memory might cause
paging in the operating system. Also, the time to initialize
the buffer pool is roughly proportional to its size. On large
installations, this initialization time may be significant.
For example, on a modern Linux x86_64 server, initialization
of a 10GB buffer pool takes approximately 6 seconds. See
Section 8.6.2, “The InnoDB Buffer Pool”
InnoDB can use checksum validation on all
pages read from the disk to ensure extra fault tolerance
against broken hardware or data files. This validation is
enabled by default. However, under some rare circumstances
(such as when running benchmarks) this extra safety feature is
unneeded and can be disabled with
--skip-innodb_checksums. This variable was
added in MySQL 5.0.3.
The number of threads that can commit at the same time. A value of 0 (the default) permits any number of transactions to commit simultaneously. This variable was added in MySQL 5.0.12.
The number of threads that can enter
concurrently is determined by the
variable. A thread is placed in a queue when it tries to enter
InnoDB if the number of threads has already
reached the concurrency limit. When a thread is permitted to
InnoDB, it is given a number of
“free tickets” equal to the value of
and the thread can enter and leave
freely until it has used up its tickets. After that point, the
thread again becomes subject to the concurrency check (and
possible queuing) the next time it tries to enter
InnoDB. The default value is 500. This
variable was added in MySQL 5.0.3.
The paths to individual data files and their sizes. The full
directory path to each data file is formed by concatenating
innodb_data_home_dir to each
path specified here. The file sizes are specified in KB, MB,
or GB (1024MB) by appending
G to the size
value. The sum of the sizes of the files must be at least
10MB. If you do not specify
default behavior is to create a single 10MB auto-extending
data file named
ibdata1. The size limit
of individual files is determined by your operating system.
You can set the file size to more than 4GB on those operating
systems that support big files. You can also use raw disk
partitions as data files. For detailed information on
InnoDB tablespace files, see
Section 14.2.1, “Configuring InnoDB”.
The common part of the directory path for all
InnoDB data files in the shared tablespace.
This setting does not affect the location of per-file
enabled. The default value is the MySQL
data directory. If you specify the value
as an empty string, you can use absolute file paths in
If this variable is enabled (the default),
InnoDB stores all data twice, first to the
doublewrite buffer, and then to the actual data files. This
variable can be turned off with
--skip-innodb_doublewrite for benchmarks or
cases when top performance is needed rather than concern for
data integrity or possible failures. This variable was added
in MySQL 5.0.3.
InnoDB shutdown mode. By default, the
value is 1, which causes a “fast” shutdown (the
normal type of shutdown). If the value is 0,
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 the value is 1,
InnoDB skips these operations at shutdown.
If the value is 2,
InnoDB will just flush
its logs and then shut down cold, as if MySQL had crashed; no
committed transaction will be lost, but crash recovery will be
done at the next startup. The value of 2 can be used as of
MySQL 5.0.5, except that it cannot be used on NetWare.
The number of file I/O threads in
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;
always uses the default value.
disabled (the default),
tables in the shared tablespace. If
InnoDB creates each new table
using its own
.ibd file for storing data
and indexes, rather than in the shared tablespace. See
Section 220.127.116.11, “Using Per-Table Tablespaces” for more
information including advantages and disadvantages of using
If the value of
is 0, the log buffer is written out to the log file once per
second and the flush to disk operation is performed on the log
file, but nothing is done at a transaction commit. When the
value is 1 (the default), the log buffer is written out to the
log file at each transaction commit and the flush to disk
operation is performed on the log file. When the value is 2,
the log buffer is written out to the file at each commit, but
the flush to disk operation is not performed on it. However,
the flushing on the log file takes place once per second also
when the value is 2. Note that the once-per-second flushing is
not 100% guaranteed to happen every second, due to process
The default value of 1 is required for full ACID compliance.
You can achieve better performance by setting the value
different from 1, but then you can lose up to one second worth
of transactions in a crash. With a value of 0, any
mysqld process crash can erase the last
second of transactions. With a value of 2, only an operating
system crash or a power outage can erase the last second of
works regardless of the value.
For the greatest possible durability and consistency in a
replication setup using
transactions, you should use
sync_binlog=1, and, before MySQL 5.0.3,
innodb-safe-binlog in your master server
innodb-safe-binlog is not needed from
Many operating systems and some disk hardware fool the
flush-to-disk operation. They may tell
mysqld that the flush has taken place,
even though it has not. Then the durability of transactions
is not guaranteed even with the setting 1, and in the worst
case a power outage can even corrupt the
InnoDB database. Using a battery-backed
disk cache in the SCSI disk controller or in the disk itself
speeds up file flushes, and makes the operation safer. You
can also try using the Unix command
hdparm to disable the caching of disk
writes in hardware caches, or use some other command
specific to the hardware vendor.
|Permitted Values (Unix)||Type|
|Permitted Values (Windows)||Type|
Defines the method used to
flush data to the
files and log
files, which can affect I/O throughput. This variable
is only configurable on Unix and Linux systems. On Windows
systems, the flush method is always
async_unbuffered and cannot be changed.
innodb_flush_method options include:
fsync() system call to flush
both the data and log files.
the default setting. The
option name should not be confused with the
fdatasync() system call, which is not
used by InnoDB as of MySQL 3.23.41.
O_SYNC to open and flush the log files,
fsync() to flush the data files.
InnoDB does not use
O_DSYNC directly because there have
been problems with it on many varieties of Unix.
directio() on Solaris) to open the data
files, and uses
fsync() to flush both
the data and log files. This option is available on some
GNU/Linux versions, FreeBSD, and Solaris.
How each settings affects performance depends on hardware
configuration and workload. Benchmark your particular
configuration to decide which setting to use, or whether to
keep the default setting. Examine the
variable to see the overall number of
fsync() calls for each setting. The mix of
read and write operations in your workload can affect how a
setting performs. For example, on a system with a hardware
RAID controller and battery-backed write cache,
O_DIRECT can help to avoid double buffering
InnoDB buffer pool and the
operating system's file system cache. On some systems where
InnoDB data and log files are located on a
SAN, the default value or
O_DSYNC might be
faster for a read-heavy workload with mostly
SELECT statements. Always test this
parameter with hardware and workload that reflect your
The crash recovery mode. Possible values are from 0 to 6. For
the meanings of these values and important information about
Section 18.104.22.168, “Forcing InnoDB Recovery”.
The timeout in seconds an
transaction may wait for a row lock before giving up. The
default value is 50 seconds. A transaction that tries to
access a row that is locked by another
InnoDB transaction will hang for at most
this many seconds before issuing the following error:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
When a lock wait timeout occurs, the current statement is not
executed. The current transaction is not
rolled back. (Until MySQL 5.0.13
rolled back the entire transaction if a lock wait timeout
happened. You can restore this behavior by starting the server
option, available as of MySQL 5.0.32. See also
Section 14.2.11, “InnoDB Error Handling”.)
InnoDB row locks only. A MySQL
table lock does not happen inside
and this timeout does not apply to waits for table locks.
InnoDB does detect transaction deadlocks in
its own lock table immediately and rolls back one transaction.
The lock wait timeout value does not apply to such a wait.
This variable affects how
InnoDB uses gap
locking for searches and index scans. Normally,
InnoDB uses an algorithm called
next-key locking that combines
index-row locking with gap locking.
performs row-level locking in such a way that when it searches
or scans a table index, it sets shared or exclusive locks on
the index records it encounters. Thus, the row-level locks are
actually index-record locks. In addition, a next-key lock on
an index record also affects the “gap” before
that index record. That is, a next-key lock is an index-record
lock plus a gap lock on the gap preceding the index record. If
one session has a shared or exclusive lock on record
R in an index, another session cannot
insert a new index record in the gap immediately before
R in the index order. See
Section 22.214.171.124, “InnoDB Record, Gap, and Next-Key Locks”.
By default, the value of
is 0 (disabled), which means that gap locking is enabled:
InnoDB uses next-key locks for searches and
index scans. To enable the variable, set it to 1. This causes
gap locking to be disabled:
only index-record locks for searches and index scans.
does not disable the use of gap locking for foreign-key
constraint checking or duplicate-key checking.
is a global setting and affects all sessions, whereas the
isolation level can be set globally for all sessions, or
individually per session.
can be set only at server startup, whereas the isolation
level can be set at startup or changed at runtime.
READ COMMITTED therefore
offers finer and more flexible control than
For additional details about the effect of isolation level on
gap locking, see Section 13.3.6, “SET TRANSACTION Syntax”.
may cause phantom problems because other sessions can insert
new rows into the gaps when gap locking is disabled. Suppose
that there is an index on the
id column of
child table and that you want to read
and lock all rows from the table having an identifier value
larger than 100, with the intention of updating some column in
the selected rows later:
SELECT * FROM child WHERE id > 100 FOR UPDATE;
The query scans the index starting from the first record where
id is greater than 100. If the locks set on
the index records in that range do not lock out inserts made
in the gaps, another session can insert a new row into the
table. Consequently, if you were to execute the same
SELECT again within the same
transaction, you would see a new row in the result set
returned by the query. This also means that if new items are
added to the database,
InnoDB does not
guarantee serializability. Therefore, if
InnoDB guarantees at most an
isolation level of
COMMITTED. (Conflict serializability is still
guaranteed.) For additional information about phantoms, see
Section 126.96.36.199, “Avoiding the Phantom Problem Using Next-Key Locking”.
Starting from MySQL 5.0.2, enabling
has an additional effect. For
InnoDB holds locks only for rows that it
updates or deletes. Record locks for nonmatching rows are
released after MySQL has evaluated the
WHERE condition. This greatly reduces the
probability of deadlocks, but they can still happen. Note that
enabling this variable still does not permit operations such
UPDATE to overtake other
similar operations (such as another
UPDATE) even when they affect
Consider the following example, beginning with this table:
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB; INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2); COMMIT;
In this case, table has no indexes, so searches and index scans use the hidden clustered index for record locking (see Section 188.8.131.52, “Clustered and Secondary Indexes”).
Suppose that one client performs an
UPDATE using these statements:
SET autocommit = 0; UPDATE t SET b = 5 WHERE b = 3;
Suppose also that a second client performs an
UPDATE by executing these
statements following those of the first client:
SET autocommit = 0; UPDATE t SET b = 4 WHERE b = 2;
InnoDB executes each
UPDATE, it first acquires an
exclusive lock for each row, and then determines whether to
modify it. If
InnoDB does not
modify the row and
is enabled, it releases the lock. Otherwise,
InnoDB retains the lock until the
end of the transaction. This affects transaction processing as
x-lock(1,2); retain x-lock x-lock(2,3); update(2,3) to (2,5); retain x-lock x-lock(3,2); retain x-lock x-lock(4,3); update(4,3) to (4,5); retain x-lock x-lock(5,2); retain x-lock
x-lock(1,2); block and wait for first UPDATE to commit or roll back
x-lock(1,2); unlock(1,2) x-lock(2,3); update(2,3) to (2,5); retain x-lock x-lock(3,2); unlock(3,2) x-lock(4,3); update(4,3) to (4,5); retain x-lock x-lock(5,2); unlock(5,2)
UPDATE proceeds part
way before it blocks. It begins acquiring x-locks, and blocks
when it tries to acquire one for a row still locked by first
UPDATE. The second
UPDATE does not proceed until
UPDATE commits or
x-lock(1,2); update(1,2) to (1,4); retain x-lock x-lock(2,3); block and wait for first UPDATE to commit or roll back
In this case, the second
must wait for a commit or rollback of the first
UPDATE, even though it affects
different rows. The first
UPDATE has an exclusive lock on
row (2,3) that it has not released. As the second
UPDATE scans rows, it tries to
acquire an exclusive lock for that same row, which it cannot
This variable is unused, and is deprecated as of MySQL 5.0.24. It is removed in MySQL 5.1
Whether to log
InnoDB archive files. This
variable is present for historical reasons, but is unused.
Recovery from a backup is done by MySQL using its own log
files, so there is no need to archive
InnoDB log files. The default for this
variable is 0.
The size in bytes of the buffer that
uses to write to the log files on disk. The default value is
1MB. Sensible values range from 1MB to 8MB. A large log buffer
enables 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 saves disk I/O.
The size in bytes of each log
file in a log
group. The combined size of log files
cannot exceed a maximum value that is slightly less than 4GB.
A pair of 2047 MB log files, for example, would allow you to
approach the range limit but not exceed it. The default value
is 5MB. Sensible values range from 1MB to
N-th of the size of the buffer
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 is 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 (and recommended) value is 2.
The directory path to the
InnoDB log files.
If you do not specify any
variables, the default is to create two files named
ib_logfile1 in the MySQL data directory.
Their size is given by the size of the
This is an integer in the range from 0 to 100. The default
value is 90. The main thread in
tries to write pages from the buffer pool so that the
percentage of dirty (not yet written) pages will not exceed
InnoDB transaction system maintains a
list of transactions that have index records delete-marked by
DELETE operations. Let the
length of this list be
DELETE operation is delayed by
milliseconds. The delay is computed in the beginning of a
purge batch, every ten seconds. The operations are not delayed
if purge cannot run because of an old consistent read view
that could see the rows to be purged.
A typical setting for a problematic workload might be 1
million, assuming that transactions are small, only 100 bytes
in size, and it is permissible to have 100MB of unpurged
InnoDB table rows.
The lag value is displayed as the history list length in the
TRANSACTIONS section of InnoDB Monitor
output. For example, if the output includes the following
lines, the lag value is 20:
------------ TRANSACTIONS ------------ Trx id counter 0 290328385 Purge done for trx's n:o < 0 290315608 undo n:o < 0 17 History list length 20
The number of identical copies of log groups to keep for the database. This should be set to 1.
This variable is relevant only if you use multiple tablespaces
InnoDB. It specifies the maximum number
.ibd files that
InnoDB can keep open at one time. The
minimum value is 10. The default value is 300.
The file descriptors used for
InnoDB only. They are independent
of those specified by the
option, and do not affect the operation of the table cache.
In MySQL 5.0.13 and up,
InnoDB rolls back
only the last statement on a transaction timeout by default.
is specified, a transaction timeout causes
InnoDB to abort and roll back the entire
transaction (the same behavior as before MySQL 5.0.13). This
variable was added in MySQL 5.0.32.
If this option is given, then after a crash recovery by
InnoDB, mysqld truncates
the binary log after the last not-rolled-back transaction in
the log. The option also causes
print an error if the binary log is smaller or shorter than it
should be. See Section 5.2.3, “The Binary Log”. This variable was
removed in MySQL 5.0.3, having been made obsolete by the
introduction of XA transaction support. You should set
ON or 1 to ensure consistency. See
|Variable Scope||Global, Session|
InnoDB support for two-phase commit
in XA transactions, causing an extra disk flush for
transaction preparation. This setting is the default. The XA
mechanism is used internally and is essential for any server
that has its binary log turned on and is accepting changes to
its data from more than one thread. If you turn it off,
transactions can be written to the binary log in a different
order from the one in which the live database is committing
them. This can produce different data when the binary log is
replayed in disaster recovery or on a replication slave. Do
not turn it off on a replication master server unless you have
an unusual setup where only one thread is able to change data.
For a server that is accepting data changes from only one
thread, it is safe and recommended to turn off this option to
improve performance for
tables. For example, you can turn it off on replication slaves
where only the replication SQL thread is changing data.
You can also turn off this option if you do not need it for safe binary logging or replication, and you also do not use an external XA transaction manager.
This variable was added in MySQL 5.0.3.
The number of times a thread waits for an
InnoDB mutex to be freed before the thread
is suspended. The default value is 20. This variable was added
in MySQL 5.0.3.
|Variable Scope||Global, Session|
autocommit = 0,
TABLES; MySQL does not return from
TABLES ... WRITE until all other threads have
released all their locks to the table. The default value of
innodb_table_locks is 1,
which means that
causes InnoDB to lock a table internally if
autocommit = 0.
|Permitted Values (<= 5.0.7)||Type|
|Permitted Values (>= 5.0.8, <= 5.0.18)||Type|
|Permitted Values (>= 5.0.19, <= 5.0.20)||Type|
|Permitted Values (>= 5.0.21)||Type|
InnoDB tries to keep the number of
operating system threads concurrently inside
InnoDB less than or equal to the limit
given by this variable (
operating system threads to process user transactions). Once
the number of threads reaches this limit, additional threads
are placed into a wait state within a “First In, First
Out” (FIFO) queue for execution. Threads waiting for
locks are not counted in the number of concurrently executing
The range of this variable is 0 to 1000. A value of 20 or
higher is interpreted as infinite concurrency before MySQL
5.0.19. From 5.0.19 on, you can disable thread concurrency
checking by setting the value to 0. Disabling thread
concurrency checking enables
create as many threads as it needs.
The default value has changed several times: 8 before MySQL 5.0.8, 20 (infinite) from 5.0.8 through 5.0.18, 0 (infinite) from 5.0.19 to 5.0.20, and 8 (finite) from 5.0.21 on.
Consider setting this variable if your MySQL instance shares
CPU resources with other applications, or if your workload or
number of concurrent users is growing. The correct setting
depends on workload, computing environment, and the version of
MySQL that you are running. You will need to test a range of
values to determine the setting that provides the best
innodb_thread_concurrency is a
dynamic variable, which allows you to experiment with
different settings on a live test system. If a particular
setting performs poorly, you can quickly set
innodb_thread_concurrency back to 0.
Use the following guidelines to help find and maintain an appropriate setting:
If the number of concurrent user threads for a workload is
less than 64, set
If your workload is consistently heavy or occasionally
spikes, start by setting
lowering the value to 96, 80, 64, and so on, until you
find the number of threads that provides the best
performance. For example, suppose your system typically
has 40 to 50 users, but periodically the number increases
to 60, 70, or even 200. You find that performance is
stable at 80 concurrent users but starts to show a
regression above this number. In this case, you would set
innodb_thread_concurrency=80 to avoid
If you do not want
InnoDB to use more
than a certain number of vCPUs for user threads (20 vCPUs
for example), set
innodb_thread_concurrency to this
number (or possibly lower, depending on performance
results). If your goal is to isolate MySQL from other
applications, you may consider binding the
mysqld process exclusively to the
vCPUs. Be aware, however, that exclusive binding could
result in non-optimal hardware usage if the
mysqld process is not consistently
busy. In this case, you might bind the
mysqld process to the vCPUs but also
allow other applications to use some or all of the vCPUs.
From an operating system perspective, using a resource
management solution (if available) to manage how CPU
time is shared among applications may be preferable to
mysqld process. For
example, you could assign 90% of vCPU time to a given
application while other critical process are
not running, and scale that value back to 40%
when other critical processes are
innodb_thread_concurrency values that
are too high can cause performance regression due to
increased contention on system internals and resources.
In some cases, the optimal
innodb_thread_concurrency setting can
be smaller than the number of vCPUs.
If an operation, such as an
TABLE operation, completes in a few minutes on
an idle system but takes hours on a busy system (perhaps
aborting due to insufficient log space), a lower but still
value has been shown to allow such operations to complete
in times comparable to those on an idle system. A lower
but still acceptable
innodb_thread_concurrency value, in
this case, is a value that might reduce overall
performance by 10 or 20 percent.
Monitor and analyze your system regularly. Changes to
workload, number of users, or computing environment may
require that you adjust the
|Permitted Values (>= 5.0.8, <= 5.0.18)||Type|
|Permitted Values (>= 5.0.19, <= 5.0.20)||Type|
|Permitted Values (>= 5.0.21)||Type|
InnoDB threads sleep before
InnoDB queue, in microseconds.
The default value is 10,000. A value of 0 disables sleep. This
variable was added in MySQL 5.0.3.
InnoDB uses random numbers to
generate dives into indexes for calculating index cardinality.
However, under certain conditions, the algorithm does not
generate random numbers, so
TABLE sometimes does not update cardinality
estimates properly. An alternative algorithm was introduced in
MySQL 5.0.82 with better randomization properties, and the
system variable which algorithm to use. The default value of
the variable is 1 (
ON), to use the original
algorithm for compatibility with existing applications. The
variable can be set to 0 (
OFF) to use the
new algorithm with improved randomness.
You should also take into consideration the value of
sync_binlog, which controls
synchronization of the binary log to disk.