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

sync_binlog=0 affects MySQL performance?

but sync_binlog=1 works well

Performance

As expected, with sync_binlog=0 there are plenty of binary log changes to be flushed to disk. There is none when running with sync_binlog=1. On the other hand, the average time to perform 10000 INSERTs jumped from 23 seconds to 41 seconds – nearly doubled.

Reliability

I’ve tested how reliable is each setting by running the script with 10000 INSERTs, forcefully powering off the whole system, starting up again and then comparing records (transactions) written to the database versus those written to binary log. I have tried it few times with both setting and the same results each time:

• with sync_binlog=1 the data in the database was always in sync with the binary log. That is, the last record succesfully inserted into the database, matches the last entry in binary log.
• with sync_binlog=0, after reboot there were thousands of records persisted in the database but missing from the binary log.

Summary

If you care about binary log (think about master-slave replication for example) set sync_binlog to 1, if you want to squeeze a bit more performance, set it to 0.

So as conclusion:

• setting sync_binlog & innodb_support_xa to 1 is required for maintaining the acidity of your database when using replication (make sure that your storage is able to deal with a large amount of sync to disk, fdatasync).
• choosing the alternative (sync_binlog != 1) can provide better performance at the cost of difficulties and conflicts during replication, and potentially losing transactions during a recovery.