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 the InnoDB adaptive hash index, you can use --innodb_adaptive_hash_index or --skip-innodb_adaptive_hash_index on the command line, or innodb_adaptive_hash_index or skip-innodb_adaptive_hash_index in an option file.

System variables that take a numeric value can be specified as --var_name=value on the command line or as var_name=value in option files.

Many system variables can be changed at runtime (see Section 5.1.5.2, “Dynamic System Variables”).

For information about GLOBAL and SESSION variable scope modifiers, refer to the SET statement documentation.

Certain options control the locations and layout of the InnoDB data files. Section 14.3, “InnoDB Configuration” explains how to use these options.

Some options, which you might not use initially, help tune InnoDB performance characteristics based on machine capacity and your database workload.

For more information on specifying options and system variables, see Section 4.2.3, “Specifying Program Options”.

--ignore-builtin-innodb

In MySQL 5.1, this option caused the server to behave as if the built-in InnoDB were not present, which enabled InnoDB Plugin to be used instead. In MySQL 5.6, InnoDB is the default storage engine and InnoDB Plugin is not used, so this option has no effect. As of MySQL 5.6.5, it is ignored.

--innodb[=value]

Controls loading of the InnoDB storage engine, if the server was compiled with InnoDB support. This option has a tristate format, with possible values of OFF, ON, or FORCE. See Section 5.1.8.1, “Installing and Uninstalling Plugins”.

To disable InnoDB, use --innodb=OFF or --skip-innodb. In this case, because the default storage engine is InnoDB, the server will not start unless you also use --default-storage-engine and --default-tmp-storage-engine to set the default to some other engine for both permanent and TEMPORARY tables.

As of MySQL 5.6.21, --innodb=OFF and --skip-innodb options are deprecated and their use results in a warning. These options will be removed in a future MySQL release.

--innodb-status-file

Controls whether InnoDB creates a file named innodb_status.pid in the MySQL data directory. If enabled, InnoDB periodically writes the output of SHOW ENGINE INNODB STATUS to this file.

By default, the file is not created. To create it, start mysqld with the --innodb-status-file=1 option. The file is deleted during normal shutdown.

--skip-innodb

Disable the InnoDB storage engine. See the description of --innodb.

daemon_memcached_enable_binlog

See Section 14.18, “InnoDB Integration with memcached” for usage details for this option.

daemon_memcached_engine_lib_name

Specifies the shared library that implements the InnoDB memcached plugin.

See Section 14.18, “InnoDB Integration with memcached” for usage details for this option.

daemon_memcached_engine_lib_path

The path of the directory containing the shared library that implements the InnoDB memcached plugin. The default value is NULL, representing the MySQL plugin directory. You should not need to modify this parameter unless specifying a different storage engine memcached plugin that is located outside of the MySQL plugin directory.

See Section 14.18, “InnoDB Integration with memcached” for usage details for this option.

daemon_memcached_option

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.18, “InnoDB Integration with memcached” for usage details for this option. For information about memcached options, refer to the memcached man page.

daemon_memcached_r_batch_size

Specifies how many memcached read operations (get) to perform before doing a COMMIT to start a new transaction. Counterpart of daemon_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.18, “InnoDB Integration with memcached” for usage details for this option.

daemon_memcached_w_batch_size

Specifies how many memcached write operations, such as add, set, or incr, to perform before doing a COMMIT to start a new transaction. Counterpart of daemon_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.18, “InnoDB Integration with memcached” for usage details for this option.

ignore_builtin_innodb

See the description of --ignore-builtin-innodb under “InnoDB Command Options” earlier in this section.

innodb_adaptive_flushing

Specifies whether to dynamically adjust the rate of flushing dirty pages in the InnoDB buffer pool based on the workload. Adjusting the flush rate dynamically is intended to avoid bursts of I/O activity. This setting is enabled by default. See Section 14.13.1.2, “Configuring the Rate of InnoDB Buffer Pool Flushing” for more information. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.

innodb_adaptive_flushing_lwm

Low water mark representing percentage of redo log capacity at which adaptive flushing is enabled.

innodb_adaptive_hash_index

Whether the InnoDB adaptive hash index is enabled or disabled. It may be desirable, depending on your workload, to dynamically enable or disable adaptive hash indexing to improve query performance. Because the adaptive hash index may not be useful for all workloads, conduct benchmarks with it both enabled and disabled, using realistic workloads. See Section 14.2.13.6, “Adaptive Hash Indexes” for details.

This variable is enabled by default. You can modify this parameter using the SET GLOBAL statement, without restarting the server. Changing the setting requires the SUPER privilege. You can also use --skip-innodb_adaptive_hash_index at server startup to disable it.

Disabling the adaptive hash index empties the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that were using the hash table access the index B-trees directly instead. When the adaptive hash index is re-enabled, the hash table is populated again during normal operation.

innodb_adaptive_max_sleep_delay

Allows InnoDB to automatically adjust the value of innodb_thread_sleep_delay up or down according to the current workload. Any non-zero value enables automated, dynamic adjustment of the innodb_thread_sleep_delay value, up to the maximum value specified in the innodb_adaptive_max_sleep_delay option. The value represents the number of microseconds. This option can be useful in busy systems, with greater than 16 InnoDB threads. (In practice, it is most valuable for MySQL systems with hundreds or thousands of simultaneous connections.)

For more information, see Section 14.13.5, “Configuring Thread Concurrency for InnoDB”.

innodb_additional_mem_pool_size

The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures. The more tables you have in your application, the more memory you allocate here. If InnoDB runs out of memory in this pool, it starts to allocate memory from the operating system and writes warning messages to the MySQL error log. The default value is 8MB.

This variable relates to the InnoDB internal memory allocator, which is unused if innodb_use_sys_malloc is enabled. As of MySQL 5.6.3, innodb_additional_mem_pool_size is deprecated and will be removed in a future MySQL release.

innodb_api_bk_commit_interval

How often to auto-commit idle connections that use the InnoDB memcached interface, in seconds. See Section 14.18, “InnoDB Integration with memcached” for usage details for this option.

innodb_api_disable_rowlock

Use this variable to disable row locks when InnoDB memcached performs DML operations. By default, innodb_api_disable_rowlock is set to OFF which means that memcached requests row locks for get and set operations. When innodb_api_disable_rowlock is set to ON, memcached requests a table lock instead of row locks.

The innodb_api_disable_rowlock option 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.

innodb_api_enable_binlog

Lets you use the InnoDB memcached plugin with the MySQL binary log. See Section 14.18, “InnoDB Integration with memcached” for usage details for this option.

innodb_api_enable_mdl

Locks the table used by the InnoDB memcached plugin, so that it cannot be dropped or altered by DDL through the SQL interface. See Section 14.18, “InnoDB Integration with memcached” for usage details for this option.

innodb_api_trx_level

Lets you control the transaction isolation level on queries processed by the memcached interface. See Section 14.18, “InnoDB Integration with memcached” for usage details for this option. The constants corresponding to the familiar names are:

innodb_autoextend_increment

The increment size (in MB) for extending the size of an auto-extend InnoDB system 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 use innodb_file_per_table=1. Those files are auto-extending regardless of the value of innodb_autoextend_increment. The initial extensions are by small amounts, after which extensions occur in increments of 4MB.

innodb_autoinc_lock_mode

The 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.6.5, “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_shutdown

Specifies whether to record the pages cached in the InnoDB buffer pool when the MySQL server is shut down, to shorten the warmup process at the next restart. Typically used in combination with innodb_buffer_pool_load_at_startup.

For related information, see Section 14.13.1.5, “Preloading the InnoDB Buffer Pool for Faster Restart”.

innodb_buffer_pool_dump_now

Immediately 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.13.1.5, “Preloading the InnoDB Buffer Pool for Faster Restart”.

innodb_buffer_pool_filename

Specifies the name of the file that holds the list of tablespace IDs and page IDs produced by innodb_buffer_pool_dump_at_shutdown or innodb_buffer_pool_dump_now. Tablespace IDs and page IDs are saved in the following format: space, page_id. By default, the file is located in the InnoDB data directory.

For related information, see Section 14.13.1.5, “Preloading the InnoDB Buffer Pool for Faster Restart”.

innodb_buffer_pool_instances

The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.

This option takes effect only when you set the innodb_buffer_pool_size to a size of 1GB or more. The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1GB.

Before MySQL 5.6.6, the default is 1. As of MySQL 5.6.6, the default is 8, except on 32-bit Windows systems, where the default depends on the value of innodb_buffer_pool_size:

innodb_buffer_pool_load_abort

Interrupts the process of restoring InnoDB buffer pool contents triggered by innodb_buffer_pool_load_at_startup or innodb_buffer_pool_load_now.

For related information, see Section 14.13.1.5, “Preloading the InnoDB Buffer Pool for Faster Restart”.

innodb_buffer_pool_load_at_startup

Specifies that, on MySQL server startup, the InnoDB buffer pool is automatically warmed up by loading the same pages it held at an earlier time. Typically used in combination with innodb_buffer_pool_dump_at_shutdown.

For related information, see Section 14.13.1.5, “Preloading the InnoDB Buffer Pool for Faster Restart”.

innodb_buffer_pool_load_now

Immediately warms up the InnoDB buffer pool by loading a set of data pages, without waiting for a server restart. Can be useful to bring cache memory back to a known state during benchmarking, or to ready the MySQL server to resume its normal workload after running queries for reports or maintenance.

For related information, see Section 14.13.1.5, “Preloading the InnoDB Buffer Pool for Faster Restart”.

innodb_buffer_pool_size

The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 128MB. The maximum value depends on the CPU architecture; the maximum is 4294967295 (2³²-1) on 32-bit systems and 18446744073709551615 (2⁶⁴-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, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server.

The larger you set this value, the less disk I/O is needed to access the same data in tables more than once. On a dedicated database server, you might set this to up to 80% of the machine physical memory size. Be prepared to scale back this value if these other issues occur:

innodb_change_buffer_max_size

Maximum size for the InnoDB change buffer, as a percentage of the total size of the buffer pool. You might increase this value for a MySQL server with heavy insert, update, and delete activity, or decrease it for a MySQL server with unchanging data used for reporting. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.

innodb_change_buffering

Whether InnoDB performs change buffering, an optimization that delays write operations to secondary indexes so that the I/O operations can be performed sequentially. The permitted values are inserts (buffer insert operations), deletes (buffer delete operations; strictly speaking, the writes that mark index records for later deletion during a purge operation), changes (buffer insert and delete-marking operations), purges (buffer purge operations, the writes when deleted index entries are finally garbage-collected), all (buffer insert, delete-marking, and purge operations) and none (do not buffer any operations). The default is all. For details, see Section 14.13.4, “Configuring InnoDB Change Buffering”. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.

innodb_checksum_algorithm

Specifies how to generate and verify the checksum stored in each disk block of each InnoDB tablespace.

innodb_checksum_algorithm replaces the innodb_checksums option as of MySQL 5.6.3. The following values are provided for compatibility:

To avoid conflicts, remove references to innodb_checksums from your configuration file and MySQL startup scripts.

The value innodb is backward-compatible with all versions of MySQL. The value crc32 uses an algorithm that is faster to compute the checksum for every modified block, and to check the checksums for each disk read. The value none writes a constant value in the checksum field rather than computing a value based on the block data. The blocks in a tablespace can use a mix of old, new, and no checksum values, being updated gradually as the data is modified; once any blocks in a tablespace are modified to use the crc32 algorithm, the associated tables cannot be read by earlier versions of MySQL.

The strict_* forms work the same as innodb, crc32, and none, except that InnoDB halts if it encounters a mix of checksum values in the same tablespace. You can only use these options in a completely new instance, to set up all tablespaces for the first time. The strict_* settings are somewhat faster, because they do not need to compute both new and old checksum values to accept both during disk reads.

The following table illustrates the difference between the none, innodb, and crc32 option values, and their strict_ counterparts. none, innodb, and crc32 write the specified type checksum value into each data block, but for compatibility accept any of the other checksum values when verifying a block during a read operation. The strict_ form of each parameter only recognizes one kind of checksum, which makes verification faster but requires that all InnoDB data files in an instance be created under the identical innodb_checksum_algorithm value.

The default value for innodb_checksum_algorithm was changed from innodb to crc32 in MySQL 5.6.6, but switched back to innodb in 5.6.7 for improved compatibility of InnoDB data files during a downgrade to an earlier MySQL version, and for use with MySQL Enterprise Backup. The limitations encountered included:

For additional information about the crc32 checksum algorithm, see Section 14.13.15, “Using the CRC32 Checksum Algorithm for Faster Checksums”.

innodb_checksums

InnoDB can 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 with innodb_checksum_algorithm.

In MySQL 5.6.3 and higher, this option is deprecated, replaced by innodb_checksum_algorithm. innodb_checksum_algorithm=innodb is the same as innodb_checksums=ON (the default). innodb_checksum_algorithm=none is the same as innodb_checksums=OFF. Remove any innodb_checksums options from your configuration files and startup scripts, to avoid conflicts with innodb_checksum_algorithm: innodb_checksums=OFF would automatically set innodb_checksum_algorithm=none; innodb_checksums=ON would be ignored and overridden by any other setting for innodb_checksum_algorithm.

innodb_cmp_per_index_enabled

Enables per-index compression-related statistics in the INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX table. Because these statistics can be expensive to gather, only enable this option on development, test, or slave instances during performance tuning related to InnoDB compressed tables.

innodb_commit_concurrency

The number of threads that can commit at the same time. A value of 0 (the default) permits any number of transactions to commit simultaneously.

The value of innodb_commit_concurrency cannot be changed at runtime from zero to nonzero or vice versa. The value can be changed from one nonzero value to another.

innodb_compression_failure_threshold_pct

Sets 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.

innodb_compression_level

Specifies the level of zlib compression to use for InnoDB compressed tables and indexes.

innodb_compression_pad_pct_max

Specifies the maximum percentage that can be reserved as free space within each compressed page, allowing room to reorganize the data and modification log within the page when a compressed table or index is updated and the data might be recompressed. Only applies when innodb_compression_failure_threshold_pct is set to a non-zero value, and the rate of compression failures passes the cutoff point.

innodb_concurrency_tickets

Determines the number of threads that can enter InnoDB concurrently. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has already reached the concurrency limit. When a thread is permitted to enter InnoDB, it is given a number of “free tickets” equal to the value of innodb_concurrency_tickets, and the thread can enter and leave InnoDB freely until it has used up its tickets. After that point, the thread again becomes subject to the concurrency check (and possible queuing) the next time it tries to enter InnoDB. The default value is 5000 as of MySQL 5.6.6, 500 before that.

With a small innodb_concurrency_tickets value, small transactions that only need to process a few rows compete fairly with larger transactions that process many rows. The disadvantage of a small innodb_concurrency_tickets value 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_tickets value, large transactions spend less time waiting for a position at the end of the queue (controlled by innodb_thread_concurrency) and more time retrieving rows. Large transactions also require fewer trips through the queue to complete their task. The disadvantage of a large innodb_concurrency_tickets value is that too many large transactions running at the same time can starve smaller transactions by making them wait a longer time before executing.

With a non-zero innodb_thread_concurrency value, you may need to adjust the innodb_concurrency_tickets value up or down to find the optimal balance between larger and smaller transactions. The SHOW ENGINE INNODB STATUS report shows the number of tickets remaining for an executing transaction in its current pass through the queue. This data may also be obtained from the TRX_CONCURRENCY_TICKETS column of the INFORMATION_SCHEMA.INNODB_TRX table.

For more information, see Section 14.13.5, “Configuring Thread Concurrency for InnoDB”.

innodb_data_file_path

The paths to individual InnoDB data files and their sizes. The full directory path to each data file is formed by concatenating innodb_data_home_dir to each path specified here. The file sizes are specified KB, MB or GB (1024MB) by appending K, M or G to 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 specify innodb_data_file_path, the default behavior is to create a single auto-extending data file, slightly larger than 12MB, named ibdata1. The size limit of individual files is determined by your operating system. You can set the file size to more than 4GB on those operating systems that support big files. You can also use raw disk partitions as data files. For detailed information on configuring InnoDB tablespace files, see Section 14.3, “InnoDB Configuration”.

innodb_data_home_dir

The common part of the directory path for all InnoDB data files in the system tablespace. This setting does not affect the location of file-per-table tablespaces when innodb_file_per_table is enabled. The default value is the MySQL data directory. If you specify the value as an empty string, you can use absolute file paths in innodb_data_file_path.

innodb_disable_sort_file_cache

If 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.

innodb_doublewrite

If this variable is enabled (the default), InnoDB stores all data twice, first to the doublewrite buffer, then to the actual data files. This variable can be turned off with --skip-innodb_doublewrite for benchmarks or cases when top performance is needed rather than concern for data integrity or possible failures.

innodb_fast_shutdown

The InnoDB shutdown mode. If the value is 0, InnoDB does a slow shutdown, a full purge and an insert buffer merge before shutting down. If the value is 1 (the default), InnoDB skips these operations at shutdown, a process known as a fast shutdown. If the value is 2, InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.

The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.

Use innodb_fast_shutdown=2 in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption.

innodb_file_format

The file format to use for new InnoDB tables. Currently, Antelope and Barracuda are supported. This applies only for tables that have their own tablespace, so for it to have an effect, innodb_file_per_table must be enabled. The Barracuda file format is required for certain InnoDB features such as table compression.

Be aware that ALTER TABLE operations that recreate InnoDB tables (ALGORITHM=COPY) will use the current innodb_file_format setting (the conditions outlined above still apply).

innodb_file_format_check

This variable can be set to 1 or 0 at server startup to enable or disable whether InnoDB checks the file format tag in the system tablespace (for example, Antelope or Barracuda). If the tag is checked and is higher than that supported by the current version of InnoDB, an error occurs and InnoDB does not start. If the tag is not higher, InnoDB sets the value of innodb_file_format_max to the file format tag.

innodb_file_format_max

At server startup, InnoDB sets the value of this variable to the file format tag in the system tablespace (for example, Antelope or Barracuda). If the server creates or opens a table with a “higher” file format, it sets the value of innodb_file_format_max to that format.

innodb_file_per_table

When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table in a separate .ibd file, rather than in the system tablespace. The storage for these InnoDB tables is reclaimed when the tables are dropped or truncated. This setting enables several other InnoDB features, such as table compression. See Section 14.5.2, “InnoDB File-Per-Table Mode” for details about such features as well as advantages and disadvantages of using file-per-table tablespaces.

Be aware that enabling innodb_file_per_table also means that an ALTER TABLE operation will move InnoDB table from the system tablespace to an individual .ibd file in cases where ALTER TABLE recreates the table (ALGORITHM=COPY).

When innodb_file_per_table is disabled, InnoDB stores 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 as DROP TABLE or TRUNCATE 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 when innodb_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_table is 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 prevent ALTER TABLE from moving InnoDB tables from the system tablespace to individual .ibd files.

innodb_file_per_table is dynamic and can be set ON or OFF using SET GLOBAL. You can also set this parameter in the MySQL configuration file (my.cnf or my.ini) but this requires shutting down and restarting the server.

Dynamically changing the value of this parameter requires the SUPER privilege and immediately affects the operation of all connections.

innodb_flush_log_at_timeout

Write and flush the logs every N seconds. innodb_flush_log_at_timeout was 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 for innodb_flush_log_at_timeout is also once per second.

innodb_flush_log_at_trx_commit

Controls the balance between strict ACID compliance for commit operations, and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value, but then you can lose up to a second of transactions in a crash.

For durability and consistency in a replication setup that uses InnoDB with transactions:

innodb_flush_method

Defines the method used to flush data to the InnoDB data files and log files, which can affect I/O throughput. This variable is only configurable on Unix and Linux systems. On Windows systems, the flush method is always async_unbuffered and cannot be changed.

The innodb_flush_method options include:

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_fsyncs status variable to see the overall number of fsync() calls for each setting. The mix of read and write operations in your workload can affect how a setting performs. For example, on a system with a hardware RAID controller and battery-backed write cache, O_DIRECT can help to avoid double buffering between the InnoDB buffer pool and the operating system's file system cache. On some systems where InnoDB data and log files are located on a SAN, the default value or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. Always test this parameter with hardware and workload that reflect your production environment. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.

innodb_flush_neighbors

Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent.

When the table data is stored on a traditional HDD storage device, flushing such neighbor pages in one operation reduces I/O overhead (primarily for disk seek operations) compared to flushing individual pages at different times. For table data stored on SSD, seek time is not a significant factor and you can turn this setting off to spread out the write operations. For general I/O tuning advice, see Section 8.5.7, “Optimizing InnoDB Disk I/O”.

innodb_flushing_avg_loops

Number of iterations for which InnoDB keeps the previously calculated snapshot of the flushing state, controlling how quickly adaptive flushing responds to changing workloads. Increasing the value makes the rate of flush operations change smoothly and gradually as the workload changes. Decreasing the value makes adaptive flushing adjust quickly to workload changes, which can cause spikes in flushing activity if the workload increases and decreases suddenly.

innodb_force_load_corrupted

Lets 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.

innodb_force_recovery

The crash recovery mode, typically only changed in serious troubleshooting situations. Possible values are from 0 to 6. For the meanings of these values and important information about innodb_force_recovery, see Section 14.19.2, “Forcing InnoDB Recovery”.

innodb_ft_aux_table

Specifies the qualified name of an InnoDB table containing a FULLTEXT index. 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_name, the INFORMATION_SCHEMA tables INNODB_FT_INDEX_TABLE, INNODB_FT_INDEX_CACHE, INNODB_FT_CONFIG, INNODB_FT_DELETED, and INNODB_FT_BEING_DELETED will show information about the search index for the specified table.

innodb_ft_cache_size

The memory allocated, in bytes, for the InnoDB FULLTEXT search index cache, which holds a parsed document in memory while creating an InnoDB FULLTEXT index. Index inserts and updates are only committed to disk when the innodb_ft_cache_size size limit is reached. innodb_ft_cache_size defines the cache size on a per table basis. To set a global limit for all tables, see innodb_ft_total_cache_size.

innodb_ft_enable_diag_print

Whether 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:

innodb_ft_enable_stopword

Specifies that a set of stopwords is associated with an InnoDB FULLTEXT index at the time the index is created. If the innodb_ft_user_stopword_table option is set, the stopwords are taken from that table. Else, if the innodb_ft_server_stopword_table option is set, the stopwords are taken from that table. Otherwise, a built-in set of default stopwords is used.

innodb_ft_max_token_size

Maximum length of words that are stored in an InnoDB FULLTEXT index. Setting a limit on this value reduces the size of the index, thus speeding up queries, by omitting long keywords or arbitrary collections of letters that are not real words and are not likely to be search terms.

innodb_ft_min_token_size

Minimum length of words that are stored in an InnoDB FULLTEXT index. Increasing this value reduces the size of the index, thus speeding up queries, by omitting common 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.

innodb_ft_num_word_optimize

Number of words to process during each OPTIMIZE TABLE operation on an InnoDB FULLTEXT index. Because a bulk insert or update operation to a table containing a full-text search index could require substantial index maintenance to incorporate all changes, you might do a series of OPTIMIZE TABLE statements, each picking up where the last left off.

innodb_ft_result_cache_limit

The InnoDB FULLTEXT search (FTS) query result cache limit (defined in bytes) per FTS query or per thread. Intermediate and final InnoDB FTS query results are handled in memory. Use innodb_ft_result_cache_limit to place a size limit on the InnoDB FTS query result cache to avoid excessive memory consumption in case of very large InnoDB FTS 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_limit for all platform types and platform bit sizes is 2**32-1. Bug #71554.

innodb_ft_server_stopword_table

This option is used to specify your own InnoDB FULLTEXT index stopword list for all InnoDB tables. To configure your own stopword list for a specific InnoDB table, use innodb_ft_user_stopword_table.

Set innodb_ft_server_stopword_table to the name of the table containing a list of stopwords, in the format db_name/table_name.

The stopword table must exist before you configure innodb_ft_server_stopword_table. innodb_ft_enable_stopword must be enabled and innodb_ft_server_stopword_table option must be configured before you create the FULLTEXT index.

The stopword table must be an InnoDB table, containing a single VARCHAR column named VALUE.

For more information, see Section 12.9.4, “Full-Text Stopwords”.

innodb_ft_sort_pll_degree

Number of threads used in parallel to index and tokenize text in an InnoDB FULLTEXT index, when building a search index. See innodb_sort_buffer_size for additional usage information.

innodb_ft_total_cache_size

The total memory allocated, in bytes, for the InnoDB FULLTEXT search 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.

innodb_ft_user_stopword_table

This option is used to specify your own InnoDB FULLTEXT index stopword list on a specific table. To configure your own stopword list for all InnoDB tables, use innodb_ft_server_stopword_table.

Set innodb_ft_user_stopword_table to the name of the table containing a list of stopwords, in the format db_name/table_name.

The stopword table must exist before you configure innodb_ft_user_stopword_table. innodb_ft_enable_stopword must be enabled and innodb_ft_user_stopword_table must be configured before you create the FULLTEXT index.

The stopword table must be an InnoDB table, containing a single VARCHAR column named VALUE.

For more information, see Section 12.9.4, “Full-Text Stopwords”.

innodb_io_capacity

The innodb_io_capacity parameter sets an upper limit on the I/O activity performed by the InnoDB background 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.

The innodb_io_capacity limit is a total limit for all buffer pool instances. When dirty pages are flushed, the innodb_io_capacity limit 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.

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 InnoDB I/O, particularly fast drives capable of high numbers of IOPS. For example, systems that use multiple disks or solid-state disks for InnoDB are likely to benefit from the ability to control this parameter.

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_capacity value to any number 100 or greater to a maximum defined by innodb_io_capacity_max. The default value is 200. You can set the value of this parameter in the MySQL option file (my.cnf or my.ini) or change it dynamically with the SET GLOBAL command, which requires the SUPER privilege.

See Section 14.13.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.7, “Optimizing InnoDB Disk I/O”.

innodb_io_capacity_max

The limit up to which InnoDB is allowed to extend the innodb_io_capacity setting in case of emergency. If you specify an innodb_io_capacity setting at startup and do not specify a value for innodb_io_capacity_max, the innodb_io_capacity_max value defaults to twice the value of innodb_io_capacity, with a lower limit of 2000. 2000 is also the initial default innodb_io_capacity_max configuration value.

The innodb_io_capacity_max setting is a total limit for all buffer pool instances.

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 to innodb_io_capacity_max, to emphasize its relationship to the innodb_io_capacity option.

innodb_large_prefix

Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes), for InnoDB tables that use the DYNAMIC and COMPRESSED row formats. (Creating such tables also requires the option values innodb_file_format=barracuda and innodb_file_per_table=true.) See Section 14.6.7, “Limits on InnoDB Tables” for the relevant maximums associated with index key prefixes under various settings.

For tables using the REDUNDANT and COMPACT row formats, this option does not affect the allowed key prefix length.

innodb_lock_wait_timeout

The length of time in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When a lock wait timeout occurs, the current statement is rolled back (not the entire transaction). To have the entire transaction roll back, start the server with the --innodb_rollback_on_timeout option. See also Section 14.19.4, “InnoDB Error Handling”.

You might decrease this value for highly interactive applications or OLTP systems, to display user feedback quickly or put the update into a queue for processing later. You might increase this value for long-running back-end operations, such as a transform step in a data warehouse that waits for other large insert or update operations to finish.

innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.

The lock wait timeout value does not apply to deadlocks, because InnoDB detects them immediately and rolls back one of the deadlocked transactions.

innodb_lock_wait_timeout can be set at runtime with the SET GLOBAL or SET SESSION statement. Changing the GLOBAL setting requires the SUPER privilege and affects the operation of all clients that subsequently connect. Any client can change the SESSION setting for innodb_lock_wait_timeout, which affects only that client.

innodb_locks_unsafe_for_binlog