13.6.4. InnoDB Startup Options and System Variables

This section describes the InnoDB-related command options and system variables. System variables that are true or false can be enabled at server startup by naming them, or disabled by using a --skip- prefix. For example, to enable or disable InnoDB checksums, you can use --innodb_checksums or --skip-innodb_checksums on the command line, or innodb_checksums or skip-innodb_checksums in an option file. System variables that take a numeric value can be specified as --var_name=value on the command line or as var_name=value in option files. For more information on specifying options and system variables, see Section 4.2.3, “Specifying Program Options”. Many of the system variables can be changed at runtime (see Section 5.1.5.2, “Dynamic System Variables”).

Certain options control the locations and layout of the InnoDB data files. Section 13.6.2, “Configuring InnoDB explains how to use these options. Many other options, that you might not use initially, help to tune InnoDB performance characteristics based on machine capacity and your database workload. The performance-related options are explained in Section 13.6.14, “InnoDB Performance Tuning and Troubleshooting” and Section 13.7.7, “Performance and Scalability Enhancements”.

Table 13.5. InnoDB Option/Variable Reference

NameCmd-LineOption fileSystem VarStatus VarVar ScopeDynamic
Com_show_innodb_status   YesBothNo
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_hash_indexYesYesYes GlobalYes
innodb_additional_mem_pool_sizeYesYesYes GlobalNo
innodb_autoextend_incrementYesYesYes GlobalYes
innodb_autoinc_lock_modeYesYesYes GlobalNo
innodb_buffer_pool_instancesYesYesYes GlobalNo
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_bufferingYesYesYes GlobalYes
innodb_checksumsYesYesYes GlobalNo
innodb_commit_concurrencyYesYesYes 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_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_trx_commitYesYesYes GlobalYes
innodb_flush_methodYesYesYes GlobalNo
innodb_force_recoveryYesYesYes GlobalNo
Innodb_have_atomic_builtins   YesGlobalNo
innodb_io_capacityYesYesYes GlobalYes
innodb_lock_wait_timeoutYesYesYes BothYes
innodb_locks_unsafe_for_binlogYesYesYes GlobalNo
innodb_log_buffer_sizeYesYesYes GlobalNo
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_max_dirty_pages_pctYesYesYes GlobalYes
innodb_max_purge_lagYesYesYes GlobalYes
innodb_mirrored_log_groupsYesYesYes GlobalNo
innodb_old_blocks_pctYesYesYes GlobalYes
innodb_old_blocks_timeYesYesYes GlobalYes
innodb_open_filesYesYesYes GlobalNo
Innodb_os_log_fsyncs   YesGlobalNo
Innodb_os_log_pending_fsyncs   YesGlobalNo
Innodb_os_log_pending_writes   YesGlobalNo
Innodb_os_log_written   YesGlobalNo
Innodb_page_size   YesGlobalNo
Innodb_pages_created   YesGlobalNo
Innodb_pages_read   YesGlobalNo
Innodb_pages_written   YesGlobalNo
innodb_purge_batch_sizeYesYesYes GlobalNo
innodb_purge_threadsYesYesYes GlobalNo
innodb_read_ahead_thresholdYesYesYes GlobalYes
innodb_read_io_threadsYesYesYes GlobalNo
innodb_replication_delayYesYesYes GlobalYes
innodb_rollback_on_timeoutYesYesYes GlobalNo
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_spin_wait_delayYesYesYes GlobalYes
innodb_stats_on_metadataYesYesYes GlobalYes
innodb_stats_sample_pagesYesYesYes GlobalYes
innodb-status-fileYesYes    
innodb_strict_modeYesYesYes BothYes
innodb_support_xaYesYesYes BothYes
innodb_sync_spin_loopsYesYesYes GlobalYes
innodb_table_locksYesYesYes BothYes
innodb_thread_concurrencyYesYesYes GlobalYes
innodb_thread_sleep_delayYesYesYes GlobalYes
Innodb_truncated_status_writes   YesGlobalNo
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

  • ignore_builtin_innodb

    Whether the server was started with the --ignore-builtin-innodb option, which causes the server to behave as if the built-in InnoDB is not present. For more information, see the description of --ignore-builtin-innodb under “InnoDB Command Options” earlier in this section.

  • innodb_adaptive_flushing

    Command-Line Format--innodb_adaptive_flushing=#
    Option-File Formatinnodb_adaptive_flushing
    Option Sets VariableYes, innodb_adaptive_flushing
    Variable Nameinnodb_adaptive_flushing
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typeboolean
    DefaultON

    InnoDB Plugin 1.0.4 and up uses a heuristic to determine when to flush dirty pages in the buffer cache. This heuristic is designed to avoid bursts of I/O activity and is used when innodb_adaptive_flushing is enabled (which is the default).

  • innodb_adaptive_hash_index

    Command-Line Format--innodb_adaptive_hash_index=#
    Option-File Formatinnodb_adaptive_hash_index
    Option Sets VariableYes, innodb_adaptive_hash_index
    Variable Nameinnodb_adaptive_hash_index
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typeboolean
    DefaultON

    Whether InnoDB adaptive hash indexes are enabled or disabled (see Section 13.6.11.4, “Adaptive Hash Indexes”). This variable is enabled by default. Use --skip-innodb_adaptive_hash_index at server startup to disable it.

  • innodb_additional_mem_pool_size

    Command-Line Format--innodb_additional_mem_pool_size=#
    Option-File Formatinnodb_additional_mem_pool_size
    Option Sets VariableYes, innodb_additional_mem_pool_size
    Variable Nameinnodb_additional_mem_pool_size
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typenumeric
    Default8388608
    Range2097152-4294967295

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

  • innodb_autoextend_increment

    Command-Line Format--innodb_autoextend_increment=#
    Option-File Formatinnodb_autoextend_increment
    Option Sets VariableYes, innodb_autoextend_increment
    Variable Nameinnodb_autoextend_increment
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default64
    Range1-1000

    The increment size (in MB) for extending the size of an auto-extending shared tablespace file when it becomes full. The default value is 8. This variable does not affect the per-table tablespace files that are created if you use innodb_file_per_table=1. 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=#
    Option-File Formatinnodb_autoinc_lock_mode
    Option Sets VariableYes, innodb_autoinc_lock_mode
    Variable Nameinnodb_autoinc_lock_mode
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typenumeric
    Default1
    Valid Values0, 1, 2

    The locking 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 13.6.5.3, “AUTO_INCREMENT Handling in InnoDB, describes the characteristics of these modes.

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

  • innodb_buffer_pool_size

    Version Introduced5.5.4
    Command-Line Format--innodb_buffer_pool_instances=#
    Option-File Formatinnodb_buffer_pool_instances
    Option Sets VariableYes, innodb_buffer_pool_instances
    Variable Nameinnodb_buffer_pool_instances
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typenumeric
    Default1
    Range1-64

    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 the innodb_buffer_pool_size to a size of 1 gigabyte or more. The total size you specify is divided up among all the buffer pools. We recommend specifying a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte.

  • innodb_buffer_pool_size

    Command-Line Format--innodb_buffer_pool_size=#
    Option-File Formatinnodb_buffer_pool_size
    Option Sets VariableYes, innodb_buffer_pool_size
    Variable Nameinnodb_buffer_pool_size
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Platform Bit Size32
    Typenumeric
    Default134217728
    Range1048576-2**32-1
     Permitted Values
    Platform Bit Size64
    Typenumeric
    Default134217728
    Range1048576-2**64-1

    The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 128MB, increased from a historical default of 8MB. The maximum value depends on the CPU architecture, 32-bit or 64-bit. For 32-bit systems, the CPU architecture and operating system sometimes impose a lower practical maximum size.

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

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

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

    • The address space must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses.

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

  • innodb_change_buffering

    Command-Line Format--innodb_change_buffering=#
    Option-File Formatinnodb_change_buffering
    Option Sets VariableYes, innodb_change_buffering
    Variable Nameinnodb_change_buffering
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values (<= 5.5.3)
    Typeenumeration
    Defaultinserts
    Valid Valuesinserts, none
     Permitted Values (>= 5.5.4)
    Typeenumeration
    Defaultall
    Valid Valuesinserts, deletes, purges, changes, all, none

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

  • innodb_checksums

    Command-Line Format--innodb_checksums
    Option-File Formatinnodb_checksums
    Option Sets VariableYes, innodb_checksums
    Variable Nameinnodb_checksums
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typeboolean
    DefaultON

    InnoDB can use checksum validation on all pages read from the disk to ensure extra fault tolerance against broken hardware or data files. This validation is enabled by default. However, under some rare circumstances (such as when running benchmarks) this extra safety feature is unneeded and can be disabled with --skip-innodb-checksums.

  • innodb_commit_concurrency

    Command-Line Format--innodb_commit_concurrency=#
    Option-File Formatinnodb_commit_concurrency
    Option Sets VariableYes, innodb_commit_concurrency
    Variable Nameinnodb_commit_concurrency
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default0
    Range0-1000

    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_concurrency_tickets

    Command-Line Format--innodb_concurrency_tickets=#
    Option-File Formatinnodb_concurrency_tickets
    Option Sets VariableYes, innodb_concurrency_tickets
    Variable Nameinnodb_concurrency_tickets
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default500
    Range1-4294967295

    The number of threads that can enter InnoDB concurrently is determined by the innodb_thread_concurrency variable. 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 500.

  • innodb_data_file_path

    Command-Line Format--innodb_data_file_path=name
    Option-File Formatinnodb_data_file_path
    Variable Nameinnodb_data_file_path
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typefile name

    The paths to individual 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 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 at least 10MB. If you do not specify innodb_data_file_path, the default behavior is to create a single 10MB auto-extending data file 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 13.6.2, “Configuring InnoDB.

  • innodb_data_home_dir

    Command-Line Format--innodb_data_home_dir=path
    Option-File Formatinnodb_data_home_dir
    Option Sets VariableYes, innodb_data_home_dir
    Variable Nameinnodb_data_home_dir
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typefile name

    The common part of the directory path for all InnoDB data files in the shared tablespace. This setting does not affect the location of per-file 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_doublewrite

    Command-Line Format--innodb-doublewrite
    Option-File Formatinnodb_doublewrite
    Option Sets VariableYes, innodb_doublewrite
    Variable Nameinnodb_doublewrite
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typeboolean

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

  • innodb_fast_shutdown

    Command-Line Format--innodb_fast_shutdown[=#]
    Option-File Formatinnodb_fast_shutdown
    Option Sets VariableYes, innodb_fast_shutdown
    Variable Nameinnodb_fast_shutdown
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default1
    Valid Values0, 1, 2

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

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

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

  • innodb_file_format

    Command-Line Format--innodb_file_format=#
    Option-File Formatinnodb_file_format
    Option Sets VariableYes, innodb_file_format
    Variable Nameinnodb_file_format
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values (>= 5.5.0, <= 5.5.6)
    Typestring
    DefaultBarracuda
    Valid ValuesAntelope, Barracuda
     Permitted Values (>= 5.5.7)
    Typestring
    DefaultAntelope
    Valid ValuesAntelope, Barracuda

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

  • innodb_file_format_check

    Command-Line Format--innodb_file_format_check=#
    Option-File Formatinnodb_file_format_check
    Option Sets VariableYes, innodb_file_format_check
    Variable Nameinnodb_file_format_check
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values (<= 5.5.0)
    Typestring
    DefaultAntelope
     Permitted Values (>= 5.5.4)
    Typestring
    DefaultBarracuda
     Permitted Values (>= 5.5.5)
    Typeboolean
    DefaultON

    As of MySQL 5.5.5, this variable can be set to 1 or 0 at server startup to enable or disable whether InnoDB checks the file format tag in the shared 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.

    Before MySQL 5.5.5, this variable can be set to 1 or 0 at server startup to enable or disable whether InnoDB checks the file format tag in the shared tablespace. If the tag is checked and is higher than that supported by the current version of InnoDB, an error occurs and InnoDB does not start. If the tag is not higher, InnoDB sets the value of innodb_file_format_check to the file format tag, which is the value seen at runtime.

  • innodb_file_format_max

    Version Introduced5.5.5
    Command-Line Format--innodb_file_format_max=#
    Option-File Formatinnodb_file_format_max
    Option Sets VariableYes, innodb_file_format_max
    Variable Nameinnodb_file_format_max
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typestring
    DefaultAntelope
    Valid ValuesAntelope, Barracuda

    At server startup, InnoDB sets the value of innodb_file_format_max to the file format tag in the shared 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.

    This variable was added in MySQL 5.5.5.

  • innodb_file_per_table

    Command-Line Format--innodb_file_per_table
    Option-File Formatinnodb_file_per_table
    Variable Nameinnodb_file_per_table
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values (>= 5.5.0, <= 5.5.6)
    Typeboolean
    DefaultON
     Permitted Values (>= 5.5.7)
    Typeboolean
    DefaultOFF

    If innodb_file_per_table is disabled (the default), InnoDB creates tables in the system tablespace. If innodb_file_per_table is enabled, InnoDB creates each new table using its own .ibd file for storing data and indexes, rather than in the system tablespace. See Section 13.6.3, “Using Per-Table Tablespaces” for information about the features, such as InnoDB table compression, that only work for tables stored in separate tablespaces.

  • innodb_flush_log_at_trx_commit

    Command-Line Format--innodb_flush_log_at_trx_commit[=#]
    Option-File Formatinnodb_flush_log_at_trx_commit
    Option Sets VariableYes, innodb_flush_log_at_trx_commit
    Variable Nameinnodb_flush_log_at_trx_commit
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default1
    Valid Values0, 1, 2

    If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

    The default value of 1 is required for full ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose up to one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. InnoDB's crash recovery works regardless of the value.

    For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in your master server my.cnf file.

    Caution

    Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld 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 the InnoDB database. 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
    Option-File Formatinnodb_flush_method
    Option Sets VariableYes, innodb_flush_method
    Variable Nameinnodb_flush_method
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Type (solaris)enumeration
    Defaultfdatasync
    Valid ValuesO_DSYNC, O_DIRECT

    By default, InnoDB uses the fsync() system call to flush both the data and log files. If innodb_flush_method option is set to O_DSYNC, InnoDB uses O_SYNC to open and flush the log files, and fsync() to flush the data files. If O_DIRECT is specified (available on some GNU/Linux versions, FreeBSD, and Solaris), InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. Note that InnoDB uses fsync() instead of fdatasync(), and it does not use O_DSYNC by default because there have been problems with it on many varieties of Unix. This variable is relevant only for Unix. On Windows, the flush method is always async_unbuffered and cannot be changed.

    Different values of this variable can have a marked effect on InnoDB performance. For example, on some systems where InnoDB data and log files are located on a SAN, it has been found that setting innodb_flush_method to O_DIRECT can degrade performance of simple SELECT statements by a factor of three.

    Formerly, a value of fdatasync also specified the default behavior. This value was removed, due to confusion that a value of fdatasync caused fsync() system calls rather than fdatasync() for flushing. To obtain the default value now, do not set any value for innodb_flush_method at startup.

  • innodb_force_recovery

    Command-Line Format--innodb_force_recovery=#
    Option-File Formatinnodb_force_recovery
    Option Sets VariableYes, innodb_force_recovery
    Variable Nameinnodb_force_recovery
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typeenumeration
    Default0
    Valid Values0, 1, 2, 3, 4, 5, 6

    The crash recovery mode. Possible values are from 0 to 6. The meanings of these values are described in Section 13.6.7.2, “Forcing InnoDB Recovery”.

    Warning

    Only set this variable greater than 0 in an emergency situation, to dump your tables from a corrupt database. As a safety measure, InnoDB prevents any changes to its data when this variable is greater than 0. This restriction also prohibits some queries that use WHERE or ORDER BY clauses, because high values can prevent queries from using indexes.

  • innodb_io_capacity

    Command-Line Format--innodb_io_capacity=#
    Option-File Formatinnodb_io_capacity
    Option Sets VariableYes, innodb_io_capacity
    Variable Nameinnodb_io_capacity
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Platform Bit Size32
    Typenumeric
    Default200
    Range100-2**32-1
     Permitted Values
    Platform Bit Size64
    Typenumeric
    Default200
    Range100-2**64-1

    The maximum number of I/O operations per second that InnoDB will perform. This variable can be set at server startup, which enables higher values to be selected for systems capable of higher I/O rates. Having a higher I/O rate can help the server handle a higher rate of row changes because it may be able to increase dirty-page flushing, deleted-row removal, and application of changes to the insert buffer. The default value of innodb_io_capacity is 200. In general, you can increase the value as a function of the number of drives used for InnoDB I/O. Although you can specify a very high number, in practice such large values cease to have any benefit; for example, a value of one million would be considered very high.

    The ability to raise the I/O limit should be especially beneficial on platforms that support many 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.

  • innodb_lock_wait_timeout

    Command-Line Format--innodb_lock_wait_timeout=#
    Option-File Formatinnodb_lock_wait_timeout
    Option Sets VariableYes, innodb_lock_wait_timeout
    Variable Nameinnodb_lock_wait_timeout
    Variable ScopeGlobal, Session
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default50
    Range1-1073741824

    The timeout in seconds an InnoDB transaction may wait 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 will hang for at most this many seconds 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 not executed. The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the --innodb_rollback_on_timeout option. See also Section 13.6.13, “InnoDB Error Handling”.)

    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.

    InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.

  • innodb_locks_unsafe_for_binlog

    Command-Line Format--innodb_locks_unsafe_for_binlog
    Option-File Formatinnodb_locks_unsafe_for_binlog
    Option Sets VariableYes, innodb_locks_unsafe_for_binlog
    Variable Nameinnodb_locks_unsafe_for_binlog
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typeboolean
    DefaultOFF

    This variable affects how InnoDB uses gap locking for searches and index scans. Normally, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order. See Section 13.6.9.4, “InnoDB Record, Gap, and Next-Key Locks”.

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

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

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

    • 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 12.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 13.6.9.5, “Avoiding the Phantom Problem Using Next-Key Locking”.

    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 13.6.11.1, “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=#
    Option-File Formatinnodb_log_buffer_size
    Option Sets VariableYes, innodb_log_buffer_size
    Variable Nameinnodb_log_buffer_size
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typenumeric
    Default8388608
    Range262144-4294967295

    The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. 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 big transactions, making the log buffer larger saves disk I/O.

  • innodb_log_file_size

    Command-Line Format--innodb_log_file_size=#
    Option-File Formatinnodb_log_file_size
    Option Sets VariableYes, innodb_log_file_size
    Variable Nameinnodb_log_file_size
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typenumeric
    Default5242880
    Range108576-4294967295

    The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.

  • innodb_log_files_in_group

    Command-Line Format--innodb_log_files_in_group=#
    Option-File Formatinnodb_log_files_in_group
    Option Sets VariableYes, innodb_log_files_in_group
    Variable Nameinnodb_log_files_in_group
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typenumeric
    Default2
    Range2-100

    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.

  • innodb_log_group_home_dir

    Command-Line Format--innodb_log_group_home_dir=path
    Option-File Formatinnodb_log_group_home_dir
    Option Sets VariableYes, innodb_log_group_home_dir
    Variable Nameinnodb_log_group_home_dir
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typefile name

    The directory path to the InnoDB redo log files. If you do not specify any InnoDB log variables, the default is to create two 5MB files named ib_logfile0 and ib_logfile1 in the MySQL data directory.

  • innodb_max_dirty_pages_pct

    Command-Line Format--innodb_max_dirty_pages_pct=#
    Option-File Formatinnodb_max_dirty_pages_pct
    Option Sets VariableYes, innodb_max_dirty_pages_pct
    Variable Nameinnodb_max_dirty_pages_pct
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default75
    Range0-99

    This is an integer in the range from 0 to 99. The default value is 75. The main thread in InnoDB tries to write pages from the buffer pool so that the percentage of dirty (not yet written) pages will not exceed this value.

  • innodb_max_purge_lag

    Command-Line Format--innodb_max_purge_lag=#
    Option-File Formatinnodb_max_purge_lag
    Option Sets VariableYes, innodb_max_purge_lag
    Variable Nameinnodb_max_purge_lag
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default0
    Range0-4294967295

    This variable controls how to delay INSERT, UPDATE, and DELETE operations when purge operations are lagging (see Section 13.6.10, “InnoDB Multi-Versioning”). The default value 0 (no delays).

    The InnoDB transaction system maintains a list of transactions that have index records delete-marked by UPDATE or DELETE operations. Let the length of this list be purge_lag. When purge_lag exceeds innodb_max_purge_lag, each INSERT, UPDATE, and DELETE operation is delayed by ((purge_lag/innodb_max_purge_lag)×10)–5 milliseconds. The delay is computed in the beginning of a purge batch, every ten seconds. The operations are not delayed if purge cannot run because of an old consistent read view that could see the rows to be purged.

    A typical setting for a problematic workload might be 1 million, assuming that transactions are small, only 100 bytes in size, and it is permissible to have 100MB of unpurged InnoDB table rows.

    The lag value is displayed as the history list length in the TRANSACTIONS section of InnoDB Monitor output. For example, if the output includes the following lines, the lag value is 20:

    ------------
    TRANSACTIONS
    ------------
    Trx id counter 0 290328385
    Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
    History list length 20
    
  • innodb_mirrored_log_groups

    The number of identical copies of log groups to keep for the database. This should be set to 1.

  • innodb_old_blocks_pct

    Command-Line Format--innodb_old_blocks_pct=#
    Option-File Formatinnodb_old_blocks_pct
    Variable Nameinnodb_old_blocks_pct
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default37
    Range5-95

    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). See Section 7.9.1, “The InnoDB Buffer Pool”

  • innodb_old_blocks_time

    Command-Line Format--innodb_old_blocks_time=#
    Option-File Formatinnodb_old_blocks_time
    Variable Nameinnodb_old_blocks_time
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default0
    Range0-2**32-1

    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. The default 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. See Section 7.9.1, “The InnoDB Buffer Pool”

  • innodb_open_files

    Command-Line Format--innodb_open_files=#
    Option-File Formatinnodb_open_files
    Variable Nameinnodb_open_files
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typenumeric
    Default300
    Range10-4294967295

    This variable is relevant only if you use multiple tablespaces in InnoDB. It specifies the maximum number of .ibd files that InnoDB can keep open at one time. The minimum value is 10. The default value is 300.

    The file descriptors used for .ibd files are for InnoDB only. They are independent of those specified by the --open-files-limit server option, and do not affect the operation of the table cache.

  • innodb_purge_batch_size

    Version Introduced5.5.4
    Command-Line Format--innodb_purge_batch_size=#
    Option-File Formatinnodb_purge_batch_size
    Variable Nameinnodb_purge_batch_size
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typenumeric
    Default20
    Range1-5000

    The granularity of changes, expressed in units of redo log records, that trigger a purge operation, flushing the changed buffer pool blocks to disk. The default value is 20, and the range is 1-5000. This option is intended for tuning performance in combination with the setting innodb_purge_threads=1, and typical users do not need to modify it.

  • innodb_purge_threads

    Version Introduced5.5.4
    Command-Line Format--innodb_purge_threads=#
    Option-File Formatinnodb_purge_threads
    Variable Nameinnodb_purge_threads
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typenumeric
    Default0
    Range0-1

    The number of background threads devoted to the InnoDB purge operation. Currently, can only be 0 (the default) or 1. The default value of 0 signifies that the purge operation is performed as part of the master thread. Running the purge operation in its own thread can reduce internal contention within InnoDB, improving scalability. Currently, the performance gain might be minimal because the background thread might encounter different kinds of contention than before. This feature primarily lays the groundwork for future performance work.

  • innodb_read_ahead_threshold

    Command-Line Format--innodb_read_ahead_threshold=#
    Option-File Formatinnodb_read_ahead_threshold
    Option Sets VariableYes, innodb_read_ahead_threshold
    Variable Nameinnodb_read_ahead_threshold
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default56
    Range0-64

    Controls the sensitivity of linear read-ahead that InnoDB uses to prefetch pages into the buffer cache. 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. The default is 56: InnoDB must read at least 56 pages sequentially from an extent to initiate an asynchronous read for the following extent.

  • innodb_read_io_threads

    Command-Line Format--innodb_read_io_threads=#
    Option-File Formatinnodb_read_io_threads
    Option Sets VariableYes, innodb_read_io_threads
    Variable Nameinnodb_read_io_threads
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typenumeric
    Default4
    Range1-64

    The number of I/O threads for read operations in InnoDB. The default value is 4.

  • innodb_replication_delay

    Command-Line Format--innodb_replication_delay=#
    Option-File Formatinnodb_replication_delay
    Option Sets VariableYes, innodb_replication_delay
    Variable Nameinnodb_replication_delay
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default0
    Range0-4294967295

    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
    Option-File Formatinnodb_rollback_on_timeout
    Option Sets VariableYes, innodb_rollback_on_timeout
    Variable Nameinnodb_rollback_on_timeout
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typeboolean
    DefaultOFF

    In MySQL 5.5, 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 (the same behavior as in MySQL 4.1).

  • innodb_spin_wait_delay

    Command-Line Format--innodb_spin_wait_delay=#
    Option-File Formatinnodb_spin_wait_delay
    Option Sets VariableYes, innodb_spin_wait_delay
    Variable Nameinnodb_spin_wait_delay
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default6
    Range0-4294967295

    The maximum delay between polls for a spin lock. The default value is 6.

  • innodb_stats_on_metadata

    Version Introduced5.5.4
    Command-Line Format--innodb_stats_on_metadata
    Option-File Formatinnodb_stats_on_metadata
    Option Sets VariableYes, innodb_stats_on_metadata
    Variable Nameinnodb_stats_on_metadata
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typeboolean
    DefaultON

    When this variable is enabled (which is the default, as before the variable was created), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.

  • innodb_stats_sample_pages

    Command-Line Format--innodb_stats_sample_pages=#
    Option-File Formatinnodb_stats_sample_pages
    Option Sets VariableYes, innodb_stats_sample_pages
    Variable Nameinnodb_stats_sample_pages
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default8
    Range1-2**64-1

    The number of index pages to sample for index distribution statistics such as are calculated by ANALYZE TABLE. The default value is 8. For more information, see Section 13.7.8, “Changes for Flexibility, Ease of Use and Reliability”.

  • innodb_strict_mode

    Command-Line Format--innodb_strict_mode=#
    Option-File Formatinnodb_strict_mode
    Option Sets VariableYes, innodb_strict_mode
    Variable Nameinnodb_strict_mode
    Variable ScopeGlobal, Session
    Dynamic VariableYes
     Permitted Values
    Typeboolean
    DefaultOFF

    Whether InnoDB returns errors rather than warnings for exceptional conditions. This is analogous to strict SQL mode. The default value is OFF.

  • innodb_support_xa

    Command-Line Format--innodb_support_xa
    Option-File Formatinnodb_support_xa
    Option Sets VariableYes, innodb_support_xa
    Variable Nameinnodb_support_xa
    Variable ScopeGlobal, Session
    Dynamic VariableYes
     Permitted Values
    Typeboolean
    DefaultTRUE

    When the variable is enabled (the default), InnoDB support for two-phase commit in XA transactions is enabled, which causes an extra disk flush for transaction preparation.

    If you do not use XA transactions, you can disable this variable to reduce the number of disk flushes and get better InnoDB performance.

  • innodb_sync_spin_loops

    Command-Line Format--innodb_sync_spin_loops=#
    Option-File Formatinnodb_sync_spin_loops
    Option Sets VariableYes, innodb_sync_spin_loops
    Variable Nameinnodb_sync_spin_loops
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default30
    Range0-4294967295

    The number of times a thread waits for an InnoDB mutex to be freed before the thread is suspended. The default value is 30.

  • innodb_table_locks

    Command-Line Format--innodb_table_locks
    Option-File Formatinnodb_table_locks
    Option Sets VariableYes, innodb_table_locks
    Variable Nameinnodb_table_locks
    Variable ScopeGlobal, Session
    Dynamic VariableYes
     Permitted Values
    Typeboolean
    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.

  • innodb_thread_concurrency

    Command-Line Format--innodb_thread_concurrency=#
    Option-File Formatinnodb_thread_concurrency
    Option Sets VariableYes, innodb_thread_concurrency
    Variable Nameinnodb_thread_concurrency
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default0
    Range0-1000

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

    The correct value for this variable is dependent on environment and workload. Try a range of different values to determine what value works for your applications. A recommended value is 2 times the number of CPUs plus the number of disks.

    The range of this variable is 0 to 1000. A value of 0 (the default) is interpreted as infinite concurrency (no concurrency checking). Disabling thread concurrency checking enables InnoDB to create as many threads as it needs.

  • innodb_thread_sleep_delay

    Command-Line Format--innodb_thread_sleep_delay=#
    Option-File Formatinnodb_thread_sleep_delay
    Option Sets VariableYes, innodb_thread_sleep_delay
    Variable Nameinnodb_thread_sleep_delay
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Typenumeric
    Default10000

    How long InnoDB threads sleep before joining the InnoDB queue, in microseconds. The default value is 10,000. A value of 0 disables sleep.

  • innodb_use_native_aio

    Version Introduced5.5.4
    Command-Line Format--innodb_use_native_aio=#
    Option-File Formatinnodb_use_native_aio
    Option Sets VariableYes, innodb_use_native_aio
    Variable Nameinnodb_use_native_aio
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typeboolean
    DefaultON

    If a problem with the asynchronous I/O subsystem in the OS prevents InnoDB from starting, start the server with this variable disabled (use innodb_use_native_aio=0 in the option file). This variable applies to Linux systems only, and cannot be changed while the server is running.

    This variable was added in MySQL 5.5.4.

  • innodb_use_sys_malloc

    Command-Line Format--innodb_use_sys_malloc=#
    Option-File Formatinnodb_use_sys_malloc
    Option Sets VariableYes, innodb_use_sys_malloc
    Variable Nameinnodb_use_sys_malloc
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typeboolean
    DefaultON

    Whether InnoDB uses the operating system memory allocator (ON) or its own (OFF). The default value is ON.

  • innodb_version

    The InnoDB version number.

  • innodb_write_io_threads

    Command-Line Format--innodb_write_io_threads=#
    Option-File Formatinnodb_write_io_threads
    Option Sets VariableYes, innodb_write_io_threads
    Variable Nameinnodb_write_io_threads
    Variable ScopeGlobal
    Dynamic VariableNo
     Permitted Values
    Typenumeric
    Default4
    Range1-64

    The number of I/O threads for write operations in InnoDB. The default value is 4.

  • sync_binlog

    Command-Line Format--sync-binlog=#
    Option-File Formatsync_binlog
    Option Sets VariableYes, sync_binlog
    Variable Namesync_binlog
    Variable ScopeGlobal
    Dynamic VariableYes
     Permitted Values
    Platform Bit Size32
    Typenumeric
    Default0
    Range0-4294967295
     Permitted Values
    Platform Bit Size64
    Typenumeric
    Default0
    Range0-18446744073709547520

    If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after every sync_binlog writes to the binary log. There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise. The default value of sync_binlog is 0, which does no synchronizing to disk. A value of 1 is the safest choice, because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

Copyright © 2010-2024 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout