Bug #75394 Improve documentation of binlog_order_commits.
Submitted: 2 Jan 2015 14:45 Modified: 24 Jan 2019 10:53
Reporter: Jean-François Gagné Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version:5.6 and 5.7 OS:Any
Assigned to: CPU Architecture:Any

[2 Jan 2015 14:45] Jean-François Gagné
Description:
Hi,

At the URLs below, the binlog_order_commits option is described:

http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_o...
http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_o...

The documentation is the following:

If this variable is enabled (the default), transactions are committed in the same order they are written to the binary log. If disabled, transactions may be committed in parallel. In some cases, disabling this variable might produce a performance increment.

It is unclear to me what what is the meaning of those 2 sentences:
1. "transactions are committed in the same order they are written to the binary log",
2. "transactions may be committed in parallel".

When a transaction is written to the binary logs, a COMMIT has already been issued on the session holding this transaction.  To my understanding, the transactions are written to the binary logs in the order they were committed, how could they then be committed in parallel ?  More details about this in the documentation would be useful.

Also, giving example of the impact of "committing transactions in parallel" vs "committing transaction in binlog order" would be useful to understand the impacts of turning this option off.

Many thanks,

JFG

How to repeat:
Not exactly a bug, more a documentation change, see description.
[24 Jan 2019 10:53] Margaret Fisher
Posted by developer:
 
Thanks for raising this issue - sorry you didn't get a response before. I have consulted with development and changed the description for this system variable to the following:

binlog_order_commits 
When this variable is enabled on a replication master (which is the default), transaction commit instructions issued to storage engines are serialized on a single thread, so that transactions are always committed in the same order as they are written to the binary log. Disabling this variable permits transaction commit instructions to be issued using multiple threads. Used in combination with binary log group commit, this prevents the commit rate of a single transaction being a bottleneck to throughput, and might therefore produce a performance improvement.

Transactions are written to the binary log at the point when all the storage engines involved have confirmed that the transaction is prepared to commit. The binary log group commit logic then commits a group of transactions after their binary log write has taken place. When binlog_order_commits is disabled, because multiple threads are used for this process, transactions in a commit group might be committed in a different order from their order in the binary log. (Transactions from a single client always commit in chronological order.) In many cases this does not matter, as operations carried out in separate transactions should produce consistent results, and if that is not the case, a single transaction ought to be used instead. 

If you want to ensure that the transaction history on the master and on a multithreaded replication slave remains identical, set slave_preserve_commit_order =1 on the replication slave.

- Thanks again for helping us to improve the documentation!