MySQL 5.5 Reference Manual Including MySQL NDB Cluster 7.2 Reference Guide

14.17 InnoDB Startup Options and System Variables

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

Table 14.7 InnoDB Option and Variable Reference

Name Cmd-Line Option File System Var Status Var Var Scope Dynamic
foreign_key_checks Yes Varies Yes
have_innodb Yes Global No
ignore_builtin_innodb Yes Yes Yes Global No
innodb Yes Yes
innodb_adaptive_flushing Yes Yes Yes Global Yes
innodb_adaptive_hash_index Yes Yes Yes Global Yes
innodb_additional_mem_pool_size Yes Yes Yes Global No
innodb_autoextend_increment Yes Yes Yes Global Yes
innodb_autoinc_lock_mode Yes Yes Yes Global No
Innodb_buffer_pool_bytes_data Yes Global No
Innodb_buffer_pool_bytes_dirty Yes Global No
innodb_buffer_pool_instances Yes Yes Yes Global No
Innodb_buffer_pool_pages_data Yes Global No
Innodb_buffer_pool_pages_dirty Yes Global No
Innodb_buffer_pool_pages_flushed Yes Global No
Innodb_buffer_pool_pages_free Yes Global No
Innodb_buffer_pool_pages_latched Yes Global No
Innodb_buffer_pool_pages_misc Yes Global No
Innodb_buffer_pool_pages_total Yes Global No
Innodb_buffer_pool_read_ahead Yes Global No
Innodb_buffer_pool_read_ahead_evicted Yes Global No
Innodb_buffer_pool_read_ahead_rnd Yes Global No
Innodb_buffer_pool_read_requests Yes Global No
Innodb_buffer_pool_reads Yes Global No
innodb_buffer_pool_size Yes Yes Yes Global No
Innodb_buffer_pool_wait_free Yes Global No
Innodb_buffer_pool_write_requests Yes Global No
innodb_change_buffering Yes Yes Yes Global Yes
innodb_change_buffering_debug Yes Yes Yes Global Yes
innodb_checksums Yes Yes Yes Global No
innodb_commit_concurrency Yes Yes Yes Global Yes
innodb_concurrency_tickets Yes Yes Yes Global Yes
innodb_data_file_path Yes Yes Yes Global No
Innodb_data_fsyncs Yes Global No
innodb_data_home_dir Yes Yes Yes Global No
Innodb_data_pending_fsyncs Yes Global No
Innodb_data_pending_reads Yes Global No
Innodb_data_pending_writes Yes Global No
Innodb_data_read Yes Global No
Innodb_data_reads Yes Global No
Innodb_data_writes Yes Global No
Innodb_data_written Yes Global No
Innodb_dblwr_pages_written Yes Global No
Innodb_dblwr_writes Yes Global No
innodb_doublewrite Yes Yes Yes Global No
innodb_fast_shutdown Yes Yes Yes Global Yes
innodb_file_format Yes Yes Yes Global Yes
innodb_file_format_check Yes Yes Yes Global Varies
innodb_file_format_max Yes Yes Yes Global Yes
innodb_file_per_table Yes Yes Yes Global Yes
innodb_flush_log_at_trx_commit Yes Yes Yes Global Yes
innodb_flush_method Yes Yes Yes Global No
innodb_force_load_corrupted Yes Yes Yes Global No
innodb_force_recovery Yes Yes Yes Global No
Innodb_have_atomic_builtins Yes Global No
innodb_io_capacity Yes Yes Yes Global Yes
innodb_large_prefix Yes Yes Yes Global Yes
innodb_limit_optimistic_insert_debug Yes Yes Yes Global Yes
innodb_lock_wait_timeout Yes Yes Yes Both Yes
innodb_locks_unsafe_for_binlog Yes Yes Yes Global No
innodb_log_buffer_size Yes Yes Yes Global No
innodb_log_file_size Yes Yes Yes Global No
innodb_log_files_in_group Yes Yes Yes Global No
innodb_log_group_home_dir Yes Yes Yes Global No
Innodb_log_waits Yes Global No
Innodb_log_write_requests Yes Global No
Innodb_log_writes Yes Global No
innodb_max_dirty_pages_pct Yes Yes Yes Global Yes
innodb_max_purge_lag Yes Yes Yes Global Yes
innodb_mirrored_log_groups Yes Yes Yes Global No
innodb_old_blocks_pct Yes Yes Yes Global Yes
innodb_old_blocks_time Yes Yes Yes Global Yes
innodb_open_files Yes Yes Yes Global No
Innodb_os_log_fsyncs Yes Global No
Innodb_os_log_pending_fsyncs Yes Global No
Innodb_os_log_pending_writes Yes Global No
Innodb_os_log_written Yes Global No
Innodb_page_size Yes Global No
Innodb_pages_created Yes Global No
Innodb_pages_read Yes Global No
Innodb_pages_written Yes Global No
innodb_print_all_deadlocks Yes Yes Yes Global Yes
innodb_purge_batch_size Yes Yes Yes Global Yes
innodb_purge_threads Yes Yes Yes Global No
innodb_random_read_ahead Yes Yes Yes Global Yes
innodb_read_ahead_threshold Yes Yes Yes Global Yes
innodb_read_io_threads Yes Yes Yes Global No
innodb_replication_delay Yes Yes Yes Global Yes
innodb_rollback_on_timeout Yes Yes Yes Global No
innodb_rollback_segments Yes Yes Yes Global Yes
Innodb_row_lock_current_waits Yes Global No
Innodb_row_lock_time Yes Global No
Innodb_row_lock_time_avg Yes Global No
Innodb_row_lock_time_max Yes Global No
Innodb_row_lock_waits Yes Global No
Innodb_rows_deleted Yes Global No
Innodb_rows_inserted Yes Global No
Innodb_rows_read Yes Global No
Innodb_rows_updated Yes Global No
innodb_spin_wait_delay Yes Yes Yes Global Yes
innodb_stats_method Yes Yes Yes Global Yes
innodb_stats_on_metadata Yes Yes Yes Global Yes
innodb_stats_sample_pages Yes Yes Yes Global Yes
innodb-status-file Yes Yes
innodb_strict_mode Yes Yes Yes Both Yes
innodb_support_xa Yes Yes Yes Both Yes
innodb_sync_spin_loops Yes Yes Yes Global Yes
innodb_table_locks Yes Yes Yes Both Yes
innodb_thread_concurrency Yes Yes Yes Global Yes
innodb_thread_sleep_delay Yes Yes Yes Global Yes
Innodb_truncated_status_writes Yes Global No
innodb_trx_purge_view_update_only_debug Yes Yes Yes Global Yes
innodb_trx_rseg_n_slots_debug Yes Yes Yes Global Yes
innodb_use_native_aio Yes Yes Yes Global No
innodb_use_sys_malloc Yes Yes Yes Global No
innodb_version Yes Global No
innodb_write_io_threads Yes Yes Yes Global No
timed_mutexes Yes Yes Yes Global Yes
unique_checks Yes Varies Yes

InnoDB Command Options

InnoDB System Variables

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