Tuning InnoDB Configuration

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!

  • Pingback: Tweets that mention Tuning InnoDB Configuration | ovais.tariq -- Topsy.com

  • http://donmcarthur.com/ Don McArthur

    Comprehensive, and a nice follow-up to your earlier piece on myisam storage engine. Thanks!

  • http://www.ovaistariq.net/ Ovais Tariq

    Thank you :)

  • inaam

    Ovais,

    I’d like to suggest that we can be a little more generous with log file size. InnoDB has made considerable improvements in recovery times (some detailed here http://blogs.innodb.com/wp/2010/04/innodb-performance-recovery/) and even with a 4G total log file size the recovery time should not exceed a few minutes. The performance benefit of having large log files on a write intensive workloads is pretty significant.

  • http://www.ovaistariq.net/ Ovais Tariq

    I agree with you on “The performance benefit of having large log files on a write intensive workloads is pretty significant.” But these performance improvements apply to the newer InnoDB plugins 1.0.7 and 1.1., And I still don’t see many people using it., So if anyone is using these newer versions then you point is absolutely valid.,

  • http://www.ovaistariq.net/ Ovais Tariq

    I agree with you on “The performance benefit of having large log files on a write intensive workloads is pretty significant.” But these performance improvements apply to the newer InnoDB plugins 1.0.7 and 1.1., And I still don’t see many people using it., So if anyone is using these newer versions then you point is absolutely valid.,

  • Pingback: Tuning InnoDB Configuration

  • Gtowey

    You forgot to mention that if you have a RAID controller with a BBU then using innodb_flush_logs_at_trx_commit=2 is safe, since the controller can write any unflushed data to disk in the even of power failure.

  • http://www.ovaistariq.net/ Ovais Tariq

    Thanks for pointing that out., But I didn’t mention it because I wanted the post to be more general,.

  • http://www.thelappool.com/lap-pool-dimensions/lap-pool-so-why-do-you-really-need-it/ lap pool dimensions

    This is
    the great blog, I’m reading them for a while, thanks for the new posts!

  • http://www.thelappool.com/lap-pool-size/choose-the-best-fit-size-lap-pool/ size lap pool

    Thanks
    for information, I’ll always keep updated here!

  • Oakley vault

    The eyewear brand companies Oakley vault sunglasses mainly for women with a couple of designs in the actual mens category. Known because of its colorful and unique sets, Oakley vault are easily recognizable using the embossed logo on the actual temples. The exquisite design is from the frames range from Oakley sunglasses outlet to classic aviator tones, with pastel and earthy colored lenses. The variety of colors the brand offers could be compared with the mood from the Cheap Oakley vault.

  • Ray Ban Sunglasses

    Ray Ban
    UK
    have produced its initial appearance about 80years
    in the past and become a hitting item inside the market speedily. These
    Ray Ban
    Sunglasses
    are committed to blocking glares and
    several other dangerous rays. Due to their distinctive traits and effectiveness
    in filtering dangerous rays, raybanuk2012.net
    grows to be widely accepted and famous all over the globe.