As Morgan announced it his blog post related to new defaults in 5.7, in 5.7.7 and newer the default value of sync_binlog is now 1.
This of course has an impact on performance especially if you don’t have fast storage like a RAID controller with a cache for example.
Already in O’reilly High Performance MySQL, is was written that the most important setting for binary logging on the master is sync_binlog = 1.
And this recommendation was made for MySQL 5.0 ! At that time the performance impact could reach 50%. In 5.6, with the addition of binary log group commit the impact from enabling sync_binlog = 1 on many non-synthetic workloads should be reduced considerably and is the safest option as all transactions are synchronized to the binary log before they are committed. Therefore, even after a crash, any transactions that are missing from the binary log are only in prepared state and those transaction will be rollback during the recovery process at server restart. This guarantees that no transaction is lost from the binary log, and is the safest option. If disabled you could have some transaction that have been committed but not present in the binary log. This could be very dangerous in some architecture were more and more people rely on replication. For example if you provision a new slave using last night’s backup and replay all binary logs since, the master and the slave won’t be in sync.
This is a major change compare to previous versions and don’t forget that it needs innodb_support_xa to be enabled (this is the default). So yes, since 5.6 during recovery the binary logs can be edited !
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.
Just for information, the current documentation, doesn’t seem to be 100% accurate. I entered a bug (#81800) to review it.
Hi!
I was debugging why in my default Ubuntu LAMP my msql 8 was strangely slow. And the reason seems to be sync_binlog = 1 but I don’t get it why it should be enabled on single host.
Hello,
Defaults are for safety and modern hardware IO. On a single instance when binary logs are enabled they are acting as the transaction coordinator. Having them sync to disk is required for consistency in case of problem. You can also use them for point-in-time recovery. Now, if you don’t use binary logs, instead of relaxing in durability, I would recommend to just disable them.
Cheers