MySQL 5.0 Reference Manual  /  ...  /  InnoDB Startup Options and System Variables

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

  • Many system variables can be changed at runtime (see Section 5.1.5.2, “Dynamic System Variables”).

  • For information about GLOBAL and SESSION variable scope modifiers, refer to the SET statement documentation.

  • For more information on specifying options and system variables, see Section 4.2.3, “Specifying Program Options”.

Table 14.2 InnoDB Option/Variable Reference

NameCmd-LineOption FileSystem VarStatus VarVar ScopeDynamic
Com_show_innodb_status   YesBothNo
foreign_key_checks  Yes SessionYes
have_innodb  Yes GlobalNo
innodbYesYes    
innodb_adaptive_hash_indexYesYesYes GlobalNo
innodb_additional_mem_pool_sizeYesYesYes GlobalNo
innodb_autoextend_incrementYesYesYes GlobalYes
innodb_buffer_pool_awe_mem_mbYesYesYes 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_rnd   YesGlobalNo
Innodb_buffer_pool_read_ahead_seq   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_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_io_threadsYesYesYes GlobalNo
innodb_file_per_tableYesYesYes GlobalNo
innodb_flush_log_at_trx_commitYesYesYes GlobalYes
innodb_flush_methodYesYesYes GlobalNo
innodb_force_recoveryYesYesYes GlobalNo
innodb_lock_wait_timeoutYesYesYes GlobalNo
innodb_locks_unsafe_for_binlogYesYesYes GlobalNo
innodb_log_arch_dirYesYesYes GlobalNo
innodb_log_archiveYesYesYes 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_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_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-safe-binlogYesYes    
innodb-status-fileYesYes    
innodb_support_xaYesYesYes BothYes
innodb_sync_spin_loopsYesYesYes GlobalYes
innodb_table_locksYesYesYes BothYes
innodb_thread_concurrencyYesYesYes GlobalYes
innodb_thread_sleep_delayYesYesYes GlobalYes
innodb_use_legacy_cardinality_algorithmYesYesYes GlobalYes
timed_mutexesYesYesYes GlobalYes
unique_checks  Yes SessionYes

InnoDB Command Options

  • --innodb

    Enables the InnoDB storage engine, if the server was compiled with InnoDB support.

    To disable InnoDB, use --skip-innodb. In this case, the server will not start if the default storage engine is set to InnoDB. Use --default-storage-engine to set the default to some other engine if necessary.

  • --innodb-status-file

    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

  • innodb_adaptive_hash_index

    Introduced5.0.52
    Command-Line Format--innodb_adaptive_hash_index=#
    System VariableNameinnodb_adaptive_hash_index
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultON

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

  • innodb_additional_mem_pool_size

    Command-Line Format--innodb_additional_mem_pool_size=#
    System VariableNameinnodb_additional_mem_pool_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default1048576
    Min Value524288
    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 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 1MB.

  • innodb_autoextend_increment

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

    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 file-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_buffer_pool_awe_mem_mb

    Command-Line Format--innodb_buffer_pool_awe_mem_mb=#
    System VariableNameinnodb_buffer_pool_awe_mem_mb
    Variable ScopeGlobal
    Dynamic VariableNo
    Platform SpecificWindows
    Permitted Values (Windows)Typeinteger
    Default0
    Min Value0
    Max Value63000

    The size of the buffer pool (in MB), if it is placed in the AWE memory. If it is greater than 0, innodb_buffer_pool_size is the window in the 32-bit address space of mysqld where InnoDB maps that AWE memory. A good value for innodb_buffer_pool_size is 500MB. The maximum possible value is 63000.

    To take advantage of AWE memory, you will need to recompile MySQL yourself. The current project settings needed for doing this can be found in the innobase/os/os0proc.c source file.

    This variable is relevant only in 32-bit Windows. If your 32-bit Windows operating system supports more than 4GB memory, using so-called Address Windowing Extensions, you can allocate the InnoDB buffer pool into the AWE physical memory using this variable.

  • innodb_buffer_pool_size

    Command-Line Format--innodb_buffer_pool_size=#
    System VariableNameinnodb_buffer_pool_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default8388608
    Min Value1048576

    The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 8MB. 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. However, do not set it too large because competition for physical memory might cause paging in the operating system. Also, 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 8.10.2, “The InnoDB Buffer Pool”

  • innodb_checksums

    Introduced5.0.3
    Command-Line Format--innodb_checksums
    System VariableNameinnodb_checksums
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    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. This variable was added in MySQL 5.0.3.

  • innodb_commit_concurrency

    Introduced5.0.12
    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. This variable was added in MySQL 5.0.12.

  • innodb_concurrency_tickets

    Introduced5.0.3
    Command-Line Format--innodb_concurrency_tickets=#
    System VariableNameinnodb_concurrency_tickets
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default500
    Min Value1
    Max Value4294967295

    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. This variable was added in MySQL 5.0.3.

    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.

  • innodb_data_file_path

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

    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 14.2.1, “Configuring InnoDB”.

  • 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 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

    Introduced5.0.3
    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, 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. This variable was added in MySQL 5.0.3.

  • 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. By default, the value is 1, which causes a fast shutdown (the normal type of shutdown). If the value is 0, InnoDB does a full purge and an insert buffer merge before a shutdown. These operations can take minutes, or even hours in extreme cases. If the value is 1, InnoDB skips these operations at shutdown. If the value is 2, InnoDB will just flush its logs and then shut down cold, as if MySQL had crashed; no committed transaction will be lost, but crash recovery will be done at the next startup. The value of 2 can be used as of MySQL 5.0.5, except that it cannot be used on NetWare.

  • innodb_file_io_threads

    Command-Line Format--innodb_file_io_threads=#
    System VariableNameinnodb_file_io_threads
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default4
    Min Value4
    Max Value64

    The number of file I/O threads in InnoDB. Normally, this should be left at the default value of 4, but disk I/O on Windows may benefit from a larger number. On Unix, increasing the number has no effect; InnoDB always uses the default value.

  • innodb_file_per_table

    Command-Line Format--innodb_file_per_table
    System VariableNameinnodb_file_per_table
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeboolean
    DefaultOFF

    If innodb_file_per_table is disabled (the default), InnoDB creates tables in the shared 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 shared tablespace. See Section 14.2.1.4, “InnoDB File-Per-Table Tablespaces” for more information including advantages and disadvantages of using file-per-table tablespaces.

  • 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

    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, you should use innodb_flush_log_at_trx_commit=1, sync_binlog=1, and, before MySQL 5.0.3, innodb-safe-binlog in your master server my.cnf file. (innodb-safe-binlog is not needed from 5.0.3 on.)

    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
    System VariableNameinnodb_flush_method
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted Values (Unix)Typestring
    DefaultNULL
    Valid Valuesfdatasync
    O_DSYNC
    littlesync
    nosync
    O_DIRECT
    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 fdatasync 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:

    • fdatasync: InnoDB uses the fsync() system call to flush both the data and log files. fsync is the default setting. The fdatasync option name should not be confused with the fdatasync() system call, which is not used by InnoDB as of MySQL 3.23.41.

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

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

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

    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. This option is used for internal performance testing and is currently unsupported. Use at your own risk.

    • unbuffered: InnoDB uses a simulated asynchronous I/O and non-buffered I/O. This option is used for internal performance testing and is currently unsupported. Use at your own risk.

    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.

  • 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. Possible values are from 0 to 6. For the meanings of these values and important information about innodb_force_recovery, see Section 14.2.6.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.

  • innodb_lock_wait_timeout

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

    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. (Until MySQL 5.0.13 InnoDB rolled back the entire transaction if a lock wait timeout happened. You can restore this behavior by starting the server with the --innodb_rollback_on_timeout option, available as of MySQL 5.0.32. See also Section 14.2.12, “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
    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. Normally, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the gap before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order. See Section 14.2.8.2, “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 13.3.6, “SET TRANSACTION Syntax”.

    Enabling innodb_locks_unsafe_for_binlog may cause phantom problems because other sessions can insert new rows into the gaps when gap locking is disabled. Suppose that there is an index on the id column of the child table and that you want to read and lock all rows from the table having an identifier value larger than 100, with the intention of updating some column in the selected rows later:

    SELECT * FROM child WHERE id > 100 FOR UPDATE;
    

    The query scans the index starting from the first record where id is greater than 100. If the locks set on the index records in that range do not lock out inserts made in the gaps, another session can insert a new row into the table. Consequently, if you were to execute the same SELECT 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 14.2.8.3, “Avoiding the Phantom Problem Using Next-Key Locking”.

    Starting from MySQL 5.0.2, enabling innodb_locks_unsafe_for_binlog has an additional effect. 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. Note that enabling this variable still does not permit operations such as UPDATE to overtake other similar operations (such as another UPDATE) even when they affect different rows.

    Consider the following example, beginning with this table:

    CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
    INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
    COMMIT;
    

    In this case, table has no indexes, so searches and index scans use the hidden clustered index for record locking (see Section 14.2.10.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)
    

    The second UPDATE proceeds part way before it blocks. It begins acquiring x-locks, and blocks when it tries to acquire one for a row still locked by first UPDATE. The second UPDATE does not proceed until the first UPDATE commits or rolls back:

    x-lock(1,2); update(1,2) to (1,4); retain x-lock
    x-lock(2,3); block and wait for first UPDATE to commit or roll back
    

    In this case, the second UPDATE must wait for a commit or rollback of the first UPDATE, even though it affects different rows. The first UPDATE has an exclusive lock on row (2,3) that it has not released. As the second UPDATE scans rows, it tries to acquire an exclusive lock for that same row, which it cannot have.

  • innodb_log_arch_dir

    This variable is unused, and is deprecated as of MySQL 5.0.24. It is removed in MySQL 5.1

  • innodb_log_archive

    Whether to log InnoDB archive files. This variable is present for historical reasons, but is unused. Recovery from a backup is done by MySQL using its own log files, so there is no need to archive InnoDB log files. The default for this variable is 0.

  • innodb_log_buffer_size

    Command-Line Format--innodb_log_buffer_size=#
    System VariableNameinnodb_log_buffer_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default1048576
    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 is 1MB. Sensible values range from 1MB to 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=#
    System VariableNameinnodb_log_file_size
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default5242880
    Min Value1048576
    Max Value4GB / 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 4GB. A pair of 2047 MB log files, for example, would allow you to approach the range limit but not exceed it. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. 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=#
    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.

  • 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 log files. 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_max_dirty_pages_pct

    Command-Line Format--innodb_max_dirty_pages_pct=#
    System VariableNameinnodb_max_dirty_pages_pct
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypenumeric
    Default90
    Min Value0
    Max Value100

    This is an integer in the range from 0 to 100. The default value is 90. 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=#
    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 14.2.9, “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_open_files

    Command-Line Format--innodb_open_files=#
    System VariableNameinnodb_open_files
    Variable ScopeGlobal
    Dynamic VariableNo
    Permitted ValuesTypeinteger
    Default300
    Min Value10
    Max Value4294967295

    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_rollback_on_timeout

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

    In MySQL 5.0.13 and up, 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 before MySQL 5.0.13). This variable was added in MySQL 5.0.32.

  • innodb-safe-binlog

    Deprecated5.0.3
    Removed5.0.3
    Command-Line Format--innodb-safe-binlog
    Permitted ValuesTypeboolean

    If this option is given, then after a crash recovery by InnoDB, mysqld truncates the binary log after the last not-rolled-back transaction in the log. The option also causes InnoDB to print an error if the binary log is smaller or shorter than it should be. See Section 5.4.3, “The Binary Log”. This variable was removed in MySQL 5.0.3, having been made obsolete by the introduction of XA transaction support. You should set innodb_support_xa to ON or 1 to ensure consistency. See innodb_support_xa.

  • innodb_support_xa

    Introduced5.0.3
    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.

    For a server that is accepting data changes from only one thread, it is safe and recommended to turn off this option to improve performance for InnoDB tables. For example, you can turn it off on replication slaves where only the replication SQL thread is changing data.

    You can also turn off this option if you do not need it for safe binary logging or replication, and you also do not use an external XA transaction manager.

    This variable was added in MySQL 5.0.3.

  • innodb_sync_spin_loops

    Introduced5.0.3
    Command-Line Format--innodb_sync_spin_loops=#
    System VariableNameinnodb_sync_spin_loops
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeinteger
    Default20
    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 20. This variable was added in MySQL 5.0.3.

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

  • innodb_thread_concurrency

    Command-Line Format--innodb_thread_concurrency=#
    System VariableNameinnodb_thread_concurrency
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (<= 5.0.7)Typeinteger
    Default8
    Min Value1
    Max Value1000
    Permitted Values (>= 5.0.8, <= 5.0.18)Typeinteger
    Default20
    Min Value1
    Max Value1000
    Permitted Values (>= 5.0.19, <= 5.0.20)Typeinteger
    Default0
    Min Value0
    Max Value1000
    Permitted Values (>= 5.0.21)Typeinteger
    Default8
    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 20 or higher is interpreted as infinite concurrency before MySQL 5.0.19. From 5.0.19 on, you can disable thread concurrency checking by setting the value to 0. Disabling thread concurrency checking enables InnoDB to create as many threads as it needs.

    The default value has changed several times: 8 before MySQL 5.0.8, 20 (infinite) from 5.0.8 through 5.0.18, 0 (infinite) from 5.0.19 to 5.0.20, and 8 (finite) from 5.0.21 on.

    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.

    • If an operation, such as an ALTER TABLE operation, completes in a few minutes on an idle system but takes hours on a busy system (perhaps aborting due to insufficient log space), a lower but still acceptable innodb_thread_concurrency value has been shown to allow such operations to complete in times comparable to those on an idle system. A lower but still acceptable innodb_thread_concurrency value, in this case, is a value that might reduce overall performance by 10 or 20 percent.

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

  • innodb_thread_sleep_delay

    Introduced5.0.3
    Command-Line Format--innodb_thread_sleep_delay=#
    System VariableNameinnodb_thread_sleep_delay
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted Values (>= 5.0.8, <= 5.0.18)Typeinteger
    Default0
    Min Value1000
    Max Value4294967295
    Permitted Values (>= 5.0.19, <= 5.0.20)Typeinteger
    Default0
    Min Value1000
    Max Value4294967295
    Permitted Values (>= 5.0.21)Typeinteger
    Default8
    Min Value1000
    Max Value4294967295

    How long InnoDB threads sleep before joining the InnoDB queue, in microseconds. The default value is 10,000. A value of 0 disables sleep. This variable was added in MySQL 5.0.3.

  • innodb_use_legacy_cardinality_algorithm

    Introduced5.0.82
    Command-Line Format--innodb_use_legacy_cardinality_algorithm=#
    System VariableNameinnodb_use_legacy_cardinality_algorithm
    Variable ScopeGlobal
    Dynamic VariableYes
    Permitted ValuesTypeboolean
    DefaultON

    InnoDB uses random numbers to generate dives into indexes for calculating index cardinality. However, under certain conditions, the algorithm does not generate random numbers, so ANALYZE TABLE sometimes does not update cardinality estimates properly. An alternative algorithm was introduced in MySQL 5.0.82 with better randomization properties, and the innodb_use_legacy_cardinality_algorithm, system variable which algorithm to use. The default value of the variable is 1 (ON), to use the original algorithm for compatibility with existing applications. The variable can be set to 0 (OFF) to use the new algorithm with improved randomness.

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


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.