The InnoDB storage engine has many parameters in the my.cnf file that can dramatically affect the performance of its tables:

have_innodb

* Indicates the availability of the InnoDB storage engine

• Specified as Boolean String (YES or NO)

If InnoDB is compiled into the installation or available as part of the binary installation, this parameter is set to YES. If the server is started with the --skip-innodb directive it is set to NO and it is not possible to create InnoDB tables.

init_connect

* Execute the SQL commands of this parameter upon connection

• Specified as a series of semicolon delimited SQL statements
• Available as of 4.1.2

In the case of InnoDB this parameter can be used to turn off autocommit for each client connection that does not have the super privilege. This can be done with the following line:

init_connect='set autocommit=0;'

Prior to 4.1.2 use the init_file command line directive and specify the statement in the initialization file.

innodb_additional_mem_pool_size

* Initial size of memory allocated for InnoDB's internal data dictionary

• Specified in bytes. Specified as a pure number. 1024 implies 1024 bytes

If the memory requirement exceeds this allocation, InnoDB dynamically acquires memory. Insure this parameter represents the size of the data dictionary to increase performance. Be sure to account for any temporary tables your application creates in the normal course of operation.

innodb_buffer_pool_awe_mem_mb

* Set memory pool specific to Windows.

• Specified in megabytes. Maximum value is 64,000.
• Available as of 4.1.0

innodb_buffer_pool_size

* Size of data and index cache for InnoDB only

• Measured in megabytes
• This is the most crucial tuning parameter for InnoDB.

Innodb does not use the key_buffer. It uses this memory structure instead. On a host dedicated to MySQL this value can be set at up to 80% of physical memory. If this number is too large, paging will occur and performance will degrade significantly.

innodb_fast_shutdown

* Speed and clean up procedure at server shutdown

• Specified as an integer

When set to 0, InnoDB does a full but slow clean up on shutdown. This means that all current transactions must complete. This has the potential to take a long time. When set to 1, the above are skipped at shutdown, however at startup, InnoDB must clean up before initializing. So set this parameter based on when it makes the most sense to pay the time penalty for internal housekeeping. There is a risk in delaying this operation so it is recommend to set this value to 0.

innodb_file_io_threads

* Number of file I/O threads

• Specified as an integer
• Only affects Windows installations

innodb_flush_log_at_trx_commit

* Determines when a transaction becomes permanent

• Specified as an integer

A value of 0 means that InnoDB writes transactions to disk once per second. This means that it is possible to lose a second's worth of transactions in the event of a server crash. The up side is a gain in speed. When set to 1, InnoDB writes the transaction to disk at every commit. When set to 2 the transaction is written to the log at every commit but only written to the tablespace once per second from the log.
In decreasing order of safety and increasing order of speed:* 1 is the safest setting but is still subject to corruption in the event of a power outage.

• 2 is still very safe, but a OS or host crash can cause the loss of up to one second of transactions.
• 0 is the least safe (and fastest) choice. A MySQL server crash can cause the loss of up to one second of transactions.

Note that this parameter only controls the behavior of InnoDB. Many Unix and Linux systems cache writes. The hdparm command can address these concerns and assist in safeguarding data in the event of a host crashing due to power outage or the like.

innodb_flush_method

* Sets the operating system call used in a flush to disk

• Specified as a string
• Affects only Unix and Linux installations

innodb_force_recovery

* Sets the level of recovery if InnoDB fails to start after a crash

• Specified as an integer between 0 and 6
• Should only be set greater than 0 in the case of recovery

This parameter is one of the least strict of the recovery options and allows InnoDB to start even if corrupt pages are detected. Six is the most strict. The database cannot be used when running with any setting other 0. InnoDB prevents writes while in any mode other than 0, however the DROP TABLE statement is allowed to facilitate dropping a table known to be causing a crash on start up.

innodb_lock_wait_timeout

* Controls the time it takes for InnoDB to recognize a deadlock condition

• Specified as an integer number of seconds
• Useful before 4.0.20 and 4.1.2

InnoDB will expire a user specified lock causing a deadlock condition in the specified number of seconds the offending transaction is rolled back.

innodb_locks_unsafe_for_binlog

* Controls aspects of InnoDB's locking method

• Specified as Boolean (TRUE or FALSE)
• Recommended left at false

innodb_max_dirty_pages_pct

* Controls pages flushed from the innodb_buffer_pool

• Specified as an integer pecent from 0 to 100
• Available as of 4.0.13 and 4.1.1

InnoDB will try to flush pages from the buffer pool in such a way that this percentage remains at any given time.

innodb_max_purge_lag

* Controls when a row is purged from the database after a read event

• Specified as an integer representing the number of write events in queue
• Available as of 4.0.22 and 4.1.6

When a row is removed from an InnoDB table it is not immediately removed from the database. Only when all read locks are released can a row be removed. This operation is referred to as a purge. Write events are delayed by the number of milliseconds given by:

The purge_lag is the number of records marked for write. In the case where the purge operation begins to fall behind, it must be run more often to avoid the database becoming completely disk dependent. In this situation, increasing the innodb_max_purge_lag causes the server to decrease the amount of time between purge events.

innodb_status_file

* Creates the innodb_status.pid file to be created in the datadir

• No specification. Appears on its own line.
• Available as of 4.0.21.

The innodb_status.pid file contains regular output from the SHOW INNODB STATUS command.

innodb_table_locks

* Allows for user's table level locks

• Specified as a value of 0 or 1
• Obsolete as of 4.0.20 and 4.1.2

Simulates the table level locking of MyISAM tables for pseudo-transactional compatibility.

innodb_thread_concurrency

* Limits the number of operating system level threads held by InnoDB.

• Specified as an integer number of threads. (>= 500 indicates no limit)

Setting this value too high can cause contention within the processor. A good rule of thumb for the value of this parameter is:

number_of_processors + number_of_disk_arrays

sync_binlog

* Sets the number of transactions committed before syncing

• Specified as an integer
• Available as of 4.1.3

A value of 0 indicates no synchronization between the tablespace and binary log. A value of 1 is safest since only one transaction is held before a write to the log must take place. To increase speed at the cost of an incomplete disaster recovery from the binary log, increase this number as appropriate.

transaction_alloc_block_size

* Size of memory block for storing a transaction

• Specified as a number of bytes
• Available as of 4.0.16
• Allocated on a per transaction basis

The queries that make up a transaction are stored in memory blocks of this size until flushed to the log upon commit. Given that most transactions are quite small in terms of the text of their query, this value should be quite small (2048). Keep in mind the size of queries being generated by your application automatically. Some can be quite large.

transaction_prealloc_size

* Size of the memory buffer for storing transaction blocks (see transaction_alloc_block_size)

• Specified in megabytes
• Available as of 4.0.16

This is the permanent area of memory for storing transaction blocks. Increasing the size of this parameter decreases the necessity for dynamic memory allocation and increases performance. This value should be set in consideration of the size of common transactions and the sync_binlog variable.

tx_isolation

* Default ACID compliance level

• Specified as string (READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE READ OR SERIALIZABLE)
• Available as of 4.0.3 for REPEATABLE READ OR SERIALIZABLE. All available as of 4.0.5.