On MySQL-5.7.2, we released a new type of multi-threaded slave (MTS). It is called logical clock based MTS. Because it can parallel apply transactions in the same schema, it has the potential to improve slave throughput on almost any application, regardless of the schema layout. After it was released, we continued to work on the framework to improve it further. Therefore, 5.7.5 includes a few enhancements, in addition to those released as part of previous DMRs. This blog post introduces one of the new features in the latest DMR.
slave_preserve_commit_order
It is a system global variable and can be set dynamically.
- SET GLOBAL slave_preserve_commit_order = {ON|OFF};
Enabling this variable ensures that the order which transactions were committed on the master is preserved on the slave. The replication threads must be stopped before enabling this variable and this variable only affects logical clock based MTS.
The implementation is tough related to binary logging. Hence there are two things you mush be know.
– Transaction Commit Order Must Equal to Binlog Order
This feature doesn’t add any information to binary log events. When applying events, it just suppose binlog_order_commits is enabled on master(it is enabled by default), and transactions’ commit order is same to the order they are written to binary log. And the order is kept in relay log too. So slave applier exactly commits transactions being applied according the order they are written into the relay log.
– Must Binlog Slave’s Update
You might worry that preserving commit order will impact slave’s throughput. Indeed, we considered it either. To have it with least performance impact, we implemented it based on binlog group commit. So this feature is only available if the binlog is enabled and log_slave_updates is on. Slave’s binlog_order_commits need to be enabled too. I did a micro-benchmark on it and the result showed it just lost 5% performance.
– Summarize The Usage
You may be confused by my explanation. Let me summarize the usage.
- On master
- binlog_order_commits should be enabled
- On slave
- binlog_order_commits should be enabled
- binary log should be enabled
- log_slave_update should be enabled
- slave_preserve_commit_order should be enabled
Use Case of The Feature
When a slave is configured to execute transactions in parallel, the transactions that execute in parallel can be committed out of order. Thus, the slave database can be in a state that never existed on the master. While it works well for most applications, it will cause problems if your applications depend on transactions’ commit order.
For example, On master
- session1> INSERT INTO t1 VALUES(1);
- session2> INSERT INTO t1 VALUES(2);
- session3> SELECT * FROM t1;
Suppose above commands are executed parallel and session1 commits its transaction before session2 commits its transaction. So session3 can get one of below results:
- empty result if it reads before both session1 and session2 commit
- 1 if it reads after session1 commits but before session2 commits
- 1 and 2 if it reads after both session1 and session2 do commit
While on slave, they may be committed in different order:
- worker1> INSERT INTO t1 VALUES(1);
- worker2> INSERT INTO t1 VALUES(2);
- session3> SELECT * FROM t1;
Suppose worker2 commits its transaction before worker1 commits its transaction. Then session3 can get one of below results:
- empty result if it reads before both worker1 and worker2 commit
- 2 if it reads after worker2 commits but before worker1 commits
- 1 and 2 if it reads after both worker1 and worker2 commit
Slave database has a state that 2 is inserted before 1. That didn’t happen on master.
If your applications require master and slave have same intermediate state, then you need to turn on this future.