Tuning InnoDB Configuration

Ovais Tariq -

I had earlier written a post on tuning the MySQL server configuration which was more geared towards the MyISAM storage engine. While that is not because I didn’t intend on ignoring InnoDB but because I had planned a whole post on tuning InnoDB related configuration. So this post is the post that I had planned, I have discussed the major configuration parameters in here that should help you out most of the times.

InnoDB buffer pool

InnoDB buffer pool has to be the most important part of any tuning activity. InnoDB relies heavily on the buffer pool, it uses it to cache indexes, row data, the adaptive hash index, the insert buffer and many other data structures used internally by InnoDB. The buffer pool is also used to buffer changes to data, so that write operations are delayed and so that they don’t have to be performed immediately on disk, thus improving the performance of write operations. Thus buffer pool is an integral part of InnoDB and its size has to be adjusted accordingly. Following is how you would set the size for different kinds of servers.

  • Set the buffer pool size to 80% or more of available RAM for a dedicated InnoDB only machine.
  • Set the size to upto 50% of available RAM on a dedicated MySQL machine if you have a mix of MyISAM and InnoDB tables.
  • Set the size to 50% of RAM you have allocated to MySQL for a machine that is not dedicated to MySQL only.

As you can guess the more memory that you can allocate to the buffer pool, the more the performance gains.

The configuration variable that deals with the size of buffer pool is innodb_buffer_pool_size.

InnoDB log file size

Choosing the right size for the InnoDB log file size, is important both for the write operations and for having a decent recovery time after server crash. First let’s see how the log file size matters for write operations.
Let’s first consider what goes inside the log actually. When records are modified the change is not written back to the tablespace on the disk immediately, instead the change is recorded at the end of the log file on disk and the page is marked as dirty. Hence, what InnoDB is actually doing is it is using its log to convert the random disk I/O into sequential I/O.
Now when the log is full, the dirty page is written out to the tablespace in log order so that space can be freed up in the log file. Now suppose in the middle of some transactions the server crashes, obviously the write operations had only been recorded in the log file. So before the server can come back live again, it goes through a recovery phase, where the changes recorded in the log file are replayed. The more the entries in the log file, the longer its going to take for the server to recover.
Hence, you see how the log file size affects both the recovery time as well as the write performance. So there has to be some balance between the recovery time and write performance when choosing the right number for the log file size. Typically anything between 128M and 512M is a good value.

The configuration variable that deals with log file size is innodb_log_file_size.

InnoDB thread concurrency

InnoDB has been designed for high concurrency and works very efficiently for applications having high concurrency. But still you have to configure the amount of concurrency that you are going to allow.

The variable that deals with concurrency is innodb_thread_concurrency which limits how many threads can enter the kernel at once.

A value of 0 means there is no limit. Theoretically speaking a high value would mean high resource utilization, but as of MySQL 5.1, you are better off limiting the concurrency to somewhere between 4 and 8, because InnoDB does have issue with higher numbers. Although the latest version of InnoDB shipping with 5.5 is said to have fixed the issues. Following is a formula you could use to calculate the value of thread concurrency you should allow:

concurrency = 2 * (Number of CPUs + Number of Disks)

When to flush the InnoDB log buffer?

When any change is made to the data, the change is not written to the disk immediately, instead its recorded in a log buffer, which is a portion of memory that InnoDB allocates to buffer changes that have to be recorded in the log file. Of course the size of the log buffer can be changed through configuration variables, but that is not the point here. The more important thing that we are interested in (and that can have an impact on write performance) is when should the changes recorded in the log buffer be actually recorded in the log file on disk. InnoDB flushes the buffer to the log file on disk when a transaction is committed, or when the buffer gets full, or once per second – whichever event happens first. The buffer will be flushed when its full, or once per second, and you can’t change this behavior. But what you can change is “when a transaction is committed” part.

The configuration variable that controls this is innodb_flush_log_at_trx_commit.
Possible values are:

  • 0: Do not flush the log buffer on transaction commit.
  • 1: Flush the log buffer to the log file on disk, on every transaction commit. This is the default setting.
  • 2: Flush the log buffer to OS cache, but not to the log file on disk.

The safest value out of the above is the value of 1 (flush on commit), which enforces ACID compliance and ensures the maximum data safety because every transaction commit is recorded on disk. But that also means that there is a overhead whenever a write operation is committed, remember that these write operations also include updates to data outside of a transaction, for example a simple UPDATE query. This can be an overkill in most of situations, because as I have mentioned above, InnoDB flushes the log every second anyway. So even if you set it 0 (no flush) or 2 (flush to OS cache), the only a possibility of any data loss is of loosing a transaction from last 1 second during a hardware failure or a MySQL server failure.

Hence if you really need full ACID compliance (say for a financial application), then choose 1, otherwise choose 0 or 2. More so if you have InnoDB tables as drop-in-place replacements for MyISAM tables then select a value of 0 or 2.

Conclusion

This is it from my side on tuning InnoDB configuration. I have touched the most important configuration options that effect the performance the most. In case you have any queries, or you need any help with tuning InnoDB you can always contact me!