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 do not guarantee a response.

IMPORTANT: You must thoroughy test any instructions on a production-like test environment first before trying anything on production systems. And, make sure it is tested for security, privacy, and safety. See our terms here.

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:

• MySQL unique key vs primary key constraints
• How to speed up a Mysql replica?
• MySQL Query Cache not necessarily a bad thing
• MySQL open files limit: open-files-limit VS open_files_limit on Linux
• How to download and install MySQLdb module for Python on Linux?

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

• What should be monitored on a busy Database/MysQL server?
• How to speed up a Mysql replica?
• The server requested authentication method unknown to the client [phpmyadmin]
• How to set default values for MySQL JSON columns
• How to delete rows in table when PK is referenced in another table

Recent Tutorials:

• How to create and maintain a fixed length queue in dart?
• What should be monitored on a busy Database/MysQL server?
• Options to store data locally in a flutter app
• Dart - x positional argument(s) expected, but 0 found.
• ?? - the null-coalescing operator in Dart