MySQL 5.7 Parallel replication

First, Reason:

One day to see the master-slave replication delay alarm a bit frequently, thinking is not completely can be solved.

General master-slave replication, with three threads involved, is single threaded: Binlog Dump (Master)----->io thread (from)-----> SQL thread (from). Replication appears to be delayed generally out of two places

1) SQL thread is not busy (may need to apply a large amount of data, and some operations from the library itself may have lock and resource conflicts; The main library can write concurrently, SQL threads cannot; main reason)

2) network jitter causes IO thread replication latency (secondary cause).

Second, the solution:

MySQL began with the concept of SQL thread multiple from 5.6, can be concurrently restored data, that is, parallel replication technology.

MySQL 5.6, set the parameter Slave_parallel_workers = 4 (>1), you can have 4 SQL thread (Coordinator thread) for parallel replication, the state is: waiting for a evant from Coordinator.


But its parallelism is based on schema, which is library-based. If there is more than one schema in the DB instance, this setting can have a significant increase in the speed of slave replication. In general, a single-Library multi-table is a more common situation,

The library-based concurrency has no OVA. The core idea is that the data of the tables under different schemas will not affect each other, that is, the slave node can use the relay log in a different Schema each assigned a similar SQL functionality of the thread,

to Replay Relay Log The transaction that the main library has committed, keeping the data consistent with the main library.

In MySQL 5.7, a parallel replication based on group submission (enhanced multi-threaded slaves) was introduced, setting the parameters slave_parallel_workers>0 and Global.slave_parallel_ Type= ' Logical_clock ',

can support a Schema Down, slave_parallel_workers One of worker concurrent execution of Threads Relay Log the transaction that is committed in the main library. Its core idea: a group of committed transactions can be played back in parallel (with binary log Group commit);

slave of the Machine Relay Log in last_committed the same transaction (Sequence_num different) can be executed concurrently.

Where the variable slave-parallel-type can have two values: database default, based on the parallel replication mode of the library; Logical_clock: Parallel replication based on group submission

MySQL 5.7 Open Enhanced multi-threaded slave configuration:

# slave Slave-parallel-type=logical_clock slave-parallel-workers=16 master_info_repository=table relay_log_info_ Repository=table Relay_log_recovery=on

At this point, MySQL has completely solved the replication delay problem, welcome to congratulate!

MySQL 5.7 Parallel replication