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 theInnoDB
adaptive hash index, you can use--innodb-adaptive-hash-index
or--skip-innodb-adaptive-hash-index
on the command line, orinnodb_adaptive_hash_index
orskip_innodb_adaptive_hash_index
in an option file.System variables that take a numeric value can be specified as
--
on the command line or asvar_name
=value
in option files.var_name
=value
Many system variables can be changed at runtime (see Section 5.1.8.2, “Dynamic System Variables”).
For information about
GLOBAL
andSESSION
variable scope modifiers, refer to theSET
statement documentation.Certain options control the locations and layout of the
InnoDB
data files. Section 14.8.1, “InnoDB Startup Configuration” explains how to use these options.Some options, which you might not use initially, help tune
InnoDB
performance characteristics based on machine capacity and your database workload.For more information on specifying options and system variables, see Section 4.2.2, “Specifying Program Options”.
Table 14.13 InnoDB Option and Variable Reference
InnoDB Command Options
-
Command-Line Format --innodb[=value]
Deprecated 5.6.21 Type Enumeration Default Value ON
Valid Values OFF
ON
FORCE
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.5.1, “Installing and Uninstalling Plugins”.To disable
InnoDB
, use--innodb=OFF
or--skip-innodb
. In this case, because the default storage engine isInnoDB
, the server does not start unless you also use--default-storage-engine
and--default-tmp-storage-engine
to set the default to some other engine for both permanent andTEMPORARY
tables.As of MySQL 5.6.21,
--innodb=OFF
and--skip-innodb
options are deprecated and their use results in a warning. Expect these options to be removed in a future MySQL release. -
Command-Line Format --innodb-status-file[={OFF|ON}]
Type Boolean Default Value OFF
The
--innodb-status-file
startup option controls whetherInnoDB
creates a file namedinnodb_status.
in the data directory and writespid
SHOW ENGINE INNODB STATUS
output to it every 15 seconds, approximately.The
innodb_status.
file is not created by default. To create it, start mysqld with thepid
--innodb-status-file
option.InnoDB
removes the file when the server is shut down normally. If an abnormal shutdown occurs, the status file may have to be removed manually.The
--innodb-status-file
option is intended for temporary use, asSHOW ENGINE INNODB STATUS
output generation can affect performance, and theinnodb_status.
file can become quite large over time.pid
For related information, see Section 14.17.2, “Enabling InnoDB Monitors”.
Disable the
InnoDB
storage engine. See the description of--innodb
.
InnoDB System Variables
daemon_memcached_enable_binlog
Command-Line Format --daemon-memcached-enable-binlog[={OFF|ON}]
System Variable daemon_memcached_enable_binlog
Scope Global Dynamic No Type Boolean Default Value OFF
Enable this option on the source server to use the
InnoDB
memcached plugin (daemon_memcached
) with the MySQL binary log. This option can only be set at server startup. You must also enable the MySQL binary log on the source server using the--log-bin
option.For more information, see Section 14.20.6, “The InnoDB memcached Plugin and Replication”.
daemon_memcached_engine_lib_name
Command-Line Format --daemon-memcached-engine-lib-name=file_name
System Variable daemon_memcached_engine_lib_name
Scope Global Dynamic No Type File name Default Value innodb_engine.so
Specifies the shared library that implements the
InnoDB
memcached plugin.For more information, see Section 14.20.3, “Setting Up the InnoDB memcached Plugin”.
daemon_memcached_engine_lib_path
Command-Line Format --daemon-memcached-engine-lib-path=dir_name
System Variable daemon_memcached_engine_lib_path
Scope Global Dynamic No Type Directory name Default Value NULL
The path of the directory containing the shared library that implements the
InnoDB
memcached plugin. The default value is NULL, representing the MySQL plugin directory. You should not need to modify this parameter unless specifying amemcached
plugin for a different storage engine that is located outside of the MySQL plugin directory.For more information, see Section 14.20.3, “Setting Up the InnoDB memcached Plugin”.
-
Command-Line Format --daemon-memcached-option=options
System Variable daemon_memcached_option
Scope Global Dynamic No Type String Default Value Used to pass space-separated memcached options to the underlying memcached memory object caching daemon on startup. For example, you might change the port that memcached listens on, reduce the maximum number of simultaneous connections, change the maximum memory size for a key-value pair, or enable debugging messages for the error log.
See Section 14.20.3, “Setting Up the InnoDB memcached Plugin” for usage details. For information about memcached options, refer to the memcached man page.
-
Command-Line Format --daemon-memcached-r-batch-size=#
System Variable daemon_memcached_r_batch_size
Scope Global Dynamic No Type Integer Default Value 1
Minimum Value 1
Maximum Value 1073741824
Specifies how many memcached read operations (
get
operations) to perform before doing aCOMMIT
to start a new transaction. Counterpart ofdaemon_memcached_w_batch_size
.This value is set to 1 by default, so that any changes made to the table through SQL statements are immediately visible to memcached operations. You might increase it to reduce the overhead from frequent commits on a system where the underlying table is only being accessed through the memcached interface. If you set the value too large, the amount of undo or redo data could impose some storage overhead, as with any long-running transaction.
For more information, see Section 14.20.3, “Setting Up the InnoDB memcached Plugin”.
-
Command-Line Format --daemon-memcached-w-batch-size=#
System Variable daemon_memcached_w_batch_size
Scope Global Dynamic No Type Integer Default Value 1
Minimum Value 1
Maximum Value 1048576
Specifies how many memcached write operations, such as
add
,set
, andincr
, to perform before doing aCOMMIT
to start a new transaction. Counterpart ofdaemon_memcached_r_batch_size
.This value is set to 1 by default, on the assumption that data being stored is important to preserve in case of an outage and should immediately be committed. When storing non-critical data, you might increase this value to reduce the overhead from frequent commits; but then the last
N
-1 uncommitted write operations could be lost if an unexpected exit occurs.For more information, see Section 14.20.3, “Setting Up the InnoDB memcached Plugin”.
-
Command-Line Format --ignore-builtin-innodb[={OFF|ON}]
Deprecated Yes System Variable ignore_builtin_innodb
Scope Global Dynamic No Type Boolean In earlier versions of MySQL, enabling this variable caused the server to behave as if the built-in
InnoDB
were not present, which enabled theInnoDB Plugin
to be used instead. In MySQL 5.6,InnoDB
is the default storage engine andInnoDB Plugin
is not used, so this variable has no effect. As of MySQL 5.6.5, it is ignored. -
Command-Line Format --innodb-adaptive-flushing[={OFF|ON}]
System Variable innodb_adaptive_flushing
Scope Global Dynamic Yes Type Boolean Default Value ON
Specifies whether to dynamically adjust the rate of flushing dirty pages in the
InnoDB
buffer pool based on the workload. Adjusting the flush rate dynamically is intended to avoid bursts of I/O activity. This setting is enabled by default. See Section 14.8.3.4, “Configuring Buffer Pool Flushing” for more information. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”. -
Command-Line Format --innodb-adaptive-flushing-lwm=#
System Variable innodb_adaptive_flushing_lwm
Scope Global Dynamic Yes Type Integer Default Value 10
Minimum Value 0
Maximum Value 70
Defines the low water mark representing percentage of redo log capacity at which adaptive flushing is enabled. For more information, see Section 14.8.3.4, “Configuring Buffer Pool Flushing”.
-
Command-Line Format --innodb-adaptive-hash-index[={OFF|ON}]
System Variable innodb_adaptive_hash_index
Scope Global Dynamic Yes Type Boolean Default Value ON
Whether the
InnoDB
adaptive hash index is enabled or disabled. It may be desirable, depending on your workload, to dynamically enable or disable adaptive hash indexing to improve query performance. Because the adaptive hash index may not be useful for all workloads, conduct benchmarks with it both enabled and disabled, using realistic workloads. See Section 14.5.3, “Adaptive Hash Index” for details.This variable is enabled by default. You can modify this parameter using the
SET GLOBAL
statement, without restarting the server. Changing the setting at runtime requires privileges sufficient to set global system variables. See Section 5.1.8.1, “System Variable Privileges”. You can also use--skip-innodb-adaptive-hash-index
at server startup to disable it.Disabling the adaptive hash index empties the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that were using the hash table access the index B-trees directly instead. When the adaptive hash index is re-enabled, the hash table is populated again during normal operation.
innodb_adaptive_max_sleep_delay
Command-Line Format --innodb-adaptive-max-sleep-delay=#
System Variable innodb_adaptive_max_sleep_delay
Scope Global Dynamic Yes Type Integer Default Value 150000
Minimum Value 0
Maximum Value 1000000
Unit microseconds Permits
InnoDB
to automatically adjust the value ofinnodb_thread_sleep_delay
up or down according to the current workload. Any nonzero value enables automated, dynamic adjustment of theinnodb_thread_sleep_delay
value, up to the maximum value specified in theinnodb_adaptive_max_sleep_delay
option. The value represents the number of microseconds. This option can be useful in busy systems, with greater than 16InnoDB
threads. (In practice, it is most valuable for MySQL systems with hundreds or thousands of simultaneous connections.)For more information, see Section 14.8.5, “Configuring Thread Concurrency for InnoDB”.
innodb_additional_mem_pool_size
Command-Line Format --innodb-additional-mem-pool-size=#
Deprecated Yes System Variable innodb_additional_mem_pool_size
Scope Global Dynamic No Type Integer Default Value 8388608
Minimum Value 2097152
Maximum Value 4294967295
Unit bytes 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 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.This variable relates to the
InnoDB
internal memory allocator, which is unused ifinnodb_use_sys_malloc
is enabled. For more information, see Section 14.8.4, “Configuring the Memory Allocator for InnoDB”.As of MySQL 5.6.3,
innodb_additional_mem_pool_size
is deprecated; expect it to be removed in a future MySQL release.-
Command-Line Format --innodb-api-bk-commit-interval=#
System Variable innodb_api_bk_commit_interval
Scope Global Dynamic Yes Type Integer Default Value 5
Minimum Value 1
Maximum Value 1073741824
Unit seconds How often to auto-commit idle connections that use the
InnoDB
memcached interface, in seconds. For more information, see Section 14.20.5.4, “Controlling Transactional Behavior of the InnoDB memcached Plugin”. -
Command-Line Format --innodb-api-disable-rowlock[={OFF|ON}]
System Variable innodb_api_disable_rowlock
Scope Global Dynamic No Type Boolean Default Value OFF
Use this option to disable row locks when
InnoDB
memcached performs DML operations. By default,innodb_api_disable_rowlock
is disabled, which means that memcached requests row locks forget
andset
operations. Wheninnodb_api_disable_rowlock
is enabled, memcached requests a table lock instead of row locks.innodb_api_disable_rowlock
is not dynamic. It must be specified on the mysqld command line or entered in the MySQL configuration file. Configuration takes effect when the plugin is installed, which occurs when the MySQL server is started.For more information, see Section 14.20.5.4, “Controlling Transactional Behavior of the InnoDB memcached Plugin”.
-
Command-Line Format --innodb-api-enable-binlog[={OFF|ON}]
System Variable innodb_api_enable_binlog
Scope Global Dynamic No Type Boolean Default Value OFF
Lets you use the
InnoDB
memcached plugin with the MySQL binary log. For more information, see Enabling the InnoDB memcached Binary Log. -
Command-Line Format --innodb-api-enable-mdl[={OFF|ON}]
System Variable innodb_api_enable_mdl
Scope Global Dynamic No Type Boolean Default Value OFF
Locks the table used by the
InnoDB
memcached plugin, so that it cannot be dropped or altered by DDL through the SQL interface. For more information, see Section 14.20.5.4, “Controlling Transactional Behavior of the InnoDB memcached Plugin”. -
Command-Line Format --innodb-api-trx-level=#
System Variable innodb_api_trx_level
Scope Global Dynamic Yes Type Integer Default Value 0
Minimum Value 0
Maximum Value 3
Controls the transaction isolation level on queries processed by the memcached interface. The constants corresponding to the familiar names are:
0 =
READ UNCOMMITTED
1 =
READ COMMITTED
2 =
REPEATABLE READ
3 =
SERIALIZABLE
For more information, see Section 14.20.5.4, “Controlling Transactional Behavior of the InnoDB memcached Plugin”.
-
Command-Line Format --innodb-autoextend-increment=#
System Variable innodb_autoextend_increment
Scope Global Dynamic Yes Type Integer Default Value 64
Minimum Value 1
Maximum Value 1000
Unit megabytes The increment size (in megabytes) for extending the size of an auto-extending
InnoDB
system tablespace file when it becomes full. The default value is 64. For related information, see System Tablespace Data File Configuration, and Resizing the System Tablespace.The
innodb_autoextend_increment
setting does not affect file-per-table tablespace files. These files are auto-extending regardless of of theinnodb_autoextend_increment
setting. The initial extensions are by small amounts, after which extensions occur in increments of 4MB. -
Command-Line Format --innodb-autoinc-lock-mode=#
System Variable innodb_autoinc_lock_mode
Scope Global Dynamic No Type Integer Default Value 1
Valid Values 0
1
2
The lock mode to use for generating auto-increment values. Permissible values are 0, 1, or 2, for traditional, consecutive, or interleaved, respectively. The default setting is 1 (consecutive). For the characteristics of each lock mode, see InnoDB AUTO_INCREMENT Lock Modes.
innodb_buffer_pool_dump_at_shutdown
Command-Line Format --innodb-buffer-pool-dump-at-shutdown[={OFF|ON}]
System Variable innodb_buffer_pool_dump_at_shutdown
Scope Global Dynamic Yes Type Boolean Default Value OFF
Specifies whether to record the pages cached in the
InnoDB
buffer pool when the MySQL server is shut down, to shorten the warmup process at the next restart. Typically used in combination withinnodb_buffer_pool_load_at_startup
.For more information, see Section 14.8.3.5, “Saving and Restoring the Buffer Pool State”.
-
Command-Line Format --innodb-buffer-pool-dump-now[={OFF|ON}]
System Variable innodb_buffer_pool_dump_now
Scope Global Dynamic Yes Type Boolean Default Value OFF
Immediately records the pages cached in the
InnoDB
buffer pool. Typically used in combination withinnodb_buffer_pool_load_now
.For more information, see Section 14.8.3.5, “Saving and Restoring the Buffer Pool State”.
-
Command-Line Format --innodb-buffer-pool-filename=file_name
System Variable innodb_buffer_pool_filename
Scope Global Dynamic Yes Type File name Default Value ib_buffer_pool
Specifies the name of the file that holds the list of tablespace IDs and page IDs produced by
innodb_buffer_pool_dump_at_shutdown
orinnodb_buffer_pool_dump_now
. Tablespace IDs and page IDs are saved in the following format:space, page_id
. By default, the file is namedib_buffer_pool
and is located in theInnoDB
data directory. A non-default location must be specified relative to the data directory.A file name can be specified at runtime, using a
SET
statement:SET GLOBAL innodb_buffer_pool_filename='file_name';
You can also specify a file name at startup, in a startup string or MySQL configuration file. When specifying a file name at startup, the file must exist or
InnoDB
returns a startup error indicating that there is no such file or directory.For more information, see Section 14.8.3.5, “Saving and Restoring the Buffer Pool State”.
-
Command-Line Format --innodb-buffer-pool-instances=#
System Variable innodb_buffer_pool_instances
Scope Global Dynamic No Type Integer Default Value (Windows, 32-bit platforms) (autosized)
Default Value (Other) 8 (or 1 if innodb_buffer_pool_size < 1GB)
Minimum Value 1
Maximum Value 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 setting
innodb_buffer_pool_size
to a size of 1GB or more. The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination ofinnodb_buffer_pool_instances
andinnodb_buffer_pool_size
so that each buffer pool instance is at least 1GB.Before MySQL 5.6.6, the default is 1. The default value in MySQL 5.6.6 and higher on 32-bit Windows systems depends on the value of
innodb_buffer_pool_size
, as described below:If
innodb_buffer_pool_size
is greater than 1.3GB, the default forinnodb_buffer_pool_instances
isinnodb_buffer_pool_size
/128MB, with individual memory allocation requests for each chunk. 1.3GB was chosen as the boundary at which there is significant risk for 32-bit Windows to be unable to allocate the contiguous address space needed for a single buffer pool.Otherwise, the default is 1.
On all other platforms, the default value in MySQL 5.6.6 and higher is 8 when
innodb_buffer_pool_size
is greater than or equal to 1GB. Otherwise, the default is 1.NoteA bug in MySQL 5.6 causes
SHOW VARIABLES
to report aninnodb_buffer_pool_instances
value of 8 wheninnodb_buffer_pool_size
is less than 1GB and only one buffer pool instance is present (Bug #18343670). As an alternative, you can useSHOW ENGINE INNODB STATUS
to check the number of buffer pool instances. If there are multiple buffer pool instances,SHOW ENGINE INNODB STATUS
output includes anINDIVIDUAL BUFFER POOL INFO
section. -
Command-Line Format --innodb-buffer-pool-load-abort[={OFF|ON}]
System Variable innodb_buffer_pool_load_abort
Scope Global Dynamic Yes Type Boolean Default Value OFF
Interrupts the process of restoring
InnoDB
buffer pool contents triggered byinnodb_buffer_pool_load_at_startup
orinnodb_buffer_pool_load_now
.For more information, see Section 14.8.3.5, “Saving and Restoring the Buffer Pool State”.
innodb_buffer_pool_load_at_startup
Command-Line Format --innodb-buffer-pool-load-at-startup[={OFF|ON}]
System Variable innodb_buffer_pool_load_at_startup
Scope Global Dynamic No Type Boolean Default Value OFF
Specifies that, on MySQL server startup, the
InnoDB
buffer pool is automatically warmed up by loading the same pages it held at an earlier time. Typically used in combination withinnodb_buffer_pool_dump_at_shutdown
.For more information, see Section 14.8.3.5, “Saving and Restoring the Buffer Pool State”.
-
Command-Line Format --innodb-buffer-pool-load-now[={OFF|ON}]
System Variable innodb_buffer_pool_load_now
Scope Global Dynamic Yes Type Boolean Default Value OFF
Immediately warms up the
InnoDB
buffer pool by loading a set of data pages, without waiting for a server restart. Can be useful to bring cache memory back to a known state during benchmarking, or to ready the MySQL server to resume its normal workload after running queries for reports or maintenance.For more information, see Section 14.8.3.5, “Saving and Restoring the Buffer Pool State”.
-
Command-Line Format --innodb-buffer-pool-size=#
System Variable innodb_buffer_pool_size
Scope Global Dynamic No Type Integer Default Value 134217728
Minimum Value 5242880
Maximum Value (64-bit platforms) 2**64-1
Maximum Value (32-bit platforms) 2**32-1
Unit bytes The size in bytes of the buffer pool, the memory area where
InnoDB
caches table and index data. The default value is 134217728 bytes (128MB). The maximum value depends on the CPU architecture; the maximum is 4294967295 (232-1) on 32-bit systems and 18446744073709551615 (264-1) on 64-bit systems. On 32-bit systems, the CPU architecture and operating system may impose a lower practical maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, settinginnodb_buffer_pool_instances
to a value greater than 1 can improve the scalability on a busy server.A larger buffer pool requires less disk I/O to access the same table data more than once. On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size. Be aware of the following potential issues when configuring buffer pool size, and be prepared to scale back the size of the buffer pool if necessary.
Competition for physical memory can 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 buffer pool size.Address space for the buffer pool 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 instances with large buffer pools, initialization time might be significant. To reduce the initialization period, you can save the buffer pool state at server shutdown and restore it at server startup. See Section 14.8.3.5, “Saving and Restoring the Buffer Pool State”.
-
Command-Line Format --innodb-change-buffer-max-size=#
System Variable innodb_change_buffer_max_size
Scope Global Dynamic Yes Type Integer Default Value 25
Minimum Value 0
Maximum Value 50
Maximum size for the
InnoDB
change buffer, as a percentage of the total size of the buffer pool. You might increase this value for a MySQL server with heavy insert, update, and delete activity, or decrease it for a MySQL server with unchanging data used for reporting. For more information, see Section 14.5.2, “Change Buffer”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”. -
Command-Line Format --innodb-change-buffering=value
System Variable innodb_change_buffering
Scope Global Dynamic Yes Type Enumeration Default Value all
Valid Values none
inserts
deletes
changes
purges
all
Whether
InnoDB
performs change buffering, an optimization that delays write operations to secondary indexes so that the I/O operations can be performed sequentially. Permitted values are described in the following table.Table 14.14 Permitted Values for innodb_change_buffering
Value Description none
Do not buffer any operations. inserts
Buffer insert operations. deletes
Buffer delete marking operations; strictly speaking, the writes that mark index records for later deletion during a purge operation. changes
Buffer inserts and delete-marking operations. purges
Buffer the physical deletion operations that happen in the background. all
The default. Buffer inserts, delete-marking operations, and purges. For more information, see Section 14.5.2, “Change Buffer”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.
-
Command-Line Format --innodb-change-buffering-debug=#
System Variable innodb_change_buffering_debug
Scope Global Dynamic Yes Type Integer Default Value 0
Minimum Value 0
Maximum Value 2
Sets a debug flag for
InnoDB
change buffering. A value of 1 forces all changes to the change buffer. A value of 2 causes an unexpected exit at merge. A default value of 0 indicates that the change buffering debug flag is not set. This option is only available when debugging support is compiled in using theWITH_DEBUG
CMake option. -
Command-Line Format --innodb-checksum-algorithm=value
System Variable innodb_checksum_algorithm
Scope Global Dynamic Yes Type Enumeration Default Value innodb
Valid Values crc32
strict_crc32
innodb
strict_innodb
none
strict_none
Specifies how to generate and verify the checksum stored in the disk blocks of
InnoDB
tablespaces.innodb_checksum_algorithm
replaces theinnodb_checksums
option in MySQL 5.6.3. The following values are provided for compatibility:innodb_checksum_algorithm=innodb
is the same asinnodb_checksums=ON
innodb_checksum_algorithm=none
is the same asinnodb_checksums=OFF
To avoid conflicts, remove references to
innodb_checksums
from MySQL configuration files and startup scripts.The value
innodb
is backward-compatible with earlier versions of MySQL. The valuecrc32
uses an algorithm that is faster to compute the checksum for every modified block, and to check the checksums for each disk read. It scans blocks 64 bits at a time, which is faster than theinnodb
checksum algorithm, which scans blocks 8 bits at a time. The valuenone
writes a constant value in the checksum field rather than computing a value based on the block data. The blocks in a tablespace can use a mix of old, new, and no checksum values, being updated gradually as the data is modified; once blocks in a tablespace are modified to use thecrc32
algorithm, the associated tables cannot be read by earlier versions of MySQL.The strict form of a checksum algorithm reports an error if it encounters a valid but non-matching checksum value in a tablespace. It is recommended that you only use strict settings in a new instance, to set up tablespaces for the first time. Strict settings are somewhat faster, because they do not need to compute all checksum values during disk reads.
NotePrior to MySQL 5.6.25, a strict mode setting for
innodb_checksum_algorithm
causedInnoDB
to halt when encountering a valid but non-matching checksum. In MySQL 5.6.25 and later, only an error message is printed, and the page is accepted as valid if it has a validinnodb
,crc32
ornone
checksum.The following table shows the difference between the
none
,innodb
, andcrc32
option values, and their strict counterparts.none
,innodb
, andcrc32
write the specified type of checksum value into each data block, but for compatibility accept other checksum values when verifying a block during a read operation. Strict settings also accept valid checksum values but print an error message when a valid non-matching checksum value is encountered. Using the strict form can make verification faster if allInnoDB
data files in an instance are created under an identicalinnodb_checksum_algorithm
value.Table 14.15 Permitted innodb_checksum_algorithm Values
Value Generated checksum (when writing) Permitted checksums (when reading) none A constant number. Any of the checksums generated by none
,innodb
, orcrc32
.innodb A checksum calculated in software, using the original algorithm from InnoDB
.Any of the checksums generated by none
,innodb
, orcrc32
.crc32 A checksum calculated using the crc32
algorithm, possibly done with a hardware assist.Any of the checksums generated by none
,innodb
, orcrc32
.strict_none A constant number Any of the checksums generated by none
,innodb
, orcrc32
.InnoDB
prints an error message if a valid but non-matching checksum is encountered.strict_innodb A checksum calculated in software, using the original algorithm from InnoDB
.Any of the checksums generated by none
,innodb
, orcrc32
.InnoDB
prints an error message if a valid but non-matching checksum is encountered.strict_crc32 A checksum calculated using the crc32
algorithm, possibly done with a hardware assist.Any of the checksums generated by none
,innodb
, orcrc32
.InnoDB
prints an error message if a valid but non-matching checksum is encountered.The default value for
innodb_checksum_algorithm
was changed frominnodb
tocrc32
in MySQL 5.6.6, but switched back toinnodb
in 5.6.7 for backward compatibility ofInnoDB
data files, and for use with MySQL Enterprise Backup. The limitations encountered included:.ibd
files containing CRC32 checksums could cause problems downgrading to MySQL versions prior to 5.6.3. MySQL 5.6.3 and up recognizes either the new or old checksum values for the block as correct when reading the block from disk, ensuring that data blocks are compatible during upgrade and downgrade regardless of the algorithm setting. If data written with new checksum values is processed by a version of MySQL earlier than 5.6.3, it could be reported as corrupted.Versions of MySQL Enterprise Backup up to 3.8.0 do not support backing up tablespaces that use CRC32 checksums. MySQL Enterprise Backup adds CRC32 checksum support in 3.8.1, with some limitations. Refer to the MySQL Enterprise Backup 3.8.1 Change History for more information.
-
Command-Line Format --innodb-checksums[={OFF|ON}]
Deprecated Yes System Variable innodb_checksums
Scope Global Dynamic No Type Boolean Default Value ON
InnoDB
can use checksum validation on all tablespace pages read from disk to ensure extra fault tolerance against hardware faults or corrupted data files. This validation is enabled by default. Under specialized circumstances (such as when running benchmarks) this safety feature can be disabled with--skip-innodb-checksums
. You can specify the method of calculating the checksum using theinnodb_checksum_algorithm
option.In MySQL 5.6.3 and higher,
innodb_checksums
is deprecated, replaced byinnodb_checksum_algorithm
.innodb_checksum_algorithm=innodb
is the same asinnodb_checksums=ON
(the default).innodb_checksum_algorithm=none
is the same asinnodb_checksums=OFF
. Removeinnodb_checksums
options from your configuration files and startup scripts to avoid conflicts withinnodb_checksum_algorithm
:innodb_checksums=OFF
automatically setsinnodb_checksum_algorithm=none
;innodb_checksums=ON
is ignored and overridden by any other setting forinnodb_checksum_algorithm
. -
Command-Line Format --innodb-cmp-per-index-enabled[={OFF|ON}]
System Variable innodb_cmp_per_index_enabled
Scope Global Dynamic Yes Type Boolean Default Value OFF
Enables per-index compression-related statistics in the Information Schema
INNODB_CMP_PER_INDEX
table. Because these statistics can be expensive to gather, only enable this option on development, test, or replica instances during performance tuning related toInnoDB
compressed tables.For more information, see Section 21.4.7, “The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables”, and Section 14.9.4, “Monitoring InnoDB Table Compression at Runtime”.
-
Command-Line Format --innodb-commit-concurrency=#
System Variable innodb_commit_concurrency
Scope Global Dynamic Yes Type Integer Default Value 0
Minimum Value 0
Maximum Value 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. innodb_compression_failure_threshold_pct
Command-Line Format --innodb-compression-failure-threshold-pct=#
System Variable innodb_compression_failure_threshold_pct
Scope Global Dynamic Yes Type Integer Default Value 5
Minimum Value 0
Maximum Value 100
Defines the compression failure rate threshold for a table, as a percentage, at which point MySQL begins adding padding within compressed pages to avoid expensive compression failures. When this threshold is passed, MySQL begins to leave additional free space within each new compressed page, dynamically adjusting the amount of free space up to the percentage of page size specified by
innodb_compression_pad_pct_max
. A value of zero disables the mechanism that monitors compression efficiency and dynamically adjusts the padding amount.For more information, see Section 14.9.6, “Compression for OLTP Workloads”.
-
Command-Line Format --innodb-compression-level=#
System Variable innodb_compression_level
Scope Global Dynamic Yes Type Integer Default Value 6
Minimum Value 0
Maximum Value 9
Specifies the level of zlib compression to use for
InnoDB
compressed tables and indexes. A higher value lets you fit more data onto a storage device, at the expense of more CPU overhead during compression. A lower value lets you reduce CPU overhead when storage space is not critical, or you expect the data is not especially compressible.For more information, see Section 14.9.6, “Compression for OLTP Workloads”.
innodb_compression_pad_pct_max
Command-Line Format --innodb-compression-pad-pct-max=#
System Variable innodb_compression_pad_pct_max
Scope Global Dynamic Yes Type Integer Default Value 50
Minimum Value 0
Maximum Value 75
Specifies the maximum percentage that can be reserved as free space within each compressed page, allowing room to reorganize the data and modification log within the page when a compressed table or index is updated and the data might be recompressed. Only applies when
innodb_compression_failure_threshold_pct
is set to a nonzero value, and the rate of compression failures passes the cutoff point.For more information, see Section 14.9.6, “Compression for OLTP Workloads”.
-
Command-Line Format --innodb-concurrency-tickets=#
System Variable innodb_concurrency_tickets
Scope Global Dynamic Yes Type Integer Default Value 5000
Minimum Value 1
Maximum Value 4294967295
Determines the number of threads that can enter
InnoDB
concurrently. 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 “ 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 5000 as of MySQL 5.6.6, 500 before that.With a small
innodb_concurrency_tickets
value, small transactions that only need to process a few rows compete fairly with larger transactions that process many rows. The disadvantage of a smallinnodb_concurrency_tickets
value is that large transactions must loop through the queue many times before they can complete, which extends the amount of time required to complete their task.With a large
innodb_concurrency_tickets
value, large transactions spend less time waiting for a position at the end of the queue (controlled byinnodb_thread_concurrency
) and more time retrieving rows. Large transactions also require fewer trips through the queue to complete their task. The disadvantage of a largeinnodb_concurrency_tickets
value is that too many large transactions running at the same time can starve smaller transactions by making them wait a longer time before executing.With a nonzero
innodb_thread_concurrency
value, you may need to adjust theinnodb_concurrency_tickets
value up or down to find the optimal balance between larger and smaller transactions. TheSHOW ENGINE INNODB STATUS
report shows the number of tickets remaining for an executing transaction in its current pass through the queue. This data may also be obtained from theTRX_CONCURRENCY_TICKETS
column of the Information SchemaINNODB_TRX
table.For more information, see Section 14.8.5, “Configuring Thread Concurrency for InnoDB”.
-
Command-Line Format --innodb-data-file-path=file_name
System Variable innodb_data_file_path
Scope Global Dynamic No Type String Default Value ibdata1:12M:autoextend
Defines the name, size, and attributes of
InnoDB
system tablespace data files. If you do not specify a value forinnodb_data_file_path
, the default behavior is to create a single auto-extending data file, slightly larger than 12MB, namedibdata1
.The full syntax for a data file specification includes the file name, file size,
autoextend
attribute, andmax
attribute:file_name:file_size[:autoextend[:max:max_file_size]]
File sizes are specified in kilobytes, megabytes, or gigabytes by appending
K
,M
orG
to the size value. If specifying the data file size in kilobytes, do so in multiples of 1024. Otherwise, KB values are rounded to nearest megabyte (MB) boundary. The sum of file sizes must be, at a minimum, slightly larger than 12MB.For additional configuration information, see System Tablespace Data File Configuration. For resizing instructions, see Resizing the System Tablespace.
-
Command-Line Format --innodb-data-home-dir=dir_name
System Variable innodb_data_home_dir
Scope Global Dynamic No Type Directory name The common part of the directory path for
InnoDB
system tablespace data files. The default value is the MySQLdata
directory. The setting is concatenated with theinnodb_data_file_path
setting. If you specify the value as an empty string, you can specify an absolute path forinnodb_data_file_path
.A trailing slash is required when specifying a value for
innodb_data_home_dir
. For example:[mysqld] innodb_data_home_dir = /path/to/myibdata/
This setting does not affect the location of file-per-table tablespaces.
For related information, see Section 14.8.1, “InnoDB Startup Configuration”.
innodb_disable_sort_file_cache
Command-Line Format --innodb-disable-sort-file-cache[={OFF|ON}]
System Variable innodb_disable_sort_file_cache
Scope Global Dynamic Yes Type Boolean Default Value OFF
Disables the operating system file system cache for merge-sort temporary files. The effect is to open such files with the equivalent of
O_DIRECT
.-
Command-Line Format --innodb-doublewrite[={OFF|ON}]
System Variable innodb_doublewrite
Scope Global Dynamic No Type Boolean Default Value ON
When enabled (the default),
InnoDB
stores all data twice, first to the doublewrite buffer, 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.For related information, see Section 14.6.5, “Doublewrite Buffer”.
-
Command-Line Format --innodb-fast-shutdown=#
System Variable innodb_fast_shutdown
Scope Global Dynamic Yes Type Integer Default Value 1
Valid Values 0
1
2
The
InnoDB
shutdown mode. If the value is 0,InnoDB
does a slow shutdown, a full purge and a change 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. innodb_fil_make_page_dirty_debug
Command-Line Format --innodb-fil-make-page-dirty-debug=#
Introduced 5.6.17 System Variable innodb_fil_make_page_dirty_debug
Scope Global Dynamic Yes Type Integer Default Value 0
Minimum Value 0
Maximum Value 2**32-1
By default, setting
innodb_fil_make_page_dirty_debug
to the ID of a tablespace immediately dirties the first page of the tablespace. Ifinnodb_saved_page_number_debug
is set to a non-default value, settinginnodb_fil_make_page_dirty_debug
dirties the specified page. Theinnodb_fil_make_page_dirty_debug
option is only available if debugging support is compiled in using theWITH_DEBUG
CMake option.-
Command-Line Format --innodb-file-format=value
System Variable innodb_file_format
Scope Global Dynamic Yes Type String Default Value Antelope
Valid Values Antelope
Barracuda
Enables an
InnoDB
file format for file-per-table tablespaces. Supported file formats areAntelope
andBarracuda
.Antelope
is the originalInnoDB
file format, which supportsREDUNDANT
andCOMPACT
row formats forInnoDB
tables.Barracuda
is the newer file format, which supportsCOMPRESSED
andDYNAMIC
row formats.COMPRESSED
andDYNAMIC
row formats enable important storage features forInnoDB
tables. See Section 14.11, “InnoDB Row Formats”.To create tables that use
COMPRESSED
orDYNAMIC
row format, theBarracuda
file format andinnodb_file_per_table
must be enabled.Changing the
innodb_file_format
setting does not affect the file format of existingInnoDB
tablespace files.For more information, see Section 14.10, “InnoDB File-Format Management”.
-
Command-Line Format --innodb-file-format-check[={OFF|ON}]
System Variable innodb_file_format_check
Scope Global Dynamic No Type Boolean Default Value ON
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 system 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.NoteDespite the default value sometimes being displayed as
ON
orOFF
, always use the numeric values 1 or 0 to turn this option on or off in your configuration file or command line string.For more information, see Section 14.10.2.1, “Compatibility Check When InnoDB Is Started”.
-
Command-Line Format --innodb-file-format-max=value
System Variable innodb_file_format_max
Scope Global Dynamic Yes Type String Default Value Antelope
Valid Values Antelope
Barracuda
At server startup,
InnoDB
sets the value of this variable to the file format tag in the system 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.For related information, see Section 14.10, “InnoDB File-Format Management”.
-
Command-Line Format --innodb-file-per-table[={OFF|ON}]
System Variable innodb_file_per_table
Scope Global Dynamic Yes Type Boolean Default Value ON
When
innodb_file_per_table
is enabled, tables are created in file-per-table tablespaces. When disabled, tables are created in the system tablespace. For information about file-per-table tablespaces, see Section 14.6.3.2, “File-Per-Table Tablespaces”. For information about theInnoDB
system tablespace, see Section 14.6.3.1, “The System Tablespace”.The
innodb_file_per_table
variable can be configured at runtime using aSET GLOBAL
statement, specified on the command line at startup, or specified in an option file. Configuration at runtime requires privileges sufficient to set global system variables (see Section 5.1.8.1, “System Variable Privileges”) and immediately affects the operation of all connections.When a table that resides in a file-per-table tablespace is truncated or dropped, the freed space is returned to the operating system. Truncating or dropping a table that resides in the system tablespace only frees space in the system tablespace. Freed space in the system tablespace can be used again for
InnoDB
data but is not returned to the operating system, as system tablespace data files never shrink.When
innodb_file_per_table
is enabled, a table-copyingALTER TABLE
operation on a table that resides in the system tablespace implicitly re-creates the table in a file-per-table tablespace. To prevent this from occurring, disableinnodb_file_per_table
before executing table-copyingALTER TABLE
operations on tables that reside in the system tablespace. -
Command-Line Format --innodb-flush-log-at-timeout=#
System Variable innodb_flush_log_at_timeout
Scope Global Dynamic Yes Type Integer Default Value 1
Minimum Value 1
Maximum Value 2700
Unit seconds Write and flush the logs every
N
seconds.innodb_flush_log_at_timeout
allows the timeout period between flushes to be increased in order to reduce flushing and avoid impacting performance of binary log group commit. Prior to the introduction of this configuration option, flushing frequency was once per second. The default setting forinnodb_flush_log_at_timeout
is also once per second. innodb_flush_log_at_trx_commit
Command-Line Format --innodb-flush-log-at-trx-commit=#
System Variable innodb_flush_log_at_trx_commit
Scope Global Dynamic Yes Type Enumeration Default Value 1
Valid Values 0
1
2
Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose transactions in a crash.
The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
For settings 0 and 2, once-per-second flushing is not 100% guaranteed. Flushing may occur more frequently due to DDL changes and other internal
InnoDB
activities that cause logs to be flushed independently of theinnodb_flush_log_at_trx_commit
setting, and sometimes less frequently due to scheduling issues. If logs are flushed once per second, up to one second of transactions can be lost in a crash. If logs are flushed more or less frequently than once per second, the amount of transactions that can be lost varies accordingly.Log flushing frequency is controlled by
innodb_flush_log_at_timeout
, which allows you to set log flushing frequency toN
seconds (whereN
is1 ... 2700
, with a default value of 1). However, any unexpected mysqld process exit can erase up toN
seconds of transactions.DDL changes and other internal
InnoDB
activities flush the log independently of theinnodb_flush_log_at_trx_commit
setting.InnoDB
crash recovery works regardless of theinnodb_flush_log_at_trx_commit
setting. Transactions are either applied entirely or erased entirely.
For durability and consistency in a replication setup that uses
InnoDB
with transactions:If binary logging is enabled, set
sync_binlog=1
.Always set
innodb_flush_log_at_trx_commit=1
.
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. In this case, the durability of transactions is not guaranteed even with the recommended settings, and in the worst case, a power outage can corrupt
InnoDB
data. 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 to disable the caching of disk writes in hardware caches.-
Command-Line Format --innodb-flush-method=value
System Variable innodb_flush_method
Scope Global Dynamic No Type String Default Value NULL
Valid Values (Unix) fsync
O_DSYNC
littlesync
nosync
O_DIRECT
O_DIRECT_NO_FSYNC
Valid Values (Windows) async_unbuffered
normal
unbuffered
Defines the method used to flush data to
InnoDB
data files and log files, which can affect I/O throughput.If
innodb_flush_method
is set toNULL
on a Unix-like system, thefsync
option is used by default. Ifinnodb_flush_method
is set toNULL
on Windows, theasync_unbuffered
option is used by default.The
innodb_flush_method
options for Unix-like systems include:fsync
:InnoDB
uses thefsync()
system call to flush both the data and log files.fsync
is the default setting.O_DSYNC
:InnoDB
usesO_SYNC
to open and flush the log files, andfsync()
to flush the data files.InnoDB
does not useO_DSYNC
directly because there have been problems with it on many varieties of Unix.littlesync
: This option is used for internal performance testing and is currently unsupported. Use at your own risk.nosync
: This option is used for internal performance testing and is currently unsupported. Use at your own risk.O_DIRECT
:InnoDB
usesO_DIRECT
(ordirectio()
on Solaris) to open the data files, and usesfsync()
to flush both the data and log files. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.O_DIRECT_NO_FSYNC
:InnoDB
usesO_DIRECT
during flushing I/O, but skips thefsync()
system call after each write operation.This setting is not suitable for file systems such as XFS and EXT4, which require an
fsync()
system call to synchronize file system metadata changes. If you are not sure whether your file system requires anfsync()
system call to synchronize file system metadata changes, useO_DIRECT
instead.Data loss is possible if redo log files and data files reside on different storage devices, and an unexpected exit occurs before data file writes are flushed from a device cache that is not battery-backed. If you use or intend to use different storage devices for redo log files and data files, and your data files reside on a device with a cache that is not battery-backed, use
O_DIRECT
instead.
The
innodb_flush_method
options for Windows systems include:async_unbuffered
:InnoDB
uses Windows asynchronous I/O and non-buffered I/O.async_unbuffered
is the default setting on Windows systems.Running MySQL server on a 4K sector hard drive on Windows is not supported with
async_unbuffered
. The workaround is to useinnodb_flush_method=normal
.normal
:InnoDB
uses simulated asynchronous I/O and buffered I/O. This option is used for internal performance testing and is currently unsupported. Use at your own risk.unbuffered
:InnoDB
uses simulated asynchronous I/O and non-buffered I/O. This option is used for internal performance testing and is currently unsupported. Use at your own risk.
How each setting 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
Innodb_data_fsyncs
status variable to see the overall number offsync()
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 between theInnoDB
buffer pool and the operating system file system cache. On some systems whereInnoDB
data and log files are located on a SAN, the default value orO_DSYNC
might be faster for a read-heavy workload with mostlySELECT
statements. Always test this parameter with hardware and workload that reflect your production environment. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”. -
Command-Line Format --innodb-flush-neighbors=#
System Variable innodb_flush_neighbors
Scope Global Dynamic Yes Type Enumeration Default Value 1
Valid Values 0
1
2
Specifies whether flushing a page from the
InnoDB
buffer pool also flushes other dirty pages in the same extent.A setting of 0 disables
innodb_flush_neighbors
. Dirty pages in the same extent are not flushed.The default setting of 1 flushes contiguous dirty pages in the same extent.
A setting of 2 flushes dirty pages in the same extent.
When the table data is stored on a traditional HDD storage device, flushing such neighbor pages in one operation reduces I/O overhead (primarily for disk seek operations) compared to flushing individual pages at different times. For table data stored on SSD, seek time is not a significant factor and you can turn this setting off to spread out write operations. For related information, see Section 14.8.3.4, “Configuring Buffer Pool Flushing”.
-
Command-Line Format --innodb-flushing-avg-loops=#
System Variable innodb_flushing_avg_loops
Scope Global Dynamic Yes Type Integer Default Value 30
Minimum Value 1
Maximum Value 1000
Number of iterations for which
InnoDB
keeps the previously calculated snapshot of the flushing state, controlling how quickly adaptive flushing responds to changing workloads. Increasing the value makes the rate of flush operations change smoothly and gradually as the workload changes. Decreasing the value makes adaptive flushing adjust quickly to workload changes, which can cause spikes in flushing activity if the workload increases and decreases suddenly.For related information, see Section 14.8.3.4, “Configuring Buffer Pool Flushing”.
-
Command-Line Format --innodb-force-load-corrupted[={OFF|ON}]
System Variable innodb_force_load_corrupted
Scope Global Dynamic No Type Boolean Default Value OFF
Permits
InnoDB
to load tables at startup that are marked as corrupted. Use only during troubleshooting, to recover data that is otherwise inaccessible. When troubleshooting is complete, disable this setting and restart the server. -
Command-Line Format --innodb-force-recovery=#
System Variable innodb_force_recovery
Scope Global Dynamic No Type Integer Default Value 0
Minimum Value 0
Maximum Value 6
The crash recovery mode, typically only changed in serious troubleshooting situations. Possible values are from 0 to 6. For the meanings of these values and important information about
innodb_force_recovery
, see Section 14.21.2, “Forcing InnoDB Recovery”.WarningOnly set this variable to a value greater than 0 in an emergency situation so that you can start
InnoDB
and dump your tables. As a safety measure,InnoDB
preventsINSERT
,UPDATE
, orDELETE
operations wheninnodb_force_recovery
is greater than 0. As of 5.6.15, aninnodb_force_recovery
setting of 4 or greater placesInnoDB
into read-only mode.These restrictions may cause replication administration commands to fail with an error because replication settings such as
relay_log_info_repository=TABLE
andmaster_info_repository=TABLE
store information inInnoDB
tables. -
System Variable innodb_ft_aux_table
Scope Global Dynamic Yes Type String Specifies the qualified name of an
InnoDB
table containing aFULLTEXT
index. This variable is intended for diagnostic purposes and can only be set at runtime. For example:SET GLOBAL innodb_ft_aux_table = 'test/t1';
After you set this variable to a name in the format
, thedb_name
/table_name
INFORMATION_SCHEMA
tablesINNODB_FT_INDEX_TABLE
,INNODB_FT_INDEX_CACHE
,INNODB_FT_CONFIG
,INNODB_FT_DELETED
, andINNODB_FT_BEING_DELETED
show information about the search index for the specified table.For more information, see Section 14.15.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.
-
Command-Line Format --innodb-ft-cache-size=#
System Variable innodb_ft_cache_size
Scope Global Dynamic No Type Integer Default Value 8000000
Minimum Value 1600000
Maximum Value 80000000
Unit bytes The memory allocated, in bytes, for the
InnoDB
FULLTEXT search index cache, which holds a parsed document in memory while creating anInnoDB
FULLTEXT
index. Index inserts and updates are only committed to disk when theinnodb_ft_cache_size
size limit is reached.innodb_ft_cache_size
defines the cache size on a per table basis. To set a global limit for all tables, seeinnodb_ft_total_cache_size
.For more information, see InnoDB Full-Text Index Cache.
-
Command-Line Format --innodb-ft-enable-diag-print[={OFF|ON}]
System Variable innodb_ft_enable_diag_print
Scope Global Dynamic Yes Type Boolean Default Value OFF
Whether to enable additional full-text search (FTS) diagnostic output. This option is primarily intended for advanced FTS debugging and is not of interest to most users. Output is printed to the error log and includes information such as:
FTS index sync progress (when the FTS cache limit is reached). For example:
FTS SYNC for table test, deleted count: 100 size: 10000 bytes SYNC words: 100
FTS optimize progress. For example:
FTS start optimize test FTS_OPTIMIZE: optimize "mysql" FTS_OPTIMIZE: processed "mysql"
FTS index build progress. For example:
Number of doc processed: 1000
For FTS queries, the query parsing tree, word weight, query processing time, and memory usage are printed. For example:
FTS Search Processing time: 1 secs: 100 millisec: row(s) 10000 Full Search Memory: 245666 (bytes), Row: 10000
-
Command-Line Format --innodb-ft-enable-stopword[={OFF|ON}]
System Variable innodb_ft_enable_stopword
Scope Global, Session Dynamic Yes Type Boolean Default Value ON
Specifies that a set of stopwords is associated with an
InnoDB
FULLTEXT
index at the time the index is created. If theinnodb_ft_user_stopword_table
option is set, the stopwords are taken from that table. Else, if theinnodb_ft_server_stopword_table
option is set, the stopwords are taken from that table. Otherwise, a built-in set of default stopwords is used.For more information, see Section 12.10.4, “Full-Text Stopwords”.
-
Command-Line Format --innodb-ft-max-token-size=#
System Variable innodb_ft_max_token_size
Scope Global Dynamic No Type Integer Default Value 84
Minimum Value 10
Maximum Value (≥ 5.6.14) 84
Maximum Value (≤ 5.6.13) 252
Maximum character length of words that are stored in an
InnoDB
FULLTEXT
index. Setting a limit on this value reduces the size of the index, thus speeding up queries, by omitting long keywords or arbitrary collections of letters that are not real words and are not likely to be search terms.For more information, see Section 12.10.6, “Fine-Tuning MySQL Full-Text Search”.
-
Command-Line Format --innodb-ft-min-token-size=#
System Variable innodb_ft_min_token_size
Scope Global Dynamic No Type Integer Default Value 3
Minimum Value 0
Maximum Value 16
Minimum length of words that are stored in an
InnoDB
FULLTEXT
index. Increasing this value reduces the size of the index, thus speeding up queries, by omitting common words that are unlikely to be significant in a search context, such as the English words “a” and “to”. For content using a CJK (Chinese, Japanese, Korean) character set, specify a value of 1.For more information, see Section 12.10.6, “Fine-Tuning MySQL Full-Text Search”.
-
Command-Line Format --innodb-ft-num-word-optimize=#
System Variable innodb_ft_num_word_optimize
Scope Global Dynamic Yes Type Integer Default Value 2000
Minimum Value 1000
Maximum Value 10000
Number of words to process during each
OPTIMIZE TABLE
operation on anInnoDB
FULLTEXT
index. Because a bulk insert or update operation to a table containing a full-text search index could require substantial index maintenance to incorporate all changes, you might do a series ofOPTIMIZE TABLE
statements, each picking up where the last left off.For more information, see Section 12.10.6, “Fine-Tuning MySQL Full-Text Search”.
-
Command-Line Format --innodb-ft-result-cache-limit=#
Introduced 5.6.13 System Variable innodb_ft_result_cache_limit
Scope Global Dynamic Yes Type Integer Default Value 2000000000
Minimum Value 1000000
Maximum Value (≥ 5.6.17) 2**32-1
Maximum Value (Unix, 64-bit platforms, ≥ 5.6.13, ≤ 5.6.16) 2**64-1
Maximum Value (Unix, 32-bit platforms, ≥ 5.6.13, ≤ 5.6.16) 2**32-1
Maximum Value (Windows, ≥ 5.6.13, ≤ 5.6.16) 2**32-1
Unit bytes The
InnoDB
full-text search query result cache limit (defined in bytes) per full-text search query or per thread. Intermediate and finalInnoDB
full-text search query results are handled in memory. Useinnodb_ft_result_cache_limit
to place a size limit on the full-text search query result cache to avoid excessive memory consumption in case of very largeInnoDB
full-text search query results (millions or hundreds of millions of rows, for example). Memory is allocated as required when a full-text search query is processed. If the result cache size limit is reached, an error is returned indicating that the query exceeds the maximum allowed memory.As of MySQL 5.6.17, the maximum value of
innodb_ft_result_cache_limit
for all platform types and bit sizes is 2**32-1. innodb_ft_server_stopword_table
Command-Line Format --innodb-ft-server-stopword-table=db_name/table_name
System Variable innodb_ft_server_stopword_table
Scope Global Dynamic Yes Type String Default Value NULL
This option is used to specify your own
InnoDB
FULLTEXT
index stopword list for allInnoDB
tables. To configure your own stopword list for a specificInnoDB
table, useinnodb_ft_user_stopword_table
.Set
innodb_ft_server_stopword_table
to the name of the table containing a list of stopwords, in the format
.db_name
/table_name
The stopword table must exist before you configure
innodb_ft_server_stopword_table
.innodb_ft_enable_stopword
must be enabled andinnodb_ft_server_stopword_table
option must be configured before you create theFULLTEXT
index.The stopword table must be an
InnoDB
table, containing a singleVARCHAR
column namedvalue
.For more information, see Section 12.10.4, “Full-Text Stopwords”.
-
Command-Line Format --innodb-ft-sort-pll-degree=#
System Variable innodb_ft_sort_pll_degree
Scope Global Dynamic No Type Integer Default Value 2
Minimum Value 1
Maximum Value 32
Number of threads used in parallel to index and tokenize text in an
InnoDB
FULLTEXT
index when building a search index.For related information, see Section 14.6.2.3, “InnoDB Full-Text Indexes”, and
innodb_sort_buffer_size
. -
Command-Line Format --innodb-ft-total-cache-size=#
Introduced 5.6.13 System Variable innodb_ft_total_cache_size
Scope Global Dynamic No Type Integer Default Value 640000000
Minimum Value 32000000
Maximum Value 1600000000
Unit bytes The total memory allocated, in bytes, for the
InnoDB
full-text search index cache for all tables. Creating numerous tables, each with aFULLTEXT
search index, could consume a significant portion of available memory.innodb_ft_total_cache_size
defines a global memory limit for all full-text search indexes to help avoid excessive memory consumption. If the global limit is reached by an index operation, a forced sync is triggered.For more information, see InnoDB Full-Text Index Cache.
-
Command-Line Format --innodb-ft-user-stopword-table=db_name/table_name
System Variable innodb_ft_user_stopword_table
Scope Global, Session Dynamic Yes Type String Default Value NULL
This option is used to specify your own
InnoDB
FULLTEXT
index stopword list on a specific table. To configure your own stopword list for allInnoDB
tables, useinnodb_ft_server_stopword_table
.Set
innodb_ft_user_stopword_table
to the name of the table containing a list of stopwords, in the format
.db_name
/table_name
The stopword table must exist before you configure
innodb_ft_user_stopword_table
.innodb_ft_enable_stopword
must be enabled andinnodb_ft_user_stopword_table
must be configured before you create theFULLTEXT
index.The stopword table must be an
InnoDB
table, containing a singleVARCHAR
column namedvalue
.For more information, see Section 12.10.4, “Full-Text Stopwords”.
-
Command-Line Format --innodb-io-capacity=#
System Variable innodb_io_capacity
Scope Global Dynamic Yes Type Integer Default Value 200
Minimum Value 100
Maximum Value (64-bit platforms) 2**64-1
Maximum Value (32-bit platforms) 2**32-1
The
innodb_io_capacity
variable defines the number of I/O operations per second (IOPS) available toInnoDB
background tasks, such as flushing pages from the buffer pool and merging data from the change buffer.For information about configuring the
innodb_io_capacity
variable, see Section 14.8.8, “Configuring InnoDB I/O Capacity”. -
Command-Line Format --innodb-io-capacity-max=#
System Variable innodb_io_capacity_max
Scope Global Dynamic Yes Type Integer Default Value see description
Minimum Value 100
Maximum Value (32-bit platforms) 2**32-1
Maximum Value (Unix, 64-bit platforms) 2**64-1
Maximum Value (Windows, 64-bit platforms) 2**32-1
If flushing activity falls behind,
InnoDB
can flush more aggressively, at a higher rate of I/O operations per second (IOPS) than defined by theinnodb_io_capacity
variable. Theinnodb_io_capacity_max
variable defines a maximum number of IOPS performed byInnoDB
background tasks in such situations.For information about configuring the
innodb_io_capacity_max
variable, see Section 14.8.8, “Configuring InnoDB I/O Capacity”. -
Command-Line Format --innodb-large-prefix[={OFF|ON}]
System Variable innodb_large_prefix
Scope Global Dynamic Yes Type Boolean Default Value OFF
Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes) for
InnoDB
tables that useDYNAMIC
orCOMPRESSED
row format. (Creating such tables also requires the option valuesinnodb_file_format=barracuda
andinnodb_file_per_table=true
.) See Section 14.22, “InnoDB Limits” for maximums associated with index key prefixes under various settings.For tables that use
REDUNDANT
orCOMPACT
row format, this option does not affect the permitted index key prefix length. innodb_limit_optimistic_insert_debug
Command-Line Format --innodb-limit-optimistic-insert-debug=#
System Variable innodb_limit_optimistic_insert_debug
Scope Global Dynamic Yes Type Integer Default Value 0
Minimum Value 0
Maximum Value 2**32-1
Limits the number of records per B-tree page. A default value of 0 means that no limit is imposed. This option is only available if debugging support is compiled in using the
WITH_DEBUG
CMake option.-
Command-Line Format --innodb-lock-wait-timeout=#
System Variable innodb_lock_wait_timeout
Scope Global, Session Dynamic Yes Type Integer Default Value 50
Minimum Value 1
Maximum Value 1073741824
Unit seconds The length of time in seconds an
InnoDB
transaction waits 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 waits at most this many seconds for write access to the row 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 rolled back (not the entire transaction). To have the entire transaction roll back, start the server with the
--innodb-rollback-on-timeout
option. See also Section 14.21.4, “InnoDB Error Handling”.You might decrease this value for highly interactive applications or OLTP systems, to display user feedback quickly or put the update into a queue for processing later. You might increase this value for long-running back-end operations, such as a transform step in a data warehouse that waits for other large insert or update operations to finish.
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.The lock wait timeout value does not apply to deadlocks, because
InnoDB
detects them immediately and rolls back one of the deadlocked transactions. See Section 14.7.5.2, “Deadlock Detection”.innodb_lock_wait_timeout
can be set at runtime with theSET GLOBAL
orSET SESSION
statement. Changing theGLOBAL
setting requires privileges sufficient to set global system variables (see Section 5.1.8.1, “System Variable Privileges”) and affects the operation of all clients that subsequently connect. Any client can change theSESSION
setting forinnodb_lock_wait_timeout
, which affects only that client. innodb_locks_unsafe_for_binlog
Command-Line Format --innodb-locks-unsafe-for-binlog[={OFF|ON}]
Deprecated Yes System Variable innodb_locks_unsafe_for_binlog
Scope Global Dynamic No Type Boolean Default Value OFF
This variable affects how
InnoDB
uses gap locking for searches and index scans.innodb_locks_unsafe_for_binlog
is deprecated; expect it to be removed in a future MySQL release.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, row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the gap before the 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 14.7.1, “InnoDB Locking”.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 effects of enabling
innodb_locks_unsafe_for_binlog
are the same as setting the transaction isolation level toREAD COMMITTED
, with these exceptions: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 more information about the effect of isolation level on gap locking, see Section 14.7.2.1, “Transaction Isolation Levels”.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 the
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 more information about phantoms, see Section 14.7.4, “Phantom Rows”.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 14.6.2.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 the 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=#
System Variable innodb_log_buffer_size
Scope Global Dynamic No Type Integer Default Value 8388608
Minimum Value 262144
Maximum Value 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 the need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. For related information, see Memory Configuration, and Section 8.5.4, “Optimizing InnoDB Redo Logging”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”. -
Command-Line Format --innodb-log-checkpoint-now[={OFF|ON}]
Introduced 5.6.12 System Variable innodb_log_checkpoint_now
Scope Global Dynamic Yes Type Boolean Default Value OFF
Enable this debug option to force
InnoDB
to write a checkpoint. This option is only available if debugging support is compiled in using theWITH_DEBUG
CMake option. -
Command-Line Format --innodb-log-compressed-pages[={OFF|ON}]
Introduced 5.6.11 System Variable innodb_log_compressed_pages
Scope Global Dynamic Yes Type Boolean Default Value ON
Specifies whether images of re-compressed pages are written to the redo log. Re-compression may occur when changes are made to compressed data.
innodb_log_compressed_pages
is enabled by default to prevent corruption that could occur if a different version of thezlib
compression algorithm is used during recovery. If you are certain that thezlib
version is not subject to change, you can disableinnodb_log_compressed_pages
to reduce redo log generation for workloads that modify compressed data.To measure the effect of enabling or disabling
innodb_log_compressed_pages
, compare redo log generation for both settings under the same workload. Options for measuring redo log generation include observing theLog sequence number
(LSN) in theLOG
section ofSHOW ENGINE INNODB STATUS
output, or monitoringInnodb_os_log_written
status for the number of bytes written to the redo log files.For related information, see Section 14.9.6, “Compression for OLTP Workloads”.
-
Command-Line Format --innodb-log-file-size=#
System Variable innodb_log_file_size
Scope Global Dynamic No Type Integer Default Value 50331648
Minimum Value 1048576
Maximum Value 512GB / innodb_log_files_in_group
Unit bytes The size in bytes of each log file in a log group. The combined size of log files (
innodb_log_file_size
*innodb_log_files_in_group
) cannot exceed a maximum value that is slightly less than 512GB. A pair of 255 GB log files, for example, approaches the limit but does not exceed it. The default value is 48MB.Generally, the combined size of the log files should be large enough that the server can smooth out peaks and troughs in workload activity, which often means that there is enough redo log space to handle more than an hour of write activity. The larger the value, the less checkpoint flush activity is required in the buffer pool, saving disk I/O. Larger log files also make crash recovery slower.
ImportantDue to Bug #69477, redo log writes for large, externally stored
BLOB
fields could overwrite the most recent checkpoint. To address this bug, a patch introduced in MySQL 5.6.20 limits the size of redo logBLOB
writes to 10% of the redo log file size. As a result of this limit,innodb_log_file_size
should be set to a value greater than 10 times the largestBLOB
data size found in the rows of your tables plus the length of other variable length fields (VARCHAR
,VARBINARY
, andTEXT
type fields).In MySQL 5.6.22, the redo log
BLOB
write limit is relaxed to 10% of the total redo log size (innodb_log_file_size
*innodb_log_files_in_group
). (Bug #19498877)For related information, see Redo Log File Configuration. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.
-
Command-Line Format --innodb-log-files-in-group=#
System Variable innodb_log_files_in_group
Scope Global Dynamic No Type Integer Default Value 2
Minimum Value 2
Maximum Value 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. The location of the files is specified byinnodb_log_group_home_dir
. The combined size of log files (innodb_log_file_size
*innodb_log_files_in_group
) can be up to 512GB.For related information, see Redo Log File Configuration.
-
Command-Line Format --innodb-log-group-home-dir=dir_name
System Variable innodb_log_group_home_dir
Scope Global Dynamic No Type Directory name The directory path to the
InnoDB
redo log files, whose number is specified byinnodb_log_files_in_group
. If you do not specify anyInnoDB
log variables, the default is to create two files namedib_logfile0
andib_logfile1
in the MySQL data directory. Log file size is given by theinnodb_log_file_size
system variable.For related information, see Redo Log File Configuration.
-
Command-Line Format --innodb-lru-scan-depth=#
System Variable innodb_lru_scan_depth
Scope Global Dynamic Yes Type Integer Default Value 1024
Minimum Value 100
Maximum Value (64-bit platforms) 2**64-1
Maximum Value (32-bit platforms) 2**32-1
A parameter that influences the algorithms and heuristics for the flush operation for the
InnoDB
buffer pool. Primarily of interest to performance experts tuning I/O-intensive workloads. It specifies, per buffer pool instance, how far down the buffer pool LRU page list the page cleaner thread scans looking for dirty pages to flush. This is a background operation performed once per second.A setting smaller than the default is generally suitable for most workloads. A value that is much higher than necessary may impact performance. Only consider increasing the value if you have spare I/O capacity under a typical workload. Conversely, if a write-intensive workload saturates your I/O capacity, decrease the value, especially in the case of a large buffer pool.
When tuning
innodb_lru_scan_depth
, start with a low value and configure the setting upward with the goal of rarely seeing zero free pages. Also, consider adjustinginnodb_lru_scan_depth
when changing the number of buffer pool instances, sinceinnodb_lru_scan_depth
*innodb_buffer_pool_instances
defines the amount of work performed by the page cleaner thread each second.For related information, see Section 14.8.3.4, “Configuring Buffer Pool Flushing”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.
-
Command-Line Format --innodb-max-dirty-pages-pct=#
System Variable innodb_max_dirty_pages_pct
Scope Global Dynamic Yes Type Numeric Default Value 75
Minimum Value 0
Maximum Value 99
InnoDB
tries to flush data from the buffer pool so that the percentage of dirty pages does not exceed this value. Specify an integer in the range from 0 to 99. The default value is 75.The
innodb_max_dirty_pages_pct
setting establishes a target for flushing activity. It does not affect the rate of flushing. For information about managing the rate of flushing, see Section 14.8.3.4, “Configuring Buffer Pool Flushing”.For related information, see Section 14.8.3.4, “Configuring Buffer Pool Flushing”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.
innodb_max_dirty_pages_pct_lwm
Command-Line Format --innodb-max-dirty-pages-pct-lwm=#
System Variable innodb_max_dirty_pages_pct_lwm
Scope Global Dynamic Yes Type Numeric Default Value 0
Minimum Value 0
Maximum Value 99
Defines a low water mark representing the percentage of dirty pages at which preflushing is enabled to control the dirty page ratio. The default of 0 disables the pre-flushing behavior entirely. The configured value should always be lower than the
innodb_max_dirty_pages_pct
value. For more information, see Section 14.8.3.4, “Configuring Buffer Pool Flushing”.-
Command-Line Format --innodb-max-purge-lag=#
System Variable innodb_max_purge_lag
Scope Global Dynamic Yes Type Integer Default Value 0
Minimum Value 0
Maximum Value 4294967295
Defines the desired maximum purge lag. If this value is exceeded, a delay is imposed on
INSERT
,UPDATE
, andDELETE
operations to allow time for purge to catch up. The default value is 0, which means there is no maximum purge lag and no delay.For more information, see Section 14.8.10, “Purge Configuration”.
-
Command-Line Format --innodb-max-purge-lag-delay=#
System Variable innodb_max_purge_lag_delay
Scope Global Dynamic Yes Type Integer Default Value 0
Minimum Value 0
Maximum Value 10000000
Unit milliseconds Specifies the maximum delay in microseconds for the delay imposed when the
innodb_max_purge_lag
threshold is exceeded. The specifiedinnodb_max_purge_lag_delay
value is an upper limit on the delay period calculated by theinnodb_max_purge_lag
formula.For more information, see Section 14.8.10, “Purge Configuration”.
Has no effect. This variable is deprecated as of MySQL 5.6.11; expect it to be removed in a future MySQL release.
-
Command-Line Format --innodb-monitor-disable={counter|module|pattern|all}
System Variable innodb_monitor_disable
Scope Global Dynamic Yes Type String This variable acts as a switch, disabling
InnoDB
metrics counters. Counter data may be queried using the Information SchemaINNODB_METRICS
table. For usage information, see Section 14.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”. -
Command-Line Format --innodb-monitor-enable={counter|module|pattern|all}
System Variable innodb_monitor_enable
Scope Global Dynamic Yes Type String This variable acts as a switch, enabling
InnoDB
metrics counters. Counter data may be queried using the Information SchemaINNODB_METRICS
table. For usage information, see Section 14.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”. -
Command-Line Format --innodb-monitor-reset={counter|module|pattern|all}
System Variable innodb_monitor_reset
Scope Global Dynamic Yes Type Enumeration Default Value NULL
Valid Values counter
module
pattern
all
This variable acts as a switch, resetting the count value for
InnoDB
metrics counters to zero. Counter data may be queried using the Information SchemaINNODB_METRICS
table. For usage information, see Section 14.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”. -
Command-Line Format --innodb-monitor-reset-all={counter|module|pattern|all}
System Variable innodb_monitor_reset_all
Scope Global Dynamic Yes Type Enumeration Default Value NULL
Valid Values counter
module
pattern
all
This variable acts as a switch, resetting all values (minimum, maximum, and so on) for
InnoDB
metrics counters. Counter data may be queried using the Information SchemaINNODB_METRICS
table. For usage information, see Section 14.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”. -
Command-Line Format --innodb-numa-interleave[={OFF|ON}]
Introduced 5.6.27 System Variable innodb_numa_interleave
Scope Global Dynamic No Type Boolean Default Value OFF
Enables the NUMA interleave memory policy for allocation of the
InnoDB
buffer pool. Wheninnodb_numa_interleave
is enabled, the NUMA memory policy is set toMPOL_INTERLEAVE
for the mysqld process. After theInnoDB
buffer pool is allocated, the NUMA memory policy is set back toMPOL_DEFAULT
. For theinnodb_numa_interleave
option to be available, MySQL must be compiled on a NUMA-enabled Linux system. -
Command-Line Format --innodb-old-blocks-pct=#
System Variable innodb_old_blocks_pct
Scope Global Dynamic Yes Type Integer Default Value 37
Minimum Value 5
Maximum Value 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). Often used in combination withinnodb_old_blocks_time
.For more information, see Section 14.8.3.2, “Making the Buffer Pool Scan Resistant”. For information about buffer pool management, the LRU algorithm, and eviction policies, see Section 14.5.1, “Buffer Pool”.
-
Command-Line Format --innodb-old-blocks-time=#
System Variable innodb_old_blocks_time
Scope Global Dynamic Yes Type Integer Default Value 1000
Minimum Value 0
Maximum Value 2**32-1
Unit milliseconds Non-zero values protect against the buffer pool being filled by data that is referenced only for a brief period, such as during a full table scan. Increasing this value offers more protection against full table scans interfering with data cached in the buffer pool.
Specifies how long in milliseconds a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. If the 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 milliseconds 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.
The default value is 1000 as of MySQL 5.6.6, 0 before that.
This variable is often used in combination with
innodb_old_blocks_pct
. For more information, see Section 14.8.3.2, “Making the Buffer Pool Scan Resistant”. For information about buffer pool management, the LRU algorithm, and eviction policies, see Section 14.5.1, “Buffer Pool”. innodb_online_alter_log_max_size
Command-Line Format --innodb-online-alter-log-max-size=#
System Variable innodb_online_alter_log_max_size
Scope Global Dynamic Yes Type Integer Default Value 134217728
Minimum Value 65536
Maximum Value 2**64-1
Unit bytes Specifies an upper limit in bytes on the size of the temporary log files used during online DDL operations for
InnoDB
tables. There is one such log file for each index being created or table being altered. This log file stores data inserted, updated, or deleted in the table during the DDL operation. The temporary log file is extended when needed by the value ofinnodb_sort_buffer_size
, up to the maximum specified byinnodb_online_alter_log_max_size
. If a temporary log file exceeds the upper size limit, theALTER TABLE
operation fails and all uncommitted concurrent DML operations are rolled back. Thus, a large value for this option allows more DML to happen during an online DDL operation, but also extends the period of time at the end of the DDL operation when the table is locked to apply the data from the log.-
Command-Line Format --innodb-open-files=#
System Variable innodb_open_files
Scope Global Dynamic No Type Integer Default Value -1
(signifies autosizing; do not assign this literal value)Minimum Value 10
Maximum Value 2147483647
Specifies the maximum number of files that
InnoDB
can have open at one time. The minimum value is 10. Ifinnodb_file_per_table
is disabled, the default value is 300; otherwise, the default value is 300 or thetable_open_cache
setting, whichever is higher. -
Command-Line Format --innodb-optimize-fulltext-only[={OFF|ON}]
System Variable innodb_optimize_fulltext_only
Scope Global Dynamic Yes Type Boolean Default Value OFF
Changes the way
OPTIMIZE TABLE
operates onInnoDB
tables. Intended to be enabled temporarily, during maintenance operations forInnoDB
tables withFULLTEXT
indexes.By default,
OPTIMIZE TABLE
reorganizes data in the clustered index of the table. When this option is enabled,OPTIMIZE TABLE
skips the reorganization of table data, and instead processes newly added, deleted, and updated token data forInnoDB
FULLTEXT
indexes. For more information, see Optimizing InnoDB Full-Text Indexes. -
Command-Line Format --innodb-page-size=#
System Variable innodb_page_size
Scope Global Dynamic No Type Enumeration Default Value 16384
Valid Values 4096
8192
16384
Specifies the page size for
InnoDB
tablespaces. Values can be specified in bytes or kilobytes. For example, a 16 kilobyte page size value can be specified as 16384, 16KB, or 16k.innodb_page_size
can only be configured prior to initializing the MySQL instance and cannot be changed afterward. If no value is specified, the instance is initialized using the default page size. See Section 14.8.1, “InnoDB Startup Configuration”.The default
16KB
page size is appropriate for a wide range of workloads, particularly for queries involving table scans and DML operations involving bulk updates. Smaller page sizes might be more efficient for OLTP workloads involving many small writes, where contention can be an issue when single pages contain many rows. Smaller pages might also be efficient with SSD storage devices, which typically use small block sizes. Keeping theInnoDB
page size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.A MySQL instance using a particular
InnoDB
page size cannot use data files or log files from an instance that uses a different page size. -
Command-Line Format --innodb-print-all-deadlocks[={OFF|ON}]
System Variable innodb_print_all_deadlocks
Scope Global Dynamic Yes Type Boolean Default Value OFF
When this option is enabled, information about all deadlocks in
InnoDB
user transactions is recorded in themysqld
error log. Otherwise, you see information about only the last deadlock, using theSHOW ENGINE INNODB STATUS
command. An occasionalInnoDB
deadlock is not necessarily an issue, becauseInnoDB
detects the condition immediately and rolls back one of the transactions automatically. You might use this option to troubleshoot why deadlocks are occurring if an application does not have appropriate error-handling logic to detect the rollback and retry its operation. A large number of deadlocks might indicate the need to restructure transactions that issue DML orSELECT ... FOR UPDATE
statements for multiple tables, so that each transaction accesses the tables in the same order, thus avoiding the deadlock condition.For related information, see Section 14.7.5, “Deadlocks in InnoDB”.
-
Command-Line Format --innodb-purge-batch-size=#
System Variable innodb_purge_batch_size
Scope Global Dynamic Yes Type Integer Default Value 300
Minimum Value 1
Maximum Value 5000
Defines the number of undo log pages that purge parses and processes in one batch from the history list. In a multithreaded purge configuration, the coordinator purge thread divides
innodb_purge_batch_size
byinnodb_purge_threads
and assigns that number of pages to each purge thread. Theinnodb_purge_batch_size
variable also defines the number of undo log pages that purge frees after every 128 iterations through the undo logs.The
innodb_purge_batch_size
option is intended for advanced performance tuning in combination with theinnodb_purge_threads
setting. Most users need not changeinnodb_purge_batch_size
from its default value.For related information, see Section 14.8.10, “Purge Configuration”.
-
Command-Line Format --innodb-purge-threads=#
System Variable innodb_purge_threads
Scope Global Dynamic No Type Integer Default Value 1
Minimum Value 1
Maximum Value 32
The number of background threads devoted to the
InnoDB
purge operation. Increasing the value creates additional purge threads, which can improve efficiency on systems where DML operations are performed on multiple tables.For related information, see Section 14.8.10, “Purge Configuration”.
-
Command-Line Format --innodb-random-read-ahead[={OFF|ON}]
System Variable innodb_random_read_ahead
Scope Global Dynamic Yes Type Boolean Default Value OFF
Enables the random read-ahead technique for optimizing
InnoDB
I/O.For details about performance considerations for different types of read-ahead requests, see Section 14.8.3.3, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.
-
Command-Line Format --innodb-read-ahead-threshold=#
System Variable innodb_read_ahead_threshold
Scope Global Dynamic Yes Type Integer Default Value 56
Minimum Value 0
Maximum Value 64
Controls the sensitivity of linear read-ahead that
InnoDB
uses to prefetch pages into the buffer pool. 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. As of MySQL 5.6.1, a value of 0 disables read-ahead. Prior to 5.6.1, a value of 0 would trigger a read-ahead upon reading the boundary page of a 64 page extent. For the default of 56,InnoDB
must read at least 56 pages sequentially from an extent to initiate an asynchronous read for the following extent.Knowing how many pages are read through the read-ahead mechanism, and how many of these pages are evicted from the buffer pool without ever being accessed, can be useful when fine-tuning the
innodb_read_ahead_threshold
setting.SHOW ENGINE INNODB STATUS
output displays counter information from theInnodb_buffer_pool_read_ahead
andInnodb_buffer_pool_read_ahead_evicted
global status variables, which report the number of pages brought into the buffer pool by read-ahead requests, and the number of such pages evicted from the buffer pool without ever being accessed, respectively. The status variables report global values since the last server restart.SHOW ENGINE INNODB STATUS
also shows the rate at which the read-ahead pages are read and the rate at which such pages are evicted without being accessed. The per-second averages are based on the statistics collected since the last invocation ofSHOW ENGINE INNODB STATUS
and are displayed in theBUFFER POOL AND MEMORY
section of theSHOW ENGINE INNODB STATUS
output.For more information, see Section 14.8.3.3, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.
-
Command-Line Format --innodb-read-io-threads=#
System Variable innodb_read_io_threads
Scope Global Dynamic No Type Integer Default Value 4
Minimum Value 1
Maximum Value 64
The number of I/O threads for read operations in
InnoDB
. Its counterpart for write threads isinnodb_write_io_threads
. For more information, see Section 14.8.6, “Configuring the Number of Background InnoDB I/O Threads”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.NoteOn Linux systems, running multiple MySQL servers (typically more than 12) with default settings for
innodb_read_io_threads
,innodb_write_io_threads
, and the Linuxaio-max-nr
setting can exceed system limits. Ideally, increase theaio-max-nr
setting; as a workaround, you might reduce the settings for one or both of the MySQL variables. -
Command-Line Format --innodb-read-only[={OFF|ON}]
System Variable innodb_read_only
Scope Global Dynamic No Type Boolean Default Value OFF
Starts
InnoDB
in read-only mode. For distributing database applications or data sets on read-only media. Can also be used in data warehouses to share the same data directory between multiple instances. For more information, see Section 14.8.2, “Configuring InnoDB for Read-Only Operation”. -
Command-Line Format --innodb-replication-delay=#
System Variable innodb_replication_delay
Scope Global Dynamic Yes Type Integer Default Value 0
Minimum Value 0
Maximum Value 4294967295
Unit milliseconds The replication thread delay in milliseconds on a replica server if
innodb_thread_concurrency
is reached. -
Command-Line Format --innodb-rollback-on-timeout[={OFF|ON}]
System Variable innodb_rollback_on_timeout
Scope Global Dynamic No Type Boolean Default Value OFF
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).For more information, see Section 14.21.4, “InnoDB Error Handling”.
-
Command-Line Format --innodb-rollback-segments=#
System Variable innodb_rollback_segments
Scope Global Dynamic Yes Type Integer Default Value 128
Minimum Value 1
Maximum Value 128
Defines the number of rollback segments used by
InnoDB
for transactions that generate undo records. The number of transactions that each rollback segment supports depends on theInnoDB
page size and the number of undo logs assigned to each transaction. For more information, see Section 14.6.7, “Undo Logs”.This setting is appropriate for tuning performance if you observe mutex contention related to the undo logs.
Although you can increase or decrease the number of rollback segments used by
InnoDB
, the number of rollback segments physically present in the system never decreases. Thus, you might start with a low value and gradually increase it to avoid allocating rollback segments that are not required. Theinnodb_rollback_segments
default and maximum value is 128.For related information, see Section 14.3, “InnoDB Multi-Versioning”. For information about configuring separate undo tablespaces, see Section 14.6.3.3, “Undo Tablespaces”.
innodb_saved_page_number_debug
Command-Line Format --innodb-saved-page-number-debug=#
Introduced 5.6.17 System Variable innodb_saved_page_number_debug
Scope Global Dynamic Yes Type Integer Default Value 0
Minimum Value 0
Maximum Value 2**23-1
Saves a page number. Setting the
innodb_fil_make_page_dirty_debug
option dirties the page defined byinnodb_saved_page_number_debug
. Theinnodb_saved_page_number_debug
option is only available if debugging support is compiled in using theWITH_DEBUG
CMake option.-
Command-Line Format --innodb-sort-buffer-size=#
System Variable innodb_sort_buffer_size
Scope Global Dynamic No Type Integer Default Value 1048576
Minimum Value 65536
Maximum Value 67108864
Unit bytes This variable defines:
The sort buffer size for online DDL operations that create or rebuild secondary indexes.
The amount by which the temporary log file is extended when recording concurrent DML during an online DDL operation, and the size of the temporary log file read buffer and write buffer.
For related information, see Section 14.13.3, “Online DDL Space Requirements”.
-
Command-Line Format --innodb-spin-wait-delay=#
System Variable innodb_spin_wait_delay
Scope Global Dynamic Yes Type Integer Default Value 6
Minimum Value 0
Maximum Value (64-bit platforms) 2**64-1
Maximum Value (32-bit platforms) 2**32-1
The maximum delay between polls for a spin lock. The low-level implementation of this mechanism varies depending on the combination of hardware and operating system, so the delay does not correspond to a fixed time interval. For more information, see Section 14.8.9, “Configuring Spin Lock Polling”.
-
Command-Line Format --innodb-stats-auto-recalc[={OFF|ON}]
System Variable innodb_stats_auto_recalc
Scope Global Dynamic Yes Type Boolean Default Value ON
Causes
InnoDB
to automatically recalculate persistent statistics after the data in a table is changed substantially. The threshold value is 10% of the rows in the table. This setting applies to tables created when theinnodb_stats_persistent
option is enabled. Automatic statistics recalculation may also be configured by specifyingSTATS_PERSISTENT=1
in aCREATE TABLE
orALTER TABLE
statement. The amount of data sampled to produce the statistics is controlled by theinnodb_stats_persistent_sample_pages
variable.For more information, see Section 14.8.11.1, “Configuring Persistent Optimizer Statistics Parameters”.
innodb_stats_include_delete_marked
Command-Line Format --innodb-stats-include-delete-marked[={OFF|ON}]
Introduced 5.6.35 System Variable innodb_stats_include_delete_marked
Scope Global Dynamic Yes Type Boolean Default Value OFF
By default,
InnoDB
reads uncommitted data when calculating statistics. In the case of an uncommitted transaction that deletes rows from a table,InnoDB
excludes records that are delete-marked when calculating row estimates and index statistics, which can lead to non-optimal execution plans for other transactions that are operating on the table concurrently using a transaction isolation level other thanREAD UNCOMMITTED
. To avoid this scenario,innodb_stats_include_delete_marked
can be enabled to ensure thatInnoDB
includes delete-marked records when calculating persistent optimizer statistics.When
innodb_stats_include_delete_marked
is enabled,ANALYZE TABLE
considers delete-marked records when recalculating statistics.innodb_stats_include_delete_marked
is a global setting that affects allInnoDB
tables. It is only applicable to persistent optimizer statistics.For related information, see Section 14.8.11.1, “Configuring Persistent Optimizer Statistics Parameters”.
-
Command-Line Format --innodb-stats-method=value
System Variable innodb_stats_method
Scope Global Dynamic Yes Type Enumeration Default Value nulls_equal
Valid Values nulls_equal
nulls_unequal
nulls_ignored
How the server treats
NULL
values when collecting statistics about the distribution of index values forInnoDB
tables. Permitted values arenulls_equal
,nulls_unequal
, andnulls_ignored
. Fornulls_equal
, allNULL
index values are considered equal and form a single value group with a size equal to the number ofNULL
values. Fornulls_unequal
,NULL
values are considered unequal, and eachNULL
forms a distinct value group of size 1. Fornulls_ignored
,NULL
values are ignored.The method used to generate table statistics influences how the optimizer chooses indexes for query execution, as described in Section 8.3.7, “InnoDB and MyISAM Index Statistics Collection”.
-
Command-Line Format --innodb-stats-on-metadata[={OFF|ON}]
System Variable innodb_stats_on_metadata
Scope Global Dynamic Yes Type Boolean Default Value OFF
This option only applies when optimizer statistics are configured to be non-persistent. Optimizer statistics are not persisted to disk when
innodb_stats_persistent
is disabled or when individual tables are created or altered withSTATS_PERSISTENT=0
. For more information, see Section 14.8.11.2, “Configuring Non-Persistent Optimizer Statistics Parameters”.When
innodb_stats_on_metadata
is enabled,InnoDB
updates non-persistent statistics when metadata statements such asSHOW TABLE STATUS
orSHOW INDEX
are run, or when accessing the Information SchemaTABLES
orSTATISTICS
tables. (These updates are similar to what happens forANALYZE TABLE
.) When disabled,InnoDB
does not update statistics during these operations. Leaving the setting disabled 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.To change the setting, issue the statement
SET GLOBAL innodb_stats_on_metadata=
, wheremode
is eithermode
ON
orOFF
(or1
or0
). Changing the setting requires privileges sufficient to set global system variables (see Section 5.1.8.1, “System Variable Privileges”) and immediately affects the operation of all connections. -
Command-Line Format --innodb-stats-persistent[={OFF|ON}]
System Variable innodb_stats_persistent
Scope Global Dynamic Yes Type Boolean Default Value ON
Specifies whether
InnoDB
index statistics are persisted to disk. Otherwise, statistics may be recalculated frequently which can lead to variations in query execution plans. This setting is stored with each table when the table is created. You can setinnodb_stats_persistent
at the global level before creating a table, or use theSTATS_PERSISTENT
clause of theCREATE TABLE
andALTER TABLE
statements to override the system-wide setting and configure persistent statistics for individual tables.For more information, see Section 14.8.11.1, “Configuring Persistent Optimizer Statistics Parameters”.
innodb_stats_persistent_sample_pages
Command-Line Format --innodb-stats-persistent-sample-pages=#
System Variable innodb_stats_persistent_sample_pages
Scope Global Dynamic Yes Type Integer Default Value 20
Minimum Value 1
Maximum Value 18446744073709551615
The number of index pages to sample when estimating cardinality and other statistics for an indexed column, such as those calculated by
ANALYZE TABLE
. Increasing the value improves the accuracy of index statistics, which can improve the query execution plan, at the expense of increased I/O during the execution ofANALYZE TABLE
for anInnoDB
table. For more information, see Section 14.8.11.1, “Configuring Persistent Optimizer Statistics Parameters”.NoteSetting a high value for
innodb_stats_persistent_sample_pages
could result in lengthyANALYZE TABLE
execution time. To estimate the number of database pages accessed byANALYZE TABLE
, see Section 14.8.11.3, “Estimating ANALYZE TABLE Complexity for InnoDB Tables”.innodb_stats_persistent_sample_pages
only applies wheninnodb_stats_persistent
is enabled for a table; wheninnodb_stats_persistent
is disabled,innodb_stats_transient_sample_pages
applies instead.-
Command-Line Format --innodb-stats-sample-pages=#
Deprecated Yes System Variable innodb_stats_sample_pages
Scope Global Dynamic Yes Type Integer Default Value 8
Minimum Value 1
Maximum Value 2**64-1
Deprecated. Use
innodb_stats_transient_sample_pages
instead. innodb_stats_transient_sample_pages
Command-Line Format --innodb-stats-transient-sample-pages=#
System Variable innodb_stats_transient_sample_pages
Scope Global Dynamic Yes Type Integer Default Value 8
Minimum Value 1
Maximum Value 18446744073709551615
The number of index pages to sample when estimating cardinality and other statistics for an indexed column, such as those calculated by
ANALYZE TABLE
. The default value is 8. Increasing the value improves the accuracy of index statistics, which can improve the query execution plan, at the expense of increased I/O when opening anInnoDB
table or recalculating statistics. For more information, see Section 14.8.11.2, “Configuring Non-Persistent Optimizer Statistics Parameters”.NoteSetting a high value for
innodb_stats_transient_sample_pages
could result in lengthyANALYZE TABLE
execution time. To estimate the number of database pages accessed byANALYZE TABLE
, see Section 14.8.11.3, “Estimating ANALYZE TABLE Complexity for InnoDB Tables”.innodb_stats_transient_sample_pages
only applies wheninnodb_stats_persistent
is disabled for a table; wheninnodb_stats_persistent
is enabled,innodb_stats_persistent_sample_pages
applies instead. Takes the place ofinnodb_stats_sample_pages
. For more information, see Section 14.8.11.2, “Configuring Non-Persistent Optimizer Statistics Parameters”.-
Command-Line Format --innodb-status-output[={OFF|ON}]
Introduced 5.6.16 System Variable innodb_status_output
Scope Global Dynamic Yes Type Boolean Default Value OFF
Enables or disables periodic output for the standard
InnoDB
Monitor. Also used in combination withinnodb_status_output_locks
to enable or disable periodic output for theInnoDB
Lock Monitor. For more information, see Section 14.17.2, “Enabling InnoDB Monitors”. -
Command-Line Format --innodb-status-output-locks[={OFF|ON}]
Introduced 5.6.16 System Variable innodb_status_output_locks
Scope Global Dynamic Yes Type Boolean Default Value OFF
Enables or disables the
InnoDB
Lock Monitor. When enabled, theInnoDB
Lock Monitor prints additional information about locks inSHOW ENGINE INNODB STATUS
output and in periodic output printed to the MySQL error log. Periodic output for theInnoDB
Lock Monitor is printed as part of the standardInnoDB
Monitor output. The standardInnoDB
Monitor must therefore be enabled for theInnoDB
Lock Monitor to print data to the MySQL error log periodically. For more information, see Section 14.17.2, “Enabling InnoDB Monitors”. -
Command-Line Format --innodb-strict-mode[={OFF|ON}]
System Variable innodb_strict_mode
Scope Global, Session Dynamic Yes Type Boolean Default Value OFF
When
innodb_strict_mode
is enabled,InnoDB
returns errors rather than warnings when checking for invalid or incompatible table options.It checks that
KEY_BLOCK_SIZE
,ROW_FORMAT
,DATA DIRECTORY
,TEMPORARY
, andTABLESPACE
options are compatible with each other and other settings.innodb_strict_mode=ON
also enables a row size check when creating or altering a table, to preventINSERT
orUPDATE
from failing due to the record being too large for the selected page size.You can enable or disable
innodb_strict_mode
on the command line when startingmysqld
, or in a MySQL configuration file. You can also enable or disableinnodb_strict_mode
at runtime with the statementSET [GLOBAL|SESSION] innodb_strict_mode=
, wheremode
is eithermode
ON
orOFF
. Changing theGLOBAL
setting requires privileges sufficient to set global system variables (see Section 5.1.8.1, “System Variable Privileges”) and affects the operation of all clients that subsequently connect. Any client can change theSESSION
setting forinnodb_strict_mode
, and the setting affects only that client. -
Command-Line Format --innodb-support-xa[={OFF|ON}]
System Variable innodb_support_xa
Scope Global, Session Dynamic Yes Type Boolean Default Value ON
Enables
InnoDB
support for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation. 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 disableinnodb_support_xa
, transactions can be written to the binary log in a different order than the live database is committing them, which can produce different data when the binary log is replayed in disaster recovery or on a replica. Do not disableinnodb_support_xa
on a replication source 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 disable this option to improve performance for
InnoDB
tables. For example, you can turn it off on replicas where only the replication SQL thread is changing data.You can also disable 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.
-
Command-Line Format --innodb-sync-array-size=#
System Variable innodb_sync_array_size
Scope Global Dynamic No Type Integer Default Value 1
Minimum Value 1
Maximum Value 1024
Defines the size of the mutex/lock wait array. Increasing the value splits the internal data structure used to coordinate threads, for higher concurrency in workloads with large numbers of waiting threads. This setting must be configured when the MySQL instance is starting up, and cannot be changed afterward. Increasing the value is recommended for workloads that frequently produce a large number of waiting threads, typically greater than 768.
-
Command-Line Format --innodb-sync-spin-loops=#
System Variable innodb_sync_spin_loops
Scope Global Dynamic Yes Type Integer Default Value 30
Minimum Value 0
Maximum Value 4294967295
The number of times a thread waits for an
InnoDB
mutex to be freed before the thread is suspended. -
Command-Line Format --innodb-table-locks[={OFF|ON}]
System Variable innodb_table_locks
Scope Global, Session Dynamic Yes Type Boolean Default Value ON
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
.innodb_table_locks = 0
has no effect for tables locked explicitly withLOCK TABLES ... WRITE
. It does have an effect for tables locked for read or write byLOCK TABLES ... WRITE
implicitly (for example, through triggers) or byLOCK TABLES ... READ
.For related information, see Section 14.7, “InnoDB Locking and Transaction Model”.
-
Command-Line Format --innodb-thread-concurrency=#
System Variable innodb_thread_concurrency
Scope Global Dynamic Yes Type Integer Default Value 0
Minimum Value 0
Maximum Value 1000
Defines the maximum number of threads permitted inside of
InnoDB
. A value of 0 (the default) is interpreted as infinite concurrency (no limit). This variable is intended for performance tuning on high concurrency systems.InnoDB
tries to keep the number of threads insideInnoDB
less than or equal to theinnodb_thread_concurrency
limit. Once the limit is reached, additional threads are placed into a “First In, First Out” (FIFO) queue for waiting threads. Threads waiting for locks are not counted in the number of concurrently executing threads.The correct setting depends on workload and computing environment. 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. Test a range of values to determine the setting that provides the best performance.
innodb_thread_concurrency
is a dynamic variable, which permits experimenting with different settings on a live test system. If a particular setting performs poorly, you can quickly setinnodb_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 consistently small and does not affect performance, set
innodb_thread_concurrency=0
(no limit).If your workload is consistently heavy or occasionally spikes, set an
innodb_thread_concurrency
value and adjust it until you find the number of threads that provides the best performance. For example, suppose that your system typically has 40 to 50 users, but periodically the number increases to 60, 70, or more. Through testing, you find that performance remains largely stable with a limit of 80 concurrent users. In this case, setinnodb_thread_concurrency
to 80.If you do not want
InnoDB
to use more than a certain number of virtual CPUs for user threads (20 virtual CPUs, for example), setinnodb_thread_concurrency
to this number (or possibly lower, depending on performance testing). If your goal is to isolate MySQL from other applications, consider binding themysqld
process exclusively to the virtual CPUs. Be aware, however, that exclusive binding can result in non-optimal hardware usage if themysqld
process is not consistently busy. In this case, you can bind themysqld
process to the virtual CPUs but allow other applications to use some or all of the virtual CPUs.NoteFrom an operating system perspective, using a resource management solution to manage how CPU time is shared among applications may be preferable to binding the
mysqld
process. For example, you could assign 90% of virtual CPU time to a given application while other critical processes are not running, and scale that value back to 40% when other critical processes are running.In some cases, the optimal
innodb_thread_concurrency
setting can be smaller than the number of virtual CPUs.An
innodb_thread_concurrency
value that is too high can cause performance regression due to increased contention on system internals and resources.Monitor and analyze your system regularly. Changes to workload, number of users, or computing environment may require that you adjust the
innodb_thread_concurrency
setting.
A value of 0 disables the
queries inside InnoDB
andqueries in queue
counters in theROW OPERATIONS
section ofSHOW ENGINE INNODB STATUS
output.For related information, see Section 14.8.5, “Configuring Thread Concurrency for InnoDB”.
-
Command-Line Format --innodb-thread-sleep-delay=#
System Variable innodb_thread_sleep_delay
Scope Global Dynamic Yes Type Integer Default Value 10000
Minimum Value 0
Maximum Value (64-bit platforms, ≤ 5.6.16) 18446744073709551615
Maximum Value (32-bit platforms, ≤ 5.6.16) 4294967295
Maximum Value (≥ 5.6.17) 1000000
Unit microseconds Defines how long
InnoDB
threads sleep before joining theInnoDB
queue, in microseconds. The default value is 10000. A value of 0 disables sleep. You can setinnodb_adaptive_max_sleep_delay
to the highest value you would allow forinnodb_thread_sleep_delay
, andInnoDB
automatically adjustsinnodb_thread_sleep_delay
up or down depending on current thread-scheduling activity. This dynamic adjustment helps the thread scheduling mechanism to work smoothly during times when the system is lightly loaded or when it is operating near full capacity.For more information, see Section 14.8.5, “Configuring Thread Concurrency for InnoDB”.
-
Command-Line Format --innodb-tmpdir=dir_name
Introduced 5.6.29 System Variable innodb_tmpdir
Scope Global, Session Dynamic Yes Type Directory name Default Value NULL
Used to define an alternate directory for temporary sort files created during online
ALTER TABLE
operations that rebuild the table.Online
ALTER TABLE
operations that rebuild the table also create an intermediate table file in the same directory as the original table. Theinnodb_tmpdir
option is not applicable to intermediate table files.A valid value is any directory path other than the MySQL data directory path. If the value is NULL (the default), temporary files are created MySQL temporary directory (
$TMPDIR
on Unix,%TEMP%
on Windows, or the directory specified by the--tmpdir
configuration option). If a directory is specified, existence of the directory and permissions are only checked wheninnodb_tmpdir
is configured using aSET
statement. If a symlink is provided in a directory string, the symlink is resolved and stored as an absolute path. The path should not exceed 512 bytes. An onlineALTER TABLE
operation reports an error ifinnodb_tmpdir
is set to an invalid directory.innodb_tmpdir
overrides the MySQLtmpdir
setting but only for onlineALTER TABLE
operations.The
FILE
privilege is required to configureinnodb_tmpdir
.The
innodb_tmpdir
option was introduced to help avoid overflowing a temporary file directory located on atmpfs
file system. Such overflows could occur as a result of large temporary sort files created during onlineALTER TABLE
operations that rebuild the table.In replication environments, only consider replicating the
innodb_tmpdir
setting if all servers have the same operating system environment. Otherwise, replicating theinnodb_tmpdir
setting could result in a replication failure when running onlineALTER TABLE
operations that rebuild the table. If server operating environments differ, it is recommended that you configureinnodb_tmpdir
on each server individually.For more information, see Section 14.13.3, “Online DDL Space Requirements”. For information about online
ALTER TABLE
operations, see Section 14.13, “InnoDB and Online DDL”. innodb_trx_purge_view_update_only_debug
Command-Line Format --innodb-trx-purge-view-update-only-debug[={OFF|ON}]
System Variable innodb_trx_purge_view_update_only_debug
Scope Global Dynamic Yes Type Boolean Default Value OFF
Pauses purging of delete-marked records while allowing the purge view to be updated. This option artificially creates a situation in which the purge view is updated but purges have not yet been performed. This option is only available if debugging support is compiled in using the
WITH_DEBUG
CMake option.-
Command-Line Format --innodb-trx-rseg-n-slots-debug=#
System Variable innodb_trx_rseg_n_slots_debug
Scope Global Dynamic Yes Type Integer Default Value 0
Minimum Value 0
Maximum Value 1024
Sets a debug flag that limits
TRX_RSEG_N_SLOTS
to a given value for thetrx_rsegf_undo_find_free
function that looks for free slots for undo log segments. This option is only available if debugging support is compiled in using theWITH_DEBUG
CMake option. -
Command-Line Format --innodb-undo-directory=dir_name
System Variable innodb_undo_directory
Scope Global Dynamic No Type Directory name Default Value .
The relative or absolute directory path where
InnoDB
creates undo tablespaces. Typically used to place undo logs on a different storage device. Used in conjunction withinnodb_rollback_segments
andinnodb_undo_tablespaces
. The default value of “.
” represents the same directory whereInnoDB
creates its other log files by default.NoteAn absolute directory path must be set for embedded MySQL installations. Otherwise, the server may not be able to locate undo tablespaces that are created when the MySQL instance is initialized.
For more information, see Section 14.6.3.3, “Undo Tablespaces”.
-
Command-Line Format --innodb-undo-logs=#
System Variable innodb_undo_logs
Scope Global Dynamic Yes Type Integer Default Value 128
Minimum Value 1
Maximum Value 128
Defines the number of rollback segments used by
InnoDB
. Theinnodb_undo_logs
option is an alias forinnodb_rollback_segments
. For more information, see the description ofinnodb_rollback_segments
. -
Command-Line Format --innodb-undo-tablespaces=#
System Variable innodb_undo_tablespaces
Scope Global Dynamic No Type Integer Default Value 0
Minimum Value 0
Maximum Value 126
The number of undo tablespaces used by
InnoDB
. The default value is 0.Because undo logs can become large during long-running transactions, having undo logs in multiple tablespaces reduces the maximum size of any one tablespace. The undo tablespace files are created in the location defined by
innodb_undo_directory
, with names in the form ofundo
, whereN
N
is a sequential series of integers (including leading zeros) representing the space ID. The default size of an undo tablespace file is 10MiB.Importantinnodb_undo_tablespaces
can only be configured prior to initializing the MySQL instance and cannot be changed afterward. If no value is specified, the instance is initialized using the default setting of 0. Attempting to restartInnoDB
with a greater number of undo tablespaces than specified when the MySQL instance was initialized results in a startup failure and an error stating thatInnoDB
did not find the expected number of undo tablespaces.For more information, see Section 14.6.3.3, “Undo Tablespaces”.
-
Command-Line Format --innodb-use-native-aio[={OFF|ON}]
System Variable innodb_use_native_aio
Scope Global Dynamic No Type Boolean Default Value ON
Specifies whether to use the Linux asynchronous I/O subsystem. This variable applies to Linux systems only, and cannot be changed while the server is running. Normally, you do not need to configure this option, because it is enabled by default.
The asynchronous I/O capability that
InnoDB
has on Windows systems is available on Linux systems. (Other Unix-like systems continue to use synchronous I/O calls.) This feature improves the scalability of heavily I/O-bound systems, which typically show many pending reads/writes inSHOW ENGINE INNODB STATUS\G
output.Running with a large number of
InnoDB
I/O threads, and especially running multiple such instances on the same server machine, can exceed capacity limits on Linux systems. In this case, you may receive the following error:EAGAIN: The specified maxevents exceeds the user's limit of available events.
You can typically address this error by writing a higher limit to
/proc/sys/fs/aio-max-nr
.However, if a problem with the asynchronous I/O subsystem in the OS prevents
InnoDB
from starting, you can start the server withinnodb_use_native_aio=0
. This option may also be disabled automatically during startup ifInnoDB
detects a potential problem such as a combination oftmpdir
location,tmpfs
file system, and Linux kernel that does not support AIO ontmpfs
.For more information, see Section 14.8.7, “Using Asynchronous I/O on Linux”.
-
Command-Line Format --innodb-use-sys-malloc[={OFF|ON}]
Deprecated Yes System Variable innodb_use_sys_malloc
Scope Global Dynamic No Type Boolean Default Value ON
Enables the operating system memory allocator. If disabled,
InnoDB
uses its own allocator. The default value isON
. For more information, see Section 14.8.4, “Configuring the Memory Allocator for InnoDB”.innodb_use_sys_malloc
is deprecated; expect it to be removed in a future MySQL release. The
InnoDB
version number. Starting in MySQL 5.6.11, separate version numbering forInnoDB
is discontinued and this value is the same theversion
number of the server.-
Command-Line Format --innodb-write-io-threads=#
System Variable innodb_write_io_threads
Scope Global Dynamic No Type Integer Default Value 4
Minimum Value 1
Maximum Value 64
The number of I/O threads for write operations in
InnoDB
. The default value is 4. Its counterpart for read threads isinnodb_read_io_threads
. For more information, see Section 14.8.6, “Configuring the Number of Background InnoDB I/O Threads”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.NoteOn Linux systems, running multiple MySQL servers (typically more than 12) with default settings for
innodb_read_io_threads
,innodb_write_io_threads
, and the Linuxaio-max-nr
setting can exceed system limits. Ideally, increase theaio-max-nr
setting; as a workaround, you might reduce the settings for one or both of the MySQL variables.
Also take into consideration the value of
sync_binlog
, which controls
synchronization of the binary log to disk.
For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.