MySQL 5.6 Release Notes
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 theInnoDBadaptive hash index, you can use--innodb_adaptive_hash_indexor--skip-innodb_adaptive_hash_indexon the command line, orinnodb_adaptive_hash_indexorskip-innodb_adaptive_hash_indexin an option file.System variables that take a numeric value can be specified as
--on the command line or asvar_name=valuevar_name=valueMany system variables can be changed at runtime (see Section 5.1.6.2, “Dynamic System Variables”).
For information about
GLOBALandSESSIONvariable scope modifiers, refer to theSETstatement documentation.Certain options control the locations and layout of the
InnoDBdata files. Section 14.6, “InnoDB Configuration” explains how to use these options.Some options, which you might not use initially, help tune
InnoDBperformance characteristics based on machine capacity and your database workload.For more information on specifying options and system variables, see Section 4.2.3, “Specifying Program Options”.
Table 14.8 InnoDB Option/Variable
Reference
InnoDB Command Options
-
Deprecated 5.5.22 Command-Line Format --ignore-builtin-innodbSystem Variable Name ignore_builtin_innodbVariable Scope Global Dynamic Variable No Permitted Values Type booleanIn MySQL 5.1, this option caused the server to behave as if the built-in
InnoDBwere not present, which enabledInnoDB Pluginto be used instead. In MySQL 5.6,InnoDBis the default storage engine andInnoDB Pluginis not used, so this option has no effect. As of MySQL 5.6.5, it is ignored. -
Deprecated 5.6.21 Command-Line Format --innodb[=value]Permitted Values Type enumerationDefault ONValid Values OFFONFORCEControls loading of the
InnoDBstorage engine, if the server was compiled withInnoDBsupport. This option has a tristate format, with possible values ofOFF,ON, orFORCE. See Section 5.5.2, “Installing and Uninstalling Plugins”.To disable
InnoDB, use--innodb=OFFor--skip-innodb. In this case, because the default storage engine isInnoDB, the server will not start unless you also use--default-storage-engineand--default-tmp-storage-engineto set the default to some other engine for both permanent andTEMPORARYtables.As of MySQL 5.6.21,
--innodb=OFFand--skip-innodboptions are deprecated and their use results in a warning. These options will be removed in a future MySQL release. -
Command-Line Format --innodb-status-filePermitted Values Type booleanDefault OFFControls whether
InnoDBcreates a file namedinnodb_status.in the MySQL data directory. If enabled,pidInnoDBperiodically writes the output ofSHOW ENGINE INNODB STATUSto this file.By default, the file is not created. To create it, start mysqld with the
--innodb-status-file=1option. The file is deleted during normal shutdown. Disable the
InnoDBstorage engine. See the description of--innodb.
InnoDB System Variables
daemon_memcached_enable_binlogIntroduced 5.6.6 Command-Line Format --daemon_memcached_enable_binlog=#System Variable Name daemon_memcached_enable_binlogVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault falseSee Section 14.20, “InnoDB memcached Plugin” for usage details for this option.
daemon_memcached_engine_lib_nameIntroduced 5.6.6 Command-Line Format --daemon_memcached_engine_lib_name=librarySystem Variable Name daemon_memcached_engine_lib_nameVariable Scope Global Dynamic Variable No Permitted Values Type file nameDefault innodb_engine.soSpecifies the shared library that implements the
InnoDBmemcached plugin.See Section 14.20, “InnoDB memcached Plugin” for usage details for this option.
daemon_memcached_engine_lib_pathIntroduced 5.6.6 Command-Line Format --daemon_memcached_engine_lib_path=directorySystem Variable Name daemon_memcached_engine_lib_pathVariable Scope Global Dynamic Variable No Permitted Values Type directory nameDefault NULLThe path of the directory containing the shared library that implements the
InnoDBmemcached plugin. The default value is NULL, representing the MySQL plugin directory. You should not need to modify this parameter unless specifying a different storage enginememcachedplugin that is located outside of the MySQL plugin directory.See Section 14.20, “InnoDB memcached Plugin” for usage details for this option.
-
Introduced 5.6.6 Command-Line Format --daemon_memcached_option=optionsSystem Variable Name daemon_memcached_optionVariable Scope Global Dynamic Variable No Permitted Values Type stringDefault 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, “InnoDB memcached Plugin” for usage details for this option. For information about memcached options, refer to the memcached man page.
-
Introduced 5.6.6 Command-Line Format --daemon_memcached_r_batch_size=#System Variable Name daemon_memcached_r_batch_sizeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 1Specifies how many memcached read operations (
get) to perform before doing aCOMMITto 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 the 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.
See Section 14.20, “InnoDB memcached Plugin” for usage details for this option.
-
Introduced 5.6.6 Command-Line Format --daemon_memcached_w_batch_size=#System Variable Name daemon_memcached_w_batch_sizeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 1Specifies how many memcached write operations, such as
add,set, orincr, to perform before doing aCOMMITto start a new transaction. Counterpart ofdaemon_memcached_r_batch_size.This value is set to 1 by default, on the assumption that any 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 in case of a crash.See Section 14.20, “InnoDB memcached Plugin” for usage details for this option.
-
Deprecated 5.5.22 Command-Line Format --ignore-builtin-innodbSystem Variable Name ignore_builtin_innodbVariable Scope Global Dynamic Variable No Permitted Values Type booleanSee the description of
--ignore-builtin-innodbunder “InnoDBCommand Options” earlier in this section. -
Command-Line Format --innodb_adaptive_flushing=#System Variable Name innodb_adaptive_flushingVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault ONSpecifies whether to dynamically adjust the rate of flushing dirty pages in the
InnoDBbuffer 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.6.3.5, “Configuring InnoDB Buffer Pool Flushing” for more information. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”. -
Introduced 5.6.6 Command-Line Format --innodb_adaptive_flushing_lwm=#System Variable Name innodb_adaptive_flushing_lwmVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 10Min Value 0Max Value 70Low water mark representing percentage of redo log capacity at which adaptive flushing is enabled.
-
Command-Line Format --innodb_adaptive_hash_index=#System Variable Name innodb_adaptive_hash_indexVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault ONWhether the
InnoDBadaptive 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.4.3, “Adaptive Hash Index” for details.This variable is enabled by default. You can modify this parameter using the
SET GLOBALstatement, without restarting the server. Changing the setting requires theSUPERprivilege. You can also use--skip-innodb_adaptive_hash_indexat 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_delayIntroduced 5.6.3 Command-Line Format --innodb_adaptive_max_sleep_delay=#System Variable Name innodb_adaptive_max_sleep_delayVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 150000Min Value 0Max Value 1000000Allows
InnoDBto automatically adjust the value ofinnodb_thread_sleep_delayup or down according to the current workload. Any non-zero value enables automated, dynamic adjustment of theinnodb_thread_sleep_delayvalue, up to the maximum value specified in theinnodb_adaptive_max_sleep_delayoption. The value represents the number of microseconds. This option can be useful in busy systems, with greater than 16InnoDBthreads. (In practice, it is most valuable for MySQL systems with hundreds or thousands of simultaneous connections.)For more information, see Section 14.6.6, “Configuring Thread Concurrency for InnoDB”.
innodb_additional_mem_pool_sizeDeprecated 5.6.3 Command-Line Format --innodb_additional_mem_pool_size=#System Variable Name innodb_additional_mem_pool_sizeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 8388608Min Value 2097152Max Value 4294967295The size in bytes of a memory pool
InnoDBuses to store data dictionary information and other internal data structures. The more tables you have in your application, the more memory you allocate here. IfInnoDBruns 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
InnoDBinternal memory allocator, which is unused ifinnodb_use_sys_mallocis enabled. As of MySQL 5.6.3,innodb_additional_mem_pool_sizeis deprecated and will be removed in a future MySQL release.-
Introduced 5.6.7 Command-Line Format --innodb_api_bk_commit_interval=#System Variable Name innodb_api_bk_commit_intervalVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 5Min Value 1Max Value 1073741824How often to auto-commit idle connections that use the
InnoDBmemcached interface, in seconds. See Section 14.20, “InnoDB memcached Plugin” for usage details for this option. -
Introduced 5.6.6 Command-Line Format --innodb_api_disable_rowlock=#System Variable Name innodb_api_disable_rowlockVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault OFFUse this variable to disable row locks when
InnoDBmemcached performs DML operations. By default,innodb_api_disable_rowlockis set toOFFwhich means that memcached requests row locks for get and set operations. Wheninnodb_api_disable_rowlockis set toON, memcached requests a table lock instead of row locks.The
innodb_api_disable_rowlockoption 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 you do each time the MySQL server is started. -
Introduced 5.6.6 Command-Line Format --innodb_api_enable_binlog=#System Variable Name innodb_api_enable_binlogVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault OFFLets you use the
InnoDBmemcached plugin with the MySQL binary log. See Section 14.20, “InnoDB memcached Plugin” for usage details for this option. -
Introduced 5.6.6 Command-Line Format --innodb_api_enable_mdl=#System Variable Name innodb_api_enable_mdlVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault OFFLocks the table used by the
InnoDBmemcached plugin, so that it cannot be dropped or altered by DDL through the SQL interface. See Section 14.20, “InnoDB memcached Plugin” for usage details for this option. -
Introduced 5.6.6 Command-Line Format --innodb_api_trx_level=#System Variable Name innodb_api_trx_levelVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 0Lets you control the transaction isolation level on queries processed by the memcached interface. See Section 14.20, “InnoDB memcached Plugin” for usage details for this option. The constants corresponding to the familiar names are:
0 =
READ UNCOMMITTED1 =
READ COMMITTED2 =
REPEATABLE READ3 =
SERIALIZABLE
-
Command-Line Format --innodb_autoextend_increment=#System Variable Name innodb_autoextend_incrementVariable Scope Global Dynamic Variable Yes Permitted Values (<= 5.6.5) Type integerDefault 8Min Value 1Max Value 1000Permitted Values (>= 5.6.6) Type integerDefault 64Min Value 1Max Value 1000The increment size (in MB) for extending the size of an auto-extend
InnoDBsystem tablespace file when it becomes full. The default value is 64 as of MySQL 5.6.6, 8 before that. This variable does not affect the per-table tablespace files that are created if you useinnodb_file_per_table=1. Those files are auto-extending regardless of the value ofinnodb_autoextend_increment. The initial extensions are by small amounts, after which extensions occur in increments of 4MB. -
Command-Line Format --innodb_autoinc_lock_mode=#System Variable Name innodb_autoinc_lock_modeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 1Valid Values 012The lock mode to use for generating auto-increment values. The permissible values are 0, 1, or 2, for “traditional”, “consecutive”, or “interleaved” lock mode, respectively. Section 14.8.6, “AUTO_INCREMENT Handling in InnoDB”, describes the characteristics of these modes.
This variable has a default of 1 (“consecutive” lock mode).
innodb_buffer_pool_dump_at_shutdownIntroduced 5.6.3 Command-Line Format --innodb_buffer_pool_dump_at_shutdown=#System Variable Name innodb_buffer_pool_dump_at_shutdownVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault OFFSpecifies 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 with
innodb_buffer_pool_load_at_startup.For related information, see Section 14.6.3.7, “Saving and Restoring the Buffer Pool State”.
-
Introduced 5.6.3 Command-Line Format --innodb_buffer_pool_dump_now=#System Variable Name innodb_buffer_pool_dump_nowVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault OFFImmediately records the pages cached in the InnoDB buffer pool. Typically used in combination with
innodb_buffer_pool_load_now.For related information, see Section 14.6.3.7, “Saving and Restoring the Buffer Pool State”.
-
Introduced 5.6.3 Command-Line Format --innodb_buffer_pool_filename=fileSystem Variable Name innodb_buffer_pool_filenameVariable Scope Global Dynamic Variable Yes Permitted Values Type file nameDefault ib_buffer_poolSpecifies the name of the file that holds the list of tablespace IDs and page IDs produced by
innodb_buffer_pool_dump_at_shutdownorinnodb_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_pooland is located in theInnoDBdata directory. A non-default location must be specified relative to the data directory.A file name can be specified at runtime, using a
SETstatement:SET GLOBAL innodb_buffer_pool_filename=
'file_name';You can also specify a user-defined file name at startup, in a startup string or MySQL configuration file. When specifying a file name at startup, the file must already exist or
InnoDBwill return a startup error indicating that there is no such file or directory.For related information, see Section 14.6.3.7, “Saving and Restoring the Buffer Pool State”.
-
Command-Line Format --innodb_buffer_pool_instances=#System Variable Name innodb_buffer_pool_instancesVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 1Min Value 1Max Value 64Permitted Values (Windows, 32-bit platforms, >= 5.6.6) Type integerDefault (autosized)Min Value 1Max Value 64Permitted Values (Other, >= 5.6.6) Type integerDefault 8 (or 1 if innodb_buffer_pool_size < 1GBMin Value 1Max Value 64The number of regions that the
InnoDBbuffer 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 takes effect only when you set the
innodb_buffer_pool_sizeto 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_instancesandinnodb_buffer_pool_sizeso 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_sizeis greater than 1.3GB, the default forinnodb_buffer_pool_instancesisinnodb_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_sizeis greater than or equal to 1GB. Otherwise, the default is 1.NoteA bug in MySQL 5.6 causes
SHOW VARIABLESto report aninnodb_buffer_pool_instancesvalue of 8 wheninnodb_buffer_pool_sizeis less than 1GB and only one buffer pool instance is present (Bug #18343670). As an alternative, you can useSHOW ENGINE INNODB STATUSto check the number of buffer pool instances. If there are multiple buffer pool instances,SHOW ENGINE INNODB STATUSoutput includes anINDIVIDUAL BUFFER POOL INFOsection. -
Introduced 5.6.3 Command-Line Format --innodb_buffer_pool_load_abort=#System Variable Name innodb_buffer_pool_load_abortVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault OFFInterrupts the process of restoring InnoDB buffer pool contents triggered by
innodb_buffer_pool_load_at_startuporinnodb_buffer_pool_load_now.For related information, see Section 14.6.3.7, “Saving and Restoring the Buffer Pool State”.
innodb_buffer_pool_load_at_startupIntroduced 5.6.3 Command-Line Format --innodb_buffer_pool_load_at_startup=#System Variable Name innodb_buffer_pool_load_at_startupVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault OFFSpecifies 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 with
innodb_buffer_pool_dump_at_shutdown.For related information, see Section 14.6.3.7, “Saving and Restoring the Buffer Pool State”.
-
Introduced 5.6.3 Command-Line Format --innodb_buffer_pool_load_now=#System Variable Name innodb_buffer_pool_load_nowVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault OFFImmediately 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 related information, see Section 14.6.3.7, “Saving and Restoring the Buffer Pool State”.
-
Command-Line Format --innodb_buffer_pool_size=#System Variable Name innodb_buffer_pool_sizeVariable Scope Global Dynamic Variable No Permitted Values (32-bit platforms) Type integerDefault 134217728Min Value 5242880Max Value 2**32-1Permitted Values (64-bit platforms) Type integerDefault 134217728Min Value 5242880Max Value 2**64-1The size in bytes of the buffer pool, the memory area where
InnoDBcaches table and index data. The default value is 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_instancesto 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.
InnoDBreserves 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 large installations, initialization time might be significant. For example, on a modern Linux x86_64 server, initialization of a 10GB buffer pool takes approximately 6 seconds. See Section 14.6.3.1, “The InnoDB Buffer Pool”.
-
Introduced 5.6.2 Command-Line Format --innodb_change_buffer_max_size=#System Variable Name innodb_change_buffer_max_sizeVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 25Min Value 0Max Value 50Maximum 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 general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.
-
Command-Line Format --innodb_change_buffering=#System Variable Name innodb_change_bufferingVariable Scope Global Dynamic Variable Yes Permitted Values Type enumerationDefault allValid Values noneinsertsdeleteschangespurgesallWhether
InnoDBperforms change buffering, an optimization that delays write operations to secondary indexes so that the I/O operations can be performed sequentially. The permitted values are described in the following table. For more information, see Section 14.6.5, “Configuring InnoDB Change Buffering”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.Table 14.9 Permitted Values for innodb_change_buffering
Value Description noneDo not buffer any operations. insertsBuffer insert operations. deletesBuffer delete marking operations; strictly speaking, the writes that mark index records for later deletion during a purge operation. changesBuffer inserts and delete-marking operations. purgesBuffer the physical deletion operations that happen in the background. allThe default. Buffer inserts, delete-marking operations, and purges. -
Command-Line Format --innodb_change_buffering_debug=#System Variable Name innodb_change_buffering_debugVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 0Max Value 2Sets a debug flag for
InnoDBchange buffering. A value of 1 forces all changes to the change buffer. A value of 2 causes a crash 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_DEBUGCMake option. -
Introduced 5.6.3 Command-Line Format --innodb_checksum_algorithm=#System Variable Name innodb_checksum_algorithmVariable Scope Global Dynamic Variable Yes Permitted Values (<= 5.6.5) Type enumerationDefault innodbValid Values innodbcrc32nonestrict_innodbstrict_crc32strict_nonePermitted Values (5.6.6) Type enumerationDefault crc32Valid Values innodbcrc32nonestrict_innodbstrict_crc32strict_nonePermitted Values (>= 5.6.7) Type enumerationDefault innodbValid Values innodbcrc32nonestrict_innodbstrict_crc32strict_noneSpecifies how to generate and verify the checksum stored in each disk block of each
InnoDBtablespace.innodb_checksum_algorithmreplaces theinnodb_checksumsoption as of MySQL 5.6.3. The following values are provided for compatibility:innodb_checksum_algorithm=innodbis the same asinnodb_checksums=ONinnodb_checksum_algorithm=noneis the same asinnodb_checksums=OFF
To avoid conflicts, remove references to
innodb_checksumsfrom your configuration file and MySQL startup scripts.The value
innodbis backward-compatible with all versions of MySQL. The valuecrc32uses 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 32 bits at a time, which is faster theinnodbchecksum algorithm, which scans blocks 8 bits at a time. The valuenonewrites 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 any blocks in a tablespace are modified to use thecrc32algorithm, the associated tables cannot be read by earlier versions of MySQL.The
strict_*forms work the same asinnodb,crc32, andnone, except thatInnoDBreports an error if it encounters a valid but non-matching checksum value in the tablespace. It is recommended that you only use thestrict_*options in a new instance, to set up all tablespaces for the first time. Thestrict_*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_algorithmcausedInnoDBto 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,crc32ornonechecksum.The following table illustrates the difference between the
none,innodb, andcrc32option values, and theirstrict_counterparts.none,innodb, andcrc32write the specified type of checksum value into each data block, but for compatibility accept any of the other checksum values when verifying a block during a read operation. Thestrict_form of each parameter also accepts any valid checksum value but prints an error message when a valid non-matching checksum value is encountered. Using thestrict_form can make verification faster if allInnoDBdata files in an instance are created under an identicalinnodb_checksum_algorithmvalue.Table 14.10 innodb_checksum_algorithm Settings
Value Generated checksum (when writing) Allowed 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 crc32algorithm, 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.InnoDBprints 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.InnoDBprints an error message if a valid but non-matching checksum is encountered.strict_crc32 A checksum calculated using the crc32algorithm, possibly done with a hardware assist.Any of the checksums generated by none,innodb, orcrc32.InnoDBprints an error message if a valid but non-matching checksum is encountered.The default value for
innodb_checksum_algorithmwas changed frominnodbtocrc32in MySQL 5.6.6, but switched back toinnodbin 5.6.7 for improved compatibility ofInnoDBdata files during a downgrade to an earlier MySQL version, and for use with MySQL Enterprise Backup. The limitations encountered included:.ibdfiles 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 level 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.
-
Deprecated 5.6.3 Command-Line Format --innodb_checksumsSystem Variable Name innodb_checksumsVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault ONInnoDBcan use checksum validation on all tablespace pages read from the 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 extra safety feature can be disabled with--skip-innodb-checksums. You can specify the method of calculating the checksum withinnodb_checksum_algorithm.In MySQL 5.6.3 and higher, this option is deprecated, replaced by
innodb_checksum_algorithm.innodb_checksum_algorithm=innodbis the same asinnodb_checksums=ON(the default).innodb_checksum_algorithm=noneis the same asinnodb_checksums=OFF. Remove anyinnodb_checksumsoptions from your configuration files and startup scripts, to avoid conflicts withinnodb_checksum_algorithm:innodb_checksums=OFFwould automatically setinnodb_checksum_algorithm=none;innodb_checksums=ONwould be ignored and overridden by any other setting forinnodb_checksum_algorithm. -
Introduced 5.6.7 Command-Line Format --innodb_cmp_per_index_enabled=#System Variable Name innodb_cmp_per_index_enabledVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault OFFValid Values OFFONEnables per-index compression-related statistics in the
INFORMATION_SCHEMA.INNODB_CMP_PER_INDEXtable. Because these statistics can be expensive to gather, only enable this option on development, test, or slave instances during performance tuning related toInnoDBcompressed tables. -
Command-Line Format --innodb_commit_concurrency=#System Variable Name innodb_commit_concurrencyVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 0Min Value 0Max Value 1000The 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_concurrencycannot 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_pctIntroduced 5.6.7 Command-Line Format --innodb_compression_failure_threshold_pct=#System Variable Name innodb_compression_failure_threshold_pctVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 5Min Value 0Max Value 100Sets the cutoff point at which MySQL begins adding padding within compressed pages to avoid expensive compression failures. 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”.
-
Introduced 5.6.7 Command-Line Format --innodb_compression_level=#System Variable Name innodb_compression_levelVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 6Min Value 0Max Value 9Specifies the level of zlib compression to use for
InnoDBcompressed tables and indexes.For more information, see Section 14.9.6, “Compression for OLTP Workloads”.
innodb_compression_pad_pct_maxIntroduced 5.6.7 Command-Line Format --innodb_compression_pad_pct_max=#System Variable Name innodb_compression_pad_pct_maxVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 50Min Value 0Max Value 75Specifies 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_pctis set to a non-zero 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 Name innodb_concurrency_ticketsVariable Scope Global Dynamic Variable Yes Permitted Values (<= 5.6.5) Type integerDefault 500Min Value 1Max Value 4294967295Permitted Values (>= 5.6.6) Type integerDefault 5000Min Value 1Max Value 4294967295Determines the number of threads that can enter
InnoDBconcurrently. A thread is placed in a queue when it tries to enterInnoDBif the number of threads has already reached the concurrency limit. When a thread is permitted to enterInnoDB, it is given a number of “free tickets” equal to the value ofinnodb_concurrency_tickets, and the thread can enter and leaveInnoDBfreely 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_ticketsvalue, 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_ticketsvalue is that large transactions must loop through the queue many times before they can complete, which extends the length of time required to complete their task.With a large
innodb_concurrency_ticketsvalue, 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_ticketsvalue 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 non-zero
innodb_thread_concurrencyvalue, you may need to adjust theinnodb_concurrency_ticketsvalue up or down to find the optimal balance between larger and smaller transactions. TheSHOW ENGINE INNODB STATUSreport 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_TICKETScolumn of theINFORMATION_SCHEMA.INNODB_TRXtable.For more information, see Section 14.6.6, “Configuring Thread Concurrency for InnoDB”.
-
Command-Line Format --innodb_data_file_path=nameSystem Variable Name innodb_data_file_pathVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.6) Type stringDefault ibdata1:10M:autoextendPermitted Values (>= 5.6.7) Type stringDefault ibdata1:12M:autoextendThe paths to individual
InnoDBdata files and their sizes. The full directory path to each data file is formed by concatenatinginnodb_data_home_dirto each path specified here. The file sizes are specified KB, MB or GB (1024MB) by appendingK,MorGto the size value. If specifying data file size in kilobytes (KB), do so in multiples of 1024. Otherwise, KB values are rounded off to nearest megabyte (MB) boundary. The sum of the sizes of the files must be at least slightly larger than 10MB. If you do not specifyinnodb_data_file_path, the default behavior is to create a single auto-extending data file, slightly larger than 12MB, namedibdata1. The size limit of individual files is determined by your operating system. You can set the file size to more than 4GB on those operating systems that support big files. You can also use raw disk partitions as data files. For detailed information on configuringInnoDBtablespace files, see Section 14.6, “InnoDB Configuration”. -
Command-Line Format --innodb_data_home_dir=dir_nameSystem Variable Name innodb_data_home_dirVariable Scope Global Dynamic Variable No Permitted Values Type directory nameThe common part of the directory path for all
InnoDBdata files in the system tablespace. This setting does not affect the location of file-per-table tablespaces wheninnodb_file_per_tableis enabled. The default value is the MySQLdatadirectory. If you specify the value as an empty string, you can use absolute file paths ininnodb_data_file_path. innodb_disable_sort_file_cacheIntroduced 5.6.4 Command-Line Format --innodb_disable_sort_file_cache=#System Variable Name innodb_disable_sort_file_cacheVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault OFFIf enabled, this variable 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. This variable was added in MySQL 5.6.4.-
Command-Line Format --innodb-doublewriteSystem Variable Name innodb_doublewriteVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault ONIf this variable is enabled (the default),
InnoDBstores all data twice, first to the doublewrite buffer, then to the actual data files. This variable can be turned off with--skip-innodb_doublewritefor benchmarks or cases when top performance is needed rather than concern for data integrity or possible failures. -
Command-Line Format --innodb_fast_shutdown[=#]System Variable Name innodb_fast_shutdownVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 1Valid Values 012The
InnoDBshutdown mode. If the value is 0,InnoDBdoes a slow shutdown, a full purge and a change buffer merge before shutting down. If the value is 1 (the default),InnoDBskips these operations at shutdown, a process known as a fast shutdown. If the value is 2,InnoDBflushes 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=2in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption. innodb_fil_make_page_dirty_debugIntroduced 5.6.17 Command-Line Format --innodb_fil_make_page_dirty_debug=#System Variable Name innodb_fil_make_page_dirty_debugVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 0Max Value 2**32-1By default, setting
innodb_fil_make_page_dirty_debugto the ID of a tablespace immediately dirties the first page of the tablespace. Ifinnodb_saved_page_number_debugis set to a non-default value, settinginnodb_fil_make_page_dirty_debugdirties the specified page. Theinnodb_fil_make_page_dirty_debugoption is only available if debugging support is compiled in using theWITH_DEBUGCMake option.-
Command-Line Format --innodb_file_format=#System Variable Name innodb_file_formatVariable Scope Global Dynamic Variable Yes Permitted Values Type stringDefault AntelopeValid Values AntelopeBarracudaThe file format to use for new
InnoDBtables. Currently,AntelopeandBarracudaare supported. This applies only for tables that have their own tablespace, so for it to have an effect,innodb_file_per_tablemust be enabled. The Barracuda file format is required for certain InnoDB features such as table compression.Be aware that
ALTER TABLEoperations that recreateInnoDBtables (ALGORITHM=COPY) will use the currentinnodb_file_formatsetting (the conditions outlined above still apply). -
Command-Line Format --innodb_file_format_check=#System Variable Name innodb_file_format_checkVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault ONThis variable can be set to 1 or 0 at server startup to enable or disable whether
InnoDBchecks the file format tag in the system tablespace (for example,AntelopeorBarracuda). If the tag is checked and is higher than that supported by the current version ofInnoDB, an error occurs andInnoDBdoes not start. If the tag is not higher,InnoDBsets the value ofinnodb_file_format_maxto the file format tag.NoteDespite the default value sometimes being displayed as
ONorOFF, always use the numeric values 1 or 0 to turn this option on or off in your configuration file or command line. -
Command-Line Format --innodb_file_format_max=#System Variable Name innodb_file_format_maxVariable Scope Global Dynamic Variable Yes Permitted Values Type stringDefault AntelopeValid Values AntelopeBarracudaAt server startup,
InnoDBsets the value of this variable to the file format tag in the system tablespace (for example,AntelopeorBarracuda). If the server creates or opens a table with a “higher” file format, it sets the value ofinnodb_file_format_maxto that format. -
Command-Line Format --innodb_file_per_tableSystem Variable Name innodb_file_per_tableVariable Scope Global Dynamic Variable Yes Permitted Values (<= 5.6.5) Type booleanDefault OFFPermitted Values (>= 5.6.6) Type booleanDefault ONWhen
innodb_file_per_tableis enabled (the default in 5.6.6 and higher),InnoDBstores the data and indexes for each newly created table in a separate.ibdfile, rather than in the system tablespace. The storage for theseInnoDBtables is reclaimed when the tables are dropped or truncated. This setting enables several otherInnoDBfeatures, such as table compression. See Section 14.7.4, “InnoDB File-Per-Table Tablespaces” for details about such features as well as advantages and disadvantages of using file-per-table tablespaces.Be aware that enabling
innodb_file_per_tablealso means that anALTER TABLEoperation will moveInnoDBtable from the system tablespace to an individual.ibdfile in cases whereALTER TABLErecreates the table (ALGORITHM=COPY).When
innodb_file_per_tableis disabled,InnoDBstores the data for all tables and indexes in the ibdata files that make up the system tablespace. This setting reduces the performance overhead of filesystem operations for operations such asDROP TABLEorTRUNCATE TABLE. It is most appropriate for a server environment where entire storage devices are devoted to MySQL data. Because the system tablespace never shrinks, and is shared across all databases in an instance, avoid loading huge amounts of temporary data on a space-constrained system wheninnodb_file_per_table=OFF. Set up a separate instance in such cases, so that you can drop the entire instance to reclaim the space.By default,
innodb_file_per_tableis enabled as of MySQL 5.6.6, disabled before that. Consider disabling it if backward compatibility with MySQL 5.5 or 5.1 is a concern. This will preventALTER TABLEfrom movingInnoDBtables from the system tablespace to individual.ibdfiles.innodb_file_per_tableis dynamic and can be setONorOFFusingSET GLOBAL. You can also set this parameter in the MySQL configuration file (my.cnformy.ini) but this requires shutting down and restarting the server.Dynamically changing the value of this parameter requires the
SUPERprivilege and immediately affects the operation of all connections. -
Introduced 5.6.6 System Variable Name innodb_flush_log_at_timeoutVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 1Min Value 1Max Value 2700Write and flush the logs every
Nseconds.innodb_flush_log_at_timeoutwas introduced in MySQL 5.6.6. It 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 MySQL 5.6.6, flushing frequency was once per second. The default setting forinnodb_flush_log_at_timeoutis also once per second. innodb_flush_log_at_trx_commitCommand-Line Format --innodb_flush_log_at_trx_commit[=#]System Variable Name innodb_flush_log_at_trx_commitVariable Scope Global Dynamic Variable Yes Permitted Values Type enumerationDefault 1Valid Values 012Controls 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 up to a second of transactions in a crash.
The default value of 1 is required for full ACID compliance. With this value, the contents of the
InnoDBlog buffer are written out to the log file at each transaction commit and the log file is flushed to disk.With a value of 0, the contents of the
InnoDBlog buffer are written to the log file approximately once per second and the log file is flushed to disk. No writes from the log buffer to the log file are performed at transaction commit. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions with any mysqld process crash.With a value of 2, the contents of the
InnoDBlog buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions in an operating system crash or a power outage.As of MySQL 5.6.6,
InnoDBlog flushing frequency is controlled byinnodb_flush_log_at_timeout, which allows you to set log flushing frequency toNseconds (whereNis1 ... 2700, with a default value of 1). However, any mysqld process crash can erase up toNseconds of transactions.DDL changes and other internal
InnoDBactivities flush theInnoDBlog independent of theinnodb_flush_log_at_trx_commitsetting.InnoDB's crash recovery works regardless of theinnodb_flush_log_at_trx_commitsetting. Transactions are either applied entirely or erased entirely.
For durability and consistency in a replication setup that uses
InnoDBwith 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. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt
InnoDBdata. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix command hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.-
Command-Line Format --innodb_flush_method=nameSystem Variable Name innodb_flush_methodVariable Scope Global Dynamic Variable No Permitted Values (Unix, <= 5.6.6) Type stringDefault NULLValid Values fsynclittlesyncnosyncO_DSYNCO_DIRECTPermitted Values (Unix, >= 5.6.7) Type stringDefault NULLValid Values fsyncO_DSYNClittlesyncnosyncO_DIRECTO_DIRECT_NO_FSYNCPermitted Values (Windows) Type stringDefault NULLValid Values async_unbufferednormalunbufferedDefines the method used to flush data to the
InnoDBdata files and log files, which can affect I/O throughput. This variable is only configurable on Unix and Linux systems.If
innodb_flush_method=NULLon a Unix-like system, thefsyncoption is used by default. Ifinnodb_flush_method=NULLon Windows, theasync_unbufferedoption is used by default.If
innodb_flush_method=NULLon a Unix-like system, thefdatasyncoption is used by default. Ifinnodb_flush_method=NULLon Windows, theasync_unbufferedoption is used by default.The
innodb_flush_methodoptions for Unix-like systems include:fsync:InnoDBuses thefsync()system call to flush both the data and log files.fsyncis the default setting.O_DSYNC:InnoDBusesO_SYNCto open and flush the log files, andfsync()to flush the data files.InnoDBdoes not useO_DSYNCdirectly 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:InnoDBusesO_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:InnoDBusesO_DIRECTduring flushing I/O, but skips thefsync()system call afterward. This setting is suitable for some types of file systems but not others. For example, it is not suitable for XFS. If you are not sure whether the file system you use requires anfsync(), for example to preserve all file metadata, useO_DIRECTinstead. This option was introduced in MySQL 5.6.7 (Bug #11754304, Bug #45892).
The
innodb_flush_methodoptions for Windows systems include:async_unbuffered:InnoDBuses Windows asynchronous I/O and non-buffered I/O.async_unbufferedis the default setting on Windows systems.normal:InnoDBuses a 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:InnoDBuses a 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 settings affects performance depends on hardware configuration and workload. Benchmark your particular configuration to decide which setting to use, or whether to keep the default setting. Examine the
Innodb_data_fsyncsstatus 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_DIRECTcan help to avoid double buffering between theInnoDBbuffer pool and the operating system's file system cache. On some systems whereInnoDBdata and log files are located on a SAN, the default value orO_DSYNCmight be faster for a read-heavy workload with mostlySELECTstatements. 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”. -
Introduced 5.6.3 Command-Line Format --innodb_flush_neighborsSystem Variable Name innodb_flush_neighborsVariable Scope Global Dynamic Variable Yes Permitted Values Type enumerationDefault 1Valid Values 012Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent.
The default value of 1 flushes contiguous dirty pages in the same extent from the buffer pool.
A setting of 0 turns
innodb_flush_neighborsoff and no other dirty pages are flushed from the buffer pool.A setting of 2 flushes dirty pages in the same extent from the buffer pool.
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 the write operations. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.
-
Introduced 5.6.6 Command-Line Format --innodb_flushing_avg_loops=#System Variable Name innodb_flushing_avg_loopsVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 30Min Value 1Max Value 1000Number 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.
-
Introduced 5.6.3 Command-Line Format --innodb_force_load_corruptedSystem Variable Name innodb_force_load_corruptedVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault OFFLets InnoDB load tables at startup that are marked as corrupted. Use only during troubleshooting, to recover data that is otherwise inaccessible. When troubleshooting is complete, turn this setting back off and restart the server.
-
Command-Line Format --innodb_force_recovery=#System Variable Name innodb_force_recoveryVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 0Min Value 0Max Value 6The 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
InnoDBand dump your tables. As a safety measure,InnoDBpreventsINSERT,UPDATE, orDELETEoperations wheninnodb_force_recoveryis greater than 0. Also, as of 5.6.15, aninnodb_force_recoverysetting of 4 or greater placesInnoDBinto read-only mode.These restrictions may cause replication administration commands to fail with an error, as replication options such as
--relay-log-info-repository=TABLEand--master-info-repository=TABLEstore information in tables inInnoDB. -
Introduced 5.6.4 System Variable Name innodb_ft_aux_tableVariable Scope Global Dynamic Variable Yes Permitted Values Type stringSpecifies the qualified name of an
InnoDBtable containing aFULLTEXTindex. This variable is intended for diagnostic purposes and can only be set at runtime. For example:mysql> set global innodb_ft_aux_table = 'test/t1';
Attempting to set this variable at startup will result in a “mysqld: option '--innodb-ft-aux-table' cannot take an argument” error and startup will abort. After you set this variable to a name in the format
db_name/table_nameINFORMATION_SCHEMAtablesINNODB_FT_INDEX_TABLE,INNODB_FT_INDEX_CACHE,INNODB_FT_CONFIG,INNODB_FT_DELETED, andINNODB_FT_BEING_DELETEDwill show information about the search index for the specified table. -
Introduced 5.6.4 Command-Line Format --innodb_ft_cache_size=#System Variable Name innodb_ft_cache_sizeVariable Scope Global Dynamic Variable No Permitted Values (>= 5.6.4, <= 5.6.9) Type integerDefault 32000000Permitted Values (>= 5.6.10) Type integerDefault 8000000Min Value 1600000Max Value 80000000The memory allocated, in bytes, for the
InnoDBFULLTEXT search index cache, which holds a parsed document in memory while creating anInnoDBFULLTEXTindex. Index inserts and updates are only committed to disk when theinnodb_ft_cache_sizesize limit is reached.innodb_ft_cache_sizedefines the cache size on a per table basis. To set a global limit for all tables, seeinnodb_ft_total_cache_size. -
Introduced 5.6.4 Command-Line Format --innodb_ft_enable_diag_print=#System Variable Name innodb_ft_enable_diag_printVariable Scope Global Dynamic Variable Yes Permitted Values (<= 5.6.6) Type booleanDefault ONPermitted Values (>= 5.6.7) Type booleanDefault OFFWhether to enable additional full-text search (FTS) diagnostic output. This option is primarily intended for advanced FTS debugging and will not be 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
-
Introduced 5.6.4 Command-Line Format --innodb_ft_enable_stopword=#System Variable Name innodb_ft_enable_stopwordVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault ONSpecifies that a set of stopwords is associated with an
InnoDBFULLTEXTindex at the time the index is created. If theinnodb_ft_user_stopword_tableoption is set, the stopwords are taken from that table. Else, if theinnodb_ft_server_stopword_tableoption is set, the stopwords are taken from that table. Otherwise, a built-in set of default stopwords is used. -
Introduced 5.6.4 Command-Line Format --innodb_ft_max_token_size=#System Variable Name innodb_ft_max_token_sizeVariable Scope Global Dynamic Variable No Permitted Values (>= 5.6.4, <= 5.6.13) Type integerDefault 84Min Value 10Max Value 252Permitted Values (>= 5.6.14) Type integerDefault 84Min Value 10Max Value 84Maximum character length of words that are stored in an InnoDB
FULLTEXTindex. 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. -
Introduced 5.6.4 Command-Line Format --innodb_ft_min_token_size=#System Variable Name innodb_ft_min_token_sizeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 3Min Value 0Max Value 16Minimum length of words that are stored in an InnoDB
FULLTEXTindex. Increasing this value reduces the size of the index, thus speeding up queries, by omitting common word 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. -
Introduced 5.6.4 Command-Line Format --innodb_ft_num_word_optimize=#System Variable Name innodb_ft_num_word_optimizeVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 2000Number of words to process during each
OPTIMIZE TABLEoperation on anInnoDBFULLTEXTindex. 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 TABLEstatements, each picking up where the last left off. -
Introduced 5.6.13 Command-Line Format --innodb_ft_result_cache_limit=#System Variable Name innodb_ft_result_cache_limitVariable Scope Global Dynamic Variable Yes Permitted Values (>= 5.6.17) Type integerDefault 2000000000Min Value 1000000Max Value 2**32-1Permitted Values (Unix, 32-bit platforms, >= 5.6.13, <= 5.6.16) Type integerDefault 2000000000Min Value 1000000Max Value 2**32-1Permitted Values (Unix, 64-bit platforms, >= 5.6.13, <= 5.6.16) Type integerDefault 2000000000Min Value 1000000Max Value 2**64-1Permitted Values (Windows, >= 5.6.13, <= 5.6.16) Type integerDefault 2000000000Min Value 1000000Max Value 2**32-1The
InnoDBFULLTEXT search (FTS) query result cache limit (defined in bytes) per FTS query or per thread. Intermediate and finalInnoDBFTS query results are handled in memory. Useinnodb_ft_result_cache_limitto place a size limit on theInnoDBFTS query result cache to avoid excessive memory consumption in case of very largeInnoDBFTS query results (millions or hundreds of millions of rows, for example). Memory is allocated as required when an FTS 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_limitfor all platform types and platform bit sizes is 2**32-1. Bug #71554. innodb_ft_server_stopword_tableIntroduced 5.6.4 Command-Line Format --innodb_ft_server_stopword_table=db_name/table_nameSystem Variable Name innodb_ft_server_stopword_tableVariable Scope Global Dynamic Variable Yes Permitted Values Type stringDefault NULLThis option is used to specify your own
InnoDBFULLTEXTindex stopword list for allInnoDBtables. To configure your own stopword list for a specificInnoDBtable, useinnodb_ft_user_stopword_table.Set
innodb_ft_server_stopword_tableto the name of the table containing a list of stopwords, in the formatdb_name/table_nameThe stopword table must exist before you configure
innodb_ft_server_stopword_table.innodb_ft_enable_stopwordmust be enabled andinnodb_ft_server_stopword_tableoption must be configured before you create theFULLTEXTindex.The stopword table must be an
InnoDBtable, containing a singleVARCHARcolumn namedvalue.For more information, see Section 12.9.4, “Full-Text Stopwords”.
-
Introduced 5.6.4 Command-Line Format --innodb_ft_sort_pll_degree=#System Variable Name innodb_ft_sort_pll_degreeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 2Min Value 1Max Value 32Number of threads used in parallel to index and tokenize text in an
InnoDBFULLTEXTindex, when building a search index. Seeinnodb_sort_buffer_sizefor additional usage information. -
Introduced 5.6.13 Command-Line Format --innodb_ft_total_cache_size=#System Variable Name innodb_ft_total_cache_sizeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 640000000Min Value 32000000Max Value 1600000000The total memory allocated, in bytes, for the
InnoDBFULLTEXTsearch index cache for all tables. Creating numerous tables, each with a full-text 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 force sync is triggered. -
Introduced 5.6.4 Command-Line Format --innodb_ft_user_stopword_table=db_name/table_nameSystem Variable Name innodb_ft_user_stopword_tableVariable Scope Global, Session Dynamic Variable Yes Permitted Values Type stringDefault NULLThis option is used to specify your own
InnoDBFULLTEXTindex stopword list on a specific table. To configure your own stopword list for allInnoDBtables, useinnodb_ft_server_stopword_table.Set
innodb_ft_user_stopword_tableto the name of the table containing a list of stopwords, in the formatdb_name/table_nameThe stopword table must exist before you configure
innodb_ft_user_stopword_table.innodb_ft_enable_stopwordmust be enabled andinnodb_ft_user_stopword_tablemust be configured before you create theFULLTEXTindex.The stopword table must be an
InnoDBtable, containing a singleVARCHARcolumn namedvalue.For more information, see Section 12.9.4, “Full-Text Stopwords”.
-
Command-Line Format --innodb_io_capacity=#System Variable Name innodb_io_capacityVariable Scope Global Dynamic Variable Yes Permitted Values (32-bit platforms) Type integerDefault 200Min Value 100Max Value 2**32-1Permitted Values (64-bit platforms) Type integerDefault 200Min Value 100Max Value 2**64-1The
innodb_io_capacityparameter sets an upper limit on the I/O activity performed byInnoDBbackground tasks, such as flushing pages from the buffer pool and merging data from the change buffer. The default value is 200. For busy systems capable of higher I/O rates, you can set a higher value at server startup to help the server handle the background maintenance work associated with a high rate of row changes.The
innodb_io_capacitylimit is a total limit for all buffer pool instances. When dirty pages are flushed, theinnodb_io_capacitylimit is divided equally among buffer pool instances.For systems with individual 5400 RPM or 7200 RPM drives, you might lower the value to the former default of
100, which represents an estimated proportion of the I/O operations per second (IOPS) available to older-generation disk drives that could perform about 100 IOPS. The current default of 200 reflects that modern storage devices are capable of much higher I/O rates.This parameter should be set to approximately the number of I/O operations that the system can perform per second. Ideally, keep the setting as low as practical, but not so low that background activities fall behind. If the value is too high, data is removed from the buffer pool and insert buffer too quickly to provide significant benefit from the caching.
In general, you can increase the value as a function of the number of drives used for
InnoDBI/O, particularly fast drives capable of high numbers of IOPS. For example, systems that use multiple disks or solid-state disks forInnoDBare likely to benefit from the ability to control this parameter.Although you can specify a very high number, in practice such large values have little if any benefit; for example, a value of one million would be considered very high.
You can set
innodb_io_capacityto any number 100 or greater to a maximum defined byinnodb_io_capacity_max.innodb_io_capacitycan be set in the MySQL option file (my.cnformy.ini) or changed dynamically using aSET GLOBALstatement, which requires theSUPERprivilege.See Section 14.6.8, “Configuring the InnoDB Master Thread I/O Rate” for more guidelines about this option. For general information about InnoDB I/O performance, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.
-
Introduced 5.6.6 Command-Line Format --innodb_io_capacity_max=#System Variable Name innodb_io_capacity_maxVariable Scope Global Dynamic Variable Yes Permitted Values (32-bit platforms) Type integerDefault see descriptionMin Value 100Max Value 2**32-1Permitted Values (Unix, 64-bit platforms) Type integerDefault see descriptionMin Value 100Max Value 2**64-1Permitted Values (Windows, 64-bit platforms) Type integerDefault see descriptionMin Value 100Max Value 2**32-1If flushing activity falls behind,
InnoDBcan flush more aggressively than the limit imposed byinnodb_io_capacity.innodb_io_capacity_maxdefines an upper limit for I/O capacity in such situations.If you specify an
innodb_io_capacitysetting at startup but do not specify a value forinnodb_io_capacity_max,innodb_io_capacity_maxdefaults to twice the value ofinnodb_io_capacitywith a minimum value of 2000, which is also the initial default value.The
innodb_io_capacity_maxsetting is a total limit for all buffer pool instances.innodb_io_capacity_maxcannot be set to a value lower than theinnodb_io_capacityvalue.Setting
innodb_io_capacity_maxtoDEFAULTusing aSETstatement (SET GLOBAL innodb_io_capacity_max=DEFAULT) setsinnodb_io_capacity_maxto the maximum value.For a brief period during MySQL 5.6 development, this variable was known as
innodb_max_io_capacity. In MySQL 5.6.7, it was renamed toinnodb_io_capacity_max, to emphasize its relationship to theinnodb_io_capacityoption. -
Introduced 5.6.3 Command-Line Format --innodb_large_prefixSystem Variable Name innodb_large_prefixVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault OFFEnable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes) for
InnoDBtables that use theDYNAMICandCOMPRESSEDrow formats. (Creating such tables also requires the option valuesinnodb_file_format=barracudaandinnodb_file_per_table=true.) See Section 14.8.8, “Limits on InnoDB Tables” for the relevant maximums associated with index key prefixes under various settings.For tables using the
REDUNDANTandCOMPACTrow formats, this option does not affect the allowed key prefix length. innodb_limit_optimistic_insert_debugCommand-Line Format --innodb_limit_optimistic_insert_debug=#System Variable Name innodb_limit_optimistic_insert_debugVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 0Min Value 0Max Value 2**32-1Limits 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_DEBUGCMake option.-
Command-Line Format --innodb_lock_wait_timeout=#System Variable Name innodb_lock_wait_timeoutVariable Scope Global, Session Dynamic Variable Yes Permitted Values Type integerDefault 50Min Value 1Max Value 1073741824The length of time in seconds an
InnoDBtransaction 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 anotherInnoDBtransaction 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_timeoutoption. 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_timeoutapplies toInnoDBrow locks only. A MySQL table lock does not happen insideInnoDBand this timeout does not apply to waits for table locks.The lock wait timeout value does not apply to deadlocks, because
InnoDBdetects them immediately and rolls back one of the deadlocked transactions.innodb_lock_wait_timeoutcan be set at runtime with theSET GLOBALorSET SESSIONstatement. Changing theGLOBALsetting requires theSUPERprivilege and affects the operation of all clients that subsequently connect. Any client can change theSESSIONsetting forinnodb_lock_wait_timeout, which affects only that client. innodb_locks_unsafe_for_binlogDeprecated 5.6.3 Command-Line Format --innodb_locks_unsafe_for_binlogSystem Variable Name innodb_locks_unsafe_for_binlogVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault OFFThis variable affects how
InnoDBuses gap locking for searches and index scans. As of MySQL 5.6.3,innodb_locks_unsafe_for_binlogis deprecated and will be removed in a future MySQL release.Normally,
InnoDBuses an algorithm called next-key locking that combines index-row locking with gap locking.InnoDBperforms row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on recordRin an index, another session cannot insert a new index record in the gap immediately beforeRin the index order. See Section 14.5.1, “InnoDB Locking”.By default, the value of
innodb_locks_unsafe_for_binlogis 0 (disabled), which means that gap locking is enabled:InnoDBuses next-key locks for searches and index scans. To enable the variable, set it to 1. This causes gap locking to be disabled:InnoDBuses only index-record locks for searches and index scans.Enabling
innodb_locks_unsafe_for_binlogdoes not disable the use of gap locking for foreign-key constraint checking or duplicate-key checking.The effect of enabling
innodb_locks_unsafe_for_binlogis similar to but not identical to setting the transaction isolation level toREAD COMMITTED:Enabling
innodb_locks_unsafe_for_binlogis 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_binlogcan be set only at server startup, whereas the isolation level can be set at startup or changed at runtime.
READ COMMITTEDtherefore offers finer and more flexible control thaninnodb_locks_unsafe_for_binlog. For additional details about the effect of isolation level on gap locking, see Section 13.3.6, “SET TRANSACTION Syntax”.Enabling
innodb_locks_unsafe_for_binlogmay 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 theidcolumn of thechildtable 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
idis 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 sameSELECTagain 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,InnoDBdoes not guarantee serializability. Therefore, ifinnodb_locks_unsafe_for_binlogis enabled,InnoDBguarantees at most an isolation level ofREAD COMMITTED. (Conflict serializability is still guaranteed.) For additional information about phantoms, see Section 14.5.4, “Phantom Rows”.Enabling
innodb_locks_unsafe_for_binloghas additional effects:For
UPDATEorDELETEstatements,InnoDBholds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated theWHEREcondition. This greatly reduces the probability of deadlocks, but they can still happen.For
UPDATEstatements, if a row is already locked,InnoDBperforms a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches theWHEREcondition of theUPDATE. If the row matches (must be updated), MySQL reads the row again and this timeInnoDBeither 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.8.9, “Clustered and Secondary Indexes”).
Suppose that one client performs an
UPDATEusing these statements:SET autocommit = 0; UPDATE t SET b = 5 WHERE b = 3;
Suppose also that a second client performs an
UPDATEby executing these statements following those of the first client:SET autocommit = 0; UPDATE t SET b = 4 WHERE b = 2;
As
InnoDBexecutes eachUPDATE, it first acquires an exclusive lock for each row, and then determines whether to modify it. IfInnoDBdoes not modify the row andinnodb_locks_unsafe_for_binlogis enabled, it releases the lock. Otherwise,InnoDBretains the lock until the end of the transaction. This affects transaction processing as follows.If
innodb_locks_unsafe_for_binlogis disabled, the firstUPDATEacquires 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
UPDATEblocks as soon as it tries to acquire any locks (because first update has retained locks on all rows), and does not proceed until the firstUPDATEcommits or rolls back:x-lock(1,2); block and wait for first UPDATE to commit or roll back
If
innodb_locks_unsafe_for_binlogis enabled, the firstUPDATEacquires 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,InnoDBdoes a “semi-consistent” read, returning the latest committed version of each row to MySQL so that MySQL can determine whether the row matches theWHEREcondition 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 Name innodb_log_buffer_sizeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 8388608Min Value 262144Max Value 4294967295The size in bytes of the buffer that
InnoDBuses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”. -
Introduced 5.6.11 Command-Line Format --innodb_log_compressed_pages=#System Variable Name innodb_log_compressed_pagesVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault ONSpecifies 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_pagesis enabled by default to prevent corruption that could occur if a different version of thezlibcompression algorithm is used during recovery. If you are certain that thezlibversion will not change, you can disableinnodb_log_compressed_pagesto 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 theLOGsection ofSHOW ENGINE INNODB STATUSoutput, or monitoringInnodb_os_log_writtenstatus for the number of bytes written to the redo log files. -
Command-Line Format --innodb_log_file_size=#System Variable Name innodb_log_file_sizeVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.2) Type integerDefault 5242880Min Value 1048576Max Value 4GB / innodb_log_files_in_groupPermitted Values (>= 5.6.3, <= 5.6.7) Type integerDefault 5242880Min Value 1048576Max Value 512GB / innodb_log_files_in_groupPermitted Values (>= 5.6.8) Type integerDefault 50331648Min Value 1048576Max Value 512GB / innodb_log_files_in_groupThe 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, would allow you to approach the limit but not exceed it. The default value is 48MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, whereNis the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. Larger log files also make crash recovery slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size less of a consideration. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.ImportantDue to Bug #69477, redo log writes for large, externally stored
BLOBfields could overwrite the most recent checkpoint. To address this bug, a patch introduced in MySQL 5.6.20 limits the size of redo logBLOBwrites to 10% of the redo log file size. As a result of this limit,innodb_log_file_sizeshould be set to a value greater than 10 times the largestBLOBdata size found in the rows of your tables plus the length of other variable length fields (VARCHAR,VARBINARY, andTEXTtype fields).In MySQL 5.6.22, the redo log
BLOBwrite limit is relaxed to 10% of the total redo log size (innodb_log_file_size*innodb_log_files_in_group). (Bug #19498877) -
Command-Line Format --innodb_log_files_in_group=#System Variable Name innodb_log_files_in_groupVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 2Min Value 2Max Value 100The number of log files in the log group.
InnoDBwrites to the files in a circular fashion. The default (and recommended) value is 2. The location of these 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. -
Command-Line Format --innodb_log_group_home_dir=dir_nameSystem Variable Name innodb_log_group_home_dirVariable Scope Global Dynamic Variable No Permitted Values Type directory nameThe directory path to the
InnoDBredo log files, whose number is specified byinnodb_log_files_in_group. If you do not specify anyInnoDBlog variables, the default is to create two files namedib_logfile0andib_logfile1in the MySQL data directory. Their size is given by the size of theinnodb_log_file_sizesystem variable. -
Introduced 5.6.3 Command-Line Format --innodb_lru_scan_depth=#System Variable Name innodb_lru_scan_depthVariable Scope Global Dynamic Variable Yes Permitted Values (32-bit platforms) Type integerDefault 1024Min Value 100Max Value 2**32-1Permitted Values (64-bit platforms) Type integerDefault 1024Min Value 100Max Value 2**64-1A parameter that influences the algorithms and heuristics for the flush operation for the
InnoDBbuffer 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 list the page cleaner thread scans looking for dirty pages to flush. This is a background operation performed once a 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 always maintaining some free pages. Also, consider adjustinginnodb_lru_scan_depthwhen changing the number of buffer pool instances, sinceinnodb_lru_scan_depth*innodb_buffer_pool_instancesdefines the amount of work performed by the page cleaner thread each second.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 Name innodb_max_dirty_pages_pctVariable Scope Global Dynamic Variable Yes Permitted Values Type numericDefault 75Min Value 0Max Value 99InnoDBtries 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_pctsetting 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.6.3.5, “Configuring InnoDB Buffer Pool Flushing”.For additional information about this variable, see Section 14.6.3.6, “Fine-tuning InnoDB Buffer Pool Flushing”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.
innodb_max_dirty_pages_pct_lwmIntroduced 5.6.6 Command-Line Format --innodb_max_dirty_pages_pct_lwm=#System Variable Name innodb_max_dirty_pages_pct_lwmVariable Scope Global Dynamic Variable Yes Permitted Values Type numericDefault 0Min Value 0Max Value 99Low water mark representing percentage of dirty pages where preflushing is enabled to control the dirty page ratio. The default of 0 disables the pre-flushing behavior entirely. For additional information about this variable, see Section 14.6.3.6, “Fine-tuning InnoDB Buffer Pool Flushing”.
-
Command-Line Format --innodb_max_purge_lag=#System Variable Name innodb_max_purge_lagVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 0Min Value 0Max Value 4294967295This variable controls how to delay
INSERT,UPDATE, andDELETEoperations when purge operations are lagging (see Section 14.3, “InnoDB Multi-Versioning”). The default value is 0 (no delays).The
InnoDBtransaction system maintains a list of transactions that have index records delete-marked byUPDATEorDELETEoperations. The length of this list represents thepurge_lagvalue. Whenpurge_lagexceedsinnodb_max_purge_lag, eachINSERT,UPDATE, andDELETEoperation is delayed.To prevent excessive delays in extreme situations where
purge_lagbecomes huge, you can put a cap on the amount of delay by setting theinnodb_max_purge_lag_delayconfiguration option. The delay is computed at the beginning of a purge batch.A typical setting for a problematic workload might be 1 million, assuming that transactions are small, only 100 bytes in size, and it is permissible to have 100MB of unpurged
InnoDBtable rows.The lag value is displayed as the history list length in the
TRANSACTIONSsection of InnoDB Monitor output. For example, if the output includes the following lines, the lag value is 20:------------ TRANSACTIONS ------------ Trx id counter 0 290328385 Purge done for trx's n:o < 0 290315608 undo n:o < 0 17 History list length 20
For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.
-
Introduced 5.6.5 Command-Line Format --innodb_max_purge_lag_delay=#System Variable Name innodb_max_purge_lag_delayVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 0Min Value 0Specifies the maximum delay in milliseconds for the delay imposed by the
innodb_max_purge_lagconfiguration option. Any non-zero value represents an upper limit on the delay period computed from the formula based on the value ofinnodb_max_purge_lag. The default of zero means that there is no upper limit imposed on the delay interval.For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.
Has no effect. This variable is deprecated as of MySQL 5.6.11 and will be removed in a future MySQL release.
-
Introduced 5.6.2 Command-Line Format --innodb_monitor_disable=[counter|module|pattern|all]System Variable Name innodb_monitor_disableVariable Scope Global Dynamic Variable Yes Permitted Values Type stringTurns off one or more counters in the
INFORMATION_SCHEMA.INNODB_METRICStable. For usage information, see Section 21.29.19, “The INFORMATION_SCHEMA INNODB_METRICS Table”. -
Introduced 5.6.2 Command-Line Format --innodb_monitor_enable=[counter|module|pattern|all]System Variable Name innodb_monitor_enableVariable Scope Global Dynamic Variable Yes Permitted Values Type stringTurns on one or more counters in the
INFORMATION_SCHEMA.INNODB_METRICStable. For usage information, see Section 21.29.19, “The INFORMATION_SCHEMA INNODB_METRICS Table”. -
Introduced 5.6.2 Command-Line Format --innodb_monitor_reset=[counter|module|pattern|all]System Variable Name innodb_monitor_resetVariable Scope Global Dynamic Variable Yes Permitted Values Type stringResets to zero the count value for one or more counters in the
INFORMATION_SCHEMA.INNODB_METRICStable. For usage information, see Section 21.29.19, “The INFORMATION_SCHEMA INNODB_METRICS Table”. -
Introduced 5.6.2 Command-Line Format --innodb_monitor_reset_all=[counter|module|pattern|all]System Variable Name innodb_monitor_reset_allVariable Scope Global Dynamic Variable Yes Permitted Values Type stringResets all values (minimum, maximum, and so on) for one or more counters in the
INFORMATION_SCHEMA.INNODB_METRICStable. For usage information, see Section 21.29.19, “The INFORMATION_SCHEMA INNODB_METRICS Table”. -
Introduced 5.6.27 Command-Line Format --innodb_numa_interleave=#System Variable Name innodb_numa_interleaveVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault OFFEnables the NUMA interleave memory policy for allocation of the
InnoDBbuffer pool. Wheninnodb_numa_interleaveis enabled, the NUMA memory policy is set toMPOL_INTERLEAVEfor the mysqld process. After theInnoDBbuffer pool is allocated, the NUMA memory policy is set back toMPOL_DEFAULT. For theinnodb_numa_interleaveoption to be available, MySQL must be compiled on a NUMA-enabled system. -
Command-Line Format --innodb_old_blocks_pct=#System Variable Name innodb_old_blocks_pctVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 37Min Value 5Max Value 95Specifies the approximate percentage of the
InnoDBbuffer 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. See Section 14.6.3.3, “Making the Buffer Pool Scan Resistant” for more information. See Section 14.6.3.1, “The InnoDB Buffer Pool” for information about buffer pool management, such as the LRU algorithm and eviction policies. -
Command-Line Format --innodb_old_blocks_time=#System Variable Name innodb_old_blocks_timeVariable Scope Global Dynamic Variable Yes Permitted Values (<= 5.6.5) Type integerDefault 0Min Value 0Max Value 2**32-1Permitted Values (>= 5.6.6) Type integerDefault 1000Min Value 0Max Value 2**32-1Non-zero values protect against the buffer pool being filled up 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 (ms) 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 ms after the first access. For example, a value of 1000 causes blocks to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist.
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. See Section 14.6.3.3, “Making the Buffer Pool Scan Resistant” for more information. See Section 14.6.3.1, “The InnoDB Buffer Pool” for information about buffer pool management, such as the LRU algorithm and eviction policies. innodb_online_alter_log_max_sizeIntroduced 5.6.6 Command-Line Format --innodb_online_alter_log_max_size=#System Variable Name innodb_online_alter_log_max_sizeVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 134217728Min Value 65536Max Value 2**64-1Specifies an upper limit on the size of the temporary log files used during online DDL operations for
InnoDBtables. 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 any temporary log file exceeds the upper size limit, theALTER TABLEoperation 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 causes a longer period 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 Name innodb_open_filesVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type integerDefault 300Min Value 10Max Value 4294967295Permitted Values (>= 5.6.6) Type integerDefault -1 (autosized)Min Value 10Max Value 4294967295This variable is relevant only if you use multiple
InnoDBtablespaces. It specifies the maximum number of.ibdfiles that MySQL can keep open at one time. The minimum value is 10. As of MySQL 5.6.6, the default value is 300 ifinnodb_file_per_tableis not enabled, and the higher of 300 andtable_open_cacheotherwise. Before 5.6.6, the default value is 300.The file descriptors used for
.ibdfiles are forInnoDBtables only. They are independent of those specified by the--open-files-limitserver option, and do not affect the operation of the table cache. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”. -
Introduced 5.6.4 Command-Line Format --innodb_optimize_fulltext_only=#System Variable Name innodb_optimize_fulltext_onlyVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault OFFChanges the way the
OPTIMIZE TABLEstatement operates onInnoDBtables. Intended to be enabled temporarily, during maintenance operations forInnoDBtables withFULLTEXTindexes.By default,
OPTIMIZE TABLEreorganizes the data in the clustered index of the table. When this option is enabled,OPTIMIZE TABLEskips this reorganization of the table data, and instead processes the newly added, deleted, and updated token data for aFULLTEXTindex, See Section 14.8.10, “InnoDB FULLTEXT Indexes” for more information aboutFULLTEXTindexes forInnoDBtables. -
Introduced 5.6.4 Command-Line Format --innodb_page_size=#kSystem Variable Name innodb_page_sizeVariable Scope Global Dynamic Variable No Permitted Values (>= 5.6.4) Type enumerationDefault 16384Valid Values 4k8k16k4096819216384Specifies the page size for all
InnoDBtablespaces in a MySQL instance. You can specify page size using the values16k(the default),8k, or4k. Alternatively, you can specify page size in bytes (4096, 8192, 16384).Importantinnodb_page_sizecan 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.The default
16kpage 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 a single page contains many rows. Smaller pages might also be efficient with SSD storage devices, which typically use small block sizes. Keeping theInnoDBpage 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”. -
Introduced 5.6.2 Command-Line Format --innodb_print_all_deadlocks=#System Variable Name innodb_print_all_deadlocksVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault OFFWhen this option is enabled, information about all deadlocks in
InnoDBuser transactions is recorded in themysqlderror log. Otherwise, you see information about only the last deadlock, using theSHOW ENGINE INNODB STATUScommand. An occasionalInnoDBdeadlock is not necessarily an issue, becauseInnoDBdetects the condition immediately, and rolls back one of the transactions automatically. You might use this option to troubleshoot why deadlocks are happening 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 UPDATEstatements for multiple tables, so that each transaction accesses the tables in the same order, thus avoiding the deadlock condition. -
Command-Line Format --innodb_purge_batch_size=#System Variable Name innodb_purge_batch_sizeVariable Scope Global Dynamic Variable Yes Permitted Values (<= 5.6.2) Type integerDefault 20Min Value 1Max Value 5000Permitted Values (>= 5.6.3) Type integerDefault 300Min Value 1Max Value 5000Defines the number of undo log pages that purge parses and processes in one batch from the history list. In a multi-threaded purge configuration, the coordinator purge thread divides
innodb_purge_batch_sizebyinnodb_purge_threadsand assigns that number of pages to each purge thread. Theinnodb_purge_batch_sizeoption also defines the number of undo log pages that purge frees after every 128 iterations through the undo logs.The
innodb_purge_batch_sizeoption is intended for advanced performance tuning in combination with theinnodb_purge_threadssetting. Most MySQL users need not changeinnodb_purge_batch_sizefrom its default value. -
Command-Line Format --innodb_purge_threads=#System Variable Name innodb_purge_threadsVariable Scope Global Dynamic Variable No Permitted Values (<= 5.6.1) Type integerDefault 0Min Value 0Max Value 1Permitted Values (>= 5.6.2, <= 5.6.4) Type integerDefault 0Min Value 0Max Value 32Permitted Values (>= 5.6.5) Type integerDefault 1Min Value 1Max Value 32The number of background threads devoted to the
InnoDBpurge operation. The default and minimum value of 1 signifies that the purge operation is always performed by a background thread, never as part of the master thread. Running the purge operation in one or more background threads helps reduce internal contention withinInnoDB, improving scalability. Increasing the value to greater than 1 creates that many separate purge threads, which can improve efficiency on systems where DML operations are performed on multiple tables. The maximum is 32. -
Introduced 5.6.3 Command-Line Format --innodb_random_read_ahead=#System Variable Name innodb_random_read_aheadVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault OFFEnables the random read-ahead technique for optimizing
InnoDBI/O. Random read-ahead functionality was removed from theInnoDB Plugin(version 1.0.4) and was therefore not included in MySQL 5.5.0 whenInnoDB Pluginbecame the “built-in” version ofInnoDB. Random read-ahead was reintroduced in MySQL 5.1.59 and 5.5.16 and higher along with theinnodb_random_read_aheadconfiguration option, which is disabled by default.See Section 14.6.3.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)” for details about the performance considerations for the different types of read-ahead requests. 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 Name innodb_read_ahead_thresholdVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 56Min Value 0Max Value 64Controls the sensitivity of linear read-ahead that
InnoDBuses to prefetch pages into the buffer pool. IfInnoDBreads at leastinnodb_read_ahead_thresholdpages 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,InnoDBmust 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 this read-ahead mechanism, and how many of them are evicted from the buffer pool without ever being accessed, can be useful to help fine-tune the
innodb_read_ahead_thresholdparameter. As of MySQL 5.5,SHOW ENGINE INNODB STATUSoutput displays counter information from theInnodb_buffer_pool_read_aheadandInnodb_buffer_pool_read_ahead_evictedglobal status variables. These variables indicate 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. These counters provide global values since the last server restart.SHOW ENGINE INNODB STATUSalso shows the rate at which the read-ahead pages are read in 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 STATUSand are displayed in theBUFFER POOL AND MEMORYsection of the output.See Section 14.6.3.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)” for more information. 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 Name innodb_read_io_threadsVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 4Min Value 1Max Value 64The number of I/O threads for read operations in
InnoDB. The default value is 4. Its counterpart for write threads isinnodb_write_io_threads. See Section 14.6.7, “Configuring the Number of Background InnoDB I/O Threads” for more information. 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-nrsetting can exceed system limits. Ideally, increase theaio-max-nrsetting; as a workaround, you might reduce the settings for one or both of the MySQL configuration options. -
Introduced 5.6.7 Command-Line Format --innodb_read_only=#System Variable Name innodb_read_onlyVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault OFFStarts
InnoDBin 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. See Section 14.6.2, “Configuring InnoDB for Read-Only Operation” for usage instructions. -
Command-Line Format --innodb_replication_delay=#System Variable Name innodb_replication_delayVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 0Min Value 0Max Value 4294967295The replication thread delay (in ms) on a slave server if
innodb_thread_concurrencyis reached. -
Command-Line Format --innodb_rollback_on_timeoutSystem Variable Name innodb_rollback_on_timeoutVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault OFFIn MySQL 5.6,
InnoDBrolls back only the last statement on a transaction timeout by default. If--innodb_rollback_on_timeoutis specified, a transaction timeout causesInnoDBto abort and roll back the entire transaction (the same behavior as in MySQL 4.1). -
Introduced 5.6.2 Command-Line Format --innodb_rollback_segments=#System Variable Name innodb_rollback_segmentsVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 128Min Value 1Max Value 128Defines how many of the rollback segments in the system tablespace are used for InnoDB transactions. This setting, while still valid, is replaced by
innodb_undo_logs. innodb_saved_page_number_debugIntroduced 5.6.17 Command-Line Format --innodb_saved_page_number_debug=#System Variable Name innodb_saved_page_number_debugVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 0Max Value 2**23-1Saves a page number. Setting the
innodb_fil_make_page_dirty_debugoption dirties the page defined byinnodb_saved_page_number_debug. Theinnodb_saved_page_number_debugoption is only available if debugging support is compiled in using theWITH_DEBUGCMake option.-
Introduced 5.6.4 Command-Line Format --innodb_sort_buffer_size=#System Variable Name innodb_sort_buffer_sizeVariable Scope Global Dynamic Variable No Permitted Values (5.6.4) Type integerDefault 1048576Min Value 524288Max Value 67108864Permitted Values (>= 5.6.5) Type integerDefault 1048576Min Value 65536Max Value 67108864Specifies the size of sort buffers used for sorting data during creation of an
InnoDBindex. The size specified defines the amount of data filled in memory for an internal sort and written out to disk, which can be referred to as a “run”. During the merge phase, pairs of buffers of the specified size are “read in” and merged. The larger the setting, the fewer “runs” and merges there are, which is important to understand from a tuning perspective.This sort area is only used for merge sorts during index creation, not during later index maintenance operations. Buffers are deallocated when index creation completes.
The value of this option also controls the amount by which the temporary log file is extended, to record concurrent DML during online DDL operations.
Before this setting was made configurable, the size was hardcoded to 1048576 bytes (1MB), and that value remains the default.
During an
ALTER TABLEorCREATE TABLEstatement that creates an index, 3 buffers are allocated, each with a size defined by this option. Additionally, auxiliary pointers are allocated to rows in the sort buffer so that the sort can run on pointers (as opposed to moving rows during the sort operation).For a typical sort operation, a formula such as this can be used to estimate memory consumption:
(6 /*FTS_NUM_AUX_INDEX*/ * (3*@@global.innodb_sort_buffer_size) + 2 * number_of_partitions * number_of_secondary_indexes_created * (@@global.innodb_sort_buffer_size/dict_index_get_min_size(index)*/) * 8 /*64-bit sizeof *buf->tuples*/")
“
@@global.innodb_sort_buffer_size/dict_index_get_min_size(index)” indicates the maximum tuples held. “2 * (@@global.innodb_sort_buffer_size/*dict_index_get_min_size(index)*/) * 8 /*64-bit size of *buf->tuples*/” indicates auxiliary pointers allocated.NoteFor 32-bit, multiply by 4 instead of 8.
For parallel sorts on a full-text index, multiply by the
innodb_ft_sort_pll_degreesetting:(6 /*FTS_NUM_AUX_INDEX*/ * @@global.innodb_ft_sort_pll_degree)
-
Command-Line Format --innodb_spin_wait_delay=#System Variable Name innodb_spin_wait_delayVariable Scope Global Dynamic Variable Yes Permitted Values (32-bit platforms) Type integerDefault 6Min Value 0Max Value 2**32-1Permitted Values (64-bit platforms) Type integerDefault 6Min Value 0Max Value 2**64-1The 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. The default value is 6. See Section 14.6.9, “Configuring Spin Lock Polling” for more information.
-
Introduced 5.6.6 Command-Line Format --innodb_stats_auto_recalc=#System Variable Name innodb_stats_auto_recalcVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault ONCauses
InnoDBto automatically recalculate persistent statistics after the data in a table is changed substantially. The threshold value is currently 10% of the rows in the table. This setting applies to tables created when theinnodb_stats_persistentoption is enabled, or where the clauseSTATS_PERSISTENT=1is enabled by aCREATE TABLEorALTER TABLEstatement. The amount of data sampled to produce the statistics is controlled by theinnodb_stats_persistent_sample_pagesconfiguration option.For additional information about
innodb_stats_auto_recalc, see Section 14.6.11.1, “Configuring Persistent Optimizer Statistics Parameters”. innodb_stats_include_delete_markedIntroduced 5.6.34 Command-Line Format --innodb_stats_include_delete_marked=#System Variable Name innodb_stats_include_delete_markedVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault OFFBy default,
InnoDBreads uncommitted data when calculating statistics. In the case of an uncommitted transaction that deletes rows from a table,InnoDBexcludes 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_markedcan be enabled to ensure thatInnoDBincludes delete-marked records when calculating persistent optimizer statistics.When
innodb_stats_include_delete_markedis enabled,ANALYZE TABLEconsiders delete-marked records when recalculating statistics.innodb_stats_include_delete_markedis a global setting that affects allInnoDBtables, and it is only applicable to persistent optimizer statistics.For related information, see Section 14.6.11.1, “Configuring Persistent Optimizer Statistics Parameters”.
-
Introduced 5.6.2 Command-Line Format --innodb_stats_method=nameSystem Variable Name innodb_stats_methodVariable Scope Global Dynamic Variable Yes Permitted Values Type enumerationDefault nulls_equalValid Values nulls_equalnulls_unequalnulls_ignoredHow the server treats
NULLvalues when collecting statistics about the distribution of index values forInnoDBtables. This variable has three possible values,nulls_equal,nulls_unequal, andnulls_ignored. Fornulls_equal, allNULLindex values are considered equal and form a single value group that has a size equal to the number ofNULLvalues. Fornulls_unequal,NULLvalues are considered unequal, and eachNULLforms a distinct value group of size 1. Fornulls_ignored,NULLvalues are ignored.The method that is used for generating 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_metadataSystem Variable Name innodb_stats_on_metadataVariable Scope Global Dynamic Variable Yes Permitted Values (<= 5.6.5) Type booleanDefault ONPermitted Values (>= 5.6.6) Type booleanDefault OFFWhen this variable is enabled,
InnoDBupdates statistics when metadata statements such asSHOW TABLE STATUSorSHOW INDEXare run, or when accessing theINFORMATION_SCHEMA.TABLESorINFORMATION_SCHEMA.STATISTICStables. (These updates are similar to what happens forANALYZE TABLE.) When disabled,InnoDBdoes not update statistics during these operations. Leaving this 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 involveInnoDBtables.To change the setting, issue the statement
SET GLOBAL innodb_stats_on_metadata=, wheremodemodeONorOFF(or1or0). Changing this setting requires theSUPERprivilege and immediately affects the operation of all connections.This variable is disabled by default as of MySQL 5.6.6, enabled before that.
-
Introduced 5.6.6 Command-Line Format --innodb_stats_persistent=settingSystem Variable Name innodb_stats_persistentVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault ONValid Values OFFON01Specifies whether
InnoDBindex 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_persistentat the global level before creating a table, or use theSTATS_PERSISTENTclause of theCREATE TABLEandALTER TABLEstatements to override the system-wide setting and configure persistent statistics for individual tables.For more information about this option, see Section 14.6.11.1, “Configuring Persistent Optimizer Statistics Parameters”.
innodb_stats_persistent_sample_pagesIntroduced 5.6.2 Command-Line Format --innodb_stats_persistent_sample_pages=#System Variable Name innodb_stats_persistent_sample_pagesVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 20The 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 TABLEfor anInnoDBtable. For additional information, see Section 14.6.11.1, “Configuring Persistent Optimizer Statistics Parameters”.NoteSetting a high value for
innodb_stats_persistent_sample_pagescould result in lengthyANALYZE TABLEexecution time. To estimate the number of database pages that will be accessed, see Section 14.6.11.3, “Estimating ANALYZE TABLE Complexity for InnoDB Tables”.This option only applies when the
innodb_stats_persistentsetting is turned on for a table; when that option is turned off for a table, theinnodb_stats_transient_sample_pagessetting applies instead.-
Deprecated 5.6.3 Command-Line Format --innodb_stats_sample_pages=#System Variable Name innodb_stats_sample_pagesVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 8Min Value 1Max Value 2**64-1Deprecated, use
innodb_stats_transient_sample_pagesinstead. innodb_stats_transient_sample_pagesIntroduced 5.6.2 Command-Line Format --innodb_stats_transient_sample_pages=#System Variable Name innodb_stats_transient_sample_pagesVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 8The 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 anInnoDBtable or recalculating statistics. For additional information, see Section 14.6.11.2, “Configuring Non-Persistent Optimizer Statistics Parameters”.NoteSetting a high value for
innodb_stats_transient_sample_pagescould result in lengthyANALYZE TABLEexecution time. To estimate the number of database pages that will be accessed, see Section 14.6.11.3, “Estimating ANALYZE TABLE Complexity for InnoDB Tables”.This option only applies when the
innodb_stats_persistentsetting is turned off for a table; when this option is turned on for a table, theinnodb_stats_persistent_sample_pagessetting applies instead. Takes the place of theinnodb_stats_sample_pagesoption.-
Introduced 5.6.16 Command-Line Format --innodb_status_outputSystem Variable Name innodb_status_outputVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault OFFEnables or disables periodic output for the standard
InnoDBMonitor. Also used in combination withinnodb_status_output_locksto enable or disable periodic output for theInnoDBLock Monitor. See Section 14.17, “InnoDB Monitors” for additional information. -
Introduced 5.6.16 Command-Line Format --innodb_status_output_locksSystem Variable Name innodb_status_output_locksVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault OFFEnables or disables the
InnoDBLock Monitor. When enabled, theInnoDBLock Monitor prints additional information about locks inSHOW ENGINE INNODB STATUSoutput and in periodic output printed to the MySQL error log. Periodic output for theInnoDBLock Monitor is printed as part of the standardInnoDBMonitor output. The standardInnoDBMonitor must therefore be enabled for theInnoDBLock Monitor to print data to the MySQL error log periodically. See Section 14.17, “InnoDB Monitors” for more information. -
Command-Line Format --innodb_strict_mode=#System Variable Name innodb_strict_modeVariable Scope Global, Session Dynamic Variable Yes Permitted Values Type booleanDefault OFFWhen
innodb_strict_modeisON,InnoDBreturns errors rather than warnings for certain conditions. The default value isOFF.Strict mode helps guard against ignored typos and syntax errors in SQL, or other unintended consequences of various combinations of operational modes and SQL statements. When
innodb_strict_modeisON,InnoDBraises error conditions in certain cases, rather than issuing a warning and processing the specified statement (perhaps with unintended behavior). This is analogous tosql_modein MySQL, which controls what SQL syntax MySQL accepts, and determines whether it silently ignores errors, or validates input syntax and data values.The
innodb_strict_modesetting affects the handling of syntax errors forCREATE TABLE,ALTER TABLEandCREATE INDEXstatements.innodb_strict_modealso enables a record size check, so that anINSERTorUPDATEnever fails due to the record being too large for the selected page size.Oracle recommends enabling
innodb_strict_modewhen usingROW_FORMATandKEY_BLOCK_SIZEclauses onCREATE TABLE,ALTER TABLE, andCREATE INDEXstatements. Wheninnodb_strict_modeisOFF,InnoDBignores conflicting clauses and creates the table or index, with only a warning in the message log. The resulting table might have different behavior than you intended, such as having no compression when you tried to create a compressed table. Wheninnodb_strict_modeisON, such problems generate an immediate error and the table or index is not created, avoiding a troubleshooting session later.You can turn
innodb_strict_modeONorOFFon the command line when you startmysqld, or in the configuration filemy.cnformy.ini. You can also enable or disableinnodb_strict_modeat runtime with the statementSET [GLOBAL|SESSION] innodb_strict_mode=, wheremodemodeONorOFF. Changing theGLOBALsetting requires theSUPERprivilege and affects the operation of all clients that subsequently connect. Any client can change theSESSIONsetting forinnodb_strict_mode, and the setting affects only that client. -
Command-Line Format --innodb_support_xaSystem Variable Name innodb_support_xaVariable Scope Global, Session Dynamic Variable Yes Permitted Values Type booleanDefault TRUEEnables
InnoDBsupport for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation. This setting is the default. The XA mechanism is used internally and is essential for any server that has its binary log turned on and is accepting changes to its data from more than one thread. If you turn it off, transactions can be written to the binary log in a different order from the one in which the live database is committing them. This can produce different data when the binary log is replayed in disaster recovery or on a replication slave. Do not turn it off on a replication master server unless you have an unusual setup where only one thread is able to change data.For a server that is accepting data changes from only one thread, it is safe and recommended to turn off this option to improve performance for
InnoDBtables. For example, you can turn it off on replication slaves where only the replication SQL thread is changing data.You can also turn off this option if you do not need it for safe binary logging or replication, and you also do not use an external XA transaction manager.
-
Introduced 5.6.3 Command-Line Format --innodb_sync_array_size=#System Variable Name innodb_sync_array_sizeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 1Min Value 1Max Value 1024Splits an 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 this option 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 Name innodb_sync_spin_loopsVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 30Min Value 0Max Value 4294967295The number of times a thread waits for an
InnoDBmutex to be freed before the thread is suspended. The default value is 30. -
Command-Line Format --innodb_table_locksSystem Variable Name innodb_table_locksVariable Scope Global, Session Dynamic Variable Yes Permitted Values Type booleanDefault TRUEIf
autocommit = 0,InnoDBhonorsLOCK TABLES; MySQL does not return fromLOCK TABLES ... WRITEuntil all other threads have released all their locks to the table. The default value ofinnodb_table_locksis 1, which means thatLOCK TABLEScauses InnoDB to lock a table internally ifautocommit = 0.In MySQL 5.6,
innodb_table_locks = 0has no effect for tables locked explicitly withLOCK TABLES ... WRITE. It does have an effect for tables locked for read or write byLOCK TABLES ... WRITEimplicitly (for example, through triggers) or byLOCK TABLES ... READ. -
Introduced 5.6.29 Command-Line Format --innodb_tmpdir=pathSystem Variable Name innodb_tmpdirVariable Scope Global, Session Dynamic Variable Yes Permitted Values Type directory nameDefault NULLThe
innodb_tmpdiroption is used to define an alternate directory for temporary sort files created during onlineALTER TABLEoperations that rebuild the table.Online
ALTER TABLEoperations that rebuild a table also create an intermediate table file in the same directory as the original table. Theinnodb_tmpdiroption 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 (
$TMPDIRon Unix,%TEMP%on Windows, or the directory specified by the--tmpdirconfiguration option). If aninnodb_tmpdirdirectory is specified, existence of the directory and permissions are only checked wheninnodb_tmpdiris configured using aSETstatement. 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 TABLEoperation reports an error ifinnodb_tmpdiris set to an invalid directory.innodb_tmpdiroverrides the MySQLtmpdirsetting but only for onlineALTER TABLEoperations.The
FILEprivilege is required to configureinnodb_tmpdir.The
innodb_tmpdiroption was introduced to help avoid overflowing a temporary file directory located on atmpfsfile system. Such overflows could occur as a result of large temporary sort files created during onlineALTER TABLEoperations that rebuild the table.In replication environments, only consider replicating an
innodb_tmpdirsetting if all servers have the same operating system environment. Otherwise, replicating aninnodb_tmpdirsetting could result in a replication failure when running onlineALTER TABLEoperations that rebuild the table. If server operating environments differ, it is recommended that you configureinnodb_tmpdiron each server individually.For related information, see Where InnoDB Stores Temporary Files.
-
Command-Line Format --innodb_thread_concurrency=#System Variable Name innodb_thread_concurrencyVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 0Min Value 0Max Value 1000InnoDBtries to keep the number of operating system threads concurrently insideInnoDBless than or equal to the limit given by this variable (InnoDBuses operating system threads to process user transactions). Once the number of threads reaches this limit, additional threads are placed into a wait state within a “First In, First Out” (FIFO) queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads.The range of this variable is 0 to 1000. A value of 0 (the default) is interpreted as infinite concurrency (no concurrency checking). Disabling thread concurrency checking enables
InnoDBto create as many threads as it needs. A value of 0 also disables thequeries inside InnoDBandqueries in queue countersin theROW OPERATIONSsection ofSHOW ENGINE INNODB STATUSoutput.Consider setting this variable if your MySQL instance shares CPU resources with other applications, or if your workload or number of concurrent users is growing. The correct setting depends on workload, computing environment, and the version of MySQL that you are running. You will need to test a range of values to determine the setting that provides the best performance.
innodb_thread_concurrencyis a dynamic variable, which allows you to experiment with different settings on a live test system. If a particular setting performs poorly, you can quickly setinnodb_thread_concurrencyback to 0.Use the following guidelines to help find and maintain an appropriate setting:
If the number of concurrent user threads for a workload is less than 64, set
innodb_thread_concurrency=0.If your workload is consistently heavy or occasionally spikes, start by setting
innodb_thread_concurrency=128, and lowering the value to 96, 80, 64, and so on, until you find the number of threads that provides the best performance. For example, suppose your system typically has 40 to 50 users, but periodically the number increases to 60, 70, or even 200. You find that performance is stable at 80 concurrent users but starts to show a regression above this number. In this case, you would setinnodb_thread_concurrency=80to avoid impacting performance.If you do not want
InnoDBto use more than a certain number of vCPUs for user threads (20 vCPUs for example), setinnodb_thread_concurrencyto this number (or possibly lower, depending on performance results). If your goal is to isolate MySQL from other applications, you may consider binding themysqldprocess exclusively to the vCPUs. Be aware, however, that exclusive binding could result in non-optimal hardware usage if themysqldprocess is not consistently busy. In this case, you might bind themysqldprocess to the vCPUs but also allow other applications to use some or all of the vCPUs.NoteFrom an operating system perspective, using a resource management solution (if available) to manage how CPU time is shared among applications may be preferable to binding the
mysqldprocess. For example, you could assign 90% of vCPU time to a given application while other critical process are not running, and scale that value back to 40% when other critical processes are running.innodb_thread_concurrencyvalues that are too high can cause performance regression due to increased contention on system internals and resources.In some cases, the optimal
innodb_thread_concurrencysetting can be smaller than the number of vCPUs.Monitor and analyze your system regularly. Changes to workload, number of users, or computing environment may require that you adjust the
innodb_thread_concurrencysetting.
For related information, see Section 14.6.6, “Configuring Thread Concurrency for InnoDB”.
-
Command-Line Format --innodb_thread_sleep_delay=#System Variable Name innodb_thread_sleep_delayVariable Scope Global Dynamic Variable Yes Permitted Values (>= 5.6.17) Type integerDefault 10000Min Value 0Max Value 1000000Permitted Values (32-bit platforms, <= 5.6.16) Type integerDefault 10000Min Value 0Max Value 4294967295Permitted Values (64-bit platforms, <= 5.6.16) Type integerDefault 10000Min Value 0Max Value 18446744073709551615How long
InnoDBthreads sleep before joining theInnoDBqueue, in microseconds. The default value is 10000. A value of 0 disables sleep. In MySQL 5.6.3 and higher, you can set the configuration optioninnodb_adaptive_max_sleep_delayto the highest value you would allow forinnodb_thread_sleep_delay, and InnoDB automatically adjustsinnodb_thread_sleep_delayup or down depending on the current thread-scheduling activity. This dynamic adjustment helps the thread scheduling mechanism to work smoothly during times when the system is lightly loaded and when it is operating near full capacity.For more information, see Section 14.6.6, “Configuring Thread Concurrency for InnoDB”.
innodb_trx_purge_view_update_only_debugCommand-Line Format --innodb_trx_purge_view_update_only_debug=#System Variable Name innodb_trx_purge_view_update_only_debugVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault OFFPauses 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_DEBUGCMake option.-
Command-Line Format --innodb_trx_rseg_n_slots_debug=#System Variable Name innodb_trx_rseg_n_slots_debugVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 0Max Value 1024Sets a debug flag that limits
TRX_RSEG_N_SLOTSto a given value for thetrx_rsegf_undo_find_freefunction which looks for a free slot for an undo log segment. This option is only available if debugging support is compiled in using theWITH_DEBUGCMake option. -
Introduced 5.6.3 Command-Line Format --innodb_undo_directory=dir_nameSystem Variable Name innodb_undo_directoryVariable Scope Global Dynamic Variable No Permitted Values Type directory nameDefault .The relative or absolute directory path where
InnoDBcreates separate tablespaces for the undo logs. Typically used to place those logs on a different storage device. Used in conjunction withinnodb_undo_logsandinnodb_undo_tablespaces, which determine the disk layout of the undo logs outside the system tablespace. The default value of “.” represents the same directory whereInnoDBcreates 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 about configuring separate tablespaces for undo logs, see Section 14.7.7, “Storing InnoDB Undo Logs in Separate Tablespaces”.
-
Introduced 5.6.3 Command-Line Format --innodb_undo_logs=#System Variable Name innodb_undo_logsVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 128Min Value 0Max Value 128Defines the number of undo logs (otherwise referred to as rollback segments) used by
InnoDBfor data-modifying transactions that generate undo records. Each undo log can host up to a maximum of 1024 transactions.This setting is appropriate for tuning performance if you observe mutex contention related to the undo logs. The
innodb_undo_logsoption replacesinnodb_rollback_segments. For the total number of available undo logs, rather than the number of active ones, see theInnodb_available_undo_logsstatus variable.Although you can increase or decrease the number of undo logs used by
InnoDB, the number of undo logs physically present in the system never decreases. Thus you might start with a low value for this parameter and gradually increase it, to avoid allocating undo logs that are not required. Ifinnodb_undo_logsis not set, it defaults to the maximum value of 128.For more information about undo logs, see Section 14.3, “InnoDB Multi-Versioning”. For information about configuring separate tablespaces for undo logs, see Section 14.7.7, “Storing InnoDB Undo Logs in Separate Tablespaces”.
-
Introduced 5.6.3 Command-Line Format --innodb_undo_tablespaces=#System Variable Name innodb_undo_tablespacesVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 0Min Value 0Max Value 126The number of tablespace files that undo logs are divided between when you use a non-zero
innodb_undo_logssetting. By default, all undo logs are part of the system tablespace, and the system tablespace always contains one undo tablespace in addition to those configured byinnodb_undo_tablespaces.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, whereNNis a sequential series of integers (including leading zeros). The default size of an undo tablespace file is 10M.Importantinnodb_undo_tablespacescan 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 (0). Attempting to restartInnoDBwith a greater number of undo tablespaces than specified when the MySQL instance was initialized results in a startup failure and an error stating thatInnoDBdid not find the expected number of undo tablespaces.For more information about configuring separate tablespaces for undo logs, see Section 14.7.7, “Storing InnoDB Undo Logs in Separate Tablespaces”.
-
Command-Line Format --innodb_use_native_aio=#System Variable Name innodb_use_native_aioVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault ONSpecifies 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 touch this option, because it is enabled by default.
As of MySQL 5.5, the asynchronous I/O capability that
InnoDBhas 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 in the output of the commandSHOW ENGINE INNODB STATUS\G.Running with a large number of
InnoDBI/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
InnoDBfrom starting, you can start the server withinnodb_use_native_aio=0disabled (useinnodb_use_native_aio=0in the option file). This option may also be turned off automatically during startup ifInnoDBdetects a potential problem such as a combination oftmpdirlocation,tmpfsfilesystem, and Linux kernel that does not support AIO ontmpfs. -
Deprecated 5.6.3 Command-Line Format --innodb_use_sys_malloc=#System Variable Name innodb_use_sys_mallocVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault ONWhether
InnoDBuses the operating system memory allocator (ON) or its own (OFF). The default value isON. See Section 14.6.4, “Configuring the Memory Allocator for InnoDB” for more information.As of MySQL 5.6.3,
innodb_use_sys_mallocis deprecated and will be removed in a future MySQL release. The
InnoDBversion number. Starting in 5.6.11, the separate numbering forInnoDBis discontinued and this value is the same as for theversionvariable.-
Command-Line Format --innodb_write_io_threads=#System Variable Name innodb_write_io_threadsVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 4Min Value 1Max Value 64The number of I/O threads for write operations in
InnoDB. The default value is 4. Its counterpart for read threads isinnodb_read_io_threads. See Section 14.6.7, “Configuring the Number of Background InnoDB I/O Threads” for more information. 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-nrsetting can exceed system limits. Ideally, increase theaio-max-nrsetting; as a workaround, you might reduce the settings for one or both of the MySQL configuration options.
You should 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”.
* This limit varies in different kernels.
http://krow.livejournal.com/542306.html
This is particularly of importance when performing a file based sync to setup replication. If you have a different (or no) innodb_log_file_size setting at the slave, you will be puzzled for hours (I was).
[*] 2009/10 Initialising a 10 GB buffer pool takes 6 seconds, larger configurations may take proportionally longer.
http://www.tocker.ca/2013/09/17/what-to-tune-in-mysql-56-after-installation.html