MySQL 5.1 Release Notes
This section describes the InnoDB-related
command options and system variables.
System variables that are true or false can be enabled at server startup by naming them, or disabled by using a
--skip-prefix. For example, to enable or disableInnoDBchecksums, you can use--innodb_checksumsor--skip-innodb_checksumson the command line, orinnodb_checksumsorskip-innodb_checksumsin 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.5.2, “Dynamic System Variables”).
For information about
GLOBALandSESSIONvariable scope modifiers, refer to theSETstatement documentation.For more information on specifying options and system variables, see Section 4.2.3, “Specifying Program Options”.
Table 14.5 InnoDB Option/Variable
Reference
InnoDB Command Options
-
Introduced 5.1.33 Command-Line Format --ignore-builtin-innodbSystem Variable Name ignore_builtin_innodbVariable Scope Global Dynamic Variable No Permitted Values Type booleanThis option causes the server to behave as if the built-in
InnoDBis not present, which enablesInnoDB Pluginto be used instead. See Section 14.6.2.1, “Using InnoDB Plugin Instead of the Built-In InnoDB”.If this option is given but
InnoDB Pluginis not used in place of the built-inInnoDB, it has the following effects:Other
InnoDBoptions (including--innodband--skip-innodb) will not be recognized and should not be used.The server will not start if the default storage engine is set to
InnoDB. Use--default-storage-engineto set the default to some other engine if necessary.InnoDBwill not appear in the output ofSHOW ENGINES.
This option was added in MySQL 5.1.33.
Controls loading of the
InnoDBstorage engine, if the server was compiled withInnoDBsupport. As of MySQL 5.1.36, this option has a tristate format, with possible values ofOFF,ON, orFORCE. Before MySQL 5.1.36, this is a boolean option. See Section 5.1.8.1, “Installing and Uninstalling Plugins”.To disable
InnoDB, use--innodb=OFFor--skip-innodb. In this case, the server will not start if the default storage engine is set toInnoDB. Use--default-storage-engineto set the default to some other engine if necessary.-
Command-Line Format --innodb-status-filePermitted Values Type booleanDefault OFFControls whether
InnoDBcreates a file namedinnodb_status.in the MySQL data directory. If enabled,<pid>InnoDBperiodically 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
-
Introduced 5.1.33 Command-Line Format --ignore-builtin-innodbSystem Variable Name ignore_builtin_innodbVariable Scope Global Dynamic Variable No Permitted Values Type booleanWhether the server was started with the
--ignore-builtin-innodboption, which causes the server to behave as if the built-inInnoDBis not present. For more information, see the description of--ignore-builtin-innodbunder “InnoDBCommand Options” earlier in this section. This variable was added in MySQL 5.1.33. -
Introduced 5.1.38 Command-Line Format --innodb_adaptive_flushing=#System Variable Name innodb_adaptive_flushingVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault ON(
InnoDB Pluginonly)InnoDB Plugin1.0.4 and up uses a heuristic to determine when to flush dirty pages in the buffer pool. This heuristic is designed to avoid bursts of I/O activity and is used wheninnodb_adaptive_flushingis enabled (which is the default). -
Introduced 5.1.24 Command-Line Format --innodb_adaptive_hash_index=#System Variable Name innodb_adaptive_hash_indexVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault ONWhether the
InnoDBadaptive hash index is enabled or disabled. The adaptive hash index feature is useful for some workloads, and not for others; conduct benchmarks with it both enabled and disabled, using realistic workloads. See Section 14.6.3.3.4, “Adaptive Hash Indexes” for details. This variable is enabled by default. Use--skip-innodb_adaptive_hash_indexat server startup to disable it. This variable was added in MySQL 5.1.24. innodb_additional_mem_pool_sizeCommand-Line Format --innodb_additional_mem_pool_size=#System Variable Name innodb_additional_mem_pool_sizeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 1048576Min Value 524288Max 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 need to 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 1MB for the built-inInnoDB, 8MB forInnoDB Plugin.This variable relates to the
InnoDBinternal memory allocator, which is unused ifinnodb_use_sys_mallocis enabled.-
Command-Line Format --innodb_autoextend_increment=#System Variable Name innodb_autoextend_incrementVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 8Min Value 1Max Value 1000The increment size (in MB) for extending the size of an auto-extending shared tablespace file when it becomes full. The default value is 8. This variable does not affect the per-table tablespace files that are created if you use
innodb_file_per_table=1. Those files are auto-extending regardless of the value ofinnodb_autoextend_increment. The initial extensions are by small amounts, after which extensions occur in increments of 4MB. -
Removed 5.1.13 Command-Line Format --innodb_buffer_pool_awe_mem_mb=#System Variable Name innodb_buffer_pool_awe_mem_mbVariable Scope Global Dynamic Variable No Platform Specific Windows Permitted Values (Windows) Type integerDefault 0Min Value 0Max Value 63000The size of the buffer pool (in MB), if it is placed in the AWE memory. If it is greater than 0,
innodb_buffer_pool_sizeis the window in the 32-bit address space of mysqld whereInnoDBmaps that AWE memory. A good value forinnodb_buffer_pool_sizeis 500MB. The maximum possible value is 63000.To take advantage of AWE memory, you will need to recompile MySQL yourself. The current project settings needed for doing this can be found in the
storage/innobase/os/os0proc.csource file.This variable was removed in MySQL 5.1.13. Before that, it is relevant only in 32-bit Windows. If your 32-bit Windows operating system supports more than 4GB memory, using so-called “Address Windowing Extensions,” you can allocate the
InnoDBbuffer pool into the AWE physical memory using this variable. -
Introduced 5.1.22 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 locking mode to use for generating auto-increment values. The permissible values are 0, 1, or 2, for “traditional”, “consecutive”, or “interleaved” lock mode, respectively. Section 14.6.5.5, “AUTO_INCREMENT Handling in InnoDB”, describes the characteristics of these modes.
This variable was added in MySQL 5.1.22 with a default of 1 (“consecutive” lock mode). Before 5.1.22,
InnoDBuses “traditional” lock mode. -
Command-Line Format --innodb_buffer_pool_size=#System Variable Name innodb_buffer_pool_sizeVariable Scope Global Dynamic Variable No Permitted Values (<= 5.1.27) Type integerDefault 8388608Min Value 1048576Permitted Values (32-bit platforms, >= 5.1.28) Type integerDefault 134217728Min Value 1048576Max Value 2**32-1Permitted Values (64-bit platforms, >= 5.1.28) Type integerDefault 134217728Min Value 1048576Max Value 2**64-1The size in bytes of the memory buffer
InnoDBuses to cache data and indexes of its tables. The default value is 128MB, increased from a historical default of 8MB. In MySQL 5.1.28 and later, 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.The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. Be prepared to scale back this value if these other issues occur:
Competition for physical memory might cause paging in the operating system.
InnoDBreserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified size.The address space must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses.
The time to initialize the buffer pool is roughly proportional to its size. On large installations, this initialization time may be significant. For example, on a modern Linux x86_64 server, initialization of a 10GB buffer pool takes approximately 6 seconds. See Section 8.10.2, “The InnoDB Buffer Pool”.
-
Introduced 5.1.38 Command-Line Format --innodb_change_buffering=#System Variable Name innodb_change_bufferingVariable Scope Global Dynamic Variable Yes Permitted Values Type enumerationDefault insertsValid Values insertsnone(
InnoDB Pluginonly) WhetherInnoDBperforms insert buffering. The permitted valuesnone(do not buffer any operations) andinserts(buffer insert operations). The default isinserts. For details, see Section 14.6.3.3.3, “Insert Buffering”. -
Introduced 5.1.53 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. -
Command-Line Format --innodb_checksumsSystem Variable Name innodb_checksumsVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault ONInnoDBcan use checksum validation on all pages read from the disk to ensure extra fault tolerance against broken hardware or data files. This validation is enabled by default. However, under some rare circumstances (such as when running benchmarks) this extra safety feature is unneeded and can be disabled with--skip-innodb-checksums. -
Command-Line Format --innodb_commit_concurrency=#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.
As of MySQL 5.1.36, the value of
innodb_commit_concurrencycannot be changed at runtime from zero to nonzero or vice versa. The value can still be changed from one nonzero value to another. -
Command-Line Format --innodb_concurrency_tickets=#System Variable Name innodb_concurrency_ticketsVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 500Min Value 1Max Value 4294967295The number of threads that can enter
InnoDBconcurrently is determined by theinnodb_thread_concurrencyvariable. 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 500.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. -
Command-Line Format --innodb_data_file_path=nameSystem Variable Name innodb_data_file_pathVariable Scope Global Dynamic Variable No Permitted Values Type stringDefault ibdata1:10M:autoextendThe paths to individual data files and their sizes. The full directory path to each data file is formed by concatenating
innodb_data_home_dirto each path specified here. The file sizes are specified MB or GB (1024MB) by appendingMorGto the size value. 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 10MB, 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.2, “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 shared tablespace. This setting does not affect the location of per-file 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. -
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, and 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. By default, the value is 1, which causes a “fast” shutdown (the normal type of shutdown). If the value is 0,InnoDBdoes a full purge and an insert buffer merge before a shutdown. These operations can take minutes, or even hours in extreme cases. If the value is 1,InnoDBskips these operations at shutdown. If the value is 2,InnoDBwill just flush its logs and then shut down cold, as if MySQL had crashed; no committed transaction will be lost, but crash recovery will be done at the next startup. A value of 2 cannot be used on NetWare. -
Introduced 5.1.38 Command-Line Format --innodb_file_format=#System Variable Name innodb_file_formatVariable Scope Global Dynamic Variable Yes Permitted Values (>= 5.1.38) Type stringDefault AntelopeValid Values AntelopeBarracuda(
InnoDB Pluginonly) The file format to use for newInnoDBtables. CurrentlyAntelopeandBarracudaare supported. This applies only for tables that have their own tablespace, so for it to have an effectinnodb_file_per_tablemust be enabled. The Barracuda file format is required for certainInnoDBfeatures such as table compression. -
Introduced 5.1.38 Command-Line Format --innodb_file_format_check=#System Variable Name innodb_file_format_checkVariable Scope Global Dynamic Variable Yes Permitted Values (<= 5.1.41) Type stringDefault AntelopePermitted Values (>= 5.1.42) Type stringDefault Barracuda(
InnoDB Pluginonly) This variable can be set to 1 or 0 at server startup to enable or disable whetherInnoDBchecks the file format tag in the shared 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_checkto the file format tag, which is the value seen at runtime. -
Command-Line Format --innodb_file_io_threads=#System Variable Name innodb_file_io_threadsVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 4Min Value 4Max Value 64The number of file I/O threads in
InnoDB. Normally, this should be left at the default value of 4, but disk I/O on Windows may benefit from a larger number. On Unix, increasing the number has no effect;InnoDBalways uses the default value.With
InnoDB Plugin, this variable is unused. Useinnodb_read_io_threadsandinnodb_write_io_threadsinstead. -
Command-Line Format --innodb_file_per_tableSystem Variable Name innodb_file_per_tableVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault OFFIf
innodb_file_per_tableis disabled (the default),InnoDBcreates tables in the system tablespace. Ifinnodb_file_per_tableis enabled,InnoDBcreates each new table using its own.ibdfile for storing data and indexes, rather than in the system tablespace. See Section 14.6.4.4, “InnoDB File-Per-Table Tablespaces” for information about advantages, disadvantages, and features, such asInnoDBtable compression, that only work for tables stored in separate tablespaces. 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 012If the value of
innodb_flush_log_at_trx_commitis 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.The default value of 1 is required for full ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose up to one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, only an operating system crash or a power outage can erase the last second of transactions.
InnoDB's crash recovery works regardless of the value.For the greatest possible durability and consistency in a replication setup using
InnoDBwith transactions, useinnodb_flush_log_at_trx_commit=1andsync_binlog=1in your master servermy.cnffile.CautionMany operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt the
InnoDBdatabase. 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.1.23) Type stringDefault NULLValid Values fdatasyncO_DSYNClittlesyncnosyncO_DIRECTPermitted Values (Unix, >= 5.1.24) Type stringDefault NULLValid Values fsynclittlesyncnosyncO_DSYNCO_DIRECTPermitted 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.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.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.
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.Prior to MySQL 5.1.24, the default
innodb_flush_methodoption was namedfdatasync. Whenfdatasyncwasspecified,InnoDBused thefsync()system call to flush both the data and log files. To avoid confusing thefdatasyncoption name with thefdatasync()system call, the option name was changed tofsyncin MySQL 5.1.24. -
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. Possible values are from 0 to 6. For the meanings of these values and important information about
innodb_force_recovery, see Section 14.6.11.2, “Forcing InnoDB Recovery”. -
Introduced 5.1.38 Command-Line Format --innodb_io_capacity=#System Variable Name innodb_io_capacityVariable Scope Global Dynamic Variable No 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-1(
InnoDB Pluginonly) An upper limit on the I/O activity performed by theInnoDBbackground tasks, such as flushing pages from the buffer pool and merging data from the insert 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. For systems with individual 5400 RPM or 7200 RPM drives, you might lower the value to the former default of100.This parameter should be set to approximately the number of I/O operations that the system can perform per second. Ideally, keep this setting as low as practical, but not so low that these 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.
The value 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.
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 the
innodb_io_capacityvalue to any number 100 or greater, and the default value is200. You can set the value of this parameter in the MySQL option file (my.cnformy.ini) or change it dynamically with theSET GLOBALcommand, which requires theSUPERprivilege.See 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.6.7, “Optimizing InnoDB Disk I/O”.
This variable was added in MySQL 5.1.38.
innodb_limit_optimistic_insert_debugIntroduced 5.1.68 Command-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 (<= 5.1.37) Name innodb_lock_wait_timeoutVariable Scope Global Dynamic Variable No System Variable (>= 5.1.38) Name innodb_lock_wait_timeoutVariable Scope Global, Session Dynamic Variable No Permitted Values Type integerDefault 50Min Value 1Max Value 1073741824The timeout 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.6.11.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.InnoDBdoes detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.For the built-in
InnoDB, this variable can be set only at server startup. ForInnoDB Plugin, it can be set at startup or changed at runtime, and has both global and session values. innodb_locks_unsafe_for_binlogCommand-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. 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.6.3.1.4, “InnoDB Record, Gap, and Next-Key Locks”.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.6.3.1.5, “Avoiding the Phantom Problem Using Next-Key Locking”.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.6.3.3.1, “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
Semi-consistent read is available as of MySQL 5.1.5. Before 5.1.5, the second
UPDATEproceeds part way before it blocks. It begins acquiring x-locks, and blocks when it tries to acquire one for a row still locked by firstUPDATE. The secondUPDATEdoes not proceed until the firstUPDATEcommits or rolls back:x-lock(1,2); update(1,2) to (1,4); retain x-lock x-lock(2,3); block and wait for first UPDATE to commit or roll back
In this case, the second
UPDATEmust wait for a commit or rollback of the firstUPDATE, even though it affects different rows. The firstUPDATEhas an exclusive lock on row (2,3) that it has not released. As the secondUPDATEscans rows, it tries to acquire an exclusive lock for that same row, which it cannot have. Thus, before MySQL 5.1.5, enablinginnodb_locks_unsafe_for_binlogstill does not permit operations such asUPDATEto overtake other similar operations (such as anotherUPDATE) even when they affect different rows.This variable is deprecated, and was removed in MySQL 5.1.21.
This variable is deprecated, and was removed in MySQL 5.1.18.
-
Command-Line Format --innodb_log_buffer_size=#System Variable Name innodb_log_buffer_sizeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 1048576Min Value 1048576Max Value 4294967295The size in bytes of the buffer that
InnoDBuses to write to the log files on disk. The default value is 1MB for the built-inInnoDB, 8MB forInnoDB Plugin. Sensible values range from 1MB to 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O. -
Command-Line Format --innodb_log_file_size=#System Variable Name innodb_log_file_sizeVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 5242880Min Value 1048576Max Value 4GB / 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 4GB. A pair of 2047 MB log files, for example, would allow you to approach the range limit but not exceed it. The default value is 5MB. Sensible values range from 1MB to 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. But larger log files also mean that recovery is slower in case of a crash. -
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. -
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
InnoDBlog files. 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. -
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 90Min Value 0Max Value 100This is an integer in the range from 0 to 100. The default value is 90 for the built-in
InnoDB, 75 forInnoDB Plugin. The main thread inInnoDBtries to write pages from the buffer pool so that the percentage of dirty (not yet written) pages will not exceed this value. -
Command-Line Format --innodb_max_purge_lag=#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.6.3.2, “InnoDB Multi-Versioning”). The default value 0 (no delays).The
InnoDBtransaction system maintains a list of transactions that have index records delete-marked byUPDATEorDELETEoperations. Let the length of this list bepurge_lag. Whenpurge_lagexceedsinnodb_max_purge_lag, eachINSERT,UPDATE, andDELETEoperation is delayed by ((purge_lag/innodb_max_purge_lag)×10)−5 milliseconds. The delay is computed in the beginning of a purge batch, every ten seconds. The operations are not delayed if purge cannot run because of an old consistent read view that could see the rows to be purged.A typical setting for a problematic workload might be 1 million, assuming that transactions are small, only 100 bytes in size, and it is permissible to have 100MB of unpurged
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
Has no effect.
-
Introduced 5.1.41 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 95(
InnoDB Pluginonly) Specifies the approximate percentage of theInnoDBbuffer pool used for the old block sublist. The range of values is 5 to 95. The default value is 37 (that is, 3/8 of the pool). See Section 8.10.2, “The InnoDB Buffer Pool” for information about buffer pool management, such as the LRU algorithm and eviction policies.This variable was added in MySQL 5.1.41.
-
Introduced 5.1.41 Command-Line Format --innodb_old_blocks_time=#System Variable Name innodb_old_blocks_timeVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 0Min Value 0Max Value 2**32-1(
InnoDB Pluginonly) Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. The default value is 0: A block inserted into the old sublist moves immediately to the new sublist the first time it is accessed, no matter how soon after insertion the access occurs. If the value is greater than 0, blocks remain in the old sublist until an access occurs at least that many ms after the first access. For example, a value of 1000 causes blocks to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist.This variable is often used in combination with
innodb_old_blocks_pct. See Section 8.10.2, “The InnoDB Buffer Pool” for information about buffer pool management, such as the LRU algorithm and eviction policies.This variable was added in MySQL 5.1.41.
-
Command-Line Format --innodb_open_files=#System Variable Name innodb_open_filesVariable Scope Global Dynamic Variable No Permitted Values Type integerDefault 300Min 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. 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. -
Introduced 5.1.59 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 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.6.7, “Optimizing InnoDB Disk I/O”.
-
Introduced 5.1.38 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 64(
InnoDB Pluginonly) Controls the sensitivity of linear read-ahead thatInnoDBuses 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. The default is 56:InnoDBmust read at least 56 pages sequentially from an extent to initiate an asynchronous read for the following extent.This variable was added in MySQL 5.1.38.
-
Introduced 5.1.38 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 64(
InnoDB Pluginonly) The number of I/O threads for read operations inInnoDB. The default value is 4.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.This variable was added in MySQL 5.1.38.
-
Introduced 5.1.38 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 4294967295(
InnoDB Pluginonly) The replication thread delay (in ms) on a slave server ifinnodb_thread_concurrencyis reached.This variable was added in MySQL 5.1.38.
-
Introduced 5.1.15 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.1,
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). This variable was added in MySQL 5.1.15. -
Introduced 5.1.38 Command-Line Format --innodb_spin_wait_delay=#System Variable Name innodb_spin_wait_delayVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 6Min Value 0Max Value 4294967295(
InnoDB Pluginonly) The maximum delay between polls for a spin lock. The low-level implementation of this mechanism varies depending on the combination of hardware and operating system, so the delay does not correspond to a fixed time interval. The default value is 6.This variable was added in MySQL 5.1.38.
-
Introduced 5.1.56 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”.
-
Introduced 5.1.17 Command-Line Format --innodb_stats_on_metadataSystem Variable (<= 5.1.31) Name innodb_stats_on_metadataVariable Scope Global Dynamic Variable No System Variable (>= 5.1.32) Name innodb_stats_on_metadataVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault ONWhen this variable is enabled (which is the default, as before the variable was created),
InnoDBupdates statistics during metadata statements such asSHOW TABLE STATUSorSHOW INDEX, or when accessing theINFORMATION_SCHEMAtablesTABLESorSTATISTICS. (These updates are similar to what happens forANALYZE TABLE.) When disabled,InnoDBdoes not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involveInnoDBtables.This variable was added in MySQL 5.1.17.
-
Introduced 5.1.38 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-1(
InnoDB Pluginonly) The number of index pages to sample for index distribution statistics such as are calculated byANALYZE TABLE. The default value is 8.This variable was added in MySQL 5.1.38.
-
Introduced 5.1.38 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.
-
Command-Line Format --innodb_sync_spin_loops=#System Variable Name innodb_sync_spin_loopsVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 20Min 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 20 for the built-inInnoDB, 30 forInnoDB Plugin. -
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. -
Command-Line Format --innodb_thread_concurrency=#System Variable Name innodb_thread_concurrencyVariable Scope Global Dynamic Variable Yes Permitted Values Type integerDefault 8Min 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 20 or higher is interpreted as infinite concurrency before MySQL 5.1.12. From 5.1.12 on, you can disable thread concurrency checking by setting the value to 0. 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.The default value for the built-in
InnoDBis 20 before MySQL 5.1.11 and 8 from 5.1.11 on. The default for theInnoDB Pluginis 0.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.
-
Command-Line Format --innodb_thread_sleep_delay=#System Variable Name innodb_thread_sleep_delayVariable Scope Global Dynamic Variable Yes Permitted Values (32-bit platforms) Type integerDefault 10000Min Value 0Max Value 4294967295Permitted Values (64-bit platforms) Type integerDefault 10000Min Value 0Max Value 18446744073709551615How long
InnoDBthreads sleep before joining theInnoDBqueue, in microseconds. The default value is 10,000. A value of 0 disables sleep. innodb_trx_purge_view_update_only_debugIntroduced 5.1.68 Command-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.-
Introduced 5.1.62 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. innodb_use_legacy_cardinality_algorithmIntroduced 5.1.35 Command-Line Format --innodb_use_legacy_cardinality_algorithm=#System Variable Name innodb_use_legacy_cardinality_algorithmVariable Scope Global Dynamic Variable Yes Permitted Values Type booleanDefault ONInnoDBuses random numbers to generate dives into indexes for calculating index cardinality. However, under certain conditions, the algorithm does not generate random numbers, soANALYZE TABLEsometimes does not update cardinality estimates properly. An alternative algorithm was introduced in MySQL 5.1.35 with better randomization properties, and theinnodb_use_legacy_cardinality_algorithm, system variable which algorithm to use. The default value of the variable is 1 (ON), to use the original algorithm for compatibility with existing applications. The variable can be set to 0 (OFF) to use the new algorithm with improved randomness.This variable is not used in
InnoDB Pluginbecause the improved algorithm is used by default. Also, the number of random dives can be changed by modifying theinnodb_stats_sample_pagessystem variable.-
Introduced 5.1.38 Command-Line Format --innodb_use_sys_malloc=#System Variable Name innodb_use_sys_mallocVariable Scope Global Dynamic Variable No Permitted Values Type booleanDefault ON(
InnoDB Pluginonly) WhetherInnoDBuses the operating system memory allocator (ON) or its own (OFF). The default value isON.This variable was added in MySQL 5.1.38.
(
InnoDB Pluginonly) TheInnoDBversion number. Starting in 5.1.68, the separate numbering forInnoDBis discontinued and this value is the same as for theversionvariable.This variable was added in MySQL 5.1.38.
-
Introduced 5.1.38 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 64(
InnoDB Pluginonly) The number of I/O threads for write operations inInnoDB. The default value is 4.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.This variable was added in MySQL 5.1.38.
You should also take into consideration the value of
sync_binlog, which controls
synchronization of the binary log to disk.
* 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