Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.6Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 34.7Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 203.5Kb
Man Pages (Zip) - 310.2Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  The InnoDB Storage Engine  /  InnoDB Startup Options and System Variables

15.14 InnoDB Startup Options and System Variables

Table 15.10 InnoDB Option/Variable Reference

NameCmd-LineOption FileSystem VarStatus VarVar ScopeDynamic
daemon_memcached_enable_binlogYesYesYes GlobalNo
daemon_memcached_engine_lib_nameYesYesYes GlobalNo
daemon_memcached_engine_lib_pathYesYesYes GlobalNo
daemon_memcached_optionYesYesYes GlobalNo
daemon_memcached_r_batch_sizeYesYesYes GlobalNo
daemon_memcached_w_batch_sizeYesYesYes GlobalNo
foreign_key_checks  Yes BothYes
ignore-builtin-innodbYesYes  GlobalNo
- Variable: ignore_builtin_innodb  Yes GlobalNo
innodbYesYes    
innodb_adaptive_flushingYesYesYes GlobalYes
innodb_adaptive_flushing_lwmYesYesYes GlobalYes
innodb_adaptive_hash_indexYesYesYes GlobalYes
innodb_adaptive_hash_index_partsYesYesYes GlobalNo
innodb_adaptive_max_sleep_delayYesYesYes GlobalYes
innodb_additional_mem_pool_sizeYesYesYes GlobalNo
innodb_api_bk_commit_intervalYesYesYes GlobalYes
innodb_api_disable_rowlockYesYesYes GlobalNo
innodb_api_enable_binlogYesYesYes GlobalNo
innodb_api_enable_mdlYesYesYes GlobalNo
innodb_api_trx_levelYesYesYes GlobalYes
innodb_autoextend_incrementYesYesYes GlobalYes
innodb_autoinc_lock_modeYesYesYes GlobalNo
Innodb_available_undo_logs   YesGlobalNo
innodb_background_drop_list_emptyYesYesYes GlobalYes
Innodb_buffer_pool_bytes_data   YesGlobalNo
Innodb_buffer_pool_bytes_dirty   YesGlobalNo
innodb_buffer_pool_chunk_sizeYesYesYes GlobalNo
innodb_buffer_pool_dump_at_shutdownYesYesYes GlobalYes
innodb_buffer_pool_dump_nowYesYesYes GlobalYes
innodb_buffer_pool_dump_pctYesYesYes GlobalYes
Innodb_buffer_pool_dump_status   YesGlobalNo
innodb_buffer_pool_filenameYesYesYes GlobalYes
innodb_buffer_pool_instancesYesYesYes GlobalNo
innodb_buffer_pool_load_abortYesYesYes GlobalYes
innodb_buffer_pool_load_at_startupYesYesYes GlobalNo
innodb_buffer_pool_load_nowYesYesYes GlobalYes
Innodb_buffer_pool_load_status   YesGlobalNo
Innodb_buffer_pool_pages_data   YesGlobalNo
Innodb_buffer_pool_pages_dirty   YesGlobalNo
Innodb_buffer_pool_pages_flushed   YesGlobalNo
Innodb_buffer_pool_pages_free   YesGlobalNo
Innodb_buffer_pool_pages_latched   YesGlobalNo
Innodb_buffer_pool_pages_misc   YesGlobalNo
Innodb_buffer_pool_pages_total   YesGlobalNo
Innodb_buffer_pool_read_ahead   YesGlobalNo
Innodb_buffer_pool_read_ahead_evicted   YesGlobalNo
Innodb_buffer_pool_read_ahead_rnd   YesGlobalNo
Innodb_buffer_pool_read_requests   YesGlobalNo
Innodb_buffer_pool_reads   YesGlobalNo
Innodb_buffer_pool_resize_status   YesGlobalNo
innodb_buffer_pool_sizeYesYesYes GlobalVaries
Innodb_buffer_pool_wait_free   YesGlobalNo
Innodb_buffer_pool_write_requests   YesGlobalNo
innodb_change_buffer_max_sizeYesYesYes GlobalYes
innodb_change_bufferingYesYesYes GlobalYes
innodb_change_buffering_debugYesYesYes GlobalYes
innodb_checksum_algorithmYesYesYes GlobalYes
innodb_checksumsYesYesYes GlobalNo
innodb_cmp_per_index_enabledYesYesYes GlobalYes
innodb_commit_concurrencyYesYesYes GlobalYes
innodb_compress_debugYesYesYes GlobalYes
innodb_compression_failure_threshold_pctYesYesYes GlobalYes
innodb_compression_levelYesYesYes GlobalYes
innodb_compression_pad_pct_maxYesYesYes GlobalYes
innodb_concurrency_ticketsYesYesYes GlobalYes
innodb_create_intrinsicYesYesYes SessionYes
innodb_data_file_pathYesYesYes GlobalNo
Innodb_data_fsyncs   YesGlobalNo
innodb_data_home_dirYesYesYes GlobalNo
Innodb_data_pending_fsyncs   YesGlobalNo
Innodb_data_pending_reads   YesGlobalNo
Innodb_data_pending_writes   YesGlobalNo
Innodb_data_read   YesGlobalNo
Innodb_data_reads   YesGlobalNo
Innodb_data_writes   YesGlobalNo
Innodb_data_written   YesGlobalNo
Innodb_dblwr_pages_written   YesGlobalNo
Innodb_dblwr_writes   YesGlobalNo
innodb_default_row_formatYesYesYes GlobalYes
innodb_disable_resize_buffer_pool_debugYesYesYes GlobalYes
innodb_disable_sort_file_cacheYesYesYes GlobalYes
innodb_doublewriteYesYesYes GlobalNo
innodb_fast_shutdownYesYesYes GlobalYes
innodb_fil_make_page_dirty_debugYesYesYes GlobalYes
innodb_file_formatYesYesYes GlobalYes
innodb_file_format_checkYesYesYes GlobalNo
innodb_file_format_maxYesYesYes GlobalYes
innodb_file_per_tableYesYesYes GlobalYes
innodb_fill_factorYesYesYes GlobalYes
innodb_flush_log_at_timeout  Yes GlobalYes
innodb_flush_log_at_trx_commitYesYesYes GlobalYes
innodb_flush_methodYesYesYes GlobalNo
innodb_flush_neighborsYesYesYes GlobalYes
innodb_flush_syncYesYesYes GlobalYes
innodb_flushing_avg_loopsYesYesYes GlobalYes
innodb_force_load_corruptedYesYesYes GlobalNo
innodb_force_recoveryYesYesYes GlobalNo
innodb_ft_aux_tableYesYesYes GlobalYes
innodb_ft_cache_sizeYesYesYes GlobalNo
innodb_ft_enable_diag_printYesYesYes GlobalYes
innodb_ft_enable_stopwordYesYesYes GlobalYes
innodb_ft_max_token_sizeYesYesYes GlobalNo
innodb_ft_min_token_sizeYesYesYes GlobalNo
innodb_ft_num_word_optimizeYesYesYes GlobalYes
innodb_ft_result_cache_limitYesYesYes GlobalYes
innodb_ft_server_stopword_tableYesYesYes GlobalYes
innodb_ft_sort_pll_degreeYesYesYes GlobalNo
innodb_ft_total_cache_sizeYesYesYes GlobalNo
innodb_ft_user_stopword_tableYesYesYes BothYes
Innodb_have_atomic_builtins   YesGlobalNo
innodb_io_capacityYesYesYes GlobalYes
innodb_io_capacity_maxYesYesYes GlobalYes
innodb_large_prefixYesYesYes GlobalYes
innodb_limit_optimistic_insert_debugYesYesYes GlobalYes
innodb_lock_wait_timeoutYesYesYes BothYes
innodb_locks_unsafe_for_binlogYesYesYes GlobalNo
innodb_log_buffer_sizeYesYesYes GlobalNo
innodb_log_checksum_algorithmYesYesYes GlobalYes
innodb_log_checksumsYesYesYes GlobalYes
innodb_log_compressed_pagesYesYesYes GlobalYes
innodb_log_file_sizeYesYesYes GlobalNo
innodb_log_files_in_groupYesYesYes GlobalNo
innodb_log_group_home_dirYesYesYes GlobalNo
Innodb_log_waits   YesGlobalNo
innodb_log_write_ahead_sizeYesYesYes GlobalYes
Innodb_log_write_requests   YesGlobalNo
Innodb_log_writes   YesGlobalNo
innodb_lru_scan_depthYesYesYes GlobalYes
innodb_max_dirty_pages_pctYesYesYes GlobalYes
innodb_max_dirty_pages_pct_lwmYesYesYes GlobalYes
innodb_max_purge_lagYesYesYes GlobalYes
innodb_max_purge_lag_delayYesYesYes GlobalYes
innodb_max_undo_log_sizeYesYesYes GlobalYes
innodb_merge_threshold_set_all_debugYesYesYes GlobalYes
innodb_monitor_disableYesYesYes GlobalYes
innodb_monitor_enableYesYesYes GlobalYes
innodb_monitor_resetYesYesYes GlobalYes
innodb_monitor_reset_allYesYesYes GlobalYes
Innodb_num_open_files   YesGlobalNo
innodb_numa_interleaveYesYesYes GlobalNo
innodb_old_blocks_pctYesYesYes GlobalYes
innodb_old_blocks_timeYesYesYes GlobalYes
innodb_online_alter_log_max_sizeYesYesYes GlobalYes
innodb_open_filesYesYesYes GlobalNo
innodb_optimize_fulltext_onlyYesYesYes GlobalYes
innodb_optimize_point_storageYesYesYes SessionYes
Innodb_os_log_fsyncs   YesGlobalNo
Innodb_os_log_pending_fsyncs   YesGlobalNo
Innodb_os_log_pending_writes   YesGlobalNo
Innodb_os_log_written   YesGlobalNo
innodb_page_cleanersYesYesYes GlobalNo
Innodb_page_size   YesGlobalNo
innodb_page_sizeYesYesYes GlobalNo
Innodb_pages_created   YesGlobalNo
Innodb_pages_read   YesGlobalNo
Innodb_pages_written   YesGlobalNo
innodb_print_all_deadlocksYesYesYes GlobalYes
innodb_purge_batch_sizeYesYesYes GlobalYes
innodb_purge_rseg_truncate_frequencyYesYesYes GlobalYes
innodb_purge_threadsYesYesYes GlobalNo
innodb_random_read_aheadYesYesYes GlobalYes
innodb_read_ahead_thresholdYesYesYes GlobalYes
innodb_read_io_threadsYesYesYes GlobalNo
innodb_read_onlyYesYesYes GlobalNo
innodb_replication_delayYesYesYes GlobalYes
innodb_rollback_on_timeoutYesYesYes GlobalNo
innodb_rollback_segmentsYesYesYes GlobalYes
Innodb_row_lock_current_waits   YesGlobalNo
Innodb_row_lock_time   YesGlobalNo
Innodb_row_lock_time_avg   YesGlobalNo
Innodb_row_lock_time_max   YesGlobalNo
Innodb_row_lock_waits   YesGlobalNo
Innodb_rows_deleted   YesGlobalNo
Innodb_rows_inserted   YesGlobalNo
Innodb_rows_read   YesGlobalNo
Innodb_rows_updated   YesGlobalNo
innodb_saved_page_number_debugYesYesYes GlobalYes
innodb_sort_buffer_sizeYesYesYes GlobalNo
innodb_spin_wait_delayYesYesYes GlobalYes
innodb_stats_auto_recalcYesYesYes GlobalYes
innodb_stats_include_delete_markedYesYesYes GlobalYes
innodb_stats_methodYesYesYes GlobalYes
innodb_stats_on_metadataYesYesYes GlobalYes
innodb_stats_persistentYesYesYes GlobalYes
innodb_stats_persistent_sample_pagesYesYesYes GlobalYes
innodb_stats_sample_pagesYesYesYes GlobalYes
innodb_stats_transient_sample_pagesYesYesYes GlobalYes
innodb-status-fileYesYes    
innodb_status_outputYesYesYes GlobalYes
innodb_status_output_locksYesYesYes GlobalYes
innodb_strict_modeYesYesYes BothYes
innodb_support_xaYesYesYes BothYes
innodb_sync_array_sizeYesYesYes GlobalNo
innodb_sync_debugYesYesYes GlobalNo
innodb_sync_spin_loopsYesYesYes GlobalYes
innodb_table_locksYesYesYes BothYes
innodb_temp_data_file_pathYesYesYes GlobalNo
innodb_thread_concurrencyYesYesYes GlobalYes
innodb_thread_sleep_delayYesYesYes GlobalYes
innodb_tmpdirYesYesYes BothYes
Innodb_truncated_status_writes   YesGlobalNo
innodb_trx_purge_view_update_only_debugYesYesYes GlobalYes
innodb_trx_rseg_n_slots_debugYesYesYes GlobalYes
innodb_undo_directoryYesYesYes GlobalNo
innodb_undo_log_truncateYesYesYes GlobalYes
innodb_undo_logsYesYesYes GlobalYes
innodb_undo_tablespacesYesYesYes GlobalNo
innodb_use_native_aioYesYesYes GlobalNo
innodb_use_sys_mallocYesYesYes GlobalNo
innodb_version  Yes GlobalNo
innodb_write_io_threadsYesYesYes GlobalNo
mecab_rc_fileYesYesYes GlobalNo
ngram_token_sizeYesYesYes GlobalNo
timed_mutexesYesYesYes GlobalYes
unique_checks  Yes BothYes

InnoDB Command Options

  • --ignore-builtin-innodb

    Deprecated5.5.22
    Command-Line Format--ignore-builtin-innodb
    System VariableNameignore_builtin_innodb
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean

    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.7, InnoDB is the default storage engine and InnoDB Plugin is not used, so this option is ignored.

  • --innodb[=value]

    Deprecated5.7.5
    Command-Line Format--innodb[=value]
    Permitted ValuesTypeenumeration
    DefaultON
    Valid ValuesOFF
    ON
    FORCE

    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 6.5.2, “Installing and Uninstalling Plugins”.

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

    As of MySQL 5.7.5, the InnoDB storage engine can no longer be disabled, and the --innodb=OFF and --skip-innodb options are deprecated and have no effect. Their use results in a warning. These options will be removed in a future MySQL release.

  • --innodb-status-file

    Command-Line Format--innodb-status-file
    Permitted ValuesTypeboolean
    DefaultOFF

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

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

  • --skip-innodb

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

InnoDB System Variables

  • daemon_memcached_enable_binlog

    Command-Line Format--daemon_memcached_enable_binlog=#
    System VariableNamedaemon_memcached_enable_binlog
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    Defaultfalse

    See Section 15.20, “InnoDB memcached Plugin” for usage details for this option.

  • daemon_memcached_engine_lib_name

    Command-Line Format--daemon_memcached_engine_lib_name=library
    System VariableNamedaemon_memcached_engine_lib_name
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypefile name
    Defaultinnodb_engine.so

    Specifies the shared library that implements the InnoDB memcached plugin.

    See Section 15.20, “InnoDB memcached Plugin” for usage details for this option.

  • daemon_memcached_engine_lib_path

    Command-Line Format--daemon_memcached_engine_lib_path=directory
    System VariableNamedaemon_memcached_engine_lib_path
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name
    DefaultNULL

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

    See Section 15.20, “InnoDB memcached Plugin” for usage details for this option.

  • daemon_memcached_option

    Command-Line Format--daemon_memcached_option=options
    System VariableNamedaemon_memcached_option
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring
    Default

    Used to pass space-separated memcached options to the underlying memcached memory object caching daemon on startup. For example, you might change the port that memcached listens on, reduce the maximum number of simultaneous connections, change the maximum memory size for a key/value pair, or enable debugging messages for the error log.

    See Section 15.20, “InnoDB memcached Plugin” for usage details for this option. For information about memcached options, refer to the memcached man page.

  • daemon_memcached_r_batch_size

    Command-Line Format--daemon_memcached_r_batch_size=#
    System VariableNamedaemon_memcached_r_batch_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default1

    Specifies how many memcached read operations (get) to perform before doing a COMMIT to start a new transaction. Counterpart of daemon_memcached_w_batch_size.

    This value is set to 1 by default, so that any changes made to the table through SQL statements are immediately visible to the memcached operations. You might increase it to reduce the overhead from frequent commits on a system where the underlying table is only being accessed through the memcached interface. If you set the value too large, the amount of undo or redo data could impose some storage overhead, as with any long-running transaction.

    See Section 15.20, “InnoDB memcached Plugin” for usage details for this option.

  • daemon_memcached_w_batch_size

    Command-Line Format--daemon_memcached_w_batch_size=#
    System VariableNamedaemon_memcached_w_batch_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default1

    Specifies how many memcached write operations, such as add, set, or incr, to perform before doing a COMMIT to start a new transaction. Counterpart of daemon_memcached_r_batch_size.

    This value is set to 1 by default, on the assumption that any data being stored is important to preserve in case of an outage and should immediately be committed. When storing non-critical data, you might increase this value to reduce the overhead from frequent commits; but then the last N-1 uncommitted write operations could be lost in case of a crash.

    See Section 15.20, “InnoDB memcached Plugin” for usage details for this option.

  • ignore_builtin_innodb

    Deprecated5.5.22
    Command-Line Format--ignore-builtin-innodb
    System VariableNameignore_builtin_innodb
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean

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

  • innodb_adaptive_flushing

    Command-Line Format--innodb_adaptive_flushing=#
    System VariableNameinnodb_adaptive_flushing
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

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

  • innodb_adaptive_flushing_lwm

    Command-Line Format--innodb_adaptive_flushing_lwm=#
    System VariableNameinnodb_adaptive_flushing_lwm
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default10
    Min Value0
    Max Value70

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

  • innodb_adaptive_hash_index

    Command-Line Format--innodb_adaptive_hash_index=#
    System VariableNameinnodb_adaptive_hash_index
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

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

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

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

  • innodb_adaptive_hash_index_parts

    Introduced5.7.8
    Command-Line Format--innodb_adaptive_hash_index_parts=#
    System VariableNameinnodb_adaptive_hash_index_parts
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypenumeric
    Default8
    Min Value1
    Max Value512

    Partitions the adaptive hash index search system. Each index is bound to a specific partition, with each partition protected by a separate latch.

    Prior to MySQL 5.7.8, the adaptive hash index search system was protected by a single latch (btr_search_latch) which could become a point of contention. With the introduction of the innodb_adaptive_hash_index_parts option, the search system is partitioned into 8 parts by default. The maximum setting is 512.

    For related information, see Section 15.4.3, “Adaptive Hash Index”.

  • innodb_adaptive_max_sleep_delay

    Command-Line Format--innodb_adaptive_max_sleep_delay=#
    System VariableNameinnodb_adaptive_max_sleep_delay
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default150000
    Min Value0
    Max Value1000000

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

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

  • innodb_additional_mem_pool_size

    Deprecated5.6.3
    Removed5.7.4
    Command-Line Format--innodb_additional_mem_pool_size=#
    System VariableNameinnodb_additional_mem_pool_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default8388608
    Min Value2097152
    Max Value4294967295

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

    This variable relates to the InnoDB internal memory allocator, which is unused if innodb_use_sys_malloc is enabled.

    innodb_additional_mem_pool_size was deprecated in MySQL 5.6.3 and removed in MySQL 5.7.4.

  • innodb_api_bk_commit_interval

    Command-Line Format--innodb_api_bk_commit_interval=#
    System VariableNameinnodb_api_bk_commit_interval
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default5
    Min Value1
    Max Value1073741824

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

  • innodb_api_disable_rowlock

    Command-Line Format--innodb_api_disable_rowlock=#
    System VariableNameinnodb_api_disable_rowlock
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultOFF

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

    The innodb_api_disable_rowlock option is not dynamic. It must be specified on the mysqld command line or entered in the MySQL configuration file. Configuration takes effect when the plugin is installed, which you do each time the MySQL server is started.

  • innodb_api_enable_binlog

    Command-Line Format--innodb_api_enable_binlog=#
    System VariableNameinnodb_api_enable_binlog
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultOFF

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

  • innodb_api_enable_mdl

    Command-Line Format--innodb_api_enable_mdl=#
    System VariableNameinnodb_api_enable_mdl
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultOFF

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

  • innodb_api_trx_level

    Command-Line Format--innodb_api_trx_level=#
    System VariableNameinnodb_api_trx_level
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0

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

  • innodb_autoextend_increment

    Command-Line Format--innodb_autoextend_increment=#
    System VariableNameinnodb_autoextend_increment
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default64
    Min Value1
    Max Value1000

    The increment size (in MB) for extending the size of an auto-extend InnoDB system tablespace file when it becomes full. The default value is 64. This variable does not affect file-per-table (.ibd) data files that are created if you use innodb_file_per_table=1, or general tablespace .ibd data files. Those files are auto-extending regardless of the value of innodb_autoextend_increment. The initial extensions are by small amounts, after which extensions occur in increments of 4MB.

  • innodb_autoinc_lock_mode

    Command-Line Format--innodb_autoinc_lock_mode=#
    System VariableNameinnodb_autoinc_lock_mode
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default1
    Valid Values0
    1
    2

    The lock mode to use for generating auto-increment values. The permissible values are 0, 1, or 2, for traditional, consecutive, or interleaved lock mode, respectively. Section 15.8.6, “AUTO_INCREMENT Handling in InnoDB”, describes the characteristics of these modes.

    This variable has a default of 1 (consecutive lock mode).

  • innodb_background_drop_list_empty

    Introduced5.7.10
    Command-Line Format--innodb_background_drop_list_empty=#
    System VariableNameinnodb_background_drop_list_empty
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Enabling the innodb_background_drop_list_empty debug option helps avoid test case failures by delaying table creation until the background drop list is empty. For example, if test case A places table t1 on the background drop list, test case B waits until the background drop list is empty before creating table t1.

  • innodb_buffer_pool_chunk_size

    Introduced5.7.5
    Command-Line Format--innodb_buffer_pool_chunk_size
    System VariableNameinnodb_buffer_pool_chunk_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default134217728
    Min Value1048576
    Max Valueinnodb_buffer_pool_size / innodb_buffer_pool_instances

    innodb_buffer_pool_chunk_size defines the chunk size for online InnoDB buffer pool resizing operations.

    As of MySQL 5.7.5, the innodb_buffer_pool_size parameter is dynamic, which allows you to resize the buffer pool without restarting the server. To avoid copying all buffer pool pages during resizing operations, the operation is performed in chunks. Chunk size is defined by innodb_buffer_pool_chunk_size. By default, innodb_buffer_pool_chunk_size is 128MB (134217728 bytes). The number of pages contained in a chunk depends on the value of innodb_page_size. innodb_buffer_pool_chunk_size can be increased or decreased in units of 1MB (1048576 bytes).

    The following conditions apply when altering the innodb_buffer_pool_chunk_size value:

    Important

    Care should be taken when changing innodb_buffer_pool_chunk_size, as changing this value can automatically increase the size of the buffer pool. Before you change innodb_buffer_pool_chunk_size, calculate the effect it will have on innodb_buffer_pool_size to ensure that the resulting buffer pool size is acceptable.

    To avoid potential performance issues, the number of chunks (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) should not exceed 1000.

    See Section 15.6.3.2, “Configuring InnoDB Buffer Pool Size” for more information.

  • innodb_buffer_pool_dump_at_shutdown

    Command-Line Format--innodb_buffer_pool_dump_at_shutdown=#
    System VariableNameinnodb_buffer_pool_dump_at_shutdown
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.6)Typeboolean
    DefaultOFF
    Permitted Values (>= 5.7.7)Typeboolean
    DefaultON

    Specifies whether to record the pages cached in the InnoDB buffer pool when the MySQL server is shut down, to shorten the warmup process at the next restart. Typically used in combination with innodb_buffer_pool_load_at_startup. The innodb_buffer_pool_dump_pct option defines the percentage of most recently used buffer pool pages to dump.

    Both innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup are enabled by default as of MySQL 5.7.7.

    For related information, see Section 15.6.3.8, “Saving and Restoring the Buffer Pool State”.

  • innodb_buffer_pool_dump_now

    Command-Line Format--innodb_buffer_pool_dump_now=#
    System VariableNameinnodb_buffer_pool_dump_now
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Immediately records the pages cached in the InnoDB buffer pool. Typically used in combination with innodb_buffer_pool_load_now.

    For related information, see Section 15.6.3.8, “Saving and Restoring the Buffer Pool State”.

  • innodb_buffer_pool_dump_pct

    Introduced5.7.2
    Command-Line Format--innodb_buffer_pool_dump_pct=#
    System VariableNameinnodb_buffer_pool_dump_pct
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.6)Typeinteger
    Default100
    Min Value1
    Max Value100
    Permitted Values (>= 5.7.7)Typeinteger
    Default25
    Min Value1
    Max Value100

    Specifies the percentage of the most recently used pages for each buffer pool to read out and dump. The range is 1 to 100. Prior to MySQL 5.7.7, the default value is 100 (dump all pages). As of MySQL 5.7.7, the default value is 25. For example, if there are 4 buffer pools with 100 pages each, and innodb_buffer_pool_dump_pct is set to 25, the 25 most recently used pages from each buffer pool are dumped.

    The change to the innodb_buffer_pool_dump_pct default value in MySQL 5.7.7 coincides with default value changes for innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup, which are both enabled by default as of MySQL 5.7.7.

  • innodb_buffer_pool_filename

    Command-Line Format--innodb_buffer_pool_filename=file
    System VariableNameinnodb_buffer_pool_filename
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypefile name
    Defaultib_buffer_pool

    Specifies the name of the file that holds the list of tablespace IDs and page IDs produced by innodb_buffer_pool_dump_at_shutdown or innodb_buffer_pool_dump_now. Tablespace IDs and page IDs are saved in the following format: space, page_id. By default, the file is named ib_buffer_pool and is located in the InnoDB data directory. A non-default location must be specified relative to the data directory.

    A file name can be specified at runtime, using a SET statement:

    SET GLOBAL innodb_buffer_pool_filename='file_name';

    You can also specify a user-defined file name at startup, in a startup string or MySQL configuration file. When specifying a file name at startup, the file must already exist or InnoDB will return a startup error indicating that there is no such file or directory.

    For related information, see Section 15.6.3.8, “Saving and Restoring the Buffer Pool State”.

  • innodb_buffer_pool_instances

    Command-Line Format--innodb_buffer_pool_instances=#
    System VariableNameinnodb_buffer_pool_instances
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (Windows, 32-bit platforms)Typeinteger
    Default(autosized)
    Min Value1
    Max Value64
    Permitted Values (Other)Typeinteger
    Default8 (or 1 if innodb_buffer_pool_size < 1GB
    Min Value1
    Max Value64

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

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

    The default value in on 32-bit Windows systems depends on the value of innodb_buffer_pool_size, as described below:

    • If innodb_buffer_pool_size is greater than 1.3GB, the default for innodb_buffer_pool_instances is innodb_buffer_pool_size/128MB, with individual memory allocation requests for each chunk. 1.3GB was chosen as the boundary at which there is significant risk for 32-bit Windows to be unable to allocate the contiguous address space needed for a single buffer pool.

    • Otherwise, the default is 1.

    On all other platforms, the default value is 8 when innodb_buffer_pool_size is greater than or equal to 1GB. Otherwise, the default is 1.

    For related information, see Section 15.6.3.2, “Configuring InnoDB Buffer Pool Size”.

  • innodb_buffer_pool_load_abort

    Command-Line Format--innodb_buffer_pool_load_abort=#
    System VariableNameinnodb_buffer_pool_load_abort
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

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

    For related information, see Section 15.6.3.8, “Saving and Restoring the Buffer Pool State”.

  • innodb_buffer_pool_load_at_startup

    Command-Line Format--innodb_buffer_pool_load_at_startup=#
    System VariableNameinnodb_buffer_pool_load_at_startup
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (<= 5.7.6)Typeboolean
    DefaultOFF
    Permitted Values (>= 5.7.7)Typeboolean
    DefaultON

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

    Both innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup are enabled by default as of MySQL 5.7.7.

    For related information, see Section 15.6.3.8, “Saving and Restoring the Buffer Pool State”.

  • innodb_buffer_pool_load_now

    Command-Line Format--innodb_buffer_pool_load_now=#
    System VariableNameinnodb_buffer_pool_load_now
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

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

    For related information, see Section 15.6.3.8, “Saving and Restoring the Buffer Pool State”.

  • innodb_buffer_pool_size

    Command-Line Format--innodb_buffer_pool_size=#
    System Variable (<= 5.7.4)Nameinnodb_buffer_pool_size
    Variable ScopeGlobal
    Dynamic VariableNo
    System Variable (>= 5.7.5)Nameinnodb_buffer_pool_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default134217728
    Min Value5242880
    Max Value2**32-1
    Permitted Values (64-bit platforms)Typeinteger
    Default134217728
    Min Value5242880
    Max Value2**64-1

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

    A larger buffer pool requires less disk I/O to access the same table data more than once. On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size. Be aware of the following potential issues when configuring buffer pool size, and be prepared to scale back the size of the buffer pool if necessary.

    • Competition for physical memory can cause paging in the operating system.

    • InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified buffer pool size.

    • Address space for the buffer pool must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses.

    • The time to initialize the buffer pool is roughly proportional to its size. On large installations, initialization time might be significant. For example, on a modern Linux x86_64 server, initialization of a 10GB buffer pool takes approximately 6 seconds. See Section 15.6.3.1, “The InnoDB Buffer Pool”.

    As of MySQL 5.7.5, when you increase or decrease buffer pool size, the operation is performed in chunks. Chunk size is defined by the innodb_buffer_pool_chunk_size configuration option, which has a default of 128 MB.

    Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you alter the buffer pool size to a value that is not equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, buffer pool size is automatically adjusted to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances that is not less than the specified buffer pool size.

    As of MySQL 5.7.5, innodb_buffer_pool_size can be set dynamically, which allows you to resize the buffer pool without restarting the server. The Innodb_buffer_pool_resize_status status variable reports the status of online buffer pool resizing operations. See Section 15.6.3.2, “Configuring InnoDB Buffer Pool Size” for more information.

  • innodb_change_buffer_max_size

    Command-Line Format--innodb_change_buffer_max_size=#
    System VariableNameinnodb_change_buffer_max_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default25
    Min Value0
    Max Value50

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

  • innodb_change_buffering

    Command-Line Format--innodb_change_buffering=#
    System VariableNameinnodb_change_buffering
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    Defaultall
    Valid Valuesnone
    inserts
    deletes
    changes
    purges
    all

    Whether InnoDB performs change buffering, an optimization that delays write operations to secondary indexes so that the I/O operations can be performed sequentially. The permitted values are described in the following table. For more information, see Section 15.6.5, “Configuring InnoDB Change Buffering”. For general I/O tuning advice, see Section 9.5.8, “Optimizing InnoDB Disk I/O”.

    Table 15.11 Permitted Values for innodb_change_buffering

    ValueDescription
    noneDo not buffer any operations.
    insertsBuffer insert operations.
    deletesBuffer delete marking operations; strictly speaking, the writes that mark index records for later deletion during a purge operation.
    changesBuffer inserts and delete-marking operations.
    purgesBuffer the physical deletion operations that happen in the background.
    allThe default. Buffer inserts, delete-marking operations, and purges.

  • innodb_change_buffering_debug

    Command-Line Format--innodb_change_buffering_debug=#
    System VariableNameinnodb_change_buffering_debug
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Max Value2

    Sets a debug flag for InnoDB change buffering. A value of 1 forces all changes to the change buffer. A value of 2 causes a crash at merge. A default value of 0 indicates that the change buffering debug flag is not set. This option is only available when debugging support is compiled in using the WITH_DEBUG CMake option.

  • innodb_checksum_algorithm

    Command-Line Format--innodb_checksum_algorithm=#
    System VariableNameinnodb_checksum_algorithm
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.6)Typeenumeration
    Defaultinnodb
    Valid Valuesinnodb
    crc32
    none
    strict_innodb
    strict_crc32
    strict_none
    Permitted Values (>= 5.7.7)Typeenumeration
    Defaultcrc32
    Valid Valuesinnodb
    crc32
    none
    strict_innodb
    strict_crc32
    strict_none

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

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

    • .ibd files containing CRC32 checksums could cause problems downgrading to MySQL versions prior to 5.6.3. MySQL 5.6.3 and up recognizes either the new or old checksum values for the block as correct when reading the block from disk, ensuring that data blocks are compatible during upgrade and downgrade regardless of the algorithm setting. If data written with new checksum values is processed by a level of MySQL earlier than 5.6.3, it could be reported as corrupted.

    • Versions of MySQL Enterprise Backup up to 3.8.0 do not support backing up tablespaces that use CRC32 checksums. MySQL Enterprise Backup adds CRC32 checksum support in 3.8.1, with some limitations. Refer to the MySQL Enterprise Backup 3.8.1 Change History for more information.

    As of MySQL 5.7.7, crc32 is once again the default value for innodb_checksum_algorithm.

    innodb_checksum_algorithm replaced the innodb_checksums option in MySQL 5.6.3. The following values were provided for compatibility, up to and including MySQL 5.7.6:

    • innodb_checksums=ON is the same as innodb_checksum_algorithm=innodb.

    • innodb_checksums=OFF is the same as innodb_checksum_algorithm=none.

    As of MySQl 5.7.7, with a default innodb_checksum_algorithm value of crc32, innodb_checksums=ON is now the same as innodb_checksum_algorithm=crc32. innodb_checksums=OFF is still the same as innodb_checksum_algorithm=none.

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

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

    The strict_* forms work the same as innodb, crc32, and none, except that InnoDB reports an error if it encounters a valid but non-matching checksum value in the tablespace. It is recommended that you only use the strict_* options in a new instance, to set up all tablespaces for the first time. The strict_* settings are somewhat faster, because they do not need to compute all checksum values during disk reads.

    Note

    Prior to MySQL 5.7.8, a strict mode setting for innodb_checksum_algorithm caused InnoDB to halt when encountering a valid but non-matching checksum. In MySQL 5.7.8 and later, only an error message is printed, and the page is accepted as valid if it has a valid innodb, crc32 or none checksum.

    The following table illustrates the difference between the none, innodb, and crc32 option values, and their strict_* counterparts. none, innodb, and crc32 write the specified type of checksum value into each data block, but for compatibility accept any of the other checksum values when verifying a block during a read operation. The strict_* form of each parameter also accepts any valid checksum value but prints an error message when a valid non-matching checksum value is encountered. Using the strict_* form can make verification faster if all InnoDB data files in an instance are created under an identical innodb_checksum_algorithm value.

    Table 15.12 innodb_checksum_algorithm Settings

    ValueGenerated checksum (when writing)Allowed checksums (when reading)
    noneA constant number.Any of the checksums generated by none, innodb, or crc32.
    innodbA checksum calculated in software, using the original algorithm from InnoDB.Any of the checksums generated by none, innodb, or crc32.
    crc32A checksum calculated using the crc32 algorithm, possibly done with a hardware assist.Any of the checksums generated by none, innodb, or crc32.
    strict_noneA constant numberAny of the checksums generated by none, innodb, or crc32. InnoDB prints an error message if a valid but non-matching checksum is encountered.
    strict_innodbA checksum calculated in software, using the original algorithm from InnoDB.Any of the checksums generated by none, innodb, or crc32. InnoDB prints an error message if a valid but non-matching checksum is encountered.
    strict_crc32A checksum calculated using the crc32 algorithm, possibly done with a hardware assist.Any of the checksums generated by none, innodb, or crc32. InnoDB prints an error message if a valid but non-matching checksum is encountered.

  • innodb_checksums

    Deprecated5.6.3
    Command-Line Format--innodb_checksums
    System VariableNameinnodb_checksums
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultON

    InnoDB can use checksum validation on all tablespace pages read from the disk to ensure extra fault tolerance against hardware faults or corrupted data files. This validation is enabled by default. Under specialized circumstances (such as when running benchmarks) this extra safety feature can be disabled with --skip-innodb-checksums. You can specify the method of calculating the checksum with innodb_checksum_algorithm.

    In MySQL 5.6.3 and higher, innodb_checksums is deprecated, replaced by innodb_checksum_algorithm.

    Prior to MySQL 5.7.7, innodb_checksums=ON is the same as innodb_checksum_algorithm=innodb. As of MySQL 5.7.7, the innodb_checksum_algorithm default value is crc32, and innodb_checksums=ON is the same as innodb_checksum_algorithm=crc32. innodb_checksums=OFF is the same as innodb_checksum_algorithm=none.

    It is recommended that you remove any innodb_checksums options from your configuration files and startup scripts, to avoid conflicts with innodb_checksum_algorithm. innodb_checksums=OFF automatically sets innodb_checksum_algorithm=none. innodb_checksums=ON is ignored and overridden by any other setting for innodb_checksum_algorithm.

  • innodb_cmp_per_index_enabled

    Command-Line Format--innodb_cmp_per_index_enabled=#
    System VariableNameinnodb_cmp_per_index_enabled
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF
    Valid ValuesOFF
    ON

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

  • innodb_commit_concurrency

    Command-Line Format--innodb_commit_concurrency=#
    System VariableNameinnodb_commit_concurrency
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Min Value0
    Max Value1000

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

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

  • innodb_compress_debug

    Introduced5.7.8
    Command-Line Format--innodb_compress_debug=#
    System VariableNameinnodb_compress_debug
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    Defaultnone
    Valid Valuesnone
    zlib
    lz4
    lz4hc

    Compresses all tables using a specified compression algorithm without having to define a COMPRESSION attribute for each table. This option is only available if debugging support is compiled in using the WITH_DEBUG CMake option.

  • innodb_compression_failure_threshold_pct

    Command-Line Format--innodb_compression_failure_threshold_pct=#
    System VariableNameinnodb_compression_failure_threshold_pct
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default5
    Min Value0
    Max Value100

    Sets the cutoff point at which MySQL begins adding padding within compressed pages to avoid expensive compression failures. A value of zero disables the mechanism that monitors compression efficiency and dynamically adjusts the padding amount.

    For more information, see Section 15.9.1.6, “Compression for OLTP Workloads”.

  • innodb_compression_level

    Command-Line Format--innodb_compression_level=#
    System VariableNameinnodb_compression_level
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default6
    Min Value0
    Max Value9

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

    For more information, see Section 15.9.1.6, “Compression for OLTP Workloads”.

  • innodb_compression_pad_pct_max

    Command-Line Format--innodb_compression_pad_pct_max=#
    System VariableNameinnodb_compression_pad_pct_max
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default50
    Min Value0
    Max Value75

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

    For more information, see Section 15.9.1.6, “Compression for OLTP Workloads”.

  • innodb_concurrency_tickets

    Command-Line Format--innodb_concurrency_tickets=#
    System VariableNameinnodb_concurrency_tickets
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default5000
    Min Value1
    Max Value4294967295

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

    With a small innodb_concurrency_tickets value, small transactions that only need to process a few rows compete fairly with larger transactions that process many rows. The disadvantage of a small innodb_concurrency_tickets value is that large transactions must loop through the queue many times before they can complete, which extends the length of time required to complete their task.

    With a large innodb_concurrency_tickets value, large transactions spend less time waiting for a position at the end of the queue (controlled by innodb_thread_concurrency) and more time retrieving rows. Large transactions also require fewer trips through the queue to complete their task. The disadvantage of a large innodb_concurrency_tickets value is that too many large transactions running at the same time can starve smaller transactions by making them wait a longer time before executing.

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

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

  • innodb_create_intrinsic

    Introduced5.7.5
    Removed5.7.6
    Command-Line Format--innodb_create_intrinsic=#
    System VariableNameinnodb_create_intrinsic
    Variable ScopeSession
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    When innodb_create_intrinsic is enabled, CREATE TEMPORY TABLE creates optimized temporary tables instead of normal temporary tables.

    An optimized temporary table is a lightweight subclass of temporary table that excludes certain functionality and benefits from optimizations that makes it faster than a normal temporary table. Like normal temporary tables, optimized temporary tables are only visible to the current connection, and are dropped when the connection is terminated. Unlike normal temporary tables, optimized temporary tables are operational when InnoDB is in read-only mode.

    Row format COMPRESSED is not supported. If you attempt to create a compressed optimized temporary table, the innodb_create_intrinsic=ON setting is ignored and InnoDB creates a normal temporary table.

    Optimized temporary table metadata is not available in the INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO table.

    Undo logging is disabled for optimized temporary tables, which means that rollback is also not supported.

    Atomicity for optimized temporary tables is supported at the row-level, not at the statement level.

    Statistics generated by the same workload may differ for intrinsic temporary tables compared to normal temporary tables, as optimized temporary tables may use a different algorithm to complete certain types of operations.

    innodb_create_intrinsic was removed in MySQL 5.7.6.

  • innodb_data_file_path

    Command-Line Format--innodb_data_file_path=name
    System VariableNameinnodb_data_file_path
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring
    Defaultibdata1:12M:autoextend

    The paths to individual InnoDB data files and their sizes. The full directory path to each data file is formed by concatenating innodb_data_home_dir to each path specified here. The file sizes are specified KB, MB or GB (1024MB) by appending K, M or G to the size value. If specifying data file size in kilobytes (KB), do so in multiples of 1024. Otherwise, KB values are rounded off to nearest megabyte (MB) boundary. The sum of the sizes of the files must be at least slightly larger than 10MB. If you do not specify innodb_data_file_path, the default behavior is to create a single auto-extending data file, slightly larger than 12MB, named ibdata1. The size limit of individual files is determined by your operating system. You can set the file size to more than 4GB on those operating systems that support big files. You can also use raw disk partitions as data files. For detailed information on configuring InnoDB tablespace files, see Section 15.6, “InnoDB Configuration”.

    As of MySQL 5.7.8, the following minimum file sizes are enforced for the first system tablespace data file (ibdata1) to ensure that there is enough space for doublewrite buffer blocks (Bug #20972309):

  • innodb_data_home_dir

    Command-Line Format--innodb_data_home_dir=dir_name
    System VariableNameinnodb_data_home_dir
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name

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

  • innodb_deadlock_detect

    Introduced5.7.15
    Command-Line Format--innodb_deadlock_detect
    System VariableNameinnodb_deadlock_detect
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

    This option is used to disable deadlock detection. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs.

  • innodb_default_row_format

    Introduced5.7.9
    Command-Line Format--innodb_default_row_format=#
    System VariableNameinnodb_default_row_format
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    DefaultDYNAMIC
    Valid ValuesDYNAMIC
    COMPACT
    REDUNDANT

    The innodb_default_row_format option, introduced in MySQL 5.7.9, defines the default row format for InnoDB tables (including user-created InnoDB temporary tables). The default setting is DYNAMIC. Other permitted values are COMPACT and REDUNDANT. The COMPRESSED row format, which is not supported for use in the system tablespace, cannot be defined as the default.

    Newly created tables use the row format defined by innodb_default_row_format when a ROW_FORMAT option is not specified explicitly or when ROW_FORMAT=DEFAULT is used.

    When a ROW_FORMAT option is not specified explicitly or when ROW_FORMAT=DEFAULT is used, any operation that rebuilds a table also silently changes the row format of the table to the format defined by innodb_default_row_format. For more information, see Section 15.11.2, “Specifying the Row Format for a Table”.

    Internal InnoDB temporary tables created by the server to process queries use the DYNAMIC row format, regardless of the innodb_default_row_format setting.

    In MySQL 5.7.8 and earlier, the default row format is COMPACT.

  • innodb_disable_sort_file_cache

    Command-Line Format--innodb_disable_sort_file_cache=#
    System VariableNameinnodb_disable_sort_file_cache
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    If enabled, this variable disables the operating system file system cache for merge-sort temporary files. The effect is to open such files with the equivalent of O_DIRECT.

  • innodb_disable_resize_buffer_pool_debug

    Introduced5.7.6
    Command-Line Format--innodb_disable_resize_buffer_pool_debug=#
    System VariableNameinnodb_disable_resize_buffer_pool_debug
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

    Disables resizing of the InnoDB buffer pool. This option is only available if debugging support is compiled in using the WITH_DEBUG CMake option.

  • innodb_doublewrite

    Command-Line Format--innodb-doublewrite
    System VariableNameinnodb_doublewrite
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultON

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

    As of MySQL 5.7.4, if system tablespace files (ibdata files) are located on Fusion-io devices that support atomic writes, doublewrite buffering is automatically disabled and Fusion-io atomic writes are used for all data files. Because the doublewrite buffer setting is global, doublewrite buffering is also disabled for data files residing on non-Fusion-io hardware. This feature is only supported on Fusion-io hardware and is only enabled for Fusion-io NVMFS on Linux. To take full advantage of this feature, an innodb_flush_method setting of O_DIRECT is recommended.

  • innodb_fast_shutdown

    Command-Line Format--innodb_fast_shutdown[=#]
    System VariableNameinnodb_fast_shutdown
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default1
    Valid Values0
    1
    2

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

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

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

  • innodb_fil_make_page_dirty_debug

    Command-Line Format--innodb_fil_make_page_dirty_debug=#
    System VariableNameinnodb_fil_make_page_dirty_debug
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Max Value2**32-1

    By default, setting innodb_fil_make_page_dirty_debug to the ID of a tablespace immediately dirties the first page of the tablespace. If innodb_saved_page_number_debug is set to a non-default value, setting innodb_fil_make_page_dirty_debug dirties the specified page. The innodb_fil_make_page_dirty_debug option is only available if debugging support is compiled in using the WITH_DEBUG CMake option.

  • innodb_file_format

    Deprecated5.7.7
    Command-Line Format--innodb_file_format=#
    System VariableNameinnodb_file_format
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.6)Typestring
    DefaultAntelope
    Valid ValuesAntelope
    Barracuda
    Permitted Values (>= 5.7.7)Typestring
    DefaultBarracuda
    Valid ValuesAntelope
    Barracuda

    The file format to use for new InnoDB tables. Currently, Antelope and Barracuda are supported. This setting only applies to tables that have their own file-per-table tablespace, so for it to have an effect, innodb_file_per_table must be enabled. The Barracuda file format is required to use Compressed or Dynamic row formats and associated features such as compression, off-page storage for large variable-length columns, and large index key prefixes (see innodb_large_prefix). This restriction does not apply to tables stored in general tablespaces. For more information, see Section 15.7.9, “InnoDB General Tablespaces”.

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

    The innodb_file_format default value was changed to Barracuda in MySQL 5.7.7. This change allows Compressed or Dynamic row formats to be used for tables stored in file-per-table tablespaces.

    The innodb_file_format option is also deprecated in MySQL 5.7.7, and will be removed in a future release. The purpose of the innodb_file_format option was to allow users to downgrade to the built-in version of InnoDB in MySQL 5.1. Now that MySQL 5.1 has reached the end of its product lifecycle, downgrade support provided by this option is no longer necessary.

  • innodb_file_format_check

    Deprecated5.7.7
    Command-Line Format--innodb_file_format_check=#
    System VariableNameinnodb_file_format_check
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultON

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

    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.

    The innodb_file_format_check option is deprecated in MySQL 5.7.7 together with the innodb_file_format option. Both options will be removed in a future release.

  • innodb_file_format_max

    Deprecated5.7.7
    Command-Line Format--innodb_file_format_max=#
    System VariableNameinnodb_file_format_max
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring
    DefaultAntelope
    Valid ValuesAntelope
    Barracuda

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

    The innodb_file_format_max option is deprecated in MySQL 5.7.7 together with the innodb_file_format option. Both options will be removed in a future release.

  • innodb_file_per_table

    Command-Line Format--innodb_file_per_table
    System VariableNameinnodb_file_per_table
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

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

    Be aware that enabling innodb_file_per_table also means that an ALTER TABLE operation will move InnoDB table from the system tablespace to an individual .ibd file in cases where ALTER TABLE recreates the table (ALGORITHM=COPY). An exception to this rule is for tables that were placed in the system tablespace using the TABLESPACE=innodb_system option with CREATE TABLE or ALTER TABLE. These tables are unaffected by the innodb_file_per_table setting and can only be moved to file-per-table tablespaces using ALTER TABLE ... TABLESPACE=innodb_file_per_table.

    When innodb_file_per_table is disabled, InnoDB stores the data for all tables and indexes in the ibdata files that make up the system tablespace. This setting reduces the performance overhead of filesystem operations for operations such as DROP TABLE or TRUNCATE TABLE. It is most appropriate for a server environment where entire storage devices are devoted to MySQL data. Because the system tablespace never shrinks, and is shared across all databases in an instance, avoid loading huge amounts of temporary data on a space-constrained system when innodb_file_per_table=OFF. Set up a separate instance in such cases, so that you can drop the entire instance to reclaim the space.

    By default, innodb_file_per_table is enabled as of MySQL 5.6.6, disabled before that. Consider disabling it if backward compatibility with MySQL 5.5 or 5.1 is a concern. This will prevent ALTER TABLE from moving InnoDB tables from the system tablespace to individual .ibd files.

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

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

  • innodb_fill_factor

    Introduced5.7.5
    Command-Line Format--innodb_fill_factor=#
    System VariableNameinnodb_fill_factor
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default100
    Min Value10
    Max Value100

    As of MySQL 5.7.5, InnoDB performs a bulk load when creating or rebuilding indexes. This method of index creation is known as a sorted index build.

    innodb_fill_factor defines the percentage of space on each B-tree page that is filled during a sorted index build, with the remaining space reserved for future index growth. For example, setting innodb_fill_factor to 80 reserves 20 percent of the space on each B-tree page for future index growth. Actual percentages may vary. The innodb_fill_factor setting is interpreted as a hint rather than a hard limit.

    As of MySQL 5.7.8, an innodb_fill_factor setting of 100 leaves 1/16 of the space in clustered index pages free for future index growth (MySQL Bug #74325).

    innodb_fill_factor applies to both B-tree leaf and non-leaf pages. It does not apply to external pages used for TEXT or BLOB entries.

    For more information, see Section 15.8.12, “Sorted Index Builds”.

  • innodb_flush_log_at_timeout

    System VariableNameinnodb_flush_log_at_timeout
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default1
    Min Value1
    Max Value2700

    Write and flush the logs every N seconds. innodb_flush_log_at_timeout was introduced in MySQL 5.6.6. It allows the timeout period between flushes to be increased in order to reduce flushing and avoid impacting performance of binary log group commit. Prior to MySQL 5.6.6, flushing frequency was once per second. The default setting for innodb_flush_log_at_timeout is also once per second.

  • innodb_flush_log_at_trx_commit

    Command-Line Format--innodb_flush_log_at_trx_commit[=#]
    System VariableNameinnodb_flush_log_at_trx_commit
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    Default1
    Valid Values0
    1
    2

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

    • The default value of 1 is required for full ACID compliance. With this value, the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk.

    • With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately once per second and the log file is flushed to disk. No writes from the log buffer to the log file are performed at transaction commit. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions with any mysqld process crash.

    • With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues. Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions in an operating system crash or a power outage.

    • As of MySQL 5.6.6, InnoDB log flushing frequency is controlled by innodb_flush_log_at_timeout, which allows you to set log flushing frequency to N seconds (where N is 1 ... 2700, with a default value of 1). However, any mysqld process crash can erase up to N seconds of transactions.

    • DDL changes and other internal InnoDB activities flush the InnoDB log independent of the innodb_flush_log_at_trx_commit setting.

    • InnoDB's crash recovery works regardless of the innodb_flush_log_at_trx_commit setting. Transactions are either applied entirely or erased entirely.

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

    • If binary logging is enabled, set sync_binlog=1.

    • Always set innodb_flush_log_at_trx_commit=1.

    Caution

    Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt 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

    Command-Line Format--innodb_flush_method=name
    System VariableNameinnodb_flush_method
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (Unix)Typestring
    DefaultNULL
    Valid Valuesfsync
    O_DSYNC
    littlesync
    nosync
    O_DIRECT
    O_DIRECT_NO_FSYNC
    Permitted Values (Windows)Typestring
    DefaultNULL
    Valid Valuesasync_unbuffered
    normal
    unbuffered

    Defines the method used to flush data to the InnoDB data files and log files, which can affect I/O throughput.

    If innodb_flush_method=NULL on a Unix-like system, the fsync option is used by default. If innodb_flush_method=NULL on Windows, the async_unbuffered option is used by default.

    The innodb_flush_method options for Unix-like systems include:

    • fsync: InnoDB uses the fsync() system call to flush both the data and log files. fsync is the default setting.

    • O_DSYNC: InnoDB uses O_SYNC to open and flush the log files, and fsync() to flush the data files. InnoDB does not use O_DSYNC directly because there have been problems with it on many varieties of Unix.

    • littlesync: This option is used for internal performance testing and is currently unsupported. Use at your own risk.

    • nosync: This option is used for internal performance testing and is currently unsupported. Use at your own risk.

    • O_DIRECT: InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.

    • O_DIRECT_NO_FSYNC: InnoDB uses O_DIRECT during flushing I/O, but skips the fsync() system call afterward. This setting is suitable for some types of file systems but not others. For example, it is not suitable for XFS. If you are not sure whether the file system you use requires an fsync(), for example to preserve all file metadata, use O_DIRECT instead. This option was introduced in MySQL 5.6.7 (Bug #11754304, Bug #45892).

    The innodb_flush_method options for Windows systems include:

    • async_unbuffered: InnoDB uses Windows asynchronous I/O and non-buffered I/O. async_unbuffered is the default setting on Windows systems.

    • normal: InnoDB uses a simulated asynchronous I/O and buffered I/O.

    • unbuffered: InnoDB uses a simulated asynchronous I/O and non-buffered I/O.

    How each settings affects performance depends on hardware configuration and workload. Benchmark your particular configuration to decide which setting to use, or whether to keep the default setting. Examine the Innodb_data_fsyncs status variable to see the overall number of fsync() calls for each setting. The mix of read and write operations in your workload can affect how a setting performs. For example, on a system with a hardware RAID controller and battery-backed write cache, O_DIRECT can help to avoid double buffering between the InnoDB buffer pool and the operating system's file system cache. On some systems where InnoDB data and log files are located on a SAN, the default value or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. Always test this parameter with hardware and workload that reflect your production environment. For general I/O tuning advice, see Section 9.5.8, “Optimizing InnoDB Disk I/O”.

  • innodb_flush_neighbors

    Command-Line Format--innodb_flush_neighbors
    System VariableNameinnodb_flush_neighbors
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    Default1
    Valid Values0
    1
    2

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

    • The default value of 1 flushes contiguous dirty pages in the same extent from the buffer pool.

    • A setting of 0 turns innodb_flush_neighbors off and no other dirty pages are flushed from the buffer pool.

    • A setting of 2 flushes dirty pages in the same extent from the buffer pool.

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

  • innodb_flush_sync

    Introduced5.7.8
    Command-Line Format--innodb_flush_sync=#
    System VariableNameinnodb_flush_sync
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

    The innodb_flush_sync parameter, which is enabled by default, causes the innodb_io_capacity setting to be ignored for bursts of I/O activity that occur at checkpoints. To adhere to the limit on InnoDB background I/O activity defined by the innodb_io_capacity setting, disable innodb_flush_sync.

  • innodb_flushing_avg_loops

    Command-Line Format--innodb_flushing_avg_loops=#
    System VariableNameinnodb_flushing_avg_loops
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default30
    Min Value1
    Max Value1000

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

  • innodb_force_load_corrupted

    Command-Line Format--innodb_force_load_corrupted
    System VariableNameinnodb_force_load_corrupted
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultOFF

    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

    Command-Line Format--innodb_force_recovery=#
    System VariableNameinnodb_force_recovery
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default0
    Min Value0
    Max Value6

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

    Warning

    Only set this variable to a value greater than 0 in an emergency situation, so that you can start InnoDB and dump your tables. As a safety measure, InnoDB prevents INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0. Also, as of 5.7.3, an innodb_force_recovery setting of 4 or greater places InnoDB into read-only mode.

    These restrictions may cause replication administration commands to fail with an error, as replication options such as --relay-log-info-repository=TABLE and --master-info-repository=TABLE store information in tables in InnoDB.

  • innodb_ft_aux_table

    Command-Line Format--innodb_ft_aux_table=# (>= 5.7.2)
    System VariableNameinnodb_ft_aux_table
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring

    Specifies the qualified name of an InnoDB table containing a FULLTEXT index. This variable is intended for diagnostic purposes.

    After you set this variable to a name in the format db_name/table_name, the INFORMATION_SCHEMA tables INNODB_FT_INDEX_TABLE, INNODB_FT_INDEX_CACHE, INNODB_FT_CONFIG, INNODB_FT_DELETED, and INNODB_FT_BEING_DELETED will show information about the search index for the specified table.

  • innodb_ft_cache_size

    Command-Line Format--innodb_ft_cache_size=#
    System VariableNameinnodb_ft_cache_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default8000000
    Min Value1600000
    Max Value80000000

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

  • innodb_ft_enable_diag_print

    Command-Line Format--innodb_ft_enable_diag_print=#
    System VariableNameinnodb_ft_enable_diag_print
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Whether to enable additional full-text search (FTS) diagnostic output. This option is primarily intended for advanced FTS debugging and will not be of interest to most users. Output is printed to the error log and includes information such as:

    • FTS index sync progress (when the FTS cache limit is reached). For example:

      FTS SYNC for table test, deleted count: 100 size: 10000 bytes
      SYNC words: 100
      
    • FTS optimize progress. For example:

      FTS start optimize test
      FTS_OPTIMIZE: optimize "mysql"
      FTS_OPTIMIZE: processed "mysql"
      
    • FTS index build progress. For example:

      Number of doc processed: 1000
      
    • For FTS queries, the query parsing tree, word weight, query processing time, and memory usage are printed. For example:

      FTS Search Processing time: 1 secs: 100 millisec: row(s) 10000
      Full Search Memory: 245666 (bytes),  Row: 10000
      
  • innodb_ft_enable_stopword

    Command-Line Format--innodb_ft_enable_stopword=#
    System VariableNameinnodb_ft_enable_stopword
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

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

  • innodb_ft_max_token_size

    Command-Line Format--innodb_ft_max_token_size=#
    System VariableNameinnodb_ft_max_token_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default84
    Min Value10
    Max Value84
    Permitted Values (<= 5.7.2)Typeinteger
    Default84
    Min Value10
    Max Value252
    Permitted Values (>= 5.7.3)Typeinteger
    Default84
    Min Value10
    Max Value84

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

  • innodb_ft_min_token_size

    Command-Line Format--innodb_ft_min_token_size=#
    System VariableNameinnodb_ft_min_token_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default3
    Min Value0
    Max Value16

    Minimum length of words that are stored in an InnoDB FULLTEXT index. Increasing this value reduces the size of the index, thus speeding up queries, by omitting common word that are unlikely to be significant in a search context, such as the English words a and to. For content using a CJK (Chinese, Japanese, Korean) character set, specify a value of 1.

  • innodb_ft_num_word_optimize

    Command-Line Format--innodb_ft_num_word_optimize=#
    System VariableNameinnodb_ft_num_word_optimize
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default2000

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

  • innodb_ft_result_cache_limit

    Introduced5.7.2
    Command-Line Format--innodb_ft_result_cache_limit=#
    System VariableNameinnodb_ft_result_cache_limit
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (>= 5.7.4)Typeinteger
    Default2000000000
    Min Value1000000
    Max Value2**32-1
    Permitted Values (Unix, 32-bit platforms, >= 5.7.2, <= 5.7.3)Typeinteger
    Default2000000000
    Min Value1000000
    Max Value2**32-1
    Permitted Values (Unix, 64-bit platforms, >= 5.7.2, <= 5.7.3)Typeinteger
    Default2000000000
    Min Value1000000
    Max Value2**64-1
    Permitted Values (Windows, >= 5.7.2, <= 5.7.3)Typeinteger
    Default2000000000
    Min Value1000000
    Max Value2**32-1

    The InnoDB FULLTEXT search (FTS) query result cache limit (defined in bytes) per FTS query or per thread. Intermediate and final InnoDB FTS query results are handled in memory. Use innodb_ft_result_cache_limit to place a size limit on the InnoDB FTS query result cache to avoid excessive memory consumption in case of very large InnoDB FTS query results (millions or hundreds of millions of rows, for example). Memory is allocated as required when an FTS query is processed. If the result cache size limit is reached, an error is returned indicating that the query exceeds the maximum allowed memory.

    As of MySQL 5.7.4, the maximum value of innodb_ft_result_cache_limit for all platform types and platform bit sizes is 2**32-1. Bug #71554.

  • innodb_ft_server_stopword_table

    Command-Line Format--innodb_ft_server_stopword_table=db_name/table_name
    System VariableNameinnodb_ft_server_stopword_table
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring
    DefaultNULL

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

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

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

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

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

  • innodb_ft_sort_pll_degree

    Command-Line Format--innodb_ft_sort_pll_degree=#
    System VariableNameinnodb_ft_sort_pll_degree
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default2
    Min Value1
    Max Value32

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

  • innodb_ft_total_cache_size

    Introduced5.7.2
    Command-Line Format--innodb_ft_total_cache_size=#
    System VariableNameinnodb_ft_total_cache_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default640000000
    Min Value32000000
    Max Value1600000000

    The total memory allocated, in bytes, for the InnoDB FULLTEXT search index cache for all tables. Creating numerous tables, each with a full-text search index, could consume a significant portion of available memory. innodb_ft_total_cache_size, defines a global memory limit for all full-text search indexes to help avoid excessive memory consumption. If the global limit is reached by an index operation, a force sync is triggered.

  • innodb_ft_user_stopword_table

    Command-Line Format--innodb_ft_user_stopword_table=db_name/table_name
    System VariableNameinnodb_ft_user_stopword_table
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypestring
    DefaultNULL

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

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

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

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

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

  • innodb_io_capacity

    Command-Line Format--innodb_io_capacity=#
    System VariableNameinnodb_io_capacity
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default200
    Min Value100
    Max Value2**32-1
    Permitted Values (64-bit platforms)Typeinteger
    Default200
    Min Value100
    Max Value2**64-1

    The innodb_io_capacity parameter sets an upper limit on the I/O activity performed by the InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer. The default value is 200. For busy systems capable of higher I/O rates, you can set a higher value at server startup, to help the server handle the background maintenance work associated with a high rate of row changes.

    The innodb_io_capacity limit is a total limit for all buffer pool instances. When dirty pages are flushed, the innodb_io_capacity limit is divided equally among buffer pool instances.

    As of MySQL 5.7.2, the innodb_io_capacity setting is also used to limit the number of buffer pool load operations per second when there is other I/O activity being performed by InnoDB background tasks.

    For systems with individual 5400 RPM or 7200 RPM drives, you might lower the value to the former default of 100.

    This parameter should be set to approximately the number of I/O operations that the system can perform per second. Ideally, keep this setting as low as practical, but not so low that these background activities fall behind. If the value is too high, data is removed from the buffer pool and insert buffer too quickly to provide significant benefit from the caching.

    The value represents an estimated proportion of the I/O operations per second (IOPS) available to older-generation disk drives that could perform about 100 IOPS. The current default of 200 reflects that modern storage devices are capable of much higher I/O rates.

    In general, you can increase the value as a function of the number of drives used for InnoDB I/O, particularly fast drives capable of high numbers of IOPS. For example, systems that use multiple disks or solid-state disks for InnoDB are likely to benefit from the ability to control this parameter.

    Although you can specify a very high number, in practice such large values have little if any benefit; for example, a value of one million would be considered very high.

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

    The innodb_flush_sync configuration option, introduced in MySQL 5.7.8, causes the innodb_io_capacity setting to be ignored during bursts of I/O activity that occur at checkpoints. innodb_flush_sync is enabled by default.

    See Section 15.6.8, “Configuring the InnoDB Master Thread I/O Rate” for more information about the innodb_io_capacity setting. For general information about InnoDB I/O performance, see Section 9.5.8, “Optimizing InnoDB Disk I/O”.

  • innodb_io_capacity_max

    Command-Line Format--innodb_io_capacity_max=#
    System VariableNameinnodb_io_capacity_max
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Defaultsee description
    Min Value100
    Max Value2**32-1
    Permitted Values (Unix, 64-bit platforms)Typeinteger
    Defaultsee description
    Min Value100
    Max Value2**64-1
    Permitted Values (Windows, 64-bit platforms)Typeinteger
    Defaultsee description
    Min Value100
    Max Value2**32-1

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

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

    innodb_io_capacity_max cannot be set to a value lower than the innodb_io_capacity value.

    Setting innodb_io_capacity_max to DEFAULT using a SET statement (SET GLOBAL innodb_io_capacity_max=DEFAULT) sets innodb_io_capacity_max to the maximum value.

    For a brief period during MySQL 5.6 development, this variable was known as innodb_max_io_capacity.

  • innodb_large_prefix

    Deprecated5.7.7
    Command-Line Format--innodb_large_prefix
    System VariableNameinnodb_large_prefix
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.6)Typeboolean
    DefaultOFF
    Permitted Values (>= 5.7.7)Typeboolean
    DefaultON

    When this option is enabled, index key prefixes longer than 767 bytes (up to 3072 bytes) are allowed for InnoDB tables that use the DYNAMIC and COMPRESSED row formats. See Section 15.8.8, “Limits on InnoDB Tables” for the relevant maximums associated with index key prefixes under various settings.

    For tables that use the REDUNDANT and COMPACT row formats, this option does not affect the permitted index key prefix length.

    innodb_large_prefix is enabled by default in MySQL 5.7.7. This change coincides with the default value change for innodb_file_format, which is set to Barracuda by default in MySQL 5.7.7. Together, these default value changes allow larger index key prefixes to be created when using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED. If either option is set to a non-default value, index key prefixes larger than 767 bytes are silently truncated.

    innodb_large_prefix is deprecated in MySQL 5.7.7 and will be removed in a future release. innodb_large_prefix was introduced in MySQL 5.5 to disable large index key prefixes for compatibility with earlier versions of InnoDB that do not support large index key prefixes.

  • innodb_limit_optimistic_insert_debug

    Command-Line Format--innodb_limit_optimistic_insert_debug=#
    System VariableNameinnodb_limit_optimistic_insert_debug
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Min Value0
    Max Value2**32-1

    Limits the number of records per B-tree page. A default value of 0 means that no limit is imposed. This option is only available if debugging support is compiled in using the WITH_DEBUG CMake option.

  • innodb_lock_wait_timeout

    Command-Line Format--innodb_lock_wait_timeout=#
    System VariableNameinnodb_lock_wait_timeout
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default50
    Min Value1
    Max Value1073741824

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

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

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

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

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

    The lock wait timeout value does not apply to deadlocks when innodb_deadlock_detect is enabled (the default) because InnoDB detects deadlocks immediately and rolls back one of the deadlocked transactions. When innodb_deadlock_detect is enabled, InnoDB relies on innodb_lock_wait_timeout for transaction rollback when a deadlock occurs.

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

  • innodb_locks_unsafe_for_binlog

    Deprecated5.6.3
    Command-Line Format--innodb_locks_unsafe_for_binlog
    System VariableNameinnodb_locks_unsafe_for_binlog
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultOFF

    This variable affects how InnoDB uses gap locking for searches and index scans. As of MySQL 5.6.3, innodb_locks_unsafe_for_binlog is deprecated and will be removed in a future MySQL release.

    Normally, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, 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 15.5.1, “InnoDB Locking”.

    By default, the value of innodb_locks_unsafe_for_binlog is 0 (disabled), which means that gap locking is enabled: InnoDB uses next-key locks for searches and index scans. To enable the variable, set it to 1. This causes gap locking to be disabled: InnoDB uses only index-record locks for searches and index scans.

    Enabling innodb_locks_unsafe_for_binlog does not disable the use of gap locking for foreign-key constraint checking or duplicate-key checking.

    The effect of enabling innodb_locks_unsafe_for_binlog is similar to but not identical to setting the transaction isolation level to READ COMMITTED:

    • Enabling innodb_locks_unsafe_for_binlog is a global setting and affects all sessions, whereas the isolation level can be set globally for all sessions, or individually per session.

    • innodb_locks_unsafe_for_binlog can be set only at server startup, whereas the isolation level can be set at startup or changed at runtime.

    READ COMMITTED therefore offers finer and more flexible control than innodb_locks_unsafe_for_binlog. For additional details about the effect of isolation level on gap locking, see Section 14.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 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. (Conflict serializability is still guaranteed.) For additional information about phantoms, see Section 15.5.4, “Phantom Rows”.

    Enabling innodb_locks_unsafe_for_binlog has additional effects:

    • For UPDATE or 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 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. 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 15.8.9, “Clustered and Secondary Indexes”).

    Suppose that one client performs an UPDATE using these statements:

    SET autocommit = 0;
    UPDATE t SET b = 5 WHERE b = 3;
    

    Suppose also that a second client performs an UPDATE by executing these statements following those of the first client:

    SET autocommit = 0;
    UPDATE t SET b = 4 WHERE b = 2;
    

    As InnoDB executes each UPDATE, it first acquires an exclusive lock for each row, and then determines whether to modify it. If InnoDB does not modify the row and innodb_locks_unsafe_for_binlog is enabled, it releases the lock. Otherwise, InnoDB retains the lock until the end of the transaction. This affects transaction processing as follows.

    If innodb_locks_unsafe_for_binlog is disabled, the first 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 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 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 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:

    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

    Command-Line Format--innodb_log_buffer_size=#
    System VariableNameinnodb_log_buffer_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (<= 5.7.5)Typeinteger
    Default8388608
    Min Value262144
    Max Value4294967295
    Permitted Values (>= 5.7.6)Typeinteger
    Default16777216
    Min Value1048576
    Max Value4294967295

    The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value changed from 8MB to 16MB in 5.7.6 with the introduction of 32k and 64k innodb_page_size values. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. For general I/O tuning advice, see Section 9.5.8, “Optimizing InnoDB Disk I/O”.

  • innodb_log_checksum_algorithm

    Introduced5.7.8
    Removed5.7.9
    Command-Line Format--innodb_log_checksum_algorithm=#
    System VariableNameinnodb_log_checksum_algorithm
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (>= 5.7.8)Typeenumeration
    Defaultinnodb
    Valid Valuesinnodb
    crc32
    none
    strict_innodb
    strict_crc32
    strict_none

    This configuration option was removed in MySQL 5.7.9 and replaced by innodb_log_checksums.

    Specifies how to generate and verify the checksum stored in each redo log disk block. innodb_log_checksum_algorithm supports same algorithms as innodb_checksum_algorithm. Previously, only the innodb algorithm was supported for redo log disk blocks. innodb_log_checksum_algorithm=innodb is the default setting.

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

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

    Table 15.13 Allowed Settings for innodb_log_checksum_algorithm

    ValueGenerated checksum (when writing)Allowed checksums (when reading)
    noneA constant number.Any of the checksums generated by none, innodb, or crc32.
    innodbA checksum calculated in software, using the original algorithm from InnoDB.Any of the checksums generated by none, innodb, or crc32.
    crc32A checksum calculated using the crc32 algorithm, possibly done with a hardware assist.Any of the checksums generated by none, innodb, or crc32.
    strict_noneA constant numberOnly the checksum generated by none.
    strict_innodbA checksum calculated in software, using the original algorithm from InnoDB.Only the checksum generated by innodb.
    strict_crc32A checksum calculated using the crc32 algorithm, possibly done with a hardware assist.Only the checksum generated by crc32.

  • innodb_log_checksums

    Introduced5.7.9
    Command-Line Format--innodb_log_checksums=#
    System VariableNameinnodb_log_checksums
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

    Enables or disables checksums for redo log pages. innodb_log_checksums replaces innodb_log_checksum_algorithm, which was removed in MySQL 5.7.9.

    innodb_log_checksums=ON enables the CRC-32C checksum algorithm for redo log pages. When innodb_log_checksums is disabled, the contents of the redo log page checksum field are ignored.

    Checksums on the redo log header page and redo log checkpoint pages are never disabled.

  • innodb_log_compressed_pages

    Command-Line Format--innodb_log_compressed_pages=#
    System VariableNameinnodb_log_compressed_pages
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

    Specifies whether images of re-compressed pages are written to the redo log. Re-compression may occur when changes are made to compressed data.

    innodb_log_compressed_pages is enabled by default to prevent corruption that could occur if a different version of the zlib compression algorithm is used during recovery. If you are certain that the zlib version will not change, you can disable innodb_log_compressed_pages to reduce redo log generation for workloads that modify compressed data.

    To measure the effect of enabling or disabling innodb_log_compressed_pages, compare redo log generation for both settings under the same workload. Options for measuring redo log generation include observing the Log sequence number (LSN) in the LOG section of SHOW ENGINE INNODB STATUS output, or monitoring Innodb_os_log_written status for the number of bytes written to the redo log files.

  • innodb_log_file_size

    Command-Line Format--innodb_log_file_size=#
    System VariableNameinnodb_log_file_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (<= 5.7.10)Typeinteger
    Default50331648
    Min Value1048576
    Max Value512GB / innodb_log_files_in_group
    Permitted Values (>= 5.7.11)Typeinteger
    Default50331648
    Min Value4194304
    Max Value512GB / innodb_log_files_in_group

    The size in bytes of each log file in a log group. The combined size of log files (innodb_log_file_size * innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 512GB. A pair of 255 GB log files, for example, would allow you to approach the limit but not exceed it. The default value is 48MB. Sensible values range from 4MB 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 9.5.8, “Optimizing InnoDB Disk I/O”.

    The minimum innodb_log_file_size value was increased from 1MB to 4MB in MySQL 5.7.11.

  • innodb_log_files_in_group

    Command-Line Format--innodb_log_files_in_group=#
    System VariableNameinnodb_log_files_in_group
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default2
    Min Value2
    Max Value100

    The number of log files in the log group. InnoDB writes to the files in a circular fashion. The default (and recommended) value is 2. The location of these files is specified by innodb_log_group_home_dir. The combined size of log files (innodb_log_file_size * innodb_log_files_in_group) can be up to 512GB.

  • innodb_log_group_home_dir

    Command-Line Format--innodb_log_group_home_dir=dir_name
    System VariableNameinnodb_log_group_home_dir
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypedirectory name

    The directory path to the InnoDB redo log files, whose number is specified by 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 system variable.

  • innodb_log_write_ahead_size

    Introduced5.7.4
    Command-Line Format--innodb_log_write_ahead_size=#
    System VariableNameinnodb_log_write_ahead_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default8192
    Min Value512 (log file block size)
    Max ValueEqual to innodb_page_size

    The write-ahead block size for the redo log, in bytes. To avoid read-on-write, set innodb_log_write_ahead_size to match the operating system or file system cache block size. Read-on-write occurs when redo log blocks are not entirely cached to the operating system or file system due to a mismatch between write-ahead block size for redo logs and operating system or file system cache block size.

    Valid values for innodb_log_write_ahead_size are multiples of the InnoDB log file block size (2^n). The minimum value is the InnoDB log file block size (512). Write-ahead does not occur when the minimum value is specified. The maximum value is equal to innodb_page_size. If you specify a value for innodb_log_write_ahead_size that is larger than the innodb_page_size value, the innodb_log_write_ahead_size value is truncated to the innodb_page_size value.

    Setting the innodb_log_write_ahead_size value too low in relation to the operating system or file system cache block size will result in read-on-write. Setting the value too high may have a slight impact on fsync performance for log file writes due to several blocks being written at once.

  • innodb_lru_scan_depth

    Command-Line Format--innodb_lru_scan_depth=#
    System VariableNameinnodb_lru_scan_depth
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default1024
    Min Value100
    Max Value2**32-1
    Permitted Values (64-bit platforms)Typeinteger
    Default1024
    Min Value100
    Max Value2**64-1

    A parameter that influences the algorithms and heuristics for the flush operation for the InnoDB buffer pool. Primarily of interest to performance experts tuning I/O-intensive workloads. It specifies, per buffer pool instance, how far down the buffer pool LRU list the page_cleaner thread scans looking for dirty pages to flush. This is a background operation performed once a second. If you have spare I/O capacity under a typical workload, increase the value. If a write-intensive workload saturates your I/O capacity, decrease the value, especially if you have a large buffer pool. For general I/O tuning advice, see Section 9.5.8, “Optimizing InnoDB Disk I/O”.

  • innodb_max_dirty_pages_pct

    Command-Line Format--innodb_max_dirty_pages_pct=#
    System VariableNameinnodb_max_dirty_pages_pct
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.4)Typenumeric
    Default75
    Min Value0
    Max Value99
    Permitted Values (>= 5.7.5)Typenumeric
    Default75
    Min Value0
    Max Value99.99

    InnoDB tries to flush data from the buffer pool so that the percentage of dirty pages does not exceed this value. The default value is 75.

    The innodb_max_dirty_pages_pct setting establishes a target for flushing activity. It does not affect the rate of flushing. For information about managing the rate of flushing, see Section 15.6.3.6, “Configuring InnoDB Buffer Pool Flushing”.

    For additional information about this variable, see Section 15.6.3.7, “Fine-tuning InnoDB Buffer Pool Flushing”. For general I/O tuning advice, see Section 9.5.8, “Optimizing InnoDB Disk I/O”.

  • innodb_max_dirty_pages_pct_lwm

    Command-Line Format--innodb_max_dirty_pages_pct_lwm=#
    System VariableNameinnodb_max_dirty_pages_pct_lwm
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.7.4)Typenumeric
    Default0
    Min Value0
    Max Value99
    Permitted Values (>= 5.7.5)Typenumeric
    Default0
    Min Value0
    Max Value99.99

    Low water mark representing percentage of dirty pages where preflushing is enabled to control the dirty page ratio. The default of 0 disables the pre-flushing behavior entirely. For additional information about this variable, see Section 15.6.3.7, “Fine-tuning InnoDB Buffer Pool Flushing”.

  • innodb_max_purge_lag

    Command-Line Format--innodb_max_purge_lag=#
    System VariableNameinnodb_max_purge_lag
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Min Value0
    Max Value4294967295

    This variable controls how to delay INSERT, UPDATE, and DELETE operations when purge operations are lagging (see Section 15.3, “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 or DELETE operations. The length of this list represents the purge_lag value. When purge_lag exceeds innodb_max_purge_lag, each INSERT, UPDATE, and DELETE operation is delayed.

    To prevent excessive delays in extreme situations where purge_lag becomes huge, you can put a cap on the amount of delay by setting the innodb_max_purge_lag_delay configuration option. The delay is computed at the beginning of a purge batch.

    A typical setting for a problematic workload might be 1 million, assuming that transactions are small, only 100 bytes in size, and it is permissible to have 100MB of unpurged 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 9.5.8, “Optimizing InnoDB Disk I/O”.

  • innodb_max_purge_lag_delay

    Command-Line Format--innodb_max_purge_lag_delay=#
    System VariableNameinnodb_max_purge_lag_delay
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Min Value0

    Specifies the maximum delay in milliseconds for the delay imposed by the innodb_max_purge_lag configuration option. Any non-zero value represents an upper limit on the delay period computed from the formula based on the value of innodb_max_purge_lag. The default of zero means that there is no upper limit imposed on the delay interval.

    For general I/O tuning advice, see Section 9.5.8, “Optimizing InnoDB Disk I/O”.

  • innodb_max_undo_log_size

    Introduced5.7.5
    Command-Line Format--innodb_max_undo_log_size=#
    System VariableNameinnodb_max_undo_log_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default1073741824
    Min Value10485760
    Max Value2**64-1

    innodb_max_undo_log_size defines a threshold size for undo tablespaces. If an undo tablespace exceeds the threshold, it can be marked for truncation when innodb_undo_log_truncate is enabled. The default value is 1024 MiB (1073741824 bytes).

    For more information, see Section 15.7.8, “Truncating Undo Logs That Reside in Undo Tablespaces”.

  • innodb_merge_threshold_set_all_debug

    Introduced5.7.6
    Command-Line Format--innodb_merge_threshold_set_all_debug=#
    System VariableNameinnodb_merge_threshold_set_all_debug
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default50
    Min Value1
    Max Value50

    Overrides the current MERGE_THRESHOLD setting with the specified value for all indexes that are currently in the dictionary cache. This option is only available if debugging support is compiled in using the WITH_DEBUG CMake option. For related information, see Section 15.6.12, “Configuring the Merge Threshold for Index Pages”.

  • innodb_monitor_disable

    Command-Line Format--innodb_monitor_disable=[counter|module|pattern|all]
    System VariableNameinnodb_monitor_disable
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring

    Turns off one or more counters in the INFORMATION_SCHEMA.INNODB_METRICS table. For usage information, see Section 22.31.20, “The INFORMATION_SCHEMA INNODB_METRICS Table”.

    As of MySQL 5.7.8, innodb_monitor_disable='latch' disables statistics collection for SHOW ENGINE INNODB MUTEX. For more information, see Section 14.7.5.15, “SHOW ENGINE Syntax”.

  • innodb_monitor_enable

    Command-Line Format--innodb_monitor_enable=[counter|module|pattern|all]
    System VariableNameinnodb_monitor_enable
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring

    Turns on one or more counters in the INFORMATION_SCHEMA.INNODB_METRICS table. For usage information, see Section 22.31.20, “The INFORMATION_SCHEMA INNODB_METRICS Table”.

    As of MySQL 5.7.8, innodb_monitor_enable='latch' enables statistics collection for SHOW ENGINE INNODB MUTEX. For more information, see Section 14.7.5.15, “SHOW ENGINE Syntax”.

  • innodb_monitor_reset

    Command-Line Format--innodb_monitor_reset=[counter|module|pattern|all]
    System VariableNameinnodb_monitor_reset
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring

    Resets to zero the count value for one or more counters in the INFORMATION_SCHEMA.INNODB_METRICS table. For usage information, see Section 22.31.20, “The INFORMATION_SCHEMA INNODB_METRICS Table”.

    As of MySQL 5.7.8, innodb_monitor_reset='latch' resets statistics reported by SHOW ENGINE INNODB MUTEX. For more information, see Section 14.7.5.15, “SHOW ENGINE Syntax”.

  • innodb_monitor_reset_all

    Command-Line Format--innodb_monitor_reset_all=[counter|module|pattern|all]
    System VariableNameinnodb_monitor_reset_all
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypestring

    Resets all values (minimum, maximum, and so on) for one or more counters in the INFORMATION_SCHEMA.INNODB_METRICS table. For usage information, see Section 22.31.20, “The INFORMATION_SCHEMA INNODB_METRICS Table”.

  • innodb_numa_interleave

    Introduced5.7.9
    Command-Line Format--innodb_numa_interleave=#
    System VariableNameinnodb_numa_interleave
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultOFF

    Enables the NUMA interleave memory policy for allocation of the InnoDB buffer pool. When innodb_numa_interleave is enabled, the NUMA memory policy is set to MPOL_INTERLEAVE for the mysqld process. After the InnoDB buffer pool is allocated, the NUMA memory policy is set back to MPOL_DEFAULT. For the innodb_numa_interleave option to be available, MySQL must be compiled on a NUMA-enabled system.

  • innodb_old_blocks_pct

    Command-Line Format--innodb_old_blocks_pct=#
    System VariableNameinnodb_old_blocks_pct
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default37
    Min Value5
    Max Value95

    Specifies the approximate percentage of the InnoDB buffer pool used for the old block sublist. The range of values is 5 to 95. The default value is 37 (that is, 3/8 of the pool). Often used in combination with innodb_old_blocks_time. See Section 15.6.3.4, “Making the Buffer Pool Scan Resistant” for more information. See Section 15.6.3.1, “The InnoDB Buffer Pool” for information about buffer pool management, such as the LRU algorithm and eviction policies.

  • innodb_old_blocks_time

    Command-Line Format--innodb_old_blocks_time=#
    System VariableNameinnodb_old_blocks_time
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default1000
    Min Value0
    Max Value2**32-1

    Non-zero values protect against the buffer pool being filled up by data that is referenced only for a brief period, such as during a full table scan. Increasing this value offers more protection against full table scans interfering with data cached in the buffer pool.

    Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. If the value is 0, a block inserted into the old sublist moves immediately to the new sublist the first time it is accessed, no matter how soon after insertion the access occurs. If the value is greater than 0, blocks remain in the old sublist until an access occurs at least that many ms after the first access. For example, a value of 1000 causes blocks to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist.

    The default value is 1000.

    This variable is often used in combination with innodb_old_blocks_pct. See Section 15.6.3.4, “Making the Buffer Pool Scan Resistant” for more information. See Section 15.6.3.1, “The InnoDB Buffer Pool” for information about buffer pool management, such as the LRU algorithm and eviction policies.

  • innodb_online_alter_log_max_size

    Command-Line Format--innodb_online_alter_log_max_size=#
    System VariableNameinnodb_online_alter_log_max_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default134217728
    Min Value65536
    Max Value2**64-1

    Specifies an upper limit on the size of the temporary log files used during online DDL operations for InnoDB tables. There is one such log file for each index being created or table being altered. This log file stores data inserted, updated, or deleted in the table during the DDL operation. The temporary log file is extended when needed by the value of innodb_sort_buffer_size, up to the maximum specified by innodb_online_alter_log_max_size. If any temporary log file exceeds the upper size limit, the ALTER TABLE operation fails and all uncommitted concurrent DML operations are rolled back. Thus, a large value for this option allows more DML to happen during an online DDL operation, but also causes a longer period at the end of the DDL operation when the table is locked to apply the data from the log.

  • innodb_open_files

    Command-Line Format--innodb_open_files=#
    System VariableNameinnodb_open_files
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default-1 (autosized)
    Min Value10
    Max Value4294967295

    This variable is relevant only if you use multiple InnoDB tablespaces. It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10. The default value is 300 if innodb_file_per_table is not enabled, and the higher of 300 and table_open_cache otherwise.

    The file descriptors used for .ibd files are for InnoDB tables only. They are independent of those specified by the --open-files-limit server option, and do not affect the operation of the table cache. For general I/O tuning advice, see Section 9.5.8, “Optimizing InnoDB Disk I/O”.

  • innodb_optimize_fulltext_only

    Command-Line Format--innodb_optimize_fulltext_only=#
    System VariableNameinnodb_optimize_fulltext_only
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Changes the way the OPTIMIZE TABLE statement operates on InnoDB tables. Intended to be enabled temporarily, during maintenance operations for InnoDB tables with FULLTEXT indexes.

    By default, OPTIMIZE TABLE reorganizes the data in the clustered index of the table. When this option is enabled, OPTIMIZE TABLE skips this reorganization of the table data, and instead processes the newly added, deleted, and updated token data for a FULLTEXT index, See Section 15.8.10, “InnoDB FULLTEXT Indexes” for more information about FULLTEXT indexes for InnoDB tables.

  • innodb_optimize_point_storage

    Introduced5.7.5
    Removed5.7.6
    Command-Line Format--innodb_optimize_point_storage=#
    System VariableNameinnodb_optimize_point_storage
    Variable ScopeSession
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Enable this variable before creating a column of type POINT to store the POINT data type internally as variable-length BLOB data.

    This variable was removed in MySQL 5.7.6.

  • innodb_page_cleaners

    Introduced5.7.4
    Command-Line Format--innodb_page_cleaners=#
    System VariableNameinnodb_page_cleaners
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (<= 5.7.7)Typeinteger
    Default1
    Min Value1
    Max Value64
    Permitted Values (>= 5.7.8)Typeinteger
    Default4
    Min Value1
    Max Value64

    The number of page cleaner threads that flush dirty pages from buffer pool instances. The page cleaner threads perform flush list and LRU flushing. A single page cleaner thread was introduced in MySQL 5.6.2 to offload buffer pool flushing work from the InnoDB master thread. As of MySQL 5.7.4, InnoDB provides support for multiple page cleaner threads. A value of 1 maintains the pre-MySQL 5.7.4 configuration in which there is a single page cleaner thread. When there are multiple page cleaner threads, buffer pool flushing tasks for each buffer pool instance are dispatched to idle page cleaner threads. The innodb_page_cleaners default value was changed from 1 to 4 in MySQL 5.7.8. If the number of page cleaner threads exceeds the number of buffer pool instances, innodb_page_cleaners is automatically set to the same value as innodb_buffer_pool_instances.

    If your workload is write-IO bound (when flushing dirty pages from buffer pool instances to data files) and if your system hardware has available capacity, increasing the number of page cleaner threads may help improve write-IO throughput.

    Multi-threaded page cleaner support is extended to shutdown and recovery phases in MySQL 5.7.5.

    As of MySQL 5.7.6, the setpriority() system call is used on Linux platforms (where it is supported and where the mysqld execution user is authorized) to give page_cleaner threads priority over other MySQL/InnoDB threads to help page flushing keep pace with the current workload. mysqld execution user authorization can be configured in /etc/security/limits.conf. For example, if mysqld is run under the mysql user, you might authorize the mysql user by adding lines similar to the following to /etc/security/limits.conf:

    mysql              hard    nice       -20
    mysql              soft    nice       -20

    Refer to your Linux operating system documentation for more information.

  • innodb_page_size

    Command-Line Format--innodb_page_size=#k
    System VariableNameinnodb_page_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (<= 5.7.5)Typeenumeration
    Default16384
    Valid Values4k
    8k
    16k
    4096
    8192
    16384
    Permitted Values (>= 5.7.6)Typeenumeration
    Default16384
    Valid Values4k
    8k
    16k
    32k
    64k
    4096
    8192
    16384
    32768
    65536

    Specifies the page size for all InnoDB tablespaces in a MySQL instance. You can specify page size using the values 64k, 32k, 16k (the default), 8k, or 4k. Alternatively, you can specify page size in bytes (65536, 32768, 16384, 8192, 4096).

    Important

    innodb_page_size can only be configured prior to initializing the MySQL instance and cannot be changed afterward. If no value is specified, the instance is initialized using the default page size.

    Support for 32k and 64k page sizes was added in MySQL 5.7.6. For both 32k and 64k page sizes, the maximum row length is approximately 16000 bytes. ROW_FORMAT=COMPRESSED is not supported when innodb_page_size is set to 32KB or 64KB. For innodb_page_size=32k, extent size is 2MB. For innodb_page_size=64k, extent size is 4MB. innodb_log_buffer_size should be set to at least 16M (which is the default as of MySQL 5.7.6) when using 32k or 64k page sizes.

    The default page size of 16KB and larger is appropriate for a wide range of workloads, particularly for queries involving table scans and DML operations involving bulk updates. Smaller page sizes might be more efficient for OLTP workloads involving many small writes, where contention can be an issue when a single page contains many rows. Smaller pages might also be efficient with SSD storage devices, which typically use small block sizes. Keeping the InnoDB page size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk.

    The minimum file size for the first system tablespace data file (ibdata1) differs depending on the innodb_page_size value. See the innodb_data_file_path option description for more information.

    For general I/O tuning advice, see Section 9.5.8, “Optimizing InnoDB Disk I/O”.

  • innodb_print_all_deadlocks

    Command-Line Format--innodb_print_all_deadlocks=#
    System VariableNameinnodb_print_all_deadlocks
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    When this option is enabled, information about all deadlocks in InnoDB user transactions is recorded in the mysqld 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

    Command-Line Format--innodb_purge_batch_size=#
    System VariableNameinnodb_purge_batch_size
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default300
    Min Value1
    Max Value5000

    Defines the number of undo log pages that purge parses and processes in one batch from the history list. In a multi-threaded purge configuration, the coordinator purge thread divides innodb_purge_batch_size by innodb_purge_threads and assigns that number of pages to each purge thread. The innodb_purge_batch_size option also defines the number of undo log pages that purge frees after every 128 iterations through the undo logs.

    The innodb_purge_batch_size option is intended for advanced performance tuning in combination with the innodb_purge_threads setting. Most MySQL users need not change innodb_purge_batch_size from its default value.

  • innodb_purge_threads

    Command-Line Format--innodb_purge_threads=#
    System VariableNameinnodb_purge_threads
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (<= 5.7.7)Typeinteger
    Default1
    Min Value1
    Max Value32
    Permitted Values (>= 5.7.8)Typeinteger
    Default4
    Min Value1
    Max Value32

    The number of background threads devoted to the InnoDB purge operation. A minimum value of 1 signifies that the purge operation is always performed by background threads, never as part of the master thread. Running the purge operation in one or more background threads helps reduce internal contention within InnoDB, improving scalability. Increasing the value to greater than 1 creates that many separate purge threads, which can improve efficiency on systems where DML operations are performed on multiple tables. The maximum is 32.

    The innodb_purge_threads default value was changed from 1 to 4 in MySQL 5.7.8.

  • innodb_purge_rseg_truncate_frequency

    Introduced5.7.5
    Command-Line Format--innodb_purge_rseg_truncate_frequency=#
    System VariableNameinnodb_purge_rseg_truncate_frequency
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default128
    Min Value1
    Max Value128

    innodb_purge_rseg_truncate_frequency defines the frequency with which the purge system frees rollback segments. An undo tablespace cannot be truncated until its rollback segments are freed. Normally, the purge system frees rollback segments once every 128 times that purge is invoked. Reducing the innodb_purge_rseg_truncate_frequency value increases the frequency with which the purge thread frees rollback segments. The default value is 128.

    innodb_purge_rseg_truncate_frequency is intended for use with innodb_undo_log_truncate. For more information, see Section 15.7.8, “Truncating Undo Logs That Reside in Undo Tablespaces”.

  • innodb_random_read_ahead

    Command-Line Format--innodb_random_read_ahead=#
    System VariableNameinnodb_random_read_ahead
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Enables the random read-ahead technique for optimizing InnoDB I/O.

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

  • innodb_read_ahead_threshold

    Command-Line Format--innodb_read_ahead_threshold=#
    System VariableNameinnodb_read_ahead_threshold
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default56
    Min Value0
    Max Value64

    Controls the sensitivity of linear 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 (64 pages), it initiates an asynchronous read for the entire following extent. The permissible range of values is 0 to 64. A value of 0 disables read-ahead. For the default of 56, InnoDB must read at least 56 pages sequentially from an extent to initiate an asynchronous read for the following extent.

    Knowing how many pages are read through 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 parameter. As of MySQL 5.5, SHOW ENGINE INNODB STATUS output displays counter information from the Innodb_buffer_pool_read_ahead and 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 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 15.6.3.5, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)” for more information. For general I/O tuning advice, see Section 9.5.8, “Optimizing InnoDB Disk I/O”.

  • innodb_read_io_threads

    Command-Line Format--innodb_read_io_threads=#
    System VariableNameinnodb_read_io_threads
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default4
    Min Value1
    Max Value64

    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. See Section 15.6.7, “Configuring the Number of Background InnoDB I/O Threads” for more information. For general I/O tuning advice, see Section 9.5.8, “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, 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_read_only

    Command-Line Format--innodb_read_only=#
    System VariableNameinnodb_read_only
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultOFF

    Starts InnoDB in read-only mode. For distributing database applications or data sets on read-only media. Can also be used in data warehouses to share the same data directory between multiple instances. See Section 15.6.2, “Configuring InnoDB for Read-Only Operation” for usage instructions.

  • innodb_replication_delay

    Command-Line Format--innodb_replication_delay=#
    System VariableNameinnodb_replication_delay
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Min Value0
    Max Value4294967295

    The replication thread delay (in ms) on a slave server if innodb_thread_concurrency is reached.

  • innodb_rollback_on_timeout

    Command-Line Format--innodb_rollback_on_timeout
    System VariableNameinnodb_rollback_on_timeout
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultOFF

    In MySQL 5.7, InnoDB rolls back only the last statement on a transaction timeout by default. If --innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction.

  • innodb_rollback_segments

    Command-Line Format--innodb_rollback_segments=#
    System VariableNameinnodb_rollback_segments
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default128
    Min Value1
    Max Value128

    Defines how many of the rollback segments in the system tablespace are used for InnoDB transactions. This setting, while still valid, is replaced by innodb_undo_logs.

  • innodb_saved_page_number_debug

    Command-Line Format--innodb_saved_page_number_debug=#
    System VariableNameinnodb_saved_page_number_debug
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Max Value2**23-1

    Saves a page number. Setting the innodb_fil_make_page_dirty_debug option dirties the page defined by innodb_saved_page_number_debug. The innodb_saved_page_number_debug option is only available if debugging support is compiled in using the WITH_DEBUG CMake option.

  • innodb_sort_buffer_size

    Command-Line Format--innodb_sort_buffer_size=#
    System VariableNameinnodb_sort_buffer_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default1048576
    Min Value65536
    Max Value67108864

    Specifies the size of sort buffers used for sorting data during creation of an InnoDB index. The size specified defines the amount of data filled in memory for an internal sort and written out to disk, which can be referred to as a run. During the merge phase, pairs of buffers of the specified size are read in and merged. The larger the setting, the fewer runs and merges there are, which is important to understand from a tuning perspective.

    This sort area is only used for merge sorts during index creation, not during later index maintenance operations. Buffers are deallocated when index creation completes.

    The value of this option also controls the amount by which the temporary log file is extended, to record concurrent DML during online DDL operations.

    Before this setting was made configurable, the size was hardcoded to 1048576 bytes (1MB), and that value remains the default.

    During an ALTER TABLE or CREATE TABLE statement that creates an index, 3 buffers are allocated, each with a size defined by this option. Additionally, auxiliary pointers are allocated to rows in the sort buffer so that the sort can run on pointers (as opposed to moving rows during the sort operation).

    For a typical sort operation, a formula such as this can be used to estimate memory consumption:

    (6 /*FTS_NUM_AUX_INDEX*/ * (3*@@global.innodb_sort_buffer_size)
    + 2 * number_of_partitions * number_of_secondary_indexes_created
    * (@@global.innodb_sort_buffer_size/dict_index_get_min_size(index)*/)
    * 8 /*64-bit sizeof *buf->tuples*/")
    

    @@global.innodb_sort_buffer_size/dict_index_get_min_size(index) indicates the maximum tuples held. 2 * (@@global.innodb_sort_buffer_size/*dict_index_get_min_size(index)*/) * 8 /*64-bit size of *buf->tuples*/ indicates auxiliary pointers allocated.

    Note

    For 32-bit, multiply by 4 instead of 8.

    For parallel sorts on a full-text index, multiply by the innodb_ft_sort_pll_degree setting:

    (6 /*FTS_NUM_AUX_INDEX*/ * @@global.innodb_ft_sort_pll_degree)
    
  • innodb_spin_wait_delay

    Command-Line Format--innodb_spin_wait_delay=#
    System VariableNameinnodb_spin_wait_delay
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (32-bit platforms)Typeinteger
    Default6
    Min Value0
    Max Value2**32-1
    Permitted Values (64-bit platforms)Typeinteger
    Default6
    Min Value0
    Max Value2**64-1

    The maximum delay between polls for a spin lock. The low-level implementation of this mechanism varies depending on the combination of hardware and operating system, so the delay does not correspond to a fixed time interval. The default value is 6. See Section 15.6.9, “Configuring Spin Lock Polling” for more information.

  • innodb_stats_auto_recalc

    Command-Line Format--innodb_stats_auto_recalc=#
    System VariableNameinnodb_stats_auto_recalc
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

    Causes InnoDB to automatically recalculate persistent statistics after the data in a table is changed substantially. The threshold value is currently 10% of the rows in the table. This setting applies to tables created when the innodb_stats_persistent option is enabled, or where the clause STATS_PERSISTENT=1 is enabled by a CREATE TABLE or ALTER TABLE statement. The amount of data sampled to produce the statistics is controlled by the innodb_stats_persistent_sample_pages configuration option.

    For additional information about innodb_stats_auto_recalc, see Section 15.6.11.1, “Configuring Persistent Optimizer Statistics Parameters”.

  • innodb_stats_include_delete_marked

    Introduced5.7.16
    Command-Line Format--innodb_stats_include_delete_marked=#
    System VariableNameinnodb_stats_include_delete_marked
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    By default, InnoDB reads uncommitted data when calculating statistics. In the case of an uncommitted transaction that deletes rows from a table, InnoDB excludes records that are delete-marked when calculating row estimates and index statistics, which can lead to non-optimal execution plans for other transactions that are operating on the table concurrently using a transaction isolation level other than READ UNCOMMITTED. To avoid this scenario, innodb_stats_include_delete_marked can be enabled to ensure that InnoDB includes delete-marked records when calculating persistent optimizer statistics.

    When innodb_stats_include_delete_marked is enabled, ANALYZE TABLE considers delete-marked records when recalculating statistics.

    innodb_stats_include_delete_marked is a global setting that affects all InnoDB tables, and it is only applicable to persistent optimizer statistics.

    For related information, see Section 15.6.11.1, “Configuring Persistent Optimizer Statistics Parameters”.

  • innodb_stats_method

    Command-Line Format--innodb_stats_method=name
    System VariableNameinnodb_stats_method
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeenumeration
    Defaultnulls_equal
    Valid Valuesnulls_equal
    nulls_unequal
    nulls_ignored

    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 9.3.7, “InnoDB and MyISAM Index Statistics Collection”.

  • innodb_stats_on_metadata

    Command-Line Format--innodb_stats_on_metadata
    System VariableNameinnodb_stats_on_metadata
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    When this variable is enabled, InnoDB updates statistics when metadata statements such as SHOW TABLE STATUS or when accessing the INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS tables. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Leaving this setting disabled can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that 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.

    This variable is disabled by default.

  • innodb_stats_persistent

    Command-Line Format--innodb_stats_persistent=setting
    System VariableNameinnodb_stats_persistent
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON
    Valid ValuesOFF
    ON
    0
    1

    Specifies whether InnoDB index statistics are persisted to disk. Otherwise, statistics may be recalculated frequently which can lead to variations in query execution plans. This setting is stored with each table when the table is created. You can set innodb_stats_persistent at the global level before creating a table, or use the STATS_PERSISTENT clause of the CREATE TABLE and ALTER TABLE statements to override the system-wide setting and configure persistent statistics for individual tables.

    For more information about this option, see Section 15.6.11.1, “Configuring Persistent Optimizer Statistics Parameters”.

  • innodb_stats_persistent_sample_pages

    Command-Line Format--innodb_stats_persistent_sample_pages=#
    System VariableNameinnodb_stats_persistent_sample_pages
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default20

    The number of index pages to sample when estimating cardinality and other statistics for an indexed column, such as those calculated by ANALYZE TABLE. Increasing the value improves the accuracy of index statistics, which can improve the query execution plan, at the expense of increased I/O during the execution of ANALYZE TABLE for an InnoDB table. For additional information, see Section 15.6.11.1, “Configuring Persistent Optimizer Statistics Parameters”.

    Note

    Setting a high value for innodb_stats_persistent_sample_pages could result in lengthy ANALYZE TABLE execution time. To estimate the number of database pages that will be accessed, see Section 15.6.11.3, “Estimating ANALYZE TABLE Complexity for InnoDB Tables”.

    This option only applies when the innodb_stats_persistent setting is turned on for a table; when that option is turned off for a table, the innodb_stats_transient_sample_pages setting applies instead.

  • innodb_stats_sample_pages

    Deprecated5.6.3
    Command-Line Format--innodb_stats_sample_pages=#
    System VariableNameinnodb_stats_sample_pages
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default8
    Min Value1
    Max Value2**64-1

    Deprecated, use innodb_stats_transient_sample_pages instead.

  • innodb_stats_transient_sample_pages

    Command-Line Format--innodb_stats_transient_sample_pages=#
    System VariableNameinnodb_stats_transient_sample_pages
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default8

    The number of index pages to sample when estimating cardinality and other statistics for an indexed column, such as those calculated by ANALYZE TABLE. The default value is 8. Increasing the value improves the accuracy of index statistics, which can improve the query execution plan, at the expense of increased I/O when opening an InnoDB table or recalculating statistics. For additional information, see Section 15.6.11.2, “Configuring Non-Persistent Optimizer Statistics Parameters”.

    Note

    Setting a high value for innodb_stats_transient_sample_pages could result in lengthy ANALYZE TABLE execution time. To estimate the number of database pages that will be accessed, see Section 15.6.11.3, “Estimating ANALYZE TABLE Complexity for InnoDB Tables”.

    This option only applies when the innodb_stats_persistent setting is turned off for a table; when this option is turned on for a table, the innodb_stats_persistent_sample_pages setting applies instead. Takes the place of the innodb_stats_sample_pages option. See Section 15.6.11.2, “Configuring Non-Persistent Optimizer Statistics Parameters” for more information.

  • innodb_status_output

    Introduced5.7.4
    Command-Line Format--innodb_status_output
    System VariableNameinnodb_status_output
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Enables or disables periodic output for the standard InnoDB Monitor. Also used in combination with innodb_status_output_locks to enable or disable periodic output for the InnoDB Lock Monitor. See Section 15.17, “InnoDB Monitors” for additional information.

  • innodb_status_output_locks

    Introduced5.7.4
    Command-Line Format--innodb_status_output_locks
    System VariableNameinnodb_status_output_locks
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Enables or disables the InnoDB Lock Monitor. When enabled, the InnoDB Lock Monitor prints additional information about locks in SHOW ENGINE INNODB STATUS output and in periodic output printed to the MySQL error log. Periodic output for the InnoDB Lock Monitor is printed as part of the standard InnoDB Monitor output. The standard InnoDB Monitor must therefore be enabled for the InnoDB Lock Monitor to print data to the MySQL error log periodically. See Section 15.17, “InnoDB Monitors” for more information.

  • innodb_strict_mode

    Command-Line Format--innodb_strict_mode=#
    System VariableNameinnodb_strict_mode
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted Values (<= 5.7.6)Typeboolean
    DefaultOFF
    Permitted Values (>= 5.7.7)Typeboolean
    DefaultON

    When innodb_strict_mode is ON, InnoDB returns errors rather than warnings for certain conditions. As of MySQL 5.7.7, the default value is ON.

    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 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, ALTER TABLE, CREATE INDEX, and OPTIMIZE TABLE 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, ALTER TABLE, and 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 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_strict_mode is not applicable to general tablespaces. Tablespace management rules for general tablespaces are strictly enforced independently of innodb_strict_mode. For more information, see Section 14.1.19, “CREATE TABLESPACE Syntax”.

  • innodb_support_xa

    Deprecated5.7.10
    Command-Line Format--innodb_support_xa
    System VariableNameinnodb_support_xa
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultTRUE

    Enables InnoDB support for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation. This setting is the default. The XA mechanism is used internally and is essential for any server that has its binary log turned on and is accepting changes to its data from more than one thread. If you turn it off, transactions can be written to the binary log in a different order from the one in which the live database is committing them. This can produce different data when the binary log is replayed in disaster recovery or on a replication slave. Do not turn it off on a replication master server unless you have an unusual setup where only one thread is able to change data.

    innodb_support_xa is deprecated and will be removed in a future MySQL release. InnoDB support for two-phase commit in XA transactions is always enabled as of MySQL 5.7.10. Disabling innodb_support_xa is no longer permitted as it makes replication unsafe and prevents performance gains associated with binary log group commit.

  • innodb_sync_array_size

    Command-Line Format--innodb_sync_array_size=#
    System VariableNameinnodb_sync_array_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default1
    Min Value1
    Max Value1024

    Splits an internal data structure used to coordinate threads, for higher concurrency in workloads with large numbers of waiting threads. This setting must be configured when the MySQL instance is starting up, and cannot be changed afterward. Increasing this option value is recommended for workloads that frequently produce a large number of waiting threads, typically greater than 768.

  • innodb_sync_spin_loops

    Command-Line Format--innodb_sync_spin_loops=#
    System VariableNameinnodb_sync_spin_loops
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default30
    Min Value0
    Max Value4294967295

    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_sync_debug

    Introduced5.7.8
    Command-Line Format--innodb_sync_debug=#
    System VariableNameinnodb_sync_debug
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultOFF

    Enables sync debug checking for the InnoDB storage engine. This option is only available if debugging support is compiled in using the WITH_DEBUG CMake option.

    Prior to MySQL 5.7.8, enabling InnoDB sync debug checking required that the Debug Sync facility be enabled using the ENABLE_DEBUG_SYNC CMake option. This requirement was removed in MYSQL 5.7.8 with the introduction of the innodb_sync_debug configuration option.

  • innodb_table_locks

    Command-Line Format--innodb_table_locks
    System VariableNameinnodb_table_locks
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultTRUE

    If autocommit = 0, InnoDB honors 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 is 1, which means that LOCK TABLES causes InnoDB to lock a table internally if autocommit = 0.

    In MySQL 5.7, innodb_table_locks = 0 has no effect for tables locked explicitly with LOCK TABLES ... WRITE. It does have an effect for tables locked for read or write by LOCK TABLES ... WRITE implicitly (for example, through triggers) or by LOCK TABLES ... READ.

  • innodb_temp_data_file_path

    Introduced5.7.1
    Command-Line Format--innodb_temp_data_file_path=file
    System VariableNameinnodb_temp_data_file_path
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypestring
    Defaultibtmp1:12M:autoextend

    Specifies the path, file name, and file size for InnoDB temporary tablespace data files. The full directory path for a file is formed by concatenating innodb_data_home_dir to the path specified by innodb_temp_data_file_path. File size is specified in KB, MB, or GB (1024MB) by appending K, M, or G to the size value. The sum of the sizes of the files must be slightly larger than 12MB. If you do not specify innodb_temp_data_file_path, the default behavior is to create a single auto-extending temporary tablespace data file, slightly larger than 12MB, named ibtmp1. The size limit of individual files is determined by your operating system. You can set the file size to more than 4GB on operating systems that support big files. Use of raw disk partitions for temporary tablespace data files is not supported.

    The name of a InnoDB temporary table tablespace data file cannot be the same as the name of a InnoDB data file. Any inability or error creating a temporary tablespace data file is treated as fatal and server startup will be refused. The temporary table tablespace has a dynamically generated space ID, which can change on each server restart.

    The InnoDB temporary tablespace is shared by all non-compressed InnoDB temporary tables. Compressed InnoDB temporary tables reside in per-table tablespace files, which are located in the temporary file directory defined by tmpdir.

    Metadata about active InnoDB temporary tables is found in INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO.

  • innodb_tmpdir

    Introduced5.7.11
    Command-Line Format--innodb_tmpdir=path
    System VariableNameinnodb_tmpdir
    Variable ScopeGlobal, Session
    Dynamic VariableYes
    Permitted ValuesTypedirectory name
    DefaultNULL

    The innodb_tmpdir option is used to define an alternate directory for temporary sort files created during online ALTER TABLE operations that rebuild the table.

    Online ALTER TABLE operations that rebuild a table also create an intermediate table file in the same directory as the original table. The innodb_tmpdir option is not applicable to intermediate table files.

    A valid value is any directory path other than the MySQL data directory path. If the value is NULL (the default), temporary files are created MySQL temporary directory ($TMPDIR on Unix, %TEMP% on Windows, or the directory specified by the --tmpdir configuration option). If an innodb_tmpdir directory is specified, existence of the directory and permissions are only checked when innodb_tmpdir is configured using a SET statement. If a symlink is provided in a directory string, the symlink is resolved and stored as an absolute path. The path should not exceed 512 bytes. An online ALTER TABLE operation reports an error if innodb_tmpdir is set to an invalid directory. innodb_tmpdir overrides the MySQL tmpdir setting but only for online ALTER TABLE operations.

    The FILE privilege is required to configure innodb_tmpdir.

    The innodb_tmpdir option was introduced to help avoid overflowing a temporary file directory located on a tmpfs file system. Such overflows could occur as a result of large temporary sort files created during online ALTER TABLE operations that rebuild the table.

    In replication environments, only consider replicating an innodb_tmpdir setting if all servers have the same operating system environment. Otherwise, replicating an innodb_tmpdir setting could result in a replication failure when running online ALTER TABLE operations that rebuild the table. If server operating environments differ, it is recommended that you configure innodb_tmpdir on each server individually.

    For related information, see Where InnoDB Stores Temporary Files.

  • innodb_thread_concurrency

    Command-Line Format--innodb_thread_concurrency=#
    System VariableNameinnodb_thread_concurrency
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Min Value0
    Max Value1000

    InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to the limit given by this variable (InnoDB uses operating system threads to process user transactions). Once the number of threads reaches this limit, additional threads are placed into a wait state within a First In, First Out (FIFO) queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads.

    The range of this variable is 0 to 1000. A value of 0 (the default) is interpreted as infinite concurrency (no concurrency checking). Disabling thread concurrency checking enables 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.

    Consider setting this variable if your MySQL instance shares CPU resources with other applications, or if your workload or number of concurrent users is growing. The correct setting depends on workload, computing environment, and the version of MySQL that you are running. You will need to test a range of values to determine the setting that provides the best performance. innodb_thread_concurrency is a dynamic variable, which allows you to experiment with different settings on a live test system. If a particular setting performs poorly, you can quickly set innodb_thread_concurrency back to 0.

    Use the following guidelines to help find and maintain an appropriate setting:

    • If the number of concurrent user threads for a workload is less than 64, set innodb_thread_concurrency=0.

    • If your workload is consistently heavy or occasionally spikes, start by setting innodb_thread_concurrency=128, and lowering the value to 96, 80, 64, and so on, until you find the number of threads that provides the best performance. For example, suppose your system typically has 40 to 50 users, but periodically the number increases to 60, 70, or even 200. You find that performance is stable at 80 concurrent users but starts to show a regression above this number. In this case, you would set innodb_thread_concurrency=80 to avoid impacting performance.

    • If you do not want InnoDB to use more than a certain number of vCPUs for user threads (20 vCPUs for example), set innodb_thread_concurrency to this number (or possibly lower, depending on performance results). If your goal is to isolate MySQL from other applications, you may consider binding the mysqld process exclusively to the vCPUs. Be aware, however, that exclusive binding could result in non-optimal hardware usage if the mysqld process is not consistently busy. In this case, you might bind the mysqld process to the vCPUs but also allow other applications to use some or all of the vCPUs.

      Note

      From an operating system perspective, using a resource management solution (if available) to manage how CPU time is shared among applications may be preferable to binding the mysqld process. For example, you could assign 90% of vCPU time to a given application while other critical process are not running, and scale that value back to 40% when other critical processes are running.

    • innodb_thread_concurrency values that are too high can cause performance regression due to increased contention on system internals and resources.

    • In some cases, the optimal innodb_thread_concurrency setting can be smaller than the number of vCPUs.

    • Monitor and analyze your system regularly. Changes to workload, number of users, or computing environment may require that you adjust the innodb_thread_concurrency setting.

    For related information, see Section 15.6.6, “Configuring Thread Concurrency for InnoDB”.

  • innodb_trx_purge_view_update_only_debug

    Command-Line Format--innodb_trx_purge_view_update_only_debug=#
    System VariableNameinnodb_trx_purge_view_update_only_debug
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    Pauses purging of delete-marked records while allowing the purge view to be updated. This option artificially creates a situation in which the purge view is updated but purges have not yet been performed. This option is only available if debugging support is compiled in using the WITH_DEBUG CMake option.

  • innodb_trx_rseg_n_slots_debug

    Command-Line Format--innodb_trx_rseg_n_slots_debug=#
    System VariableNameinnodb_trx_rseg_n_slots_debug
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default0
    Max Value1024

    Sets a debug flag that limits TRX_RSEG_N_SLOTS to a given value for the trx_rsegf_undo_find_free function which looks for a free slot for an undo log segment. This option is only available if debugging support is compiled in using the WITH_DEBUG CMake option.

  • innodb_thread_sleep_delay

    Command-Line Format--innodb_thread_sleep_delay=#
    System VariableNameinnodb_thread_sleep_delay
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (>= 5.7.4)Typeinteger
    Default10000
    Min Value0
    Max Value1000000
    Permitted Values (32-bit platforms, <= 5.7.3)Typeinteger
    Default10000
    Min Value0
    Max Value4294967295
    Permitted Values (64-bit platforms, <= 5.7.3)Typeinteger
    Default10000
    Min Value0
    Max Value18446744073709551615

    How long InnoDB threads sleep before joining the InnoDB queue, in microseconds. The default value is 10000. A value of 0 disables sleep. In MySQL 5.6.3 and higher, you can set the configuration option innodb_adaptive_max_sleep_delay to the highest value you would allow for innodb_thread_sleep_delay, and InnoDB automatically adjusts innodb_thread_sleep_delay up or down depending on the current thread-scheduling activity. This dynamic adjustment helps the thread scheduling mechanism to work smoothly during times when the system is lightly loaded and when it is operating near full capacity.

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

  • innodb_undo_directory

    Command-Line Format--innodb_undo_directory=dir_name
    System VariableNameinnodb_undo_directory
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (<= 5.7.7)Typedirectory name
    Default.
    Permitted Values (>= 5.7.8)Typedirectory name

    The path where InnoDB creates separate tablespaces for the undo logs. Typically used to place undo logs on a different storage device. Used in conjunction with innodb_undo_logs and innodb_undo_tablespaces, which determine the disk layout of the undo logs outside the system tablespace.

    Prior to MySQL 5.7.8, the innodb_undo_directory default value is ., which represents the same directory where InnoDB creates its other log files by default. As of MySQL 5.7.8, there is no default value (it is NULL). If a path is not specified, undo tablespaces are created in the MySQL data directory, as defined by datadir.

    For more information about configuring separate tablespaces for undo logs, see Section 15.7.7, “Storing InnoDB Undo Logs in Separate Tablespaces”.

  • innodb_undo_log_truncate

    Introduced5.7.5
    Command-Line Format--innodb_undo_log_truncate=#
    System VariableNameinnodb_undo_log_truncate
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultOFF

    When you enable innodb_undo_log_truncate, undo tablespaces that exceed the threshold value defined by innodb_max_undo_log_size are marked for truncation. Only undo logs that reside in undo tablespaces can be truncated. Truncation of undo logs that reside in the system tablespace is not supported. For truncation to occur, there must be at least two undo tablespaces and two redo-enabled undo logs configured to use the undo tablespaces. This means that innodb_undo_tablespaces must be set to a value equal to or greater than 2, and innodb_undo_logs must set to a value equal to or greater than 35.

    The innodb_purge_rseg_truncate_frequency configuration option can be used to expedite truncation of undo tablepaces.

    For more information, see Section 15.7.8, “Truncating Undo Logs That Reside in Undo Tablespaces”.

  • innodb_undo_logs

    Command-Line Format--innodb_undo_logs=#
    System VariableNameinnodb_undo_logs
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default128
    Min Value0
    Max Value128

    Defines the number of undo logs (otherwise referred to as rollback segments) used by InnoDB. The innodb_undo_logs option replaces innodb_rollback_segments.

    One undo log is always assigned to the system tablespace, and as of MySQL 5.7.2, 32 undo logs are reserved for use by temporary tables and are hosted in the temporary tablespace (ibtmp1). To allocate additional undo logs for data-modifying transactions that generate undo records, innodb_undo_logs must be set to a value greater than 33. If you configure separate undo tablespaces, the undo log in the system tablespace is rendered inactive. Each undo log can host up to a maximum of 1024 transactions.

    When innodb_undo_logs is set to 32 or less, InnoDB assigns one undo log to the system tablespace and 32 to the temporary tablespace (ibtmp1).

    When innodb_undo_logs is set to a value greater than 32, InnoDB assigns one undo log to the system tablespace, 32 to the temporary tablespace (ibtmp1), and any additional undo logs are assigned to undo tablespaces, if present. If undo tabespaces are not present, additional undo logs are assigned to the system tablespace.

    Although you can increase or decrease the number of undo logs used by InnoDB, the number of undo logs physically present in the system never decreases. Thus you might start with a low value for this parameter and gradually increase it, to avoid allocating undo logs that are not required. If innodb_undo_logs is not set, it defaults to the maximum value of 128. For the total number of available undo logs, rather than the number of active ones, see the Innodb_available_undo_logs status variable.

    For more information about undo logs, see Section 15.3, “InnoDB Multi-Versioning”. For information about configuring separate tablespaces for undo logs, see Section 15.7.7, “Storing InnoDB Undo Logs in Separate Tablespaces”.

  • innodb_undo_tablespaces

    Command-Line Format--innodb_undo_tablespaces=#
    System VariableNameinnodb_undo_tablespaces
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (<= 5.7.7)Typeinteger
    Default0
    Min Value0
    Max Value126
    Permitted Values (>= 5.7.8)Typeinteger
    Default0
    Min Value0
    Max Value95

    The number of tablespace files that the undo logs are divided between. By default, all undo logs are part of the system tablespace, and the system tablespace always contains one undo tablespace in addition to those configured by innodb_undo_tablespaces.

    Because undo logs can become large during long-running transactions, having undo logs in multiple tablespaces reduces the maximum size of any one tablespace. The undo tablespace files are created in the location defined by innodb_undo_directory, with names in the form of undoN, where N is a sequential series of integers (including leading zeros). The default size of an undo tablespace file is 10M.

    Important

    innodb_undo_tablespaces can only be configured prior to initializing the MySQL instance and cannot be changed afterward. If no value is specified, the instance is initialized using the default setting (0). Attempting to restart InnoDB with a greater number of undo tablespaces than specified when the MySQL instance was initialized results in a startup failure and an error stating that InnoDB did not find the expected number of undo tablespaces.

    As MySQL 5.7.2, 32 of 128 undo logs were reserved for temporary tables, as described in Section 15.4.12.1, “InnoDB Temporary Table Undo Logs”. One undo log is always allocated to the system tablespace, which leaves 95 undo logs available for undo tablepaces. This change effectively reduced the innodb_undo_tablespaces maximum limit from 126 to 95, and in MySQL 5.7.8, the innodb_undo_tablespaces maximum value was officially reduced to 95.

    For information about configuring separate tablespaces for undo logs, see Section 15.7.7, “Storing InnoDB Undo Logs in Separate Tablespaces”.

  • innodb_use_native_aio

    Command-Line Format--innodb_use_native_aio=#
    System VariableNameinnodb_use_native_aio
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultON

    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 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 disabled (use 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.

  • innodb_use_sys_malloc

    Deprecated5.6.3
    Removed5.7.4
    Command-Line Format--innodb_use_sys_malloc=#
    System VariableNameinnodb_use_sys_malloc
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultON

    Whether InnoDB uses the operating system memory allocator (ON) or its own (OFF). The default value is ON. See Section 15.6.4, “Configuring the Memory Allocator for InnoDB” for more information.

    innodb_use_sys_malloc was deprecated in MySQL 5.6.3 and removed in MySQL 5.7.4.

  • innodb_version

    The InnoDB version number. In 5.7, the separate numbering for InnoDB does not apply and this value is the same as for the version variable.

  • innodb_write_io_threads

    Command-Line Format--innodb_write_io_threads=#
    System VariableNameinnodb_write_io_threads
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default4
    Min Value1
    Max Value64

    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. See Section 15.6.7, “Configuring the Number of Background InnoDB I/O Threads” for more information. For general I/O tuning advice, see Section 9.5.8, “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, 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, which controls synchronization of the binary log to disk.

    For general I/O tuning advice, see Section 9.5.8, “Optimizing InnoDB Disk I/O”.


User Comments
  Posted by Morgan Tocker on December 28, 2006
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.
  Posted by Brian Aker on August 16, 2007
Commentary on Innodb parameters for an 8way machine:
http://krow.livejournal.com/542306.html
  Posted by Willem de Groot on September 20, 2007
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).

  Posted by Simon Mudd on October 13, 2009
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.

  Posted by Morgan Tocker on November 15, 2013
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
Sign Up Login You must be logged in to post a comment.