14.12 InnoDB Startup Options and System Variables

14.12.1 Changes to InnoDB Startup Options and System Variables

Table 14.5 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
have_innodb  Yes GlobalNo
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_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_buffer_pool_bytes_data   YesGlobalNo
Innodb_buffer_pool_bytes_dirty   YesGlobalNo
innodb_buffer_pool_dump_at_shutdownYesYesYes GlobalYes
innodb_buffer_pool_dump_nowYesYesYes 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_requests   YesGlobalNo
Innodb_buffer_pool_reads   YesGlobalNo
innodb_buffer_pool_sizeYesYesYes GlobalNo
Innodb_buffer_pool_wait_free   YesGlobalNo
Innodb_buffer_pool_write_requests   YesGlobalNo
innodb_change_buffer_max_sizeYesYesYes GlobalYes
innodb_change_bufferingYesYesYes GlobalYes
innodb_checksum_algorithmYesYesYes GlobalYes
innodb_checksumsYesYesYes GlobalNo
innodb_cmp_per_index_enabledYesYesYes GlobalYes
innodb_commit_concurrencyYesYesYes GlobalYes
innodb_compression_failure_threshold_pctYesYesYes GlobalYes
innodb_compression_levelYesYesYes GlobalYes
innodb_compression_pad_pct_maxYesYesYes GlobalYes
innodb_concurrency_ticketsYesYesYes GlobalYes
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_disable_sort_file_cacheYesYesYes GlobalYes
innodb_doublewriteYesYesYes GlobalNo
innodb_fast_shutdownYesYesYes GlobalYes
innodb_file_formatYesYesYes GlobalYes
innodb_file_format_checkYesYesYes GlobalNo
innodb_file_format_maxYesYesYes GlobalYes
innodb_file_per_tableYesYesYes GlobalYes
innodb_flush_log_at_timeout  Yes GlobalYes
innodb_flush_log_at_trx_commitYesYesYes GlobalYes
innodb_flush_methodYesYesYes GlobalNo
innodb_flush_neighborsYesYesYes 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_lock_wait_timeoutYesYesYes BothYes
innodb_locks_unsafe_for_binlogYesYesYes GlobalNo
innodb_log_buffer_sizeYesYesYes GlobalNo
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_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_mirrored_log_groupsYesYesYes GlobalNo
innodb_monitor_disableYesYesYes GlobalYes
innodb_monitor_enableYesYesYes GlobalYes
innodb_monitor_resetYesYesYes GlobalYes
innodb_monitor_reset_allYesYesYes GlobalYes
Innodb_num_open_files   YesGlobalNo
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_os_log_fsyncs   YesGlobalNo
Innodb_os_log_pending_fsyncs   YesGlobalNo
Innodb_os_log_pending_writes   YesGlobalNo
Innodb_os_log_written   YesGlobalNo
innodb_page_sizeYesYesYes GlobalNo
Innodb_page_size   YesGlobalNo
Innodb_pages_created   YesGlobalNo
Innodb_pages_read   YesGlobalNo
Innodb_pages_written   YesGlobalNo
innodb_print_all_deadlocksYesYesYes GlobalYes
innodb_purge_batch_sizeYesYesYes 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_sort_buffer_sizeYesYesYes GlobalNo
innodb_spin_wait_delayYesYesYes GlobalYes
innodb_stats_auto_recalcYesYesYes 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_spin_loopsYesYesYes GlobalYes
innodb_table_locksYesYesYes BothYes
innodb_thread_concurrencyYesYesYes GlobalYes
innodb_thread_sleep_delayYesYesYes GlobalYes
Innodb_truncated_status_writes   YesGlobalNo
innodb_undo_directoryYesYesYes GlobalNo
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
timed_mutexesYesYesYes GlobalYes
unique_checks  Yes BothYes

InnoDB Command Options

InnoDB System Variables

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

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