MySQL 5.7.2 features enhanced Multi-threaded slave which can be used to apply transactions in parallel even within a single database. Internal details of its working can be found in an earlier post. In this post we will see how we can configure our replication slave to use this enhancement.
MySQL 5.7.2 has a new system variable --slave-parallel-type which is dynamic. It can be set to the following values:
1. DATABASE : (Default) Use the db partitioned MTS (1 worker per database)
2. LOGICAL_CLOCK: Use logical clock based parallelization mode.
Apart from this the original option of --slave-parallel-workers=N is still valid and it sets that number of workers that we need to spawn. Also since the slave leverages the group of transactions that have committed in parallel on the slave, it makes sense to leave --binlog-max-flush-queue-time=0 which is the default value intact, on the master. This will ensure that the leader thread on the master flushes all the transactions queued in the FLUSH QUEUE of binlog group commit without getting timed out, thereby delivering maximum parallelization on the slave.
Finally to summarize the steps to set up the enhanced MTS
1. start master with --binlog-max-flush-queue-time=0
1.a. Start slave server with --slave-parallel-type=LOGICAL_CLOCK --slave-parallel-workers=N
1.b Start the slave server normally. Change the MTS options dynamically using
mysql: STOP SLAVE: --if the slave is running
mysql: SET GLOBAL SLAVE_PARALLEL_TYPE='LOGICAL_CLOCK';
mysql: SET GLOBAL SLAVE_PARALLEL_WORKER=N;
mysql: START SLAVE:
A small Demo:1. We created 5 tables in a single test database on master and used 5 clients to do inserts on them, in parallel.
2. The slave was configured as --slave-parallel-type="logical_clock" and --slave-parallel-workers=5.
3. We let the slave replicate from the master and we checked the status of the workers by using
performance schema tables for replication and show processlist command
Here is the sample output on the slave (click on the image to zoom)
When to use enhanced MTS
Since the slave uses the parallelization information from the master, it performs best when there are multiple clients on the master and there are multiple transactions committing at the same time. In case the master is underloaded, spawning multiple threads may not have effect on the slave performance, and may even lead to performance degradation.
This enhancement is available in MySQL 5.7.2 which can be downloaded from the MySQL download page. So try it out and let us know your valuable feedback.