mysql sync_binlog

Question

I was told that in mysql synchronization to ensure that everything is being replicated and data is safe, the syn_binlog setting (in my.conf) should be set to 1.

Only this kills performance, what is the best way to solve this?

I also read that this is mostly the cause of ext3 filesystem on linux. is it safe to set sync_binlog to 0?

Basically the real question is, do you need to turn on sync_binlog if you don't want to lose data?

Answer 1

The performance hit is the cost of the additional seek and writes. sync_binlog = 1 basically tells MySQL how many binary log writes to buffer before flushing the log writes to disk. If it's set to 1, then every write is flushed. 0 tells MySQL to let the filesystem handle it. So, if you have a disk failure you may lose some indeterminate number of binary log writes that are part of the last transaction.

Depending on your storage set up (SAN with battery backed cache, direct attached drives, etc.) a setting of 0 may be safe, or it may not. How much data loss can you and your application tolerate?

to quote the MySQL reference:

The default value of sync_binlog is 0, which does no synchronizing to disk. A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).