How to flush binary logs

Question

I read a comment on another post that InnoDB can be optimized for speed by not flushing binary logs on every commit.

This is the first time I hear about this, so what are these binary logs and what's this flushing all about? Why should I do it (and when not to do it) and how?

Answer 1

MySQL's binary log acts as the database equivalent the journal in a journaling file system. Every query gets logged in it, so that if the DB crashes at some point, you can "replay" the binary log and rebuild the DB with minimal pain. It's also used in replication.

More details here: http://dev.mysql.com/doc/refman/5.1/en/binary-log.html

Answer 2

InnoDB recovers from crashes by replaying the logs. And it's also used for replicating master server to slave ones.

Answer 3

The binary logs are used in crash recovery and replication. Assuming they are enabled, the two main applicable settings regarding flushing are:

• innodb_flush_log_at_trx_commit=2
• sync_binlog=0

Those settings favor speed over "guaranteed" reliability because they allow the OS to delay in flushing the buffers to disk.

If you are running on a cheap disk controller (most people are), flushing the logs out on every commit is very slow. On a site that does a lot of writes, I've seen page execution time drop 10x-100x! It's easy to run your own benchmarks and see how it affects you.

To be very clear, if you want maximum recovery benefits, you must set both of those values to 1. As configured above, you may lose a few seconds of data in the case of a system crash. For some sites, that is not acceptable. For blogs and forums, it probably is a worthwhile trade-off.

You can read more about the settings here:

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html

There are many memory related options like 'innodb_buffer_pool_size' to configure as well. A search on MySQL InnoDB tuning should help out.