Mysql – Parallel replication is not working on a 5.7 slave from a 5.7 master

MySQLmysql-5.7parallelismreplication

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

Best Answer

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.

Related Question