Saturday, September 21, 2013

MySQL 5.7 Enhanced MTS: configuring slave for Intra-database parallelization

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

ON MASTER:
1. start master with --binlog-max-flush-queue-time=0

ON SLAVE:
1.a. Start slave server with --slave-parallel-type=LOGICAL_CLOCK --slave-parallel-workers=N

Or alternatively,

1.b Start the slave server normally. Change the MTS options dynamically using
the following

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.

Conclusion

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.

4 comments:

  1. Rohit,

    Have you tried parallel replication using a 5.6 master? I've configured master's variables as you mentioned and slave keeps throwing these errors:
    Last_SQL_Errno: 1755
    Last_SQL_Error: Cannot execute the current event group in the parallel mode. Encountered event Query, relay-log name ./francisco-mariadb-25cbf970-f4fd-4e23-b79a-62e7dbb02af3-relay-bin.000058, position 662 which prevents execution of this event group in parallel mode. Reason: The master does not support the selected parallelization mode. It may be too old, or replication was started from an event internal to a transaction..

    Any ideas?

    Best.

    ReplyDelete
  2. Enhanced MTS is not available when using an old master. i.e 5.6 or before. This is because the enhanced MTS requires master to store information of the binlog groups, which are not being captured by the events generated on a 5.6 master. The enhanced MTS is only available for 5.7.1+ (master) ====> 5.7.1+(slave) replication.

    ReplyDelete
  3. MySQL is one of the easy to use ways for you. It is extremely powerful, scalable and secure as compared to anything else. It is known to be the ideal database solution for your websites due to its small size and speed. Database Diagram Tool

    ReplyDelete