This section describes the InnoDB
-related
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 disable InnoDB
checksums,
you can use --innodb_checksums
or
--skip-innodb_checksums
on the command line, or
innodb_checksums
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
var_name
=value
in option files. For more information on specifying options and
system variables, see Section 4.2.3, “Specifying Program Options”. Many of
the system variables can be changed at runtime (see
Section 5.1.5.2, “Dynamic System Variables”).
var_name
=value
Certain options control the locations and layout of the
InnoDB
data files.
Section 13.6.2, “Configuring InnoDB
” explains how to use these
options. Many other options, that you might not use initially,
help to tune InnoDB
performance characteristics
based on machine capacity and your database
workload. The
performance-related options are explained in
Section 13.6.14, “InnoDB
Performance Tuning and Troubleshooting” and
Section 13.7.7, “Performance and Scalability Enhancements”.
Table 13.5. InnoDB
Option/Variable
Reference
InnoDB
Command Options
Command-Line Format --ignore-builtin-innodb
Option-File Format ignore-builtin-innodb
Option Sets Variable Yes, ignore_builtin_innodb
Variable Name ignore-builtin-innodb
Variable Scope Global Dynamic Variable No Permitted Values Type boolean
This option causes the server to behave as if the built-in
InnoDB
is not present. It has these effects:Other
InnoDB
options (including--innodb
and--skip-innodb
) will not be recognized and should not be used.The server will not start if the default storage engine is set to
InnoDB
. Use--default-storage-engine
to set the default to some other engine if necessary.InnoDB
will not appear in the output ofSHOW ENGINES
.
Controls loading of the
InnoDB
storage engine, if the server was compiled withInnoDB
support. This option has a tristate format, with possible values ofOFF
,ON
, orFORCE
. See Section 5.1.3, “Server Options for Loading Plugins”.To disable
InnoDB
, use--innodb=OFF
or--skip-innodb
. In this case, the server will not start if the default storage engine is set toInnoDB
. Use--default-storage-engine
to set the default to some other engine if necessary.Controls whether
InnoDB
creates a file namedinnodb_status.
in the MySQL data directory. If enabled,<pid>
InnoDB
periodically writes the output ofSHOW ENGINE INNODB STATUS
to this file.By default, the file is not created. To create it, start mysqld with the
--innodb-status-file=1
option. The file is deleted during normal shutdown.Disable the
InnoDB
storage engine. See the description of--innodb
.
InnoDB
System Variables
Whether the server was started with the
--ignore-builtin-innodb
option, which causes the server to behave as if the built-inInnoDB
is not present. For more information, see the description of--ignore-builtin-innodb
under “InnoDB
Command Options” earlier in this section.Command-Line Format --innodb_adaptive_flushing=#
Option-File Format innodb_adaptive_flushing
Option Sets Variable Yes, innodb_adaptive_flushing
Variable Name innodb_adaptive_flushing
Variable Scope Global Dynamic Variable Yes Permitted Values Type boolean
Default ON
InnoDB Plugin
1.0.4 and up uses a heuristic to determine when to flush dirty pages in the buffer cache. This heuristic is designed to avoid bursts of I/O activity and is used wheninnodb_adaptive_flushing
is enabled (which is the default).Command-Line Format --innodb_adaptive_hash_index=#
Option-File Format innodb_adaptive_hash_index
Option Sets Variable Yes, innodb_adaptive_hash_index
Variable Name innodb_adaptive_hash_index
Variable Scope Global Dynamic Variable Yes Permitted Values Type boolean
Default ON
Whether InnoDB adaptive hash indexes are enabled or disabled (see Section 13.6.11.4, “Adaptive Hash Indexes”). This variable is enabled by default. Use
--skip-innodb_adaptive_hash_index
at server startup to disable it.innodb_additional_mem_pool_size
Command-Line Format --innodb_additional_mem_pool_size=#
Option-File Format innodb_additional_mem_pool_size
Option Sets Variable Yes, innodb_additional_mem_pool_size
Variable Name innodb_additional_mem_pool_size
Variable Scope Global Dynamic Variable No Permitted Values Type numeric
Default 8388608
Range 2097152-4294967295
The size in bytes 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 need to allocate here. IfInnoDB
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 8MB.Command-Line Format --innodb_autoextend_increment=#
Option-File Format innodb_autoextend_increment
Option Sets Variable Yes, innodb_autoextend_increment
Variable Name innodb_autoextend_increment
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 64
Range 1-1000
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
innodb_file_per_table=1
. Those files are auto-extending regardless of the value ofinnodb_autoextend_increment
. The initial extensions are by small amounts, after which extensions occur in increments of 4MB.Command-Line Format --innodb_autoinc_lock_mode=#
Option-File Format innodb_autoinc_lock_mode
Option Sets Variable Yes, innodb_autoinc_lock_mode
Variable Name innodb_autoinc_lock_mode
Variable Scope Global Dynamic Variable No Permitted Values Type numeric
Default 1
Valid Values 0
,1
,2
The locking mode to use for generating auto-increment values. The permissible values are 0, 1, or 2, for “traditional”, “consecutive”, or “interleaved” lock mode, respectively. Section 13.6.5.3, “
AUTO_INCREMENT
Handling inInnoDB
”, describes the characteristics of these modes.This variable has a default of 1 (“consecutive” lock mode).
Version Introduced 5.5.4 Command-Line Format --innodb_buffer_pool_instances=#
Option-File Format innodb_buffer_pool_instances
Option Sets Variable Yes, innodb_buffer_pool_instances
Variable Name innodb_buffer_pool_instances
Variable Scope Global Dynamic Variable No Permitted Values Type numeric
Default 1
Range 1-64
The number of regions that the
InnoDB
buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.This option only takes effect when you set the
innodb_buffer_pool_size
to a size of 1 gigabyte or more. The total size you specify is divided up among all the buffer pools. We recommend specifying a combination ofinnodb_buffer_pool_instances
andinnodb_buffer_pool_size
so that each buffer pool instance is at least 1 gigabyte.Command-Line Format --innodb_buffer_pool_size=#
Option-File Format innodb_buffer_pool_size
Option Sets Variable Yes, innodb_buffer_pool_size
Variable Name innodb_buffer_pool_size
Variable Scope Global Dynamic Variable No Permitted Values Platform Bit Size 32
Type numeric
Default 134217728
Range 1048576-2**32-1
Permitted Values Platform Bit Size 64
Type numeric
Default 134217728
Range 1048576-2**64-1
The size in bytes of the memory buffer
InnoDB
uses to cache data and indexes of its tables. The default value is 128MB, increased from a historical default of 8MB. The maximum value depends on the CPU architecture, 32-bit or 64-bit. For 32-bit systems, the CPU architecture and operating system sometimes impose a lower practical maximum size.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. Be prepared to scale back this value if these other issues occur:
Competition for physical memory might cause paging in the operating system.
InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified size.
The address space must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses.
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 7.9.1, “The
InnoDB
Buffer Pool”.
Command-Line Format --innodb_change_buffering=#
Option-File Format innodb_change_buffering
Option Sets Variable Yes, innodb_change_buffering
Variable Name innodb_change_buffering
Variable Scope Global Dynamic Variable Yes Permitted Values (<= 5.5.3) Type enumeration
Default inserts
Valid Values inserts
,none
Permitted Values (>= 5.5.4) Type enumeration
Default all
Valid Values inserts
,deletes
,purges
,changes
,all
,none
Whether
InnoDB
performs change buffering, an optimization that delays write operations to secondary indexes so that the I/O operations can be performed sequentially. The permitted values areinserts
(buffer insert operations),deletes
(buffer delete operations; strictly speaking, the writes that mark index records for later deletion during a purge operation),changes
(buffer insert and delete-marking operations),purges
(buffer purge operations, the writes when deleted index entries are finally garbage-collected),all
(buffer insert, delete-marking, and purge operations) andnone
(do not buffer any operations). The default isall
. For details, see Section 13.7.7.4, “Controlling InnoDB Change Buffering”.Command-Line Format --innodb_checksums
Option-File Format innodb_checksums
Option Sets Variable Yes, innodb_checksums
Variable Name innodb_checksums
Variable Scope Global Dynamic Variable No Permitted Values Type boolean
Default ON
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
.Command-Line Format --innodb_commit_concurrency=#
Option-File Format innodb_commit_concurrency
Option Sets Variable Yes, innodb_commit_concurrency
Variable Name innodb_commit_concurrency
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 0
Range 0-1000
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.
The value of
innodb_commit_concurrency
cannot be changed at runtime from zero to nonzero or vice versa. The value can be changed from one nonzero value to another.Command-Line Format --innodb_concurrency_tickets=#
Option-File Format innodb_concurrency_tickets
Option Sets Variable Yes, innodb_concurrency_tickets
Variable Name innodb_concurrency_tickets
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 500
Range 1-4294967295
The number of threads that can enter
InnoDB
concurrently is determined by theinnodb_thread_concurrency
variable. A thread is placed in a queue when it tries to enterInnoDB
if the number of threads has already reached the concurrency limit. When a thread is permitted to enterInnoDB
, it is given a number of “free tickets” equal to the value ofinnodb_concurrency_tickets
, and the thread can enter and leaveInnoDB
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 enterInnoDB
. The default value is 500.Command-Line Format --innodb_data_file_path=name
Option-File Format innodb_data_file_path
Variable Name innodb_data_file_path
Variable Scope Global Dynamic Variable No Permitted Values Type file name
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 appendingK
,M
, orG
to the size value. The sum of the sizes of the files must be at least 10MB. If you do not specifyinnodb_data_file_path
, the default behavior is to create a single 10MB auto-extending data file namedibdata1
. 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 configuringInnoDB
tablespace files, see Section 13.6.2, “ConfiguringInnoDB
”.Command-Line Format --innodb_data_home_dir=path
Option-File Format innodb_data_home_dir
Option Sets Variable Yes, innodb_data_home_dir
Variable Name innodb_data_home_dir
Variable Scope Global Dynamic Variable No Permitted Values Type file name
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 tablespaces wheninnodb_file_per_table
is enabled. The default value is the MySQL data directory. If you specify the value as an empty string, you can use absolute file paths ininnodb_data_file_path
.Command-Line Format --innodb-doublewrite
Option-File Format innodb_doublewrite
Option Sets Variable Yes, innodb_doublewrite
Variable Name innodb_doublewrite
Variable Scope Global Dynamic Variable No Permitted Values Type boolean
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.Command-Line Format --innodb_fast_shutdown[=#]
Option-File Format innodb_fast_shutdown
Option Sets Variable Yes, innodb_fast_shutdown
Variable Name innodb_fast_shutdown
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 1
Valid Values 0
,1
,2
The
InnoDB
shutdown mode. If the value is 0,InnoDB
does a slow shutdown, a full purge and an insert buffer merge before shutting down. If the value is 1 (the default),InnoDB
skips these operations at shutdown, a process known as a fast shutdown. If the value is 2,InnoDB
flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.
Use
innodb_fast_shutdown=2
in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption.Command-Line Format --innodb_file_format=#
Option-File Format innodb_file_format
Option Sets Variable Yes, innodb_file_format
Variable Name innodb_file_format
Variable Scope Global Dynamic Variable Yes Permitted Values (>= 5.5.0, <= 5.5.6) Type string
Default Barracuda
Valid Values Antelope
,Barracuda
Permitted Values (>= 5.5.7) Type string
Default Antelope
Valid Values Antelope
,Barracuda
The file format to use for new
InnoDB
tables. Currently,Antelope
andBarracuda
are supported. This applies only for tables that have their own tablespace, so for it to have an effect,innodb_file_per_table
must be enabled. The Barracuda file format is required for certain InnoDB features such as table compression.Command-Line Format --innodb_file_format_check=#
Option-File Format innodb_file_format_check
Option Sets Variable Yes, innodb_file_format_check
Variable Name innodb_file_format_check
Variable Scope Global Dynamic Variable No Permitted Values (<= 5.5.0) Type string
Default Antelope
Permitted Values (>= 5.5.4) Type string
Default Barracuda
Permitted Values (>= 5.5.5) Type boolean
Default ON
As of MySQL 5.5.5, this variable can be set to 1 or 0 at server startup to enable or disable whether
InnoDB
checks the file format tag in the shared tablespace (for example,Antelope
orBarracuda
). If the tag is checked and is higher than that supported by the current version ofInnoDB
, an error occurs andInnoDB
does not start. If the tag is not higher,InnoDB
sets the value ofinnodb_file_format_max
to the file format tag.Before MySQL 5.5.5, this variable can be set to 1 or 0 at server startup to enable or disable whether
InnoDB
checks the file format tag in the shared tablespace. If the tag is checked and is higher than that supported by the current version ofInnoDB
, an error occurs andInnoDB
does not start. If the tag is not higher,InnoDB
sets the value ofinnodb_file_format_check
to the file format tag, which is the value seen at runtime.Version Introduced 5.5.5 Command-Line Format --innodb_file_format_max=#
Option-File Format innodb_file_format_max
Option Sets Variable Yes, innodb_file_format_max
Variable Name innodb_file_format_max
Variable Scope Global Dynamic Variable Yes Permitted Values Type string
Default Antelope
Valid Values Antelope
,Barracuda
At server startup,
InnoDB
sets the value ofinnodb_file_format_max
to the file format tag in the shared tablespace (for example,Antelope
orBarracuda
). If the server creates or opens a table with a “higher” file format, it sets the value ofinnodb_file_format_max
to that format.This variable was added in MySQL 5.5.5.
Command-Line Format --innodb_file_per_table
Option-File Format innodb_file_per_table
Variable Name innodb_file_per_table
Variable Scope Global Dynamic Variable Yes Permitted Values (>= 5.5.0, <= 5.5.6) Type boolean
Default ON
Permitted Values (>= 5.5.7) Type boolean
Default OFF
If
innodb_file_per_table
is disabled (the default),InnoDB
creates tables in the system tablespace. Ifinnodb_file_per_table
is enabled,InnoDB
creates each new table using its own.ibd
file for storing data and indexes, rather than in the system tablespace. See Section 13.6.3, “Using Per-Table Tablespaces” for information about the features, such asInnoDB
table compression, that only work for tables stored in separate tablespaces.innodb_flush_log_at_trx_commit
Command-Line Format --innodb_flush_log_at_trx_commit[=#]
Option-File Format innodb_flush_log_at_trx_commit
Option Sets Variable Yes, innodb_flush_log_at_trx_commit
Variable Name innodb_flush_log_at_trx_commit
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 1
Valid Values 0
,1
,2
If the value of
innodb_flush_log_at_trx_commit
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 scheduling issues.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 transactions.
InnoDB
's crash recovery works regardless of the value.For the greatest possible durability and consistency in a replication setup using
InnoDB
with transactions, useinnodb_flush_log_at_trx_commit=1
andsync_binlog=1
in your master servermy.cnf
file.CautionMany 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.Command-Line Format --innodb_flush_method=name
Option-File Format innodb_flush_method
Option Sets Variable Yes, innodb_flush_method
Variable Name innodb_flush_method
Variable Scope Global Dynamic Variable No Permitted Values Type (solaris) enumeration
Default fdatasync
Valid Values O_DSYNC
,O_DIRECT
By default,
InnoDB
uses thefsync()
system call to flush both the data and log files. Ifinnodb_flush_method
option is set toO_DSYNC
,InnoDB
usesO_SYNC
to open and flush the log files, andfsync()
to flush the data files. IfO_DIRECT
is specified (available on some GNU/Linux versions, FreeBSD, and Solaris),InnoDB
usesO_DIRECT
(ordirectio()
on Solaris) to open the data files, and usesfsync()
to flush both the data and log files. Note thatInnoDB
usesfsync()
instead offdatasync()
, and it does not useO_DSYNC
by default because there have been problems with it on many varieties of Unix. This variable is relevant only for Unix. On Windows, the flush method is alwaysasync_unbuffered
and cannot be changed.Different values of this variable can have a marked effect on
InnoDB
performance. For example, on some systems whereInnoDB
data and log files are located on a SAN, it has been found that settinginnodb_flush_method
toO_DIRECT
can degrade performance of simpleSELECT
statements by a factor of three.Formerly, a value of
fdatasync
also specified the default behavior. This value was removed, due to confusion that a value offdatasync
causedfsync()
system calls rather thanfdatasync()
for flushing. To obtain the default value now, do not set any value forinnodb_flush_method
at startup.Command-Line Format --innodb_force_recovery=#
Option-File Format innodb_force_recovery
Option Sets Variable Yes, innodb_force_recovery
Variable Name innodb_force_recovery
Variable Scope Global Dynamic Variable No Permitted Values Type enumeration
Default 0
Valid Values 0
,1
,2
,3
,4
,5
,6
The crash recovery mode. Possible values are from 0 to 6. The meanings of these values are described in Section 13.6.7.2, “Forcing
InnoDB
Recovery”.WarningOnly set this variable greater than 0 in an emergency situation, to dump your tables from a corrupt database. As a safety measure,
InnoDB
prevents any changes to its data when this variable is greater than 0. This restriction also prohibits some queries that useWHERE
orORDER BY
clauses, because high values can prevent queries from using indexes.Command-Line Format --innodb_io_capacity=#
Option-File Format innodb_io_capacity
Option Sets Variable Yes, innodb_io_capacity
Variable Name innodb_io_capacity
Variable Scope Global Dynamic Variable Yes Permitted Values Platform Bit Size 32
Type numeric
Default 200
Range 100-2**32-1
Permitted Values Platform Bit Size 64
Type numeric
Default 200
Range 100-2**64-1
The maximum number of I/O operations per second that
InnoDB
will perform. This variable can be set at server startup, which enables higher values to be selected for systems capable of higher I/O rates. Having a higher I/O rate can help the server handle a higher rate of row changes because it may be able to increase dirty-page flushing, deleted-row removal, and application of changes to the insert buffer. The default value ofinnodb_io_capacity
is 200. In general, you can increase the value as a function of the number of drives used forInnoDB
I/O. Although you can specify a very high number, in practice such large values cease to have any benefit; for example, a value of one million would be considered very high.The ability to raise the I/O limit should be especially beneficial on platforms that support many IOPS. For example, systems that use multiple disks or solid-state disks for
InnoDB
are likely to benefit from the ability to control this parameter.Command-Line Format --innodb_lock_wait_timeout=#
Option-File Format innodb_lock_wait_timeout
Option Sets Variable Yes, innodb_lock_wait_timeout
Variable Name innodb_lock_wait_timeout
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type numeric
Default 50
Range 1-1073741824
The timeout in seconds an
InnoDB
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 anotherInnoDB
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. (To have the entire transaction roll back, start the server with the
--innodb_rollback_on_timeout
option. See also Section 13.6.13, “InnoDB
Error Handling”.)innodb_lock_wait_timeout
applies toInnoDB
row locks only. A MySQL table lock does not happen insideInnoDB
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.innodb_locks_unsafe_for_binlog
Command-Line Format --innodb_locks_unsafe_for_binlog
Option-File Format innodb_locks_unsafe_for_binlog
Option Sets Variable Yes, innodb_locks_unsafe_for_binlog
Variable Name innodb_locks_unsafe_for_binlog
Variable Scope Global Dynamic Variable No Permitted Values Type boolean
Default OFF
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.InnoDB
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 recordR
in an index, another session cannot insert a new index record in the gap immediately beforeR
in the index order. See Section 13.6.9.4, “InnoDB
Record, Gap, and Next-Key Locks”.By default, the value of
innodb_locks_unsafe_for_binlog
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:InnoDB
uses only index-record locks for searches and index scans.Enabling
innodb_locks_unsafe_for_binlog
does not disable the use of gap locking for foreign-key constraint checking or duplicate-key checking.The effect of enabling
innodb_locks_unsafe_for_binlog
is similar to but not identical to setting the transaction isolation level toREAD COMMITTED
:Enabling
innodb_locks_unsafe_for_binlog
is a global setting and affects all sessions, whereas the isolation level can be set globally for all sessions, or individually per session.innodb_locks_unsafe_for_binlog
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 thaninnodb_locks_unsafe_for_binlog
. For additional details about the effect of isolation level on gap locking, see Section 12.3.6, “SET TRANSACTION
Syntax”.Enabling
innodb_locks_unsafe_for_binlog
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 theid
column of thechild
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 sameSELECT
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, ifinnodb_locks_unsafe_for_binlog
is enabled,InnoDB
guarantees at most an isolation level ofREAD COMMITTED
. (Conflict serializability is still guaranteed.) For additional information about phantoms, see Section 13.6.9.5, “Avoiding the Phantom Problem Using Next-Key Locking”.Enabling
innodb_locks_unsafe_for_binlog
has additional effects:For
UPDATE
orDELETE
statements,InnoDB
holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated theWHERE
condition. This greatly reduces the probability of deadlocks, but they can still happen.For
UPDATE
statements, if a row is already locked,InnoDB
performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches theWHERE
condition of theUPDATE
. If the row matches (must be updated), MySQL reads the row again and this timeInnoDB
either locks it or waits for a lock on it.
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 13.6.11.1, “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;
As
InnoDB
executes eachUPDATE
, it first acquires an exclusive lock for each row, and then determines whether to modify it. IfInnoDB
does not modify the row andinnodb_locks_unsafe_for_binlog
is enabled, it releases the lock. Otherwise,InnoDB
retains the lock until the end of the transaction. This affects transaction processing as follows.If
innodb_locks_unsafe_for_binlog
is disabled, the firstUPDATE
acquires x-locks and does not release any of them: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
The second
UPDATE
blocks as soon as it tries to acquire any locks (because first update has retained locks on all rows), and does not proceed until the firstUPDATE
commits or rolls back:x-lock(1,2); block and wait for first UPDATE to commit or roll back
If
innodb_locks_unsafe_for_binlog
is enabled, the firstUPDATE
acquires x-locks and releases those for rows that it does not modify: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)
For the second
UPDATE
,InnoDB
does a “semi-consistent” read, returning the latest committed version of each row to MySQL so that MySQL can determine whether the row matches theWHERE
condition of theUPDATE
:x-lock(1,2); update(1,2) to (1,4); retain x-lock x-lock(2,3); unlock(2,3) x-lock(3,2); update(3,2) to (3,4); retain x-lock x-lock(4,3); unlock(4,3) x-lock(5,2); update(5,2) to (5,4); retain x-lock
Command-Line Format --innodb_log_buffer_size=#
Option-File Format innodb_log_buffer_size
Option Sets Variable Yes, innodb_log_buffer_size
Variable Name innodb_log_buffer_size
Variable Scope Global Dynamic Variable No Permitted Values Type numeric
Default 8388608
Range 262144-4294967295
The size in bytes of the buffer that
InnoDB
uses to write to the log files on disk. The default value is 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.Command-Line Format --innodb_log_file_size=#
Option-File Format innodb_log_file_size
Option Sets Variable Yes, innodb_log_file_size
Variable Name innodb_log_file_size
Variable Scope Global Dynamic Variable No Permitted Values Type numeric
Default 5242880
Range 108576-4294967295
The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB. The default value is 5MB. Sensible values range from 1MB to 1/
N
-th of the size of the buffer pool, whereN
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.Command-Line Format --innodb_log_files_in_group=#
Option-File Format innodb_log_files_in_group
Option Sets Variable Yes, innodb_log_files_in_group
Variable Name innodb_log_files_in_group
Variable Scope Global Dynamic Variable No Permitted Values Type numeric
Default 2
Range 2-100
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.Command-Line Format --innodb_log_group_home_dir=path
Option-File Format innodb_log_group_home_dir
Option Sets Variable Yes, innodb_log_group_home_dir
Variable Name innodb_log_group_home_dir
Variable Scope Global Dynamic Variable No Permitted Values Type file name
The directory path to the
InnoDB
redo log files. If you do not specify anyInnoDB
log variables, the default is to create two 5MB files namedib_logfile0
andib_logfile1
in the MySQL data directory.Command-Line Format --innodb_max_dirty_pages_pct=#
Option-File Format innodb_max_dirty_pages_pct
Option Sets Variable Yes, innodb_max_dirty_pages_pct
Variable Name innodb_max_dirty_pages_pct
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 75
Range 0-99
This is an integer in the range from 0 to 99. The default value is 75. The main thread in
InnoDB
tries to write pages from the buffer pool so that the percentage of dirty (not yet written) pages will not exceed this value.Command-Line Format --innodb_max_purge_lag=#
Option-File Format innodb_max_purge_lag
Option Sets Variable Yes, innodb_max_purge_lag
Variable Name innodb_max_purge_lag
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 0
Range 0-4294967295
This variable controls how to delay
INSERT
,UPDATE
, andDELETE
operations when purge operations are lagging (see Section 13.6.10, “InnoDB
Multi-Versioning”). The default value 0 (no delays).The
InnoDB
transaction system maintains a list of transactions that have index records delete-marked byUPDATE
orDELETE
operations. Let the length of this list bepurge_lag
. Whenpurge_lag
exceedsinnodb_max_purge_lag
, eachINSERT
,UPDATE
, andDELETE
operation is delayed by ((purge_lag
/innodb_max_purge_lag
)×10)–5 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.
Command-Line Format --innodb_old_blocks_pct=#
Option-File Format innodb_old_blocks_pct
Variable Name innodb_old_blocks_pct
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 37
Range 5-95
Specifies the approximate percentage of the
InnoDB
buffer pool used for the old block sublist. The range of values is 5 to 95. The default value is 37 (that is, 3/8 of the pool). See Section 7.9.1, “TheInnoDB
Buffer Pool”Command-Line Format --innodb_old_blocks_time=#
Option-File Format innodb_old_blocks_time
Variable Name innodb_old_blocks_time
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 0
Range 0-2**32-1
Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. The default value is 0: A block inserted into the old sublist moves immediately to the new sublist the first time it is accessed, no matter how soon after insertion the access occurs. If the value is greater than 0, blocks remain in the old sublist until an access occurs at least that many ms after the first access. For example, a value of 1000 causes blocks to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist. See Section 7.9.1, “The
InnoDB
Buffer Pool”Command-Line Format --innodb_open_files=#
Option-File Format innodb_open_files
Variable Name innodb_open_files
Variable Scope Global Dynamic Variable No Permitted Values Type numeric
Default 300
Range 10-4294967295
This variable is relevant only if you use multiple tablespaces in
InnoDB
. It specifies the maximum number of.ibd
files thatInnoDB
can keep open at one time. The minimum value is 10. The default value is 300.The file descriptors used for
.ibd
files are forInnoDB
only. They are independent of those specified by the--open-files-limit
server option, and do not affect the operation of the table cache.Version Introduced 5.5.4 Command-Line Format --innodb_purge_batch_size=#
Option-File Format innodb_purge_batch_size
Variable Name innodb_purge_batch_size
Variable Scope Global Dynamic Variable No Permitted Values Type numeric
Default 20
Range 1-5000
The granularity of changes, expressed in units of redo log records, that trigger a purge operation, flushing the changed buffer pool blocks to disk. The default value is 20, and the range is 1-5000. This option is intended for tuning performance in combination with the setting
innodb_purge_threads=1
, and typical users do not need to modify it.Version Introduced 5.5.4 Command-Line Format --innodb_purge_threads=#
Option-File Format innodb_purge_threads
Variable Name innodb_purge_threads
Variable Scope Global Dynamic Variable No Permitted Values Type numeric
Default 0
Range 0-1
The number of background threads devoted to the InnoDB purge operation. Currently, can only be 0 (the default) or 1. The default value of 0 signifies that the purge operation is performed as part of the master thread. Running the purge operation in its own thread can reduce internal contention within InnoDB, improving scalability. Currently, the performance gain might be minimal because the background thread might encounter different kinds of contention than before. This feature primarily lays the groundwork for future performance work.
Command-Line Format --innodb_read_ahead_threshold=#
Option-File Format innodb_read_ahead_threshold
Option Sets Variable Yes, innodb_read_ahead_threshold
Variable Name innodb_read_ahead_threshold
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 56
Range 0-64
Controls the sensitivity of linear read-ahead that
InnoDB
uses to prefetch pages into the buffer cache. IfInnoDB
reads at leastinnodb_read_ahead_threshold
pages sequentially from an extent (64 pages), it initiates an asynchronous read for the entire following extent. The permissible range of values is 0 to 64. The default is 56:InnoDB
must read at least 56 pages sequentially from an extent to initiate an asynchronous read for the following extent.Command-Line Format --innodb_read_io_threads=#
Option-File Format innodb_read_io_threads
Option Sets Variable Yes, innodb_read_io_threads
Variable Name innodb_read_io_threads
Variable Scope Global Dynamic Variable No Permitted Values Type numeric
Default 4
Range 1-64
The number of I/O threads for read operations in
InnoDB
. The default value is 4.Command-Line Format --innodb_replication_delay=#
Option-File Format innodb_replication_delay
Option Sets Variable Yes, innodb_replication_delay
Variable Name innodb_replication_delay
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 0
Range 0-4294967295
The replication thread delay (in ms) on a slave server if
innodb_thread_concurrency
is reached.Command-Line Format --innodb_rollback_on_timeout
Option-File Format innodb_rollback_on_timeout
Option Sets Variable Yes, innodb_rollback_on_timeout
Variable Name innodb_rollback_on_timeout
Variable Scope Global Dynamic Variable No Permitted Values Type boolean
Default OFF
In MySQL 5.5,
InnoDB
rolls back only the last statement on a transaction timeout by default. If--innodb_rollback_on_timeout
is specified, a transaction timeout causesInnoDB
to abort and roll back the entire transaction (the same behavior as in MySQL 4.1).Command-Line Format --innodb_spin_wait_delay=#
Option-File Format innodb_spin_wait_delay
Option Sets Variable Yes, innodb_spin_wait_delay
Variable Name innodb_spin_wait_delay
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 6
Range 0-4294967295
The maximum delay between polls for a spin lock. The default value is 6.
Version Introduced 5.5.4 Command-Line Format --innodb_stats_on_metadata
Option-File Format innodb_stats_on_metadata
Option Sets Variable Yes, innodb_stats_on_metadata
Variable Name innodb_stats_on_metadata
Variable Scope Global Dynamic Variable Yes Permitted Values Type boolean
Default ON
When this variable is enabled (which is the default, as before the variable was created),
InnoDB
updates statistics during metadata statements such asSHOW TABLE STATUS
orSHOW INDEX
, or when accessing theINFORMATION_SCHEMA
tablesTABLES
orSTATISTICS
. (These updates are similar to what happens forANALYZE TABLE
.) When disabled,InnoDB
does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involveInnoDB
tables.Command-Line Format --innodb_stats_sample_pages=#
Option-File Format innodb_stats_sample_pages
Option Sets Variable Yes, innodb_stats_sample_pages
Variable Name innodb_stats_sample_pages
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 8
Range 1-2**64-1
The number of index pages to sample for index distribution statistics such as are calculated by
ANALYZE TABLE
. The default value is 8. For more information, see Section 13.7.8, “Changes for Flexibility, Ease of Use and Reliability”.Command-Line Format --innodb_strict_mode=#
Option-File Format innodb_strict_mode
Option Sets Variable Yes, innodb_strict_mode
Variable Name innodb_strict_mode
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type boolean
Default OFF
Whether
InnoDB
returns errors rather than warnings for exceptional conditions. This is analogous to strict SQL mode. The default value isOFF
.Command-Line Format --innodb_support_xa
Option-File Format innodb_support_xa
Option Sets Variable Yes, innodb_support_xa
Variable Name innodb_support_xa
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type boolean
Default TRUE
When the variable is enabled (the default),
InnoDB
support for two-phase commit in XA transactions is enabled, which causes an extra disk flush for transaction preparation.If you do not use XA transactions, you can disable this variable to reduce the number of disk flushes and get better
InnoDB
performance.Command-Line Format --innodb_sync_spin_loops=#
Option-File Format innodb_sync_spin_loops
Option Sets Variable Yes, innodb_sync_spin_loops
Variable Name innodb_sync_spin_loops
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 30
Range 0-4294967295
The number of times a thread waits for an
InnoDB
mutex to be freed before the thread is suspended. The default value is 30.Command-Line Format --innodb_table_locks
Option-File Format innodb_table_locks
Option Sets Variable Yes, innodb_table_locks
Variable Name innodb_table_locks
Variable Scope Global, Session Dynamic Variable Yes Permitted Values Type boolean
Default TRUE
If
autocommit = 0
,InnoDB
honorsLOCK TABLES
; MySQL does not return fromLOCK TABLES ... WRITE
until all other threads have released all their locks to the table. The default value ofinnodb_table_locks
is 1, which means thatLOCK TABLES
causes InnoDB to lock a table internally ifautocommit = 0
.Command-Line Format --innodb_thread_concurrency=#
Option-File Format innodb_thread_concurrency
Option Sets Variable Yes, innodb_thread_concurrency
Variable Name innodb_thread_concurrency
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 0
Range 0-1000
InnoDB
tries to keep the number of operating system threads concurrently insideInnoDB
less than or equal to the limit given by this variable. Once the number of threads reaches this limit, additional threads are placed into a wait state within a FIFO queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads.The correct value for this variable is dependent on environment and workload. Try a range of different values to determine what value works for your applications. A recommended value is 2 times the number of CPUs plus the number of disks.
The range of this variable is 0 to 1000. A value of 0 (the default) is interpreted as infinite concurrency (no concurrency checking). Disabling thread concurrency checking enables InnoDB to create as many threads as it needs.
Command-Line Format --innodb_thread_sleep_delay=#
Option-File Format innodb_thread_sleep_delay
Option Sets Variable Yes, innodb_thread_sleep_delay
Variable Name innodb_thread_sleep_delay
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 10000
How long
InnoDB
threads sleep before joining theInnoDB
queue, in microseconds. The default value is 10,000. A value of 0 disables sleep.Version Introduced 5.5.4 Command-Line Format --innodb_use_native_aio=#
Option-File Format innodb_use_native_aio
Option Sets Variable Yes, innodb_use_native_aio
Variable Name innodb_use_native_aio
Variable Scope Global Dynamic Variable No Permitted Values Type boolean
Default ON
If a problem with the asynchronous I/O subsystem in the OS prevents
InnoDB
from starting, start the server with this variable disabled (useinnodb_use_native_aio=0
in the option file). This variable applies to Linux systems only, and cannot be changed while the server is running.This variable was added in MySQL 5.5.4.
Command-Line Format --innodb_use_sys_malloc=#
Option-File Format innodb_use_sys_malloc
Option Sets Variable Yes, innodb_use_sys_malloc
Variable Name innodb_use_sys_malloc
Variable Scope Global Dynamic Variable No Permitted Values Type boolean
Default ON
Whether
InnoDB
uses the operating system memory allocator (ON
) or its own (OFF
). The default value isON
.The
InnoDB
version number.Command-Line Format --innodb_write_io_threads=#
Option-File Format innodb_write_io_threads
Option Sets Variable Yes, innodb_write_io_threads
Variable Name innodb_write_io_threads
Variable Scope Global Dynamic Variable No Permitted Values Type numeric
Default 4
Range 1-64
The number of I/O threads for write operations in
InnoDB
. The default value is 4.Command-Line Format --sync-binlog=#
Option-File Format sync_binlog
Option Sets Variable Yes, sync_binlog
Variable Name sync_binlog
Variable Scope Global Dynamic Variable Yes Permitted Values Platform Bit Size 32
Type numeric
Default 0
Range 0-4294967295
Permitted Values Platform Bit Size 64
Type numeric
Default 0
Range 0-18446744073709547520
If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using
fdatasync()
) after everysync_binlog
writes to the binary log. There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise. The default value ofsync_binlog
is 0, which does no synchronizing to disk. A value of 1 is the safest choice, because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).