--innodb

Enables the InnoDB storage engine, if the server was compiled with InnoDB support. Use --skip-innodb to disable InnoDB.

--innodb_status_file

Causes InnoDB to create a file named <datadir>/innodb_status.<pid> in the MySQL data directory. InnoDB periodically writes the output of SHOW ENGINE INNODB STATUS to this file.

innodb_adaptive_hash_index

Whether InnoDB adaptive hash indexes are enabled or disabled. By default, this variable is enabled. Use --skip-innodb_adaptive_hash_index at server startup to disable it. See Section 13.6.13.3, “Adaptive Hash Indexes” This variable was added in MySQL 6.0.5.

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

innodb_autoextend_increment

The increment size (in MB) for extending the size of an auto-extending tablespace when it becomes full. The default value is 8.

innodb_autoinc_lock_mode

The locking mode to use for generating auto-increment values. The allowable values are 0, 1, or 2, for “traditional”, “consecutive”, or “interleaved” lock mode, respectively. The characteristics of these modes are described in Section 13.6.6.3, “How AUTO_INCREMENT Handling Works in InnoDB”.

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

innodb_buffer_pool_size

The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. 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.

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. Setting this parameter to 0 allows any number of transactions to commit simultaneously.

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

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 MB or GB (1024MB) by appending 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. See Section 13.6.3.2, “Using Raw Devices for the Shared Tablespace”.

innodb_data_home_dir

The common part of the directory path for all InnoDB data files. If you do not set this value, the default is the MySQL data directory. You can specify the value as an empty string, in which case you can use absolute file paths in innodb_data_file_path.

innodb_doublewrite

By default, InnoDB stores all data twice, first to the doublewrite buffer, and then to the actual data files. This variable is enabled by default. It 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

If you set this variable to 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 you set this variable to 1, InnoDB skips these operations at shutdown. The default value is 1. If you set it to 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. A value of 2 cannot be used on NetWare.

innodb_file_io_threads

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

If this variable is enabled, InnoDB creates each new table using its own .ibd file for storing data and indexes, rather than in the shared tablespace. The default is to create tables in the shared tablespace. See Section 13.6.3.1, “Using Per-Table Tablespaces”.

innodb_flush_log_at_trx_commit

When innodb_flush_log_at_trx_commit is set to 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 this 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 set to 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 this variable is 1, which is the value that is required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. If you set the value to 0, then any mysqld process crash can erase the last second of transactions. If you set the value to 2, then only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB's crash recovery is not affected and thus crash recovery does work regardless of the value. Note that 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.

Note: 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 and sync_binlog=1 in your master server my.cnf file.

innodb_flush_method

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

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

innodb_force_recovery

The crash recovery mode.

innodb_lock_wait_timeout

The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default is 50 seconds. When trying to access a row that is locked by another InnoDB transaction a connection will hang for at most this many seconds before issuing the following error:

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

In this case, the current statement is not executed. The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the --innodb_rollback_on_timeout option. See also Section 13.6.15, “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. This timeout does not apply to such a wait.

innodb_locks_unsafe_for_binlog

This variable controls next-key locking in InnoDB searches and index scans. By default, this variable is 0 (disabled), which means that next-key locking is enabled.

Normally, InnoDB uses an algorithm called next-key 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 any index records it encounters. Thus, the row-level locks are actually index record locks.

The locks that InnoDB sets on index records also affect the “gap” preceding that index record. If a user has a shared or exclusive lock on record R in an index, another user cannot insert a new index record immediately before R in the order of the index.

Enabling innodb_locks_unsafe_for_binlog causes InnoDB not to use next-key locking in searches or index scans. Next-key locking is still used to ensure foreign key constraints and duplicate key checking. Note that enabling this variable may cause phantom problems: Suppose that there is an index on the id column and that you want to read and lock all children from the child table with 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 do not lock out inserts made in the gaps, another client can insert a new row into the table. If you execute the same SELECT within the same transaction, you 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 this variable is enabled, InnoDB guarantees at most isolation level READ COMMITTED. (Conflict serializability is still guaranteed.)

Enabling this variable has an additional effect: InnoDB in an UPDATE or a DELETE only locks rows that it updates or deletes. This greatly reduces the probability of deadlocks, but they can happen. Note that enabling this variable still does not allow operations such as UPDATE to overtake other similar operations (such as another UPDATE) even in the case when they affect different rows. Consider the following example, beginning with this table:

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

Suppose that one client executes these statements:

SET AUTOCOMMIT = 0;
UPDATE A SET B = 5 WHERE B = 3;

Then suppose that another client executes these statements following those of the first client:

SET AUTOCOMMIT = 0;
UPDATE A SET B = 4 WHERE B = 2;

In this case, the second UPDATE must wait for a commit or rollback of the first UPDATE. The first UPDATE has an exclusive lock on row (2,3), and the second UPDATE while scanning rows also tries to acquire an exclusive lock for the same row, which it cannot have. This is because UPDATE two first acquires an exclusive lock on a row and then determines whether the row belongs to the result set. If not, it releases the unnecessary lock, when the innodb_locks_unsafe_for_binlog variable is enabled.

Therefore, InnoDB executes UPDATE one as follows:

x-lock(1,2)
unlock(1,2)
x-lock(2,3)
update(2,3) to (2,5)
x-lock(3,2)
unlock(3,2)
x-lock(4,3)
update(4,3) to (4,5)
x-lock(5,2)
unlock(5,2)

InnoDB executes UPDATE two as follows:

x-lock(1,2)
update(1,2) to (1,4)
x-lock(2,3) - wait for query one to commit or rollback

innodb_log_buffer_size

The size in bytes of the buffer that InnoDB uses to write to the log files on disk. Sensible values range from 1MB to 8MB. The default is 1MB. A large log buffer allows 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 on 32-bit computers. The default 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) is 2.

innodb_log_group_home_dir

The directory path to the InnoDB log files. If you do not specify any InnoDB log variables, the default is to create two 5MB files names 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 100. The default 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

This variable controls how to delay INSERT, UPDATE and DELETE operations when the purge operations are lagging (see Section 13.6.12, “Implementation of Multi-Versioning”). The default value of this variable is 0, meaning that there are no delays.

The InnoDB transaction system maintains a list of transactions that have delete-marked index records 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 our transactions are small, only 100 bytes in size, and we can allow 100MB of unpurged rows in our tables.

innodb_mirrored_log_groups

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

innodb_open_files

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

In MySQL 6.0, InnoDB rolls back only the last statement on a transaction timeout. If this option is given, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1).

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 updates 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_support_xa

When set to ON or 1 (the default), this variable enables InnoDB support for two-phase commit in XA transactions. Enabling innodb_support_xa causes an extra disk flush for transaction preparation.

If you do not wish to use XA, you can disable this variable by setting it to OFF or 0 to reduce the number of disk flushes and get better InnoDB performance.

Setting innodb_support_xa to ON or 1 on a replication master — or on any MySQL server where binary logging is in use — ensures that the binary log does not get out of sync compared to the table data.

innodb_sync_spin_loops

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

innodb_table_locks

If AUTOCOMMIT=0, InnoDB honors LOCK TABLES; MySQL does not return from LOCK TABLE .. 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

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. You will need to try a range of different values to determine what value works for your application.

The range of this variable is 0 to 1000. You can disable thread concurrency checking by setting the value to 0, which allows InnoDB to create as many threads as it needs.

The default value is 8.

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.

sync_binlog

If the value of this variable is positive, the MySQL server synchronizes its binary log to disk (fdatasync()) after every sync_binlog writes to this binary log. Note that there is one write to the binary log per statement if in autocommit mode, and otherwise one write per transaction. The default value 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/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).