1

Note: Following are the parameters that we turned ON on the 5.7 replica for parallel replication to happen:

slave_preserve_commit_order = 1
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 10
log_slave_updates = 1
backup retention enabled for 1 day on the slave

On the 5.7 master, the following parameters were set:

binlog_order_commits = 1
binlog_group_commit_sync_delay = 50

We can see from watching the following query that only single threaded replication is happening:

select * from performance_schema.threads where name="thread/sql/slave_worker";

By executing the above query we saw that only one of the slave workers are actually committing to the database and for all the other threads it shows a status of "Waiting for an event from Coordinator"

Would like to know why exactly parallel replication is not working

2 Answers 2

1

MySQL can only use as many threads as are actually needed for replication given your database workload. The fact that those threads are visible would imply that parallel execution is in effect. Take a look at what a "SHOW PROCESSLIST" yields. More specifically, look at what the "time" column for the replication threads shows.

1
  • I was previously ignoring the fact that the parallelism that can be achieved by the slave workers depends heavily on the workload. Thanks Apr 11, 2019 at 7:20
1

The parallelism that can be achieved on any 5.7 instance which is a slave to a 5.7 master depends heavily on the workload.

We can however tweak the level of parallelism to a certain degree using the binlog_group_commit_sync_delay variable on the master. Increasing this variable typically increases the number of transactions to be committed in a group(in the binary log of the master), therefore increasing the number of parallel transactions on the slave.

Typically in most cases of oltp transactions, having binlog_order_commit=1 on the master and slave_preserve_commit_order=1 on the slave is the way to go as commits going out of order can render the slave data becoming inconsistent. Note that having for slave_preser_commit_order to be enabled, binary logs must be enabled on the slave. Technically this would slow down the slave thus impacting performance however this is a technical restriction of mysql and slave_preserve_commit_order has really nothing to do with binary logging. Folks have reported this to mysql and its probably going to be removed in future releases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.