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:
- 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.
tags cloud
popular searches
free download for mysql database server 5.1.5, linux, php, bison, gearman, ubuntu, java, mysql, mysql mysql, install mysql, tools, install cairo, laptop, mysql initialization, source code
Similar Tutorials:
- How to delete rows in table when PK is referenced in another table
- How to find all tables of a particular storage engine in MySQL?
- How to install MySQL Gearman UDF on Ubuntu/Debian Linux
- Download all Aspersa MySQL tools/scripts through a single command
- Not-so-well-known differences between MyISAM and Innodb MySQL server storage engines
Tutorials in 'Database Management Systems > MySQL' (more):
- 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
- Download, configure, compile, and install MySQL 5.6
Comments (write a comment):
0 comments so far. Be the first one to leave a comment on this article.