How to set innodb_flush_log_at_timeout in MySQL 5.6?

Category:

Tutorials > Database Management Systems > MySQL > Administration

A setting that isn't used much but could be used more is innodb_flush_log_at_timeout, which controls how often InnoDB does an fsync from the OS buffer to disk.

How innodb_flush_log_at_timeout interacts with innodb_flush_log_at_trx_commit?

innodb_flush_log_at_trx_commit=1: writes from InnoDB internal log buffer to OS buffer and does an fsync to disk at each transaction commit. So, innodb_flush_log_at_timeout doesn't matter much. This setting is durable for MySQL crash and power outage.
innodb_flush_log_at_trx_commit=2: flush of InnoDB internal log buffer to OS buffer at each commit, fsync to disk at innodb_flush_log_at_trx_timeout interval. This setting is durable for MySQL crash but not power outage.

innodb_flush_log_at_trx_commit=0: writes to InnoDB internal log buffer at each commit. Does the fsync (and implicit log buffer flush) at innodb_flush_log_at_trx_timeout interval. This setting is not durable for MySQL crash or power outage.

Tuning innodb_flush_log_at_trx_commit and innodb_flush_log_at_timeout

So, you have two tuning options:

Change innodb_flush_log_at_trx_commit from 2 to 0 to reduce the number of flushes from the InnoDB log buffer to the OS buffer.
Change innodb_flush_log_at_timeout from 1 to 10 or 2 or whatever seconds to reduce the fsync rate.

The first option (above) is a good starting point, then move on to option 2. Or, try setting innodb_flush_log_at_trx_commit to 0 and innodb_flush_log_at_trx_timeout to 10. Then adjust the timeout to a lower value until you get to a good compromise between performance and durability. After that, try innodb_flush_log_at_trx_commit=2 instead of 1 to see if that then makes such difference or not.

IMPORTANT: Please make sure you thoroughly test any new changes in a test environment first before pushing changes to production.

Did this tutorial help a little? How about buy me a cup of coffee?

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I recommend thoroughy testing on a production-like test system first before moving to production.