Description:
The following paragraph from http://dev.mysql.com/doc/refman/5.7/en/binary-log.html is wrong in many ways as of MySQL 5.6:
"By default, the binary log is not synchronized to disk at each write. So if the operating system or machine (not only the MySQL server) crashes, there is a chance that the last statements of the binary log are lost. To prevent this, you can make the binary log be synchronized to disk after every N writes to the binary log, with the sync_binlog system variable. See Section 5.1.4, “Server System Variables”. 1 is the safest value for sync_binlog, but also the slowest. Even with sync_binlog set to 1, there is still the chance of an inconsistency between the table content and binary log content in case of a crash. For example, if you are using InnoDB tables and the MySQL server processes a COMMIT statement, it writes the whole transaction to the binary log and then commits this transaction into InnoDB. If the server crashes between those two operations, the transaction is rolled back by InnoDB at restart but still exists in the binary log. To resolve this, you should set --innodb_support_xa to 1. Although this option is related to the support of XA transactions in InnoDB, it also ensures that the binary log and InnoDB data files are synchronized."
1. The information about sync_binlog is wrong. It only syncs every N commit groups, not every N writes.
2. The information about how the binary log is written, is wrong. It writes many *prepared* transactions in sequence, then syncs the binary log, then performs a commit.
3. The information about how the binary log is restored, is over-cautious. innodb_support_xa defaults to 1 so there should not normally be anything to worry about.
The following paragraph from http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_support_xa is misleading:
"For a server that is accepting data changes from only one thread, it is safe and recommended to turn off this option to improve performance for InnoDB tables. For example, you can turn it off on replication slaves where only the replication SQL thread is changing data."
As of 5.6, replication can be done in parallel threads, and in that case it is important to use XA for the server. As of 5.7, there are very little limitations so we should recommend most users to enable multi-threaded slave.
Even in the case of a true single-threaded server with binary log enabled, the validity of this statement should be re-evaluated, since the introduction of binary log group commit in 5.6 may have changed this.
How to repeat:
-
