Problems with sync_binlog

[kvheerden]

Today my Master DB server (MySQL 5.5) suddenly started failing to write to binary log files. All INSERT and UPDATE queries gets a “query end” status, and then it doesn’t release the connection.

I found a couple of related topics on StackOverflow, but the only way I was able to fix the issue was to set sync_binlog to 0. Now it seems that most queries are never even being written to the binary logs, which is not good for replication.

The most likely reason would be due to some file system related issue (such as full drive, etc.), but everything seems to check out.

Thanks

[yogesh777]

“query end” stats occurs after processing a query but before freeing the items, i think if sync_binlog is set to1 and there are huge insert/updates on server then there are chances of interlocking problem on disk when multiple thread want to write the file at the same time. changing sync_binlog to 0 changes the behavior of binary log flush where operating system decides when to fsync() rather than flushing after every commit when sync_binlog is 1.
However, both options have different performance impact.

[niljoshi]

Hi,

As Yogesh described, sync_binlog=1 can affect the performance because of extra fsync(), probably matters for “slowness”, and this is expected. It depends on filesystem too as ext3 is not working properly with sync_binlog. You can get more information here.

http://www.mysqlperformanceblog.com/2009/01/21/beware-ext3-a nd-sync-binlog-do-not-play-well-together/

If you are using battery backup cache OR RAID then it might be help to improve the performance as they make synchronization very fast.

Btw: Even If you’ll set sync_binlog=0, all queries will be written into the binary logs. So it will not affect the replication but yes, in case of server crash, you can loose some of the binary log entries due to sync_binlog=0.