sync_binlog=0 affects MySQL performance?but sync_binlog=1 works well

[DehuaYang]

Hello , right now I found some strange things in one MySQL instance .

backgroup
we use SSD to store datafile , but the binlog files are stored in the SAS disk
sync_binlog=0
TPS 4000 insert+update per second
max_binlog_size[500M]

when MySQL creates new binlog file , we found that some transcations commit statement would be written into slow.log

and when I set sync_binlog 0->2000->1000->200->100 .
Finally , when the sync_binlog=0 , the commit statement disappear in the slow.log .

Hope for you help . I am really appreciated.

[niljoshi]

Hi,

I’m bit confused here. Your subject says " sync_binlog=0 affects MySQL performance，but sync_binlog=1 works well" but your above message says “Finally , when the sync_binlog=0 , the commit statement disappear in the slow.log” means your performance problem resolved by setting sync_binlog=0. Can you please explain?

AFAIK, sync_binlog=1 can affect the performance because 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/