Chapter 14. Storage Engines

Concurrency: Some applications have more granular lock requirements (such as row-level locks) than others. Choosing the right locking strategy can reduce overhead and therefore improve overall performance. This area also includes support for capabilities such as multi-version concurrency control or "snapshot" read.

Transaction Support: Not every application needs transactions, but for those that do, there are very well defined requirements such as ACID compliance and more.

Referential Integrity: The need to have the server enforce relational database referential integrity through DDL defined foreign keys.

Physical Storage: This involves everything from the overall page size for tables and indexes as well as the format used for storing data to physical disk.

Index Support: Different application scenarios tend to benefit from different index strategies. Each storage engine generally has its own indexing methods, although some (such as B-tree indexes) are common to nearly all engines.

Memory Caches: Different applications respond better to some memory caching strategies than others, so although some memory caches are common to all storage engines (such as those used for user connections or MySQL's high-speed Query Cache), others are uniquely defined only when a particular storage engine is put in play.

Performance Aids: This includes multiple I/O threads for parallel operations, thread concurrency, database checkpointing, bulk insert handling, and more.

Miscellaneous Target Features: This may include support for geospatial operations, security restrictions for certain data manipulation operations, and other similar features.

If your server crashes because of a hardware or software issue, regardless of what was happening in the database at the time, you don't need to do anything special after restarting the database. InnoDB automatically finalizes any changes that were committed before the time of the crash, and undoes any changes that were in process but not committed. Just restart and continue where you left off.

The InnoDB buffer pool caches table and index data as the data is accessed. Frequently used data is processed directly from memory. This cache applies to so many types of information, and speeds up processing so much, that dedicated database servers assign up to 80% of their physical memory to the InnoDB buffer pool.

If you split up related data into different tables, you can set up foreign keys that enforce referential integrity. Update or delete data, and the related data in other tables is updated or deleted automatically. Try to insert data into a secondary table without corresponding data in the primary table, and it gets kicked out automatically.

If data becomes corrupted on disk or in memory, a checksum mechanism alerts you to the bogus data before you use it.

When you design your database with appropriate primary key columns for each table, operations involving those columns are automatically optimized. It is very fast to reference the primary key columns in WHERE clauses, ORDER BY clauses, GROUP BY clauses, and join operations.

Inserts, updates, deletes are optimized by an automatic mechanism called change buffering. InnoDB not only allows concurrent read and write access to the same table, it caches changed data to streamline disk I/O.

Performance benefits are not limited to giant tables with long-running queries. When the same rows are accessed over and over from a table, a feature called the Adaptive Hash Index takes over to make these lookups even faster, as if they came out of a hash table.

Specify a primary key for every table using the most frequently queried column or columns, or an auto-increment value if there isn't an obvious primary key.

Embrace the idea of joins, where data is pulled from multiple tables based on identical ID values from those tables. For fast join performance, define foreign keys on the join columns, and declare those columns with the same datatype in each table. The foreign keys also propagate deletes or updates to all affected tables, and prevent insertion of data in a child table if the corresponding IDs are not present in the parent table.

Turn off autocommit. Committing hundreds of times a second puts a cap on performance (limited by the write speed of your storage device).

Bracket sets of related changes, logical units of work, with START TRANSACTION and COMMIT statements. While you don't want to commit too often, you also don't want to issue huge batches of INSERT, UPDATE, or DELETE statements that run for hours without committing.

Stop using LOCK TABLE statements. InnoDB can handle multiple sessions all reading and writing to the same table at once, without sacrificing reliability or high performance. To get exclusive write access to a set of rows, use the SELECT ... FOR UPDATE syntax to lock just the rows you intend to update.

Enable the innodb_file_per_table option to put the data and indexes for individual tables into separate files, instead of in a single giant system tablespace. (This setting is required to use some of the other features, such as table compression and fast truncation.)

Evaluate whether your data and access patterns benefit from the new InnoDB table compression feature (ROW_FORMAT=COMPRESSED on the CREATE TABLE statement. You can compress InnoDB tables without sacrificing read/write capability.

Run your server with the option --sql_mode=NO_ENGINE_SUBSTITUTION to prevent tables being created with a different storage engine if there is an issue with the one specified in the ENGINE= clause of CREATE TABLE.

You can compress tables and associated indexes.

You can create and drop indexes with much less performance or availability impact than before.

Truncating a table is very fast, and can free up disk space for the operating system to reuse, rather than freeing up space within the system tablespace that only InnoDB could reuse.

The storage layout for table data is more efficient for BLOBs and long text fields.

You can monitor the internal workings of the storage engine by querying INFORMATION_SCHEMA tables.

You can monitor the performance details of the storage engine by querying performance_schema tables.

There are many many performance improvements. In particular, crash recovery, the automatic process that makes all data consistent when the database is restarted, is fast and reliable. (Now much much faster than long-time InnoDB users are used to.) The bigger the database, the more dramatic the speedup.

Most new performance features are automatic, or at most require setting a value for a configuration option. For details, see Section 14.4.7, "InnoDB Performance and Scalability Enhancements". For InnoDB-specific tuning techniques you can apply in your application code, see Section 8.5, "Optimizing for InnoDB Tables". Advanced users can review Section 14.3.4, "InnoDB Startup Options and System Variables".

Issue the command SHOW VARIABLES LIKE 'have_innodb'; to confirm that InnoDB is available at all. If the result is NO, you have a mysqld binary that was compiled without InnoDB support and you need to get a different one. If the result is DISABLED, go back through your startup options and configuration file and get rid of any skip-innodb option.

Issue the command SHOW ENGINES; to see all the different MySQL storage engines. Look for DEFAULT in the InnoDB line.

You can back up or restore a single table quickly without interrupting the use of other InnoDB tables, using the MySQL Enterprise Backup product. See Backing Up and Restoring a Single .ibd File for the procedure and restrictions.

Storing specific tables on separate physical disks, for I/O optimization or backup purposes.

Restoring backups of single tables quickly without interrupting the use of other InnoDB tables.

Using compressed row format to compress table data.

Reclaiming disk space when truncating a table.

There are no uncommitted modifications by transactions in the .ibd file.

There are no unmerged insert buffer entries in the .ibd file.

Purge has removed all delete-marked index records from the .ibd file.

mysqld has flushed all modified pages of the .ibd file from the buffer pool to the file.

--ignore-builtin-innodb

In MySQL 5.1, this option caused the server to behave as if the built-in InnoDB were not present, which enabled InnoDB Plugin to be used instead. In MySQL 5.5, InnoDB is the default storage engine and InnoDB Plugin is not used, so this option has no effect. As of MySQL 5.5.22, it is deprecated and its use results in a warning.

--innodb[=value]

Controls loading of the InnoDB storage engine, if the server was compiled with InnoDB support. This option has a tristate format, with possible values of OFF, ON, or FORCE. See Section 5.1.8.1, "Installing and Uninstalling Plugins".

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

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

ignore_builtin_innodb

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

innodb_adaptive_flushing

Specifies whether to dynamically adjust the rate of flushing dirty pages in the InnoDB buffer pool based on the workload. Adjusting the flush rate dynamically is intended to avoid bursts of I/O activity. This setting is enabled by default.

innodb_adaptive_hash_index

Whether the InnoDB adaptive hash index is enabled or disabled. The adaptive hash index feature is useful for some workloads, and not for others; conduct benchmarks with it both enabled and disabled, using realistic workloads. See Section 14.3.11.4, "Adaptive Hash Indexes" for details. This variable is enabled by default. Use --skip-innodb_adaptive_hash_index at server startup to disable it.

innodb_additional_mem_pool_size

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

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

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 14.3.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_instances

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 takes effect only when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more. The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte.

innodb_buffer_pool_size

The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 128MB. The maximum value depends on the CPU architecture; the maximum is 4294967295 (2³²-1) on 32-bit systems and 18446744073709551615 (2⁶⁴-1) on 64-bit systems. On 32-bit systems, the CPU architecture and operating system may impose a lower practical maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server.

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:

innodb_change_buffering

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 14.4.7.4, "Controlling InnoDB Change Buffering".

innodb_checksums

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

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

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

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.3.2, "Configuring InnoDB".

innodb_data_home_dir

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

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

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

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

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

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

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

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.

innodb_flush_method

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.

Depending on hardware configuration, setting innodb_flush_method to O_DIRECT can either have either a positive or negative effect on performance. Benchmark your particular configuration to decide which setting to use. The mix of read and write operations in your workload can also affect which setting performs better for you. 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 filesystem 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 the same type of hardware and workload that reflects your production environment.

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_load_corrupted

Lets InnoDB load tables at startup that are marked as corrupted. Use only during troubleshooting, to recover data that is otherwise inaccessible. When troubleshooting is complete, turn this setting back off and restart the server.

innodb_force_recovery

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

innodb_io_capacity

An upper limit on the I/O activity performed by the InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the insert buffer. The default value is 200. For busy systems capable of higher I/O rates, you can set a higher value at server startup, to help the server handle the background maintenance work associated with a high rate of row changes. For systems with individual 5400 RPM or 7200 RPM drives, you might lower the value to the former default of 100.

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

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

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

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

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

See Section 14.4.7.11, "Controlling the Master Thread I/O Rate" for more guidelines about this option. For general information about InnoDB I/O performance, see Section 8.5.7, "Optimizing InnoDB Disk I/O".

innodb_large_prefix

Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes), for InnoDB tables that use the DYNAMIC and COMPRESSED row formats. (Creating such tables also requires the option values innodb_file_format=barracuda and innodb_file_per_table=true.) See Section 14.3.15, "Limits on InnoDB Tables" for the relevant maximums associated with index key prefixes under various settings.

For tables using the REDUNDANT and COMPACT row formats, this option does not affect the allowed key prefix length. It does introduce a new error possibility. When this setting is enabled, attempting to create an index prefix with a key length greater than 3072 for a REDUNDANT or COMPACT table causes an error ER_INDEX_COLUMN_TOO_LONG (1727).

innodb_lock_wait_timeout

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

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

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

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

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

The lock wait timeout value does not apply to deadlocks, because InnoDB detects them immediately and rolls back one of the deadlocked transactions.

innodb_locks_unsafe_for_binlog

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.3.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:

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.3.9.5, "Avoiding the Phantom Problem Using Next-Key Locking".

Enabling innodb_locks_unsafe_for_binlog has additional effects:

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.3.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-lockx-lock(2,3); update(2,3) to (2,5); retain x-lockx-lock(3,2); retain x-lockx-lock(4,3); update(4,3) to (4,5); retain x-lockx-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-lockx-lock(3,2); unlock(3,2)x-lock(4,3); update(4,3) to (4,5); retain x-lockx-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-lockx-lock(2,3); unlock(2,3)x-lock(3,2); update(3,2) to (3,4); retain x-lockx-lock(4,3); unlock(4,3)x-lock(5,2); update(5,2) to (5,4); retain x-lock

innodb_log_buffer_size

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

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

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

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

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

This variable controls how to delay INSERT, UPDATE, and DELETE operations when purge operations are lagging (see Section 14.3.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 290328385Purge done for trx's n:o < 0 290315608 undo n:o < 0 17History list length 20

innodb_mirrored_log_groups

Has no effect.

innodb_old_blocks_pct

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 8.9.1, "The InnoDB Buffer Pool" for information about buffer pool management, such as the LRU algorithm and eviction policies.

innodb_old_blocks_time

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

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

This variable is often used in combination with innodb_old_blocks_pct. See Section 8.9.1, "The InnoDB Buffer Pool" for information about buffer pool management, such as the LRU algorithm and eviction policies.

innodb_open_files

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

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

innodb_purge_batch_size

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

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

Controls the sensitivity of linear read-ahead that InnoDB uses to prefetch pages into the buffer pool. If InnoDB reads at least innodb_read_ahead_threshold pages sequentially from an extent (64 pages), it initiates an asynchronous read for the entire following extent. The permissible range of values is 0 to 64. 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

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

innodb_replication_delay

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

innodb_rollback_on_timeout

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_rollback_segments

Defines how many of the rollback segments in the system tablespace that InnoDB uses within a transaction. You might reduce this value from its default of 128 if a smaller number of rollback segments performs better for your workload.

innodb_spin_wait_delay

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

innodb_stats_method

How the server treats NULL values when collecting statistics about the distribution of index values for InnoDB tables. This variable has three possible values, nulls_equal, nulls_unequal, and nulls_ignored. For nulls_equal, all NULL index values are considered equal and form a single value group that has a size equal to the number of NULL values. For nulls_unequal, NULL values are considered unequal, and each NULL forms a distinct value group of size 1. For nulls_ignored, NULL values are ignored.

The method that is used for generating table statistics influences how the optimizer chooses indexes for query execution, as described in Section 8.3.7, "InnoDB and MyISAM Index Statistics Collection".

innodb_stats_on_metadata

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

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 14.4.8, "Changes for Flexibility, Ease of Use and Reliability".

innodb_strict_mode

Whether InnoDB returns errors rather than warnings for certain conditions. This is analogous to strict SQL mode. The default value is OFF. See Section 14.4.8.4, "InnoDB Strict Mode" for a list of the conditions that are affected.

innodb_support_xa

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.

innodb_sync_spin_loops

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

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.

As of MySQL 5.5.3, innodb_table_locks = 0 has no effect for tables locked explicitly with LOCK TABLES ... WRITE. It still has an effect for tables locked for read or write by LOCK TABLES ... WRITE implicitly (for example, through triggers) or by LOCK TABLES ... READ.

innodb_thread_concurrency

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. A value of 0 also disables the queries inside InnoDB and queries in queue counters in the ROW OPERATIONS section of SHOW ENGINE INNODB STATUS output.

innodb_thread_sleep_delay

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

Specifies whether to use the Linux asynchronous I/O subsystem. This variable applies to Linux systems only, and cannot be changed while the server is running.

Normally, you do not need to touch this option, because it is enabled by default. 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 option could also be turned off automatically during startup, if InnoDB detects a potential problem such as a combination of tmpdir location, tmpfs filesystem, and Linux kernel that that does not support AIO on tmpfs.

This variable was added in MySQL 5.5.4.

innodb_use_sys_malloc

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

innodb_version

The InnoDB version number. Starting in 5.5.30, the separate numbering for InnoDB is discontinued and this value is the same as for the version variable.

innodb_write_io_threads

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

sync_binlog

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

If innodb_fast_shutdown is not set to 2: Stop the MySQL server and make sure that it shuts down without errors (to ensure that there is no information for outstanding transactions in the log). Copy the old log files into a safe place in case something went wrong during the shutdown and you need them to recover the tablespace. Delete the old log files from the log file directory, edit my.cnf to change the log file configuration, and start the MySQL server again. mysqld sees that no InnoDB log files exist at startup and creates new ones.

If innodb_fast_shutdown is set to 2: Set innodb_fast_shutdown to 1:

mysql> SET GLOBAL innodb_fast_shutdown = 1;

Then follow the instructions in the previous item.