How to set innodb_flush_log_at_timeout in MySQL 5.6?

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:

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

0 comments so far. Be the first one to leave a comment on this article.

Name
Email address
Website
Comments
	Notify me of follow-up comments via email.

Similar Tutorials:

• Fast, parallel restore from SQL dumps (mysqldump) for MySQL
• Transfering MySQL Administrator/Query Browser connection details
• How to delete rows in table when PK is referenced in another table
• Is MySQL 5.6 slower than MySQL 5.5? No, it's faster
• Not-so-well-known differences between MyISAM and Innodb MySQL server storage engines

Tutorials in 'Database Management Systems > MySQL' (more):

• How to set default values for MySQL JSON columns
• How to delete rows in table when PK is referenced in another table
• Unique primary keys(PKs) for MySQL using Redis
• How to rename MySQL DB by moving tables
• Is MySQL 5.6 slower than MySQL 5.5? No, it's faster

Recent Tutorials:

• How to set default values for MySQL JSON columns
• How to delete rows in table when PK is referenced in another table
• Unique primary keys(PKs) for MySQL using Redis
• How to rename MySQL DB by moving tables
• How to compile and install Varnish-cache on Linux