MySQL innodb parameters

痛定思痛。 2022-08-27 13:58 168阅读 0赞

本文转载自：http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html

## `InnoDB` Startup Options and System Variables ##

\[[\+/-][-]\]

[14.15.1 Changes to`InnoDB` Startup Options and System Variables][14.15.1 Changes to_InnoDB_ Startup Options and System Variables]

This section describes the `InnoDB`\-related command options and system variables.

*  System variables that are true or false can be enabled at server startup by naming them, or disabled by using a`--skip-` prefix. For example, to enable or disable `InnoDB` checksums, you can use [ `--innodb_checksums`][_--innodb_checksums] or [ `--skip-innodb_checksums`][_--innodb_checksums] on the command line, or [ `innodb_checksums`][_--innodb_checksums] or `skip-innodb_checksums` 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”][Section_5.1.5.2_ _Dynamic System Variables]).
 *  For information about `GLOBAL` and `SESSION` variable scope modifiers, refer to the [ `SET`][_SET] statement documentation.
 *  Certain options control the locations and layout of the `InnoDB` data files. [ Section 14.6, “`InnoDB` Configuration”][Section_14.6_ _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”][Section_4.2.3_ _Specifying Program Options].

**Table 14.4 `InnoDB` Option/Variable Reference**

<table> 
 <thead> 
 <tr> 
 <th>Name</th> 
 <th>Cmd-Line</th> 
 <th>Option file</th> 
 <th>System Var</th> 
 <th>Status Var</th> 
 <th>Var Scope</th> 
 <th>Dynamic</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks" rel="nofollow">foreign_key_checks</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Both</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_have_innodb" rel="nofollow">have_innodb</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#option_mysqld_ignore-builtin-innodb" rel="nofollow">ignore-builtin-innodb</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td>- Variable: <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_ignore_builtin_innodb" rel="nofollow"> ignore_builtin_innodb</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#option_mysqld_innodb" rel="nofollow">innodb</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_adaptive_flushing" rel="nofollow">innodb_adaptive_flushing</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_adaptive_hash_index" rel="nofollow">innodb_adaptive_hash_index</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_additional_mem_pool_size" rel="nofollow">innodb_additional_mem_pool_size</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_autoextend_increment" rel="nofollow">innodb_autoextend_increment</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode" rel="nofollow">innodb_autoinc_lock_mode</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_bytes_data" rel="nofollow">Innodb_buffer_pool_bytes_data</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_bytes_dirty" rel="nofollow">Innodb_buffer_pool_bytes_dirty</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances" rel="nofollow">innodb_buffer_pool_instances</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_pages_data" rel="nofollow">Innodb_buffer_pool_pages_data</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_pages_dirty" rel="nofollow">Innodb_buffer_pool_pages_dirty</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_pages_flushed" rel="nofollow">Innodb_buffer_pool_pages_flushed</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_pages_free" rel="nofollow">Innodb_buffer_pool_pages_free</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_pages_latched" rel="nofollow">Innodb_buffer_pool_pages_latched</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_pages_misc" rel="nofollow">Innodb_buffer_pool_pages_misc</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_pages_total" rel="nofollow">Innodb_buffer_pool_pages_total</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_read_ahead" rel="nofollow">Innodb_buffer_pool_read_ahead</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_read_ahead_evicted" rel="nofollow">Innodb_buffer_pool_read_ahead_evicted</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_read_requests" rel="nofollow">Innodb_buffer_pool_read_requests</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_reads" rel="nofollow">Innodb_buffer_pool_reads</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size" rel="nofollow">innodb_buffer_pool_size</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_wait_free" rel="nofollow">Innodb_buffer_pool_wait_free</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_write_requests" rel="nofollow">Innodb_buffer_pool_write_requests</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_change_buffering" rel="nofollow">innodb_change_buffering</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_checksums" rel="nofollow">innodb_checksums</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_commit_concurrency" rel="nofollow">innodb_commit_concurrency</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_concurrency_tickets" rel="nofollow">innodb_concurrency_tickets</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_data_file_path" rel="nofollow">innodb_data_file_path</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_data_fsyncs" rel="nofollow">Innodb_data_fsyncs</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_data_home_dir" rel="nofollow">innodb_data_home_dir</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_data_pending_fsyncs" rel="nofollow">Innodb_data_pending_fsyncs</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_data_pending_reads" rel="nofollow">Innodb_data_pending_reads</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_data_pending_writes" rel="nofollow">Innodb_data_pending_writes</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_data_read" rel="nofollow">Innodb_data_read</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_data_reads" rel="nofollow">Innodb_data_reads</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_data_writes" rel="nofollow">Innodb_data_writes</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_data_written" rel="nofollow">Innodb_data_written</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_dblwr_pages_written" rel="nofollow">Innodb_dblwr_pages_written</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_dblwr_writes" rel="nofollow">Innodb_dblwr_writes</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_doublewrite" rel="nofollow">innodb_doublewrite</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_fast_shutdown" rel="nofollow">innodb_fast_shutdown</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_format" rel="nofollow">innodb_file_format</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_format_check" rel="nofollow">innodb_file_format_check</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_format_max" rel="nofollow">innodb_file_format_max</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table" rel="nofollow">innodb_file_per_table</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit" rel="nofollow">innodb_flush_log_at_trx_commit</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_method" rel="nofollow">innodb_flush_method</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_force_load_corrupted" rel="nofollow">innodb_force_load_corrupted</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_force_recovery" rel="nofollow">innodb_force_recovery</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_have_atomic_builtins" rel="nofollow">Innodb_have_atomic_builtins</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_io_capacity" rel="nofollow">innodb_io_capacity</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_large_prefix" rel="nofollow">innodb_large_prefix</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout" rel="nofollow">innodb_lock_wait_timeout</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Both</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog" rel="nofollow">innodb_locks_unsafe_for_binlog</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_buffer_size" rel="nofollow">innodb_log_buffer_size</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_file_size" rel="nofollow">innodb_log_file_size</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_files_in_group" rel="nofollow">innodb_log_files_in_group</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_group_home_dir" rel="nofollow">innodb_log_group_home_dir</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_log_waits" rel="nofollow">Innodb_log_waits</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_log_write_requests" rel="nofollow">Innodb_log_write_requests</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_log_writes" rel="nofollow">Innodb_log_writes</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct" rel="nofollow">innodb_max_dirty_pages_pct</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_max_purge_lag" rel="nofollow">innodb_max_purge_lag</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_mirrored_log_groups" rel="nofollow">innodb_mirrored_log_groups</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_old_blocks_pct" rel="nofollow">innodb_old_blocks_pct</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_old_blocks_time" rel="nofollow">innodb_old_blocks_time</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_open_files" rel="nofollow">innodb_open_files</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_os_log_fsyncs" rel="nofollow">Innodb_os_log_fsyncs</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_os_log_pending_fsyncs" rel="nofollow">Innodb_os_log_pending_fsyncs</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_os_log_pending_writes" rel="nofollow">Innodb_os_log_pending_writes</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_os_log_written" rel="nofollow">Innodb_os_log_written</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_page_size" rel="nofollow">Innodb_page_size</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_pages_created" rel="nofollow">Innodb_pages_created</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_pages_read" rel="nofollow">Innodb_pages_read</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_pages_written" rel="nofollow">Innodb_pages_written</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks" rel="nofollow">innodb_print_all_deadlocks</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_purge_batch_size" rel="nofollow">innodb_purge_batch_size</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_purge_threads" rel="nofollow">innodb_purge_threads</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_random_read_ahead" rel="nofollow">innodb_random_read_ahead</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_read_ahead_threshold" rel="nofollow">innodb_read_ahead_threshold</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_read_io_threads" rel="nofollow">innodb_read_io_threads</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_replication_delay" rel="nofollow">innodb_replication_delay</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_rollback_on_timeout" rel="nofollow">innodb_rollback_on_timeout</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_rollback_segments" rel="nofollow">innodb_rollback_segments</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_row_lock_current_waits" rel="nofollow">Innodb_row_lock_current_waits</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_row_lock_time" rel="nofollow">Innodb_row_lock_time</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_row_lock_time_avg" rel="nofollow">Innodb_row_lock_time_avg</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_row_lock_time_max" rel="nofollow">Innodb_row_lock_time_max</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_row_lock_waits" rel="nofollow">Innodb_row_lock_waits</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_rows_deleted" rel="nofollow">Innodb_rows_deleted</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_rows_inserted" rel="nofollow">Innodb_rows_inserted</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_rows_read" rel="nofollow">Innodb_rows_read</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_rows_updated" rel="nofollow">Innodb_rows_updated</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_spin_wait_delay" rel="nofollow">innodb_spin_wait_delay</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_stats_method" rel="nofollow">innodb_stats_method</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata" rel="nofollow">innodb_stats_on_metadata</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_stats_sample_pages" rel="nofollow">innodb_stats_sample_pages</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#option_mysqld_innodb-status-file" rel="nofollow">innodb-status-file</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_strict_mode" rel="nofollow">innodb_strict_mode</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Both</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_support_xa" rel="nofollow">innodb_support_xa</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Both</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_sync_spin_loops" rel="nofollow">innodb_sync_spin_loops</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_table_locks" rel="nofollow">innodb_table_locks</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Both</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_thread_concurrency" rel="nofollow">innodb_thread_concurrency</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_thread_sleep_delay" rel="nofollow">innodb_thread_sleep_delay</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_truncated_status_writes" rel="nofollow">Innodb_truncated_status_writes</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_use_native_aio" rel="nofollow">innodb_use_native_aio</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_use_sys_malloc" rel="nofollow">innodb_use_sys_malloc</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_version" rel="nofollow">innodb_version</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_write_io_threads" rel="nofollow">innodb_write_io_threads</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_timed_mutexes" rel="nofollow">timed_mutexes</a></td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Global</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td><a href="http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_unique_checks" rel="nofollow">unique_checks</a></td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 <td>Yes</td> 
 <td>&nbsp;</td> 
 <td>Both</td> 
 <td>Yes</td> 
 </tr> 
 </tbody> 
</table>

### `InnoDB` Command Options ###

* [`--ignore-builtin-innodb`][--ignore-builtin-innodb]
 
 <table> 
 <tbody> 
 <tr> 
 <td>Deprecated</td> 
 <td colspan="3">5.5.22</td> 
 </tr> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--ignore-builtin-innodb</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>ignore-builtin-innodb</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_ignore_builtin_innodb" rel="nofollow">ignore_builtin_innodb</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 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.5,`InnoDB` is the default storage engine and `InnoDB Plugin` is not used, so this option has no effect. As of MySQL 5.5.22, it is deprecated and its use results in a warning.
 * [`--innodb[=value]`][--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”][Section_5.1.8.1_ _Installing and Uninstalling Plugins].
 
 To disable `InnoDB`, use [ `--innodb=OFF`][--innodb_value] or [ `--skip-innodb`][--innodb_value]. In this case, because the default storage engine is[`InnoDB`][InnoDB], the server will not start unless you also use[`--default-storage-engine`][--default-storage-engine] to set the default to some other engine.
 * [`--innodb-status-file`][--innodb-status-file]
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb-status-file</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb-status-file</code></td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>OFF</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 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`][_SHOW ENGINE INNODB STATUS] to this file.
 
 By default, the file is not created. To create it, start [ **mysqld**][_mysqld] with the [ `--innodb-status-file=1`][--innodb-status-file] option. The file is deleted during normal shutdown.
 * [`--skip-innodb`][--innodb_value]
 
 Disable the `InnoDB` storage engine. See the description of[`--innodb`][--innodb_value].

### `InnoDB` System Variables ###

* `ignore_builtin_innodb`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Deprecated</td> 
 <td colspan="3">5.5.22</td> 
 </tr> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--ignore-builtin-innodb</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>ignore-builtin-innodb</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_ignore_builtin_innodb" rel="nofollow">ignore_builtin_innodb</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 See the description of [ `--ignore-builtin-innodb`][--ignore-builtin-innodb] under “`InnoDB` Command Options” earlier in this section.
 * `innodb_adaptive_flushing`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_adaptive_flushing=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_adaptive_flushing</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_adaptive_flushing" rel="nofollow">innodb_adaptive_flushing</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>ON</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 Specifies whether to dynamically adjust the rate of flushing [ dirty pages][dirty pages] in the `InnoDB` [ buffer pool][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.16.2.2, “Configuring the Rate of `InnoDB` Buffer Pool Flushing”][Section_14.16.2.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”][Section_8.5.7_ _Optimizing _InnoDB_ Disk I_O].
 * `innodb_adaptive_hash_index`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_adaptive_hash_index=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_adaptive_hash_index</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_adaptive_hash_index" rel="nofollow">innodb_adaptive_hash_index</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>ON</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 Whether the `InnoDB` [ adaptive hash index][adaptive hash index] is enabled or disabled. It may be desirable, depending on your workload, to dynamically enable or disable[adaptive hash indexing][adaptive hash index] to improve query performance. Because the adaptive hash index may not be useful for all workloads, conduct benchmarks with it both enabled and disabled, using realistic workloads. See[Section 14.5.13.5, “Adaptive Hash Indexes”][Section_14.5.13.5_ _Adaptive Hash Indexes] for details.
 
 This variable is enabled by default. As of MySQL 5.5, 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_additional_mem_pool_size`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_additional_mem_pool_size=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_additional_mem_pool_size</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_additional_mem_pool_size" rel="nofollow">innodb_additional_mem_pool_size</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>8388608</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>2097152 .. 4294967295</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The size in bytes of a memory pool `InnoDB` uses to store[data dictionary][] information and other internal data structures. The more tables you have in your application, the more memory you need to allocate here. 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`][innodb_use_sys_malloc] is enabled.
 * `innodb_autoextend_increment`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_autoextend_increment=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_autoextend_increment</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_autoextend_increment" rel="nofollow">innodb_autoextend_increment</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>64</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>1 .. 1000</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The increment size (in MB) for extending the size of an auto-extending [ system tablespace][system 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`][innodb_file_per_table_1]. Those files are auto-extending regardless of the value of[`innodb_autoextend_increment`][innodb_autoextend_increment]. The initial extensions are by small amounts, after which extensions occur in increments of 4MB.
 * `innodb_autoinc_lock_mode`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_autoinc_lock_mode=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_autoinc_lock_mode</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode" rel="nofollow">innodb_autoinc_lock_mode</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>1</code></td> 
 </tr> 
 <tr> 
 <td>Valid Values</td> 
 <td colspan="2"><code>0</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>1</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>2</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The [ lock mode][lock mode] to use for generating [ auto-increment][auto-increment] values. The permissible values are 0, 1, or 2, for “traditional”, “consecutive”, or“interleaved” lock mode, respectively.[Section 14.9.5, “`AUTO_INCREMENT` Handling in `InnoDB`”][Section_14.9.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_instances`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Introduced</td> 
 <td colspan="3">5.5.4</td> 
 </tr> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_buffer_pool_instances=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_buffer_pool_instances</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances" rel="nofollow">innodb_buffer_pool_instances</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>1</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>1 .. 64</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The number of regions that the [ `InnoDB`][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 1 gigabyte 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`][innodb_buffer_pool_instances] and [ `innodb_buffer_pool_size`][_innodb_buffer_pool_size] so that each buffer pool instance is at least 1 gigabyte.
 * `innodb_buffer_pool_size`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_buffer_pool_size=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_buffer_pool_size</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size" rel="nofollow">innodb_buffer_pool_size</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Platform Bit Size</td> 
 <td colspan="2"><code>32</code></td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>134217728</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>5242880 .. 2**32-1</code></td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Platform Bit Size</td> 
 <td colspan="2"><code>64</code></td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>134217728</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>5242880 .. 2**64-1</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The size in bytes of the [ buffer pool][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 (232\-1) on 32-bit systems and 18446744073709551615 (264\-1) on 64-bit systems. On 32-bit systems, the CPU architecture and operating system may impose a lower practical maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, setting[`innodb_buffer_pool_instances`][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:
 
 * Competition for physical memory might cause paging in the operating system.
 * `InnoDB` reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified size.
 * The address space must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses.
 * The time to initialize the buffer pool is roughly proportional to its size. On large installations, this initialization time might be significant. For example, on a modern Linux x86\_64 server, initialization of a 10GB buffer pool takes approximately 6 seconds. See [ Section 8.9.1, “The `InnoDB` Buffer Pool”][Section_8.9.1_ _The _InnoDB_ Buffer Pool].
 * `innodb_change_buffering`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_change_buffering=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_change_buffering</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_change_buffering" rel="nofollow">innodb_change_buffering</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values (&lt;= 5.5.3)</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>enumeration</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>inserts</code></td> 
 </tr> 
 <tr> 
 <td>Valid Values</td> 
 <td colspan="2"><code>inserts</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>none</code></td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values (&gt;= 5.5.4)</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>enumeration</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>all</code></td> 
 </tr> 
 <tr> 
 <td>Valid Values</td> 
 <td colspan="2"><code>inserts</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>deletes</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>purges</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>changes</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>all</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>none</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 Whether [ `InnoDB`][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.16.5, “Configuring `InnoDB` Change Buffering”][Section_14.16.5_ _Configuring _InnoDB_ Change Buffering].
 * `innodb_checksums`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_checksums</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_checksums</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_checksums" rel="nofollow">innodb_checksums</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>ON</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 `InnoDB` can use checksum validation on all pages read from the disk to ensure extra fault tolerance against broken hardware or data files. This validation is enabled by default. However, under some rare circumstances (such as when running benchmarks) this extra safety feature is unneeded and can be disabled with[`--skip-innodb-checksums`][_--innodb_checksums].
 * `innodb_commit_concurrency`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_commit_concurrency=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_commit_concurrency</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_commit_concurrency" rel="nofollow">innodb_commit_concurrency</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>0</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>0 .. 1000</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The number of [ threads][threads] that can [ commit][commit] at the same time. A value of 0 (the default) permits any number of [ transactions][transactions] to commit simultaneously.
 
 The value of [ `innodb_commit_concurrency`][_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_concurrency_tickets`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_concurrency_tickets=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_concurrency_tickets</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_concurrency_tickets" rel="nofollow">innodb_concurrency_tickets</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>500</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>1 .. 4294967295</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 Determines the number of [ threads][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`][_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 500.
 * `innodb_data_file_path`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_data_file_path=name</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_data_file_path</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_data_file_path" rel="nofollow">innodb_data_file_path</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>string</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>ibdata1:10M:autoextend</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The paths to individual `InnoDB` [ data files][data files] and their sizes. The full directory path to each data file is formed by concatenating[`innodb_data_home_dir`][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`][_innodb_data_file_path], the default behavior is to create a single auto-extending data file, slightly larger than 10MB, 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][tablespace] files, see [ Section 14.6, “`InnoDB` Configuration”][Section_14.6_ _InnoDB_ Configuration].
 * `innodb_data_home_dir`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_data_home_dir=path</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_data_home_dir</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_data_home_dir" rel="nofollow">innodb_data_home_dir</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>directory name</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The common part of the directory path for all `InnoDB`[data files][] in the [ system tablespace][system tablespace]. This setting does not affect the location of [ file-per-table][file-per-table] tablespaces when [ `innodb_file_per_table`][innodb_file_per_table_1] 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_data_file_path].
 * `innodb_doublewrite`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb-doublewrite</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_doublewrite</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_doublewrite" rel="nofollow">innodb_doublewrite</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 If this variable is enabled (the default), `InnoDB` stores all data twice, first to the[doublewrite buffer][], and then to the actual [ data files][data files]. This variable can be turned off with [ `--skip-innodb_doublewrite`][_--skip-innodb_doublewrite] for benchmarks or cases when top performance is needed rather than concern for data integrity or possible failures.
 * `innodb_fast_shutdown`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_fast_shutdown[=#]</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_fast_shutdown</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_fast_shutdown" rel="nofollow">innodb_fast_shutdown</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>1</code></td> 
 </tr> 
 <tr> 
 <td>Valid Values</td> 
 <td colspan="2"><code>0</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>1</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>2</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The `InnoDB` [ shutdown][shutdown] mode. If the value is 0, `InnoDB` does a [ slow shutdown][slow shutdown], a full [ purge][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`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_file_format=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_file_format</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_format" rel="nofollow">innodb_file_format</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values (&lt;= 5.5.6)</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>string</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>Barracuda</code></td> 
 </tr> 
 <tr> 
 <td>Valid Values</td> 
 <td colspan="2"><code>Antelope</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>Barracuda</code></td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values (&gt;= 5.5.7)</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>string</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>Antelope</code></td> 
 </tr> 
 <tr> 
 <td>Valid Values</td> 
 <td colspan="2"><code>Antelope</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>Barracuda</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The [ file format][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`][innodb_file_per_table_1] must be enabled. The [ Barracuda][Barracuda] file format is required for certain InnoDB features such as table [ compression][compression].
 
 Be aware that [ `ALTER TABLE`][_ALTER TABLE] operations that recreate `InnoDB` tables (`ALTER OFFLINE`) will use the current`innodb_file_format` setting (the conditions outlined above still apply).
 * `innodb_file_format_check`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_file_format_check=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_file_format_check</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_format_check" rel="nofollow">innodb_file_format_check</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>string</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>Antelope</code></td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values (&gt;= 5.5.1)</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>string</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>Barracuda</code></td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values (&gt;= 5.5.5)</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>ON</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 As of MySQL 5.5.5, this variable can be set to 1 or 0 at server startup to enable or disable whether`InnoDB` checks the [ file format][file format] tag in the [ system tablespace][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`][_innodb_file_format_max] to the file format tag.
 
 Before MySQL 5.5.5, this variable can be set to 1 or 0 at server startup to enable or disable whether`InnoDB` checks the file format tag in the shared tablespace. If the tag is checked and is higher than that supported by the current version of`InnoDB`, an error occurs and `InnoDB` does not start. If the tag is not higher,`InnoDB` sets the value of [ `innodb_file_format_check`][_innodb_file_format_check] to the file format tag, which is the value seen at runtime.
 
 Note
 
 Despite the default value sometimes being displayed as `ON` or`OFF`, always use the numeric values 1 or 0 to turn this option on or off in your configuration file or command line.
 * `innodb_file_format_max`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Introduced</td> 
 <td colspan="3">5.5.5</td> 
 </tr> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_file_format_max=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_file_format_max</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_format_max" rel="nofollow">innodb_file_format_max</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>string</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>Antelope</code></td> 
 </tr> 
 <tr> 
 <td>Valid Values</td> 
 <td colspan="2"><code>Antelope</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>Barracuda</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 At server startup, `InnoDB` sets the value of this variable to the[file format][] tag in the [ system tablespace][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`][_innodb_file_format_max] to that format.
 
 This variable was added in MySQL 5.5.5.
 * `innodb_file_per_table`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_file_per_table</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_file_per_table</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table" rel="nofollow">innodb_file_per_table</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values (&lt;= 5.5.6)</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>ON</code></td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values (&gt;= 5.5.7)</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>OFF</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 When `innodb_file_per_table` is disabled, `InnoDB` stores the data for all tables and indexes in the [ ibdata files][ibdata files] that make up the [ system tablespace][system tablespace]. This setting reduces the performance overhead of filesystem operations for operations such as[`DROP TABLE`][DROP TABLE] or [ `TRUNCATE TABLE`][_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.
 
 When `innodb_file_per_table` is enabled, `InnoDB` stores data and indexes for each newly created table in a separate [ `.ibd` file][_.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][compression]. See [ Section 14.8.2, “InnoDB File-Per-Table Mode”][Section_14.8.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`][innodb_file_per_table_1] also means that an [ `ALTER TABLE`][_ALTER TABLE] operation will move `InnoDB` table from the system tablespace to an individual `.ibd` file in cases where [ `ALTER TABLE`][_ALTER TABLE] recreates the table (`ALTER OFFLINE`).
 
 In MySQL 5.5 and higher, the configuration parameter [ `innodb_file_per_table`][innodb_file_per_table_1] is dynamic, and can be set`ON` or `OFF` using `SET GLOBAL`. Previously, the only way to set this parameter was in the MySQL[configuration file][] (`my.cnf` or `my.ini`), and changing it required 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_trx_commit`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_flush_log_at_trx_commit[=#]</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_flush_log_at_trx_commit</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit" rel="nofollow">innodb_flush_log_at_trx_commit</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>enumeration</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>1</code></td> 
 </tr> 
 <tr> 
 <td>Valid Values</td> 
 <td colspan="2"><code>0</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>1</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>2</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 Controls the balance between strict [ ACID][ACID] compliance for [ commit][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 one second worth of[transactions][] in a crash.
 
 * The default value of 1 is required for full ACID compliance. With this value, the[log buffer][] is written out to the [ log file][log file] at each transaction commit and the [ flush][flush] to disk operation is performed on the log file.
 * With a value of 0, any [ **mysqld**][_mysqld] process crash can erase the last second of transactions. 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.
 * With a value of 2, an operating system crash or a power outage can erase the last second of commit records. 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.
 * `InnoDB`'s [ crash recovery][crash recovery] works regardless of the value. Transactions are either applied entirely or erased entirely.
 
 For the greatest possible durability and consistency in a replication setup using`InnoDB` with transactions, use `innodb_flush_log_at_trx_commit=1` and `sync_binlog=1` in your master server`my.cnf` file.
 
 Caution
 
 Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell[**mysqld**][_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`InnoDB` data. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try 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.
 * `innodb_flush_method`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_flush_method=name</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_flush_method</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_method" rel="nofollow">innodb_flush_method</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type (Linux)</td> 
 <td colspan="2"><code>string</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>fdatasync</code></td> 
 </tr> 
 <tr> 
 <td>Valid Values</td> 
 <td colspan="2"><code>fdatasync</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>O_DSYNC</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>O_DIRECT</code></td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type (HP-UX)</td> 
 <td colspan="2"><code>string</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>fdatasync</code></td> 
 </tr> 
 <tr> 
 <td>Valid Values</td> 
 <td colspan="2"><code>fdatasync</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>O_DSYNC</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>O_DIRECT</code></td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type (Solaris)</td> 
 <td colspan="2"><code>string</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>fdatasync</code></td> 
 </tr> 
 <tr> 
 <td>Valid Values</td> 
 <td colspan="2"><code>fdatasync</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>O_DSYNC</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>O_DIRECT</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 Controls the system calls used to [ flush][flush] data to the `InnoDB` [ data files][data files] and [ log files][log file], which can influence I/O throughput. This variable is relevant only for Unix and Linux systems. On Windows systems, the flush method is always`async_unbuffered` and cannot be changed.
 
 By default, `InnoDB` uses the `fsync()` system call to flush both the data and log files. If [ `innodb_flush_method`][_innodb_flush_method] option is set to `O_DSYNC`, `InnoDB` uses `O_SYNC` to open and flush the log files, and`fsync()` to flush the data files. If `O_DIRECT` is specified (available on some GNU/Linux versions, FreeBSD, and Solaris),`InnoDB` uses `O_DIRECT` (or`directio()` on Solaris) to open the data files, and uses`fsync()` to flush both the data and log files. Note that`InnoDB` uses `fsync()` instead of`fdatasync()`, and it does not use `O_DSYNC` by default because there have been problems with it on many varieties of Unix.
 
 Depending on hardware configuration, setting `innodb_flush_method` to`O_DIRECT` can either have either a positive or negative effect on performance. Benchmark your particular configuration to decide which setting to use. The mix of read and write operations in your workload can also affect which setting performs better for you. 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 filesystem 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 the same type of hardware and workload that reflects your production environment. For general I/O tuning advice, see[Section 8.5.7, “Optimizing `InnoDB` Disk I/O”][Section_8.5.7_ _Optimizing _InnoDB_ Disk I_O].
 
 Formerly, a value of `fdatasync` also specified the default behavior. This value was removed, due to confusion that a value of`fdatasync` caused `fsync()` system calls rather than`fdatasync()` for flushing. To obtain the default value now, do not set any value for[`innodb_flush_method`][_innodb_flush_method] at startup.
 * [`innodb_force_load_corrupted`][innodb_force_load_corrupted]
 
 <table> 
 <tbody> 
 <tr> 
 <td>Introduced</td> 
 <td colspan="3">5.5.18</td> 
 </tr> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_force_load_corrupted</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_force_load_corrupted</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_force_load_corrupted" rel="nofollow">innodb_force_load_corrupted</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>OFF</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 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`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_force_recovery=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_force_recovery</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_force_recovery" rel="nofollow">innodb_force_recovery</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>0</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>0 .. 6</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The [ crash recovery][crash recovery] mode, typically only changed in serious troubleshooting situations. Possible values are from 0 to 6. The meanings of these values are described in[Section 14.21.2, “Starting `InnoDB` on a Corrupted Database”][Section_14.21.2_ _Starting _InnoDB_ on a Corrupted Database].
 
 Warning
 
 Only set this variable greater than 0 in an emergency situation, to dump your tables from a corrupt database. As a safety measure,`InnoDB` prevents any changes to its data when this variable is greater than 0. This restriction also prohibits some queries that use`WHERE` or `ORDER BY` clauses, because high values can prevent queries from using indexes, to guard against possible corrupt index data.
 * `innodb_io_capacity`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_io_capacity=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_io_capacity</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_io_capacity" rel="nofollow">innodb_io_capacity</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Platform Bit Size</td> 
 <td colspan="2"><code>32</code></td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>200</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>100 .. 2**32-1</code></td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Platform Bit Size</td> 
 <td colspan="2"><code>64</code></td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>200</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>100 .. 2**64-1</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The [ `innodb_io_capacity`][_innodb_io_capacity] parameter sets an upper limit on the I/O activity performed by the`InnoDB` background tasks, such as [ flushing][flush] pages from the [ buffer pool][buffer pool] and merging data from the [ insert buffer][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 of `100`.
 
 The [ `innodb_io_capacity`][_innodb_io_capacity] limit is a total limit for all buffer pool instances. When dirty pages are flushed, the[`innodb_io_capacity`][_innodb_io_capacity] limit is divided equally among buffer pool instances.
 
 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`][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`][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, and 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.16.9, “Configuring the `InnoDB` Master Thread I/O Rate”][Section_14.16.9_ _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”][Section_8.5.7_ _Optimizing _InnoDB_ Disk I_O].
 * `innodb_large_prefix`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Introduced</td> 
 <td colspan="3">5.5.14</td> 
 </tr> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_large_prefix</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_large_prefix</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_large_prefix" rel="nofollow">innodb_large_prefix</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>OFF</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 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`][innodb_file_format_barracuda] and [ `innodb_file_per_table=true`][innodb_file_per_table_1].) See [ Section 14.9.7, “Limits on `InnoDB` Tables”][Section_14.9.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. It does introduce a new error possibility. When this setting is enabled, attempting to create an index prefix with a key length greater than 3072 for a`REDUNDANT` or `COMPACT` table causes an[`ER_INDEX_COLUMN_TOO_LONG`][ER_INDEX_COLUMN_TOO_LONG] error.
 * `innodb_lock_wait_timeout`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_lock_wait_timeout=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_lock_wait_timeout</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout" rel="nofollow">innodb_lock_wait_timeout</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global, Session</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>50</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>1 .. 1073741824</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The length of time in seconds an `InnoDB` [ transaction][transactions] waits for a [ row lock][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][rolled back] (not the entire transaction). To have the entire transaction roll back, start the server with the[`--innodb_rollback_on_timeout`][--innodb_rollback_on_timeout] option. See also [ Section 14.21.4, “`InnoDB` Error Handling”][Section_14.21.4_ _InnoDB_ Error Handling].
 
 You might decrease this value for highly interactive applications or [ OLTP][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][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][deadlocks], because `InnoDB` detects them immediately and rolls back one of the deadlocked transactions.
 
 As of MySQL 5.5, [ `innodb_lock_wait_timeout`][_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`][_innodb_lock_wait_timeout], which affects only that client.
 * `innodb_locks_unsafe_for_binlog`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_locks_unsafe_for_binlog</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_locks_unsafe_for_binlog</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog" rel="nofollow">innodb_locks_unsafe_for_binlog</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>OFF</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 This variable affects how `InnoDB` uses gap locking for searches and index scans. Normally,`InnoDB` uses an algorithm called *next-key locking* that combines index-row locking with gap locking. `InnoDB` performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record `R` in an index, another session cannot insert a new index record in the gap immediately before`R` in the index order. See [ Section 14.5.6, “`InnoDB` Record, Gap, and Next-Key Locks”][Section_14.5.6_ _InnoDB_ Record_ Gap_ and Next-Key Locks].
 
 By default, the value of `innodb_locks_unsafe_for_binlog` is 0 (disabled), which means that gap locking is enabled:`InnoDB` uses next-key locks for searches and index scans. To enable the variable, set it to 1. This causes gap locking to be disabled:`InnoDB` uses only index-record locks for searches and index scans.
 
 Enabling `innodb_locks_unsafe_for_binlog` does not disable the use of gap locking for foreign-key constraint checking or duplicate-key checking.
 
 The effect of enabling `innodb_locks_unsafe_for_binlog` is similar to but not identical to setting the transaction isolation level to[`READ COMMITTED`][READ COMMITTED]:
 
 * Enabling [ `innodb_locks_unsafe_for_binlog`][_innodb_locks_unsafe_for_binlog] is a global setting and affects all sessions, whereas the isolation level can be set globally for all sessions, or individually per session.
 * [`innodb_locks_unsafe_for_binlog`][_innodb_locks_unsafe_for_binlog] can be set only at server startup, whereas the isolation level can be set at startup or changed at runtime.
 
 [`READ COMMITTED`][READ COMMITTED] therefore offers finer and more flexible control than[`innodb_locks_unsafe_for_binlog`][_innodb_locks_unsafe_for_binlog]. For additional details about the effect of isolation level on gap locking, see[Section 13.3.6, “`SET TRANSACTION` Syntax”][Section_13.3.6_ _SET TRANSACTION_ Syntax].
 
 Enabling `innodb_locks_unsafe_for_binlog` may cause phantom problems because other sessions can insert new rows into the gaps when gap locking is disabled. Suppose that there is an index on the`id` column of the `child` table and that you want to read and lock all rows from the table having an identifier value larger than 100, with the intention of updating some column in the selected rows later:
 
 SELECT * FROM child WHERE id > 100 FOR UPDATE;
 
 The query scans the index starting from the first record where `id` is greater than 100. If the locks set on the index records in that range do not lock out inserts made in the gaps, another session can insert a new row into the table. Consequently, if you were to execute the same[`SELECT`][SELECT] again within the same transaction, you would see a new row in the result set returned by the query. This also means that if new items are added to the database,`InnoDB` does not guarantee serializability. Therefore, if`innodb_locks_unsafe_for_binlog` is enabled, `InnoDB` guarantees at most an isolation level of [ `READ COMMITTED`][READ COMMITTED]. (Conflict serializability is still guaranteed.) For additional information about phantoms, see[Section 14.5.7, “Avoiding the Phantom Problem Using Next-Key Locking”][Section_14.5.7_ _Avoiding the Phantom Problem Using Next-Key Locking].
 
 Enabling `innodb_locks_unsafe_for_binlog` has additional effects:
 
 * For [ `UPDATE`][_UPDATE] or [ `DELETE`][_DELETE] statements, `InnoDB` holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the`WHERE` condition. This greatly reduces the probability of deadlocks, but they can still happen.
 * For [ `UPDATE`][_UPDATE] statements, if a row is already locked, `InnoDB` performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the`WHERE` condition of the [ `UPDATE`][_UPDATE]. If the row matches (must be updated), MySQL reads the row again and this time`InnoDB` either locks it or waits for a lock on it.
 
 Consider the following example, beginning with this table:
 
 CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
 INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
 COMMIT;
 
 In this case, table has no indexes, so searches and index scans use the hidden clustered index for record locking (see[Section 14.5.13.2, “Clustered and Secondary Indexes”][Section_14.5.13.2_ _Clustered and Secondary Indexes]).
 
 Suppose that one client performs an [ `UPDATE`][_UPDATE] using these statements:
 
 SET autocommit = 0;
 UPDATE t SET b = 5 WHERE b = 3;
 
 Suppose also that a second client performs an [ `UPDATE`][_UPDATE] by executing these statements following those of the first client:
 
 SET autocommit = 0;
 UPDATE t SET b = 4 WHERE b = 2;
 
 As [ `InnoDB`][InnoDB] executes each [ `UPDATE`][_UPDATE], it first acquires an exclusive lock for each row, and then determines whether to modify it. If[`InnoDB`][InnoDB] does not modify the row and `innodb_locks_unsafe_for_binlog` is enabled, it releases the lock. Otherwise,[`InnoDB`][InnoDB] retains the lock until the end of the transaction. This affects transaction processing as follows.
 
 If `innodb_locks_unsafe_for_binlog` is disabled, the first[`UPDATE`][_UPDATE] acquires x-locks and does not release any of them:
 
 x-lock(1,2); retain x-lock
 x-lock(2,3); update(2,3) to (2,5); retain x-lock
 x-lock(3,2); retain x-lock
 x-lock(4,3); update(4,3) to (4,5); retain x-lock
 x-lock(5,2); retain x-lock
 
 The second [ `UPDATE`][_UPDATE] blocks as soon as it tries to acquire any locks (because first update has retained locks on all rows), and does not proceed until the first[`UPDATE`][_UPDATE] commits or rolls back:
 
 x-lock(1,2); block and wait for first UPDATE to commit or roll back
 
 If `innodb_locks_unsafe_for_binlog` is enabled, the first[`UPDATE`][_UPDATE] acquires x-locks and releases those for rows that it does not modify:
 
 x-lock(1,2); unlock(1,2)
 x-lock(2,3); update(2,3) to (2,5); retain x-lock
 x-lock(3,2); unlock(3,2)
 x-lock(4,3); update(4,3) to (4,5); retain x-lock
 x-lock(5,2); unlock(5,2)
 
 For the second `UPDATE`, `InnoDB` does a“semi-consistent” read, returning the latest committed version of each row to MySQL so that MySQL can determine whether the row matches the`WHERE` condition of the [ `UPDATE`][_UPDATE]:
 
 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
 * `innodb_log_buffer_size`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_log_buffer_size=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_log_buffer_size</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_buffer_size" rel="nofollow">innodb_log_buffer_size</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>8388608</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>262144 .. 4294967295</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The size in bytes of the buffer that `InnoDB` uses to write to the[log files][log file] on disk. The default value is 8MB. A large [ log buffer][log buffer] enables large [ transactions][transactions] to run without a need to write the log to disk before the transactions[commit][]. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. For general I/O tuning advice, see[Section 8.5.7, “Optimizing `InnoDB` Disk I/O”][Section_8.5.7_ _Optimizing _InnoDB_ Disk I_O].
 * `innodb_log_file_size`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_log_file_size=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_log_file_size</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_file_size" rel="nofollow">innodb_log_file_size</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>5242880</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>1048576 .. 4GB / innodb_log_files_in_group</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The size in bytes of each [ log file][log file] in a [ log group][log group]. The combined size of log files (`innodb_log_file_size` \*[`innodb_log_files_in_group`][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, where*`N`* is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. Larger log files also make[crash recovery][] slower, although improvements to recovery performance in MySQL 5.5 and higher make the log file size less of a consideration. For general I/O tuning advice, see[Section 8.5.7, “Optimizing `InnoDB` Disk I/O”][Section_8.5.7_ _Optimizing _InnoDB_ Disk I_O].
 * `innodb_log_files_in_group`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_log_files_in_group=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_log_files_in_group</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_files_in_group" rel="nofollow">innodb_log_files_in_group</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>2</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>2 .. 100</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The number of [ log files][log file] in the [ log group][log group]. `InnoDB` writes to the files in a circular fashion. The default (and recommended) value is 2. The location of these files is specified by[`innodb_log_group_home_dir`][innodb_log_group_home_dir].
 * `innodb_log_group_home_dir`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_log_group_home_dir=path</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_log_group_home_dir</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_group_home_dir" rel="nofollow">innodb_log_group_home_dir</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>directory name</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The directory path to the `InnoDB` [ redo log][redo log] files, whose number is specified by [ `innodb_log_files_in_group`][innodb_log_files_in_group]. If you do not specify any`InnoDB` log variables, the default is to create two files named`ib_logfile0` and `ib_logfile1` in the MySQL data directory. Their size is given by the size of the[`innodb_log_file_size`][innodb_log_file_size] system variable.
 * `innodb_max_dirty_pages_pct`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_max_dirty_pages_pct=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_max_dirty_pages_pct</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct" rel="nofollow">innodb_max_dirty_pages_pct</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>75</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>0 .. 99</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 `InnoDB` tries to [ flush][flush] data from the [ buffer pool][buffer pool] so that the percentage of [ dirty pages][dirty pages] does not exceed this value. Specify an integer in the range from 0 to 99. The default value is 75.
 
 For additional information about this variable, see [ Section 14.16.2.2, “Configuring the Rate of `InnoDB` Buffer Pool Flushing”][Section_14.16.2.2_ _Configuring the Rate of _InnoDB_ Buffer Pool Flushing]. For general I/O tuning advice, see[Section 8.5.7, “Optimizing `InnoDB` Disk I/O”][Section_8.5.7_ _Optimizing _InnoDB_ Disk I_O].
 * `innodb_max_purge_lag`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_max_purge_lag=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_max_purge_lag</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_max_purge_lag" rel="nofollow">innodb_max_purge_lag</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>0</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>0 .. 4294967295</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 This variable controls how to delay [ `INSERT`][_INSERT], [ `UPDATE`][_UPDATE], and [ `DELETE`][_DELETE] operations when [ purge][purge] operations are lagging (see [ Section 14.5.12, “`InnoDB` Multi-Versioning”][Section_14.5.12_ _InnoDB_ Multi-Versioning]). The default value is 0 (no delays).
 
 The `InnoDB` transaction system maintains a list of transactions that have index records delete-marked by[`UPDATE`][_UPDATE] or [ `DELETE`][_DELETE] operations. The length of this list represents the*`purge_lag`* value. When*`purge_lag`* exceeds[`innodb_max_purge_lag`][innodb_max_purge_lag], each [ `INSERT`][_INSERT], [ `UPDATE`][_UPDATE], and [ `DELETE`][_DELETE] operation is delayed by ((*`purge_lag`*/[`innodb_max_purge_lag`][innodb_max_purge_lag])×10)–5 milliseconds. The delay is computed in the beginning of a purge batch, every ten seconds. The operations are not delayed if purge cannot run because of an old[consistent read][] view that could see the rows to be purged.
 
 A typical setting for a problematic workload might be 1 million, assuming that transactions are small, only 100 bytes in size, and it is permissible to have 100MB of unpurged`InnoDB` table rows.
 
 The lag value is displayed as the history list length in the `TRANSACTIONS` section of InnoDB Monitor output. For example, if the output includes the following lines, the lag value is 20:
 
 ------------
 TRANSACTIONS
 ------------
 Trx id counter 0 290328385
 Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
 History list length 20
 
 For general I/O tuning advice, see [ Section 8.5.7, “Optimizing `InnoDB` Disk I/O”][Section_8.5.7_ _Optimizing _InnoDB_ Disk I_O].
 * `innodb_mirrored_log_groups`
 
 Has no effect.
 * `innodb_old_blocks_pct`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_old_blocks_pct=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_old_blocks_pct</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_old_blocks_pct" rel="nofollow">innodb_old_blocks_pct</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>37</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>5 .. 95</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 Specifies the approximate percentage of the `InnoDB`[buffer pool][] used for the old block [ sublist][sublist]. The range of values is 5 to 95. The default value is 37 (that is, 3/8 of the pool). See[Section 14.16.2.3, “Making the Buffer Pool Scan Resistant”][Section_14.16.2.3_ _Making the Buffer Pool Scan Resistant] for more information. See[Section 8.9.1, “The `InnoDB` Buffer Pool”][Section_8.9.1_ _The _InnoDB_ Buffer Pool] for information about buffer pool management, such as the[LRU][] algorithm and [ eviction][eviction] policies.
 * `innodb_old_blocks_time`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_old_blocks_time=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_old_blocks_time</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_old_blocks_time" rel="nofollow">innodb_old_blocks_time</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>0</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>0 .. 2**32-1</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 Non-zero values protect against the [ buffer pool][buffer pool] being filled up by data that is referenced only for a brief period, such as during a[full table scan][]. Increasing this value offers more protection against full table scans interfering with data cached in the buffer pool.
 
 Specifies how long in milliseconds (ms) a block inserted into the old [ sublist][sublist] must stay there after its first access before it can be moved to the new sublist. If the value is 0, a block inserted into the old sublist moves immediately to the new sublist the first time it is accessed, no matter how soon after insertion the access occurs. If the value is greater than 0, blocks remain in the old sublist until an access occurs at least that many ms after the first access. For example, a value of 1000 causes blocks to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist.
 
 This variable is often used in combination with [ `innodb_old_blocks_pct`][_innodb_old_blocks_pct]. See [ Section 14.16.2.3, “Making the Buffer Pool Scan Resistant”][Section_14.16.2.3_ _Making the Buffer Pool Scan Resistant] for more information. See[Section 8.9.1, “The `InnoDB` Buffer Pool”][Section_8.9.1_ _The _InnoDB_ Buffer Pool] for information about buffer pool management, such as the[LRU][] algorithm and [ eviction][eviction] policies.
 * `innodb_open_files`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_open_files=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_open_files</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_open_files" rel="nofollow">innodb_open_files</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>300</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>10 .. 4294967295</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 This variable is relevant only if you use multiple `InnoDB`[tablespaces][tablespace]. It specifies the maximum number of [ `.ibd` files][_.ibd_ file] that MySQL can keep open at one time. The minimum value is 10. The default value is 300.
 
 The file descriptors used for `.ibd` files are for`InnoDB` tables only. They are independent of those specified by the[`--open-files-limit`][--open-files-limit] server option, and do not affect the operation of the table cache.
 * `innodb_print_all_deadlocks`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Introduced</td> 
 <td colspan="3">5.5.30</td> 
 </tr> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_print_all_deadlocks=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_print_all_deadlocks</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks" rel="nofollow">innodb_print_all_deadlocks</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>OFF</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 When this option is enabled, information about all [ deadlocks][deadlocks] in `InnoDB` user transactions is recorded in the`mysqld` [ error log][error log]. Otherwise, you see information about only the last deadlock, using the`SHOW ENGINE INNODB STATUS` command. An occasional `InnoDB` deadlock is not necessarily an issue, because `InnoDB` detects the condition immediately, and rolls back one of the transactions automatically. You might use this option to troubleshoot why deadlocks are happening if an application does not have appropriate error-handling logic to detect the rollback and retry its operation. A large number of deadlocks might indicate the need to restructure transactions that issue[DML][] or `SELECT ... FOR UPDATE` statements for multiple tables, so that each transaction accesses the tables in the same order, thus avoiding the deadlock condition.
 * `innodb_purge_batch_size`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Introduced</td> 
 <td colspan="3">5.5.4</td> 
 </tr> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_purge_batch_size=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_purge_batch_size</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_purge_batch_size" rel="nofollow">innodb_purge_batch_size</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values (&gt;= 5.5.4)</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>20</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>1 .. 5000</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The granularity of changes, expressed in units of [ redo log][redo log] records, that trigger a [ purge][purge] operation, flushing the changed [ buffer pool][buffer pool] blocks to disk. This option is intended for tuning performance in combination with the setting`innodb_purge_threads=n`, and typical users do not need to modify it.
 * `innodb_purge_threads`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Introduced</td> 
 <td colspan="3">5.5.4</td> 
 </tr> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_purge_threads=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_purge_threads</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_purge_threads" rel="nofollow">innodb_purge_threads</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values (&gt;= 5.5.4)</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>0</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>0 .. 1</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The number of background threads devoted to the InnoDB [ purge][purge] operation. Currently, can only be 0 (the default) or 1. The default value of 0 signifies that the purge operation is performed as part of the[master thread][]. Running the purge operation in its own thread can reduce internal contention within InnoDB, improving scalability. Currently, the performance gain might be minimal because the background thread might encounter different kinds of contention than before. This feature primarily lays the groundwork for future performance work.
 * `innodb_random_read_ahead`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Introduced</td> 
 <td colspan="3">5.5.16</td> 
 </tr> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_random_read_ahead=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_random_read_ahead</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_random_read_ahead" rel="nofollow">innodb_random_read_ahead</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>OFF</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 Enables the random [ read-ahead][read-ahead] technique for optimizing `InnoDB` I/O. This is a setting that was originally on by default, then was removed in MySQL 5.5, and now is available but turned off by default. See[Section 14.16.2.1, “Configuring `InnoDB` Buffer Pool Prefetching (Read-Ahead)”][Section_14.16.2.1_ _Configuring _InnoDB_ Buffer Pool Prefetching _Read-Ahead] for details about the performance considerations for the different types of read-ahead requests. For general I/O tuning advice, see[Section 8.5.7, “Optimizing `InnoDB` Disk I/O”][Section_8.5.7_ _Optimizing _InnoDB_ Disk I_O].
 * `innodb_read_ahead_threshold`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_read_ahead_threshold=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_read_ahead_threshold</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_read_ahead_threshold" rel="nofollow">innodb_read_ahead_threshold</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>56</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>0 .. 64</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 Controls the sensitivity of linear [ read-ahead][read-ahead] that `InnoDB` uses to prefetch pages into the[buffer pool][]. If `InnoDB` reads at least `innodb_read_ahead_threshold` pages sequentially from an [ extent][extent] (64 pages), it initiates an asynchronous read for the entire following extent. The permissible range of values is 0 to 64. The default is 56:`InnoDB` must read at least 56 pages sequentially from an extent to initiate an asynchronous read for the following extent.
 
 Knowing how many pages are read through this read-ahead mechanism, and how many of them are evicted from the buffer pool without ever being accessed, can be useful to help fine-tune the[`innodb_read_ahead_threshold`][innodb_read_ahead_threshold] parameter. As of MySQL 5.5,[`SHOW ENGINE INNODB STATUS`][_SHOW ENGINE INNODB STATUS] output displays counter information from the[`Innodb_buffer_pool_read_ahead`][Innodb_buffer_pool_read_ahead] and [ `Innodb_buffer_pool_read_ahead_evicted`][_Innodb_buffer_pool_read_ahead_evicted] global status variables. These variables indicate the number of pages brought into the[buffer pool][] by read-ahead requests, and the number of such pages [ evicted][eviction] from the buffer pool without ever being accessed respectively. These counters provide global values since the last server restart.
 
 `SHOW ENGINE INNODB STATUS` also shows the rate at which the read-ahead pages are read in and the rate at which such pages are evicted without being accessed. The per-second averages are based on the statistics collected since the last invocation of `SHOW ENGINE INNODB STATUS` and are displayed in the`BUFFER POOL AND MEMORY` section of the output.
 
 See [ Section 14.16.2.1, “Configuring `InnoDB` Buffer Pool Prefetching (Read-Ahead)”][Section_14.16.2.1_ _Configuring _InnoDB_ Buffer Pool Prefetching _Read-Ahead] for more information. For general I/O tuning advice, see[Section 8.5.7, “Optimizing `InnoDB` Disk I/O”][Section_8.5.7_ _Optimizing _InnoDB_ Disk I_O].
 * `innodb_read_io_threads`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_read_io_threads=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_read_io_threads</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_read_io_threads" rel="nofollow">innodb_read_io_threads</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>4</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>1 .. 64</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The number of I/O threads for read operations in `InnoDB`. The default value is 4. Its counterpart for write threads is[`innodb_write_io_threads`][innodb_write_io_threads]. See [ Section 14.16.7, “Configuring the Number of Background `InnoDB` I/O Threads”][Section_14.16.7_ _Configuring the Number of Background _InnoDB_ I_O Threads] for more information. For general I/O tuning advice, see[Section 8.5.7, “Optimizing `InnoDB` Disk I/O”][Section_8.5.7_ _Optimizing _InnoDB_ Disk I_O].
 
 Note
 
 On Linux systems, running multiple MySQL servers (typically more than 12) with default settings for`innodb_read_io_threads`, [ `innodb_write_io_threads`][innodb_write_io_threads], and the Linux `aio-max-nr` setting can exceed system limits. Ideally, increase the `aio-max-nr` setting; as a workaround, you might reduce the settings for one or both of the MySQL configuration options.
 * `innodb_replication_delay`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_replication_delay=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_replication_delay</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_replication_delay" rel="nofollow">innodb_replication_delay</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>0</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>0 .. 4294967295</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The replication thread delay (in ms) on a slave server if [ `innodb_thread_concurrency`][_innodb_thread_concurrency] is reached.
 * `innodb_rollback_on_timeout`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_rollback_on_timeout</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_rollback_on_timeout</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_rollback_on_timeout" rel="nofollow">innodb_rollback_on_timeout</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>OFF</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 In MySQL 5.5, `InnoDB` [ rolls back][rolled back] only the last statement on a transaction timeout by default. If [ `--innodb_rollback_on_timeout`][--innodb_rollback_on_timeout] is specified, a transaction timeout causes`InnoDB` to abort and roll back the entire transaction (the same behavior as in MySQL 4.1).
 * `innodb_rollback_segments`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Introduced</td> 
 <td colspan="3">5.5.11</td> 
 </tr> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_rollback_segments=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_rollback_segments</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_rollback_segments" rel="nofollow">innodb_rollback_segments</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>128</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>1 .. 128</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 Defines how many of the [ rollback segments][rollback segments] in the [ system tablespace][system tablespace] that InnoDB uses within a [ transaction][transactions]. You might reduce this value from its default of 128 if a smaller number of rollback segments performs better for your workload.
 * `innodb_spin_wait_delay`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_spin_wait_delay=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_spin_wait_delay</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_spin_wait_delay" rel="nofollow">innodb_spin_wait_delay</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>6</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>0 .. 4294967295</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The maximum delay between polls for a [ spin][spin] lock. The low-level implementation of this mechanism varies depending on the combination of hardware and operating system, so the delay does not correspond to a fixed time interval. The default value is 6. See[Section 14.16.11, “Configuring Spin Lock Polling”][Section_14.16.11_ _Configuring Spin Lock Polling] for more information.
 * `innodb_stats_method`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Introduced</td> 
 <td colspan="3">5.5.10</td> 
 </tr> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_stats_method=name</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_stats_method</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_stats_method" rel="nofollow">innodb_stats_method</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>enumeration</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>nulls_equal</code></td> 
 </tr> 
 <tr> 
 <td>Valid Values</td> 
 <td colspan="2"><code>nulls_equal</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>nulls_unequal</code></td> 
 </tr> 
 <tr> 
 <td colspan="2"><code>nulls_ignored</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 How the server treats `NULL` values when collecting[statistics][] about the distribution of index values for `InnoDB` tables. This variable has three possible values,`nulls_equal`, `nulls_unequal`, and`nulls_ignored`. For `nulls_equal`, all`NULL` index values are considered equal and form a single value group that has a size equal to the number of`NULL` values. For `nulls_unequal`,`NULL` values are considered unequal, and each `NULL` forms a distinct value group of size 1. For `nulls_ignored`,`NULL` values 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”][Section_8.3.7_ _InnoDB_ and _MyISAM_ Index Statistics Collection].
 * `innodb_stats_on_metadata`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Introduced</td> 
 <td colspan="3">5.5.4</td> 
 </tr> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_stats_on_metadata</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_stats_on_metadata</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata" rel="nofollow">innodb_stats_on_metadata</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>ON</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 When this variable is enabled (which is the default, as before the variable was created),`InnoDB` updates [ statistics][statistics] when metadata statements such as [ `SHOW TABLE STATUS`][_SHOW TABLE STATUS] or [ `SHOW INDEX`][_SHOW INDEX] are run, or when accessing the `INFORMATION_SCHEMA` tables [ `TABLES`][_TABLES] or [ `STATISTICS`][_STATISTICS]. (These updates are similar to what happens for[`ANALYZE TABLE`][ANALYZE TABLE].) When disabled, `InnoDB` does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of[execution plans][] for queries that involve `InnoDB` tables.
 
 To change the setting, issue the statement `SET GLOBAL innodb_stats_on_metadata=mode`, where`mode` is either`ON` or `OFF` (or `1` or `0`). Changing this setting requires the `SUPER` privilege and immediately affects the operation of all connections.
 * `innodb_stats_sample_pages`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_stats_sample_pages=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_stats_sample_pages</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_stats_sample_pages" rel="nofollow">innodb_stats_sample_pages</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>8</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>1 .. 2**64-1</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The number of index pages to sample for index distribution [ statistics][statistics] such as are calculated by [ `ANALYZE TABLE`][ANALYZE TABLE]. The default value is 8. For additional information, see[Section 14.16.15, “Configuring Optimizer Statistics Parameters”][Section_14.16.15_ _Configuring Optimizer Statistics Parameters].
 
 Setting a high value for `innodb_stats_sample_pages` could result in lengthy[`ANALYZE TABLE`][ANALYZE TABLE] execution time. To estimate the number of database pages accessed by[`ANALYZE TABLE`][ANALYZE TABLE], see [ Section 14.16.16, “Estimating `ANALYZE TABLE` Complexity for`InnoDB` Tables”][Section_14.16.16_ _Estimating _ANALYZE TABLE_ Complexity for_InnoDB_ Tables].
 * `innodb_strict_mode`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_strict_mode=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_strict_mode</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_strict_mode" rel="nofollow">innodb_strict_mode</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global, Session</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>OFF</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 When `innodb_strict_mode` is `ON`, `InnoDB` returns errors rather than warnings for certain conditions. The default value is`OFF`.
 
 [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_mode` is`ON`, `InnoDB` raises error conditions in certain cases, rather than issuing a warning and processing the specified statement (perhaps with unintended behavior). This is analogous to[`sql_mode`][sql_mode] in 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_mode` setting affects the handling of syntax errors for[`CREATE TABLE`][CREATE TABLE], [ `ALTER TABLE`][_ALTER TABLE] and [ `CREATE INDEX`][_CREATE INDEX] statements. `innodb_strict_mode` also enables a record size check, so that an`INSERT` or `UPDATE` never fails due to the record being too large for the selected page size.
 
 Oracle recommends enabling `innodb_strict_mode` when using`ROW_FORMAT` and `KEY_BLOCK_SIZE` clauses on[`CREATE TABLE`][CREATE TABLE], [ `ALTER TABLE`][_ALTER TABLE], and [ `CREATE INDEX`][_CREATE INDEX] statements. When `innodb_strict_mode` is `OFF`, `InnoDB` ignores 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. When `innodb_strict_mode` is `ON`, such problems generate an immediate error and the table or index is not created, avoiding a troubleshooting session later.
 
 You can turn `innodb_strict_mode` `ON` or `OFF` on the command line when you start `mysqld`, or in the [ configuration file][configuration file] `my.cnf` or `my.ini`. You can also enable or disable `innodb_strict_mode` at runtime with the statement`SET [GLOBAL|SESSION] innodb_strict_mode=mode`, where`mode` is either`ON` or `OFF`. 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_strict_mode`, and the setting affects only that client.
 * `innodb_support_xa`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_support_xa</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_support_xa</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_support_xa" rel="nofollow">innodb_support_xa</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global, Session</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>TRUE</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 Enables `InnoDB` support for two-phase commit in [ XA][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[`InnoDB`][InnoDB] tables. 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.
 * `innodb_sync_spin_loops`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_sync_spin_loops=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_sync_spin_loops</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_sync_spin_loops" rel="nofollow">innodb_sync_spin_loops</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>30</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>0 .. 4294967295</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The number of times a thread waits for an `InnoDB` mutex to be freed before the thread is suspended. The default value is 30.
 * `innodb_table_locks`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_table_locks</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_table_locks</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_table_locks" rel="nofollow">innodb_table_locks</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global, Session</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>TRUE</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 If [ `autocommit = 0`][_autocommit _ 0], `InnoDB` honors[`LOCK TABLES`][LOCK TABLES]; MySQL does not return from `LOCK TABLES ... WRITE` until all other threads have released all their locks to the table. The default value of[`innodb_table_locks`][innodb_table_locks] is 1, which means that [ `LOCK TABLES`][LOCK TABLES] causes InnoDB to lock a table internally if[`autocommit = 0`][_autocommit _ 0].
 
 As of MySQL 5.5.3, [ `innodb_table_locks = 0`][innodb_table_locks] has no effect for tables locked explicitly with[`LOCK TABLES ... WRITE`][LOCK TABLES]. It still has an effect for tables locked for read or write by[`LOCK TABLES ... WRITE`][LOCK TABLES] implicitly (for example, through triggers) or by[`LOCK TABLES ... READ`][LOCK TABLES].
 * `innodb_thread_concurrency`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_thread_concurrency=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_thread_concurrency</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_thread_concurrency" rel="nofollow">innodb_thread_concurrency</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>0</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>0 .. 1000</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 `InnoDB` tries to keep the number of operating system threads concurrently inside`InnoDB` less than or equal to the limit given by this variable. Once the number of threads reaches this limit, additional threads are placed into a wait state within a FIFO queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads.
 
 The correct value for this variable is dependent on environment and workload. Try a range of different values to determine what value works for your applications. A recommended value is 2 times the number of CPUs plus the number of disks.
 
 The range of this variable is 0 to 1000. A value of 0 (the default) is interpreted as infinite concurrency (no concurrency checking). Disabling thread concurrency checking enables InnoDB to create as many threads as it needs. A value of 0 also disables the`queries inside InnoDB` and `queries in queue counters` in the`ROW OPERATIONS` section of `SHOW ENGINE INNODB STATUS` output.
 * `innodb_thread_sleep_delay`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_thread_sleep_delay=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_thread_sleep_delay</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_thread_sleep_delay" rel="nofollow">innodb_thread_sleep_delay</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">Yes</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values (&lt;= 5.5.36)</td> 
 </tr> 
 <tr> 
 <td>Platform Bit Size</td> 
 <td colspan="2"><code>32</code></td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>10000</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>0 .. 4294967295</code></td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values (&lt;= 5.5.36)</td> 
 </tr> 
 <tr> 
 <td>Platform Bit Size</td> 
 <td colspan="2"><code>64</code></td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>10000</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>0 .. 18446744073709551615</code></td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values (&gt;= 5.5.37)</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>10000</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>0 .. 1000000</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 How long `InnoDB` threads sleep before joining the `InnoDB` queue, in microseconds. The default value is 10,000. A value of 0 disables sleep.
 * `innodb_use_native_aio`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Introduced</td> 
 <td colspan="3">5.5.4</td> 
 </tr> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_use_native_aio=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_use_native_aio</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_use_native_aio" rel="nofollow">innodb_use_native_aio</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>ON</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 Specifies whether to use the Linux asynchronous I/O subsystem. This variable applies to Linux systems only, and cannot be changed while the server is running. Normally, you do not need to touch this option, because it is enabled by default.
 
 As of MySQL 5.5, the [ asynchronous I/O][asynchronous I_O] capability that `InnoDB` has on Windows systems is available on Linux systems. (Other Unix-like systems continue to use synchronous I/O calls.) This feature improves the scalability of heavily I/O-bound systems, which typically show many pending reads/writes in the output of the command `SHOW ENGINE INNODB STATUS\G`.
 
 Running with a large number of `InnoDB` I/O threads, and especially running multiple such instances on the same server machine, can exceed capacity limits on Linux systems. In this case, you may receive the following error:
 
 EAGAIN: The specified maxevents exceeds the user's limit of available events.
 
 You can typically address this error by writing a higher limit to `/proc/sys/fs/aio-max-nr`.
 
 However, if a problem with the asynchronous I/O subsystem in the OS prevents `InnoDB` from starting, you can start the server with [ `innodb_use_native_aio=0`][_innodb_use_native_aio_0] disabled (use [ `innodb_use_native_aio=0`][_innodb_use_native_aio_0] in the option file). This option may also be turned off automatically during startup if`InnoDB` detects a potential problem such as a combination of`tmpdir` location, `tmpfs` filesystem, and Linux kernel that does not support AIO on`tmpfs`.
 
 This variable was added in MySQL 5.5.4.
 * `innodb_use_sys_malloc`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_use_sys_malloc=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_use_sys_malloc</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_use_sys_malloc" rel="nofollow">innodb_use_sys_malloc</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>boolean</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>ON</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 Whether `InnoDB` uses the operating system memory allocator (`ON`) or its own (`OFF`). The default value is`ON`. See [ Section 14.16.4, “Configuring the Memory Allocator for `InnoDB`”][Section_14.16.4_ _Configuring the Memory Allocator for _InnoDB] for more information.
 * `innodb_version`
 
 The `InnoDB` version number. Starting in 5.5.30, the separate numbering for`InnoDB` is discontinued and this value is the same as for the[`version`][version] variable.
 * `innodb_write_io_threads`
 
 <table> 
 <tbody> 
 <tr> 
 <td>Command-Line Format</td> 
 <td colspan="3"><code>--innodb_write_io_threads=#</code></td> 
 </tr> 
 <tr> 
 <td>Option-File Format</td> 
 <td colspan="3"><code>innodb_write_io_threads</code></td> 
 </tr> 
 <tr> 
 <td>System Variable Name</td> 
 <td colspan="3"><code><a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_write_io_threads" rel="nofollow">innodb_write_io_threads</a></code></td> 
 </tr> 
 <tr> 
 <td>Variable Scope</td> 
 <td colspan="3">Global</td> 
 </tr> 
 <tr> 
 <td>Dynamic Variable</td> 
 <td colspan="3">No</td> 
 </tr> 
 <tr> 
 <td>&nbsp;</td> 
 <td colspan="3">Permitted Values</td> 
 </tr> 
 <tr> 
 <td>Type</td> 
 <td colspan="2"><code>numeric</code></td> 
 </tr> 
 <tr> 
 <td>Default</td> 
 <td colspan="2"><code>4</code></td> 
 </tr> 
 <tr> 
 <td>Range</td> 
 <td colspan="2"><code>1 .. 64</code></td> 
 </tr> 
 </tbody> 
 </table>
 
 The number of I/O threads for write operations in `InnoDB`. The default value is 4. Its counterpart for read threads is[`innodb_read_io_threads`][innodb_read_io_threads]. See [ Section 14.16.7, “Configuring the Number of Background `InnoDB` I/O Threads”][Section_14.16.7_ _Configuring the Number of Background _InnoDB_ I_O Threads] for more information. For general I/O tuning advice, see[Section 8.5.7, “Optimizing `InnoDB` Disk I/O”][Section_8.5.7_ _Optimizing _InnoDB_ Disk I_O].
 
 Note
 
 On Linux systems, running multiple MySQL servers (typically more than 12) with default settings for`innodb_read_io_threads`, [ `innodb_write_io_threads`][innodb_write_io_threads], and the Linux `aio-max-nr` setting can exceed system limits. Ideally, increase the `aio-max-nr` setting; as a workaround, you might reduce the settings for one or both of the MySQL configuration options.

You should also take into consideration the value of [ `sync_binlog`][_sync_binlog], which controls synchronization of the binary log to disk.

For general I/O tuning advice, see [ Section 8.5.7, “Optimizing `InnoDB` Disk I/O”][Section_8.5.7_ _Optimizing _InnoDB_ Disk I_O].

[Previous][] / [Next][14.15.1 Changes to_InnoDB_ Startup Options and System Variables] / [ Up][InnoDB] / [ Table of Contents][Table of Contents]

# User Comments #

<table> 
 <tbody> 
 <tr> 
 <td>Posted by Morgan Tocker on December 28 2006 1:21am</td> 
 <td align="right">[<a href="http://dev.mysql.com/doc/mysql/comment.php?id=8280&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-parameters.html&amp;action=delete" rel="nofollow">Delete</a>] [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=8280&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-parameters.html" rel="nofollow">Edit</a>]</td> 
 </tr> 
 </tbody> 
</table>

Be careful when being too aggressive with settings like innodb\_buffer\_pool\_size. Although your system might have a lot of RAM installed, a 32-bit Linux operating can't allocate more than 2.2-2.7G\* per process.  
  
\* This limit varies in different kernels.

<table> 
 <tbody> 
 <tr> 
 <td>Posted by Sunny Walia on April 4 2007 1:55am</td> 
 <td align="right">[<a href="http://dev.mysql.com/doc/mysql/comment.php?id=8509&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-parameters.html&amp;action=delete" rel="nofollow">Delete</a>] [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=8509&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-parameters.html" rel="nofollow">Edit</a>]</td> 
 </tr> 
 </tbody> 
</table>

I am using innodb\_file\_per\_table to separate the files out so when i delete database, we can get our disk usage back. I go into details in my blog which I hope helps somebody.  
[http://crazytoon.com/2007/04/03/mysql-ibdata-files-do-not-shrink-on-database-deletion-innodb/][http_crazytoon.com_2007_04_03_mysql-ibdata-files-do-not-shrink-on-database-deletion-innodb]

<table> 
 <tbody> 
 <tr> 
 <td>Posted by Brian Aker on August 16 2007 3:08pm</td> 
 <td align="right">[<a href="http://dev.mysql.com/doc/mysql/comment.php?id=8823&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-parameters.html&amp;action=delete" rel="nofollow">Delete</a>] [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=8823&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-parameters.html" rel="nofollow">Edit</a>]</td> 
 </tr> 
 </tbody> 
</table>

Commentary on Innodb parameters for an 8way machine:  
[http://krow.livejournal.com/542306.html][http_krow.livejournal.com_542306.html]

<table> 
 <tbody> 
 <tr> 
 <td>Posted by Willem de Groot on September 20 2007 3:39pm</td> 
 <td align="right">[<a href="http://dev.mysql.com/doc/mysql/comment.php?id=8920&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-parameters.html&amp;action=delete" rel="nofollow">Delete</a>] [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=8920&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-parameters.html" rel="nofollow">Edit</a>]</td> 
 </tr> 
 </tbody> 
</table>

Changing innodb\_log\_file\_size can yield strange errors, such as: Incorrect information in file: './db010840/notifications.frm'  
  
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).

<table> 
 <tbody> 
 <tr> 
 <td>Posted by Simon Mudd on October 13 2009 3:25pm</td> 
 <td align="right">[<a href="http://dev.mysql.com/doc/mysql/comment.php?id=10417&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-parameters.html&amp;action=delete" rel="nofollow">Delete</a>] [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=10417&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-parameters.html" rel="nofollow">Edit</a>]</td> 
 </tr> 
 </tbody> 
</table>

NOTE: The time to Initialise the innodb buffer pool is roughly proportional to the size of the pool created. On large installations\[\*\] this initialisation time may be significant.  
  
\[\*\] 2009/10 Initialising a 10 GB buffer pool takes 6 seconds, larger configurations may take proportionally longer.

<table> 
 <tbody> 
 <tr> 
 <td>Posted by K Thomas Kuruvilla on July 16 2012 4:32pm</td> 
 <td align="right">[<a href="http://dev.mysql.com/doc/mysql/comment.php?id=12359&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-parameters.html&amp;action=delete" rel="nofollow">Delete</a>] [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=12359&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-parameters.html" rel="nofollow">Edit</a>]</td> 
 </tr> 
 </tbody> 
</table>

It would have been better if the parameters given in here were with concrete examples. I hope it would be done in the future releases.

<table> 
 <tbody> 
 <tr> 
 <td>Posted by Morgan Tocker on November 15 2013 11:34pm</td> 
 <td align="right">[<a href="http://dev.mysql.com/doc/mysql/comment.php?id=12777&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-parameters.html&amp;action=delete" rel="nofollow">Delete</a>] [<a href="http://dev.mysql.com/doc/mysql/comment.php?id=12777&amp;return=%2Fdoc%2Frefman%2F5.5%2Fen%2Finnodb-parameters.html" rel="nofollow">Edit</a>]</td> 
 </tr> 
 </tbody> 
</table>

I wrote a guide for what to tune in MySQL 5.6 after installation here:  
[http://www.tocker.ca/2013/09/17/what-to-tune-in-mysql-56-after-installation.html][http_www.tocker.ca_2013_09_17_what-to-tune-in-mysql-56-after-installation.html]

[-]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#
[14.15.1 Changes to_InnoDB_ Startup Options and System Variables]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameter-changes.html
[_--innodb_checksums]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_checksums
[Section_5.1.5.2_ _Dynamic System Variables]: http://dev.mysql.com/doc/refman/5.5/en/dynamic-system-variables.html
[_SET]: http://dev.mysql.com/doc/refman/5.5/en/set-statement.html
[Section_14.6_ _InnoDB_ Configuration]: http://dev.mysql.com/doc/refman/5.5/en/innodb-configuration.html
[workload]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_workload
[Section_4.2.3_ _Specifying Program Options]: http://dev.mysql.com/doc/refman/5.5/en/program-options.html
[--ignore-builtin-innodb]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#option_mysqld_ignore-builtin-innodb
[--innodb_value]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#option_mysqld_innodb
[Section_5.1.8.1_ _Installing and Uninstalling Plugins]: http://dev.mysql.com/doc/refman/5.5/en/server-plugin-loading.html
[InnoDB]: http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html
[--default-storage-engine]: http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_default-storage-engine
[--innodb-status-file]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#option_mysqld_innodb-status-file
[_SHOW ENGINE INNODB STATUS]: http://dev.mysql.com/doc/refman/5.5/en/show-engine.html
[_mysqld]: http://dev.mysql.com/doc/refman/5.5/en/mysqld.html
[dirty pages]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_dirty_page
[buffer pool]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_buffer_pool
[Section_14.16.2.2_ _Configuring the Rate of _InnoDB_ Buffer Pool Flushing]: http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-adaptive_flushing.html
[Section_8.5.7_ _Optimizing _InnoDB_ Disk I_O]: http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-diskio.html
[adaptive hash index]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_adaptive_hash_index
[Section_14.5.13.5_ _Adaptive Hash Indexes]: http://dev.mysql.com/doc/refman/5.5/en/innodb-adaptive-hash.html
[data dictionary]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_data_dictionary
[innodb_use_sys_malloc]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_use_sys_malloc
[system tablespace]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_system_tablespace
[innodb_file_per_table_1]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table
[innodb_autoextend_increment]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_autoextend_increment
[lock mode]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_lock_mode
[auto-increment]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_auto_increment
[Section_14.9.5_ _AUTO_INCREMENT_ Handling in _InnoDB]: http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html
[innodb_buffer_pool_instances]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances
[_innodb_buffer_pool_size]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
[Section_8.9.1_ _The _InnoDB_ Buffer Pool]: http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
[Section_14.16.5_ _Configuring _InnoDB_ Change Buffering]: http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-change_buffering.html
[threads]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_thread
[commit]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_commit
[transactions]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_transaction
[_innodb_commit_concurrency]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_commit_concurrency
[_innodb_concurrency_tickets]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_concurrency_tickets
[data files]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_data_files
[innodb_data_home_dir]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_data_home_dir
[_innodb_data_file_path]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_data_file_path
[use raw disk partitions as data files]: http://dev.mysql.com/doc/refman/5.5/en/innodb-raw-devices.html
[tablespace]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_tablespace
[file-per-table]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_file_per_table
[doublewrite buffer]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_doublewrite_buffer
[_--skip-innodb_doublewrite]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_doublewrite
[shutdown]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_shutdown
[slow shutdown]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_slow_shutdown
[purge]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_purge
[fast shutdown]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_fast_shutdown
[crash recovery]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_crash_recovery
[file format]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_file_format
[Barracuda]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_barracuda
[compression]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_compression
[_ALTER TABLE]: http://dev.mysql.com/doc/refman/5.5/en/alter-table.html
[_innodb_file_format_max]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_format_max
[_innodb_file_format_check]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_format_check
[ibdata files]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_ibdata_file
[DROP TABLE]: http://dev.mysql.com/doc/refman/5.5/en/drop-table.html
[_TRUNCATE TABLE]: http://dev.mysql.com/doc/refman/5.5/en/truncate-table.html
[instance]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_instance
[_.ibd_ file]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_ibd_file
[Section_14.8.2_ _InnoDB File-Per-Table Mode]: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html
[configuration file]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_configuration_file
[ACID]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_acid
[log buffer]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_log_buffer
[log file]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_log_file
[flush]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_flush
[_innodb_flush_method]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_method
[innodb_force_load_corrupted]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_force_load_corrupted
[Section_14.21.2_ _Starting _InnoDB_ on a Corrupted Database]: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
[_innodb_io_capacity]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_io_capacity
[insert buffer]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_insert_buffer
[Section_14.16.9_ _Configuring the _InnoDB_ Master Thread I_O Rate]: http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-thread_io_rate.html
[innodb_file_format_barracuda]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_format
[Section_14.9.7_ _Limits on _InnoDB_ Tables]: http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html
[ER_INDEX_COLUMN_TOO_LONG]: http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_index_column_too_long
[row lock]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_row_lock
[rolled back]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_rollback
[--innodb_rollback_on_timeout]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_rollback_on_timeout
[Section_14.21.4_ _InnoDB_ Error Handling]: http://dev.mysql.com/doc/refman/5.5/en/innodb-error-handling.html
[OLTP]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_oltp
[table lock]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_table_lock
[deadlocks]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_deadlock
[_innodb_lock_wait_timeout]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout
[Section_14.5.6_ _InnoDB_ Record_ Gap_ and Next-Key Locks]: http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-locks.html
[READ COMMITTED]: http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_read-committed
[_innodb_locks_unsafe_for_binlog]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog
[Section_13.3.6_ _SET TRANSACTION_ Syntax]: http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html
[SELECT]: http://dev.mysql.com/doc/refman/5.5/en/select.html
[Section_14.5.7_ _Avoiding the Phantom Problem Using Next-Key Locking]: http://dev.mysql.com/doc/refman/5.5/en/innodb-next-key-locking.html
[_UPDATE]: http://dev.mysql.com/doc/refman/5.5/en/update.html
[_DELETE]: http://dev.mysql.com/doc/refman/5.5/en/delete.html
[Section_14.5.13.2_ _Clustered and Secondary Indexes]: http://dev.mysql.com/doc/refman/5.5/en/innodb-index-types.html
[log group]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_log_group
[innodb_log_files_in_group]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_files_in_group
[innodb_log_group_home_dir]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_group_home_dir
[redo log]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_redo_log
[innodb_log_file_size]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_file_size
[_INSERT]: http://dev.mysql.com/doc/refman/5.5/en/insert.html
[Section_14.5.12_ _InnoDB_ Multi-Versioning]: http://dev.mysql.com/doc/refman/5.5/en/innodb-multi-versioning.html
[innodb_max_purge_lag]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_max_purge_lag
[consistent read]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_consistent_read
[sublist]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_sublist
[Section_14.16.2.3_ _Making the Buffer Pool Scan Resistant]: http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-midpoint_insertion.html
[LRU]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_lru
[eviction]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_eviction
[full table scan]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_full_table_scan
[_innodb_old_blocks_pct]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_old_blocks_pct
[--open-files-limit]: http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_open-files-limit
[error log]: http://dev.mysql.com/doc/refman/5.5/en/error-log.html
[DML]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_dml
[master thread]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_master_thread
[read-ahead]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_read_ahead
[Section_14.16.2.1_ _Configuring _InnoDB_ Buffer Pool Prefetching _Read-Ahead]: http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-read_ahead.html
[extent]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_extent
[innodb_read_ahead_threshold]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_read_ahead_threshold
[Innodb_buffer_pool_read_ahead]: http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_read_ahead
[_Innodb_buffer_pool_read_ahead_evicted]: http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Innodb_buffer_pool_read_ahead_evicted
[innodb_write_io_threads]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_write_io_threads
[Section_14.16.7_ _Configuring the Number of Background _InnoDB_ I_O Threads]: http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-multiple_io_threads.html
[_innodb_thread_concurrency]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_thread_concurrency
[rollback segments]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_rollback_segment
[spin]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_spin
[Section_14.16.11_ _Configuring Spin Lock Polling]: http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-spin_lock_polling.html
[statistics]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_statistics
[Section_8.3.7_ _InnoDB_ and _MyISAM_ Index Statistics Collection]: http://dev.mysql.com/doc/refman/5.5/en/myisam-index-statistics.html
[_SHOW TABLE STATUS]: http://dev.mysql.com/doc/refman/5.5/en/show-table-status.html
[_SHOW INDEX]: http://dev.mysql.com/doc/refman/5.5/en/show-index.html
[_TABLES]: http://dev.mysql.com/doc/refman/5.5/en/tables-table.html
[_STATISTICS]: http://dev.mysql.com/doc/refman/5.5/en/statistics-table.html
[ANALYZE TABLE]: http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html
[execution plans]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_query_execution_plan
[Section_14.16.15_ _Configuring Optimizer Statistics Parameters]: http://dev.mysql.com/doc/refman/5.5/en/innodb-statistics-estimation.html
[Section_14.16.16_ _Estimating _ANALYZE TABLE_ Complexity for_InnoDB_ Tables]: http://dev.mysql.com/doc/refman/5.5/en/innodb-analyze-table-complexity.html
[Strict mode]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_strict_mode
[sql_mode]: http://dev.mysql.com/doc/refman/5.5/en/sql-mode.html
[CREATE TABLE]: http://dev.mysql.com/doc/refman/5.5/en/create-table.html
[_CREATE INDEX]: http://dev.mysql.com/doc/refman/5.5/en/create-index.html
[XA]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_xa
[_autocommit _ 0]: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_autocommit
[LOCK TABLES]: http://dev.mysql.com/doc/refman/5.5/en/lock-tables.html
[innodb_table_locks]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_table_locks
[asynchronous I_O]: http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_asynchronous_io
[_innodb_use_native_aio_0]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_use_native_aio
[Section_14.16.4_ _Configuring the Memory Allocator for _InnoDB]: http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-use_sys_malloc.html
[version]: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_version
[innodb_read_io_threads]: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_read_io_threads
[_sync_binlog]: http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_sync_binlog
[Previous]: http://dev.mysql.com/doc/refman/5.5/en/innodb-create-index-limitations.html
[Table of Contents]: http://dev.mysql.com/doc/refman/5.5/en/index.html
[http_crazytoon.com_2007_04_03_mysql-ibdata-files-do-not-shrink-on-database-deletion-innodb]: http://crazytoon.com/2007/04/03/mysql-ibdata-files-do-not-shrink-on-database-deletion-innodb/
[http_krow.livejournal.com_542306.html]: http://krow.livejournal.com/542306.html
[http_www.tocker.ca_2013_09_17_what-to-tune-in-mysql-56-after-installation.html]: http://www.tocker.ca/2013/09/17/what-to-tune-in-mysql-56-after-installation.html