MySQL Concurrent replication Series two: Multi-threaded Replication 2016

Concurrent Replication ( Parallel Replication ) Series II: Enhanced multi-threaded slaves

Ma Pengfei Technology MySQL database engineer

First, we comb the basic principle of traditional mysql/mariadb replication:

Master-slave replication is done through three threads, Binlog dump threads running on the master node, I/O threads, and SQL threads running on the slave node

• The master node binlog the dump thread, and master pushes the updated Binlog content to the slave node when the slave node is properly connected to master.
• The I/O thread of the slave node that is copied to the local relay log log file by reading the master node Binlog log name and offset information.
• The SQL thread for the slave node that reads the relay log log information and replays the transactions committed on the master node locally, achieving the same purpose as the Master library data.

Question 1:

The DB instance of the master node concurrently runs multiple threads at the same time commits the transaction, the committed transaction is written to the binary log log in the logical time (database LSN number), and the Slave node is written to the local relay log log through the I/O thread, but the slave node is only SQL single thread to perform log information in relay log replay main Library commit transaction, resulting in latency (lag) of the primary standby database

Think 1:

So in order to reduce the synchronization delay time of the primary and standby data, can the slave node run multiple functions, such as SQL threads, to replay the transactions performed in the main library because the repository is delayed by only single-threaded compensation data? The answer is of course: Yes! But we need to address the following issues:

1, slave local relay log record is the binary log log information of master, log information according to the chronological order of the transaction records, then in order to ensure the master data consistency,slave Nodes must be executed in the same order, and if the order is inconsistent, the risk of inconsistent master repository data is easily caused .

Such as:

Commit T1 and T2 transactions on the master node in the following order

1. state0:x= 1, y= 1

2. T1: {x:= Read (y);

3. x:= x+1;

4. Write (x);

5. Commit; }

6.
state1:x= 2, y= 1

7. T2: {y:= Read (x);

8. y:=y+1;

9. Write (y);

Commit; }

11.
State2:x= 2, y= 3

Slave nodes perform T1 and T2 in reverse order:

1. state0:x= 1, y= 1

2. T2: {y:= Read (x);

3. y:= y+1;

4. Write (y);

5. Commit; }

6.
state1:x= 1, y= 2

7. T1: {x:= Read (y);

8. x:=x+1;

9. Write (x);

Commit; }

11.
State2:x= 3, y= 2

MySQL 5.6 Improvements:

MySQL version 5.6 introduces concurrent replication (schema level), which is based on the schema-level concurrent replication core idea: "Data from tables in different schemas will not affect each other, The slave node can allocate a SQL -like thread to each of the different schemas in the relay log To replay the transactions already committed by the main library in relay log, keeping the data consistent with the main library . " The MySQL5.6 version of concurrent replication is visible, and a schema allocates a function similar to SQL threads .

Implementation 1:

Slave node on concurrent Replication (slave_parallel_workers=3) For example, the current slave SQL thread is Coordinator (Coordinator), and the thread that executes the relay log log is worker ( The current SQL thread not only acts as a coordinator, but also replays the transactions submitted by the main library in the relay log.

1. +-----+-------------+-----------+------+---------+-------+--------------------------------------------------- -----+------------------+

2. | Id | User | Host | db | Command | Time | State | Info |

3. +-----+-------------+-----------+------+---------+-------+--------------------------------------------------- -----+------------------+

4. | 1 |           System user | | NULL | Connect | 29923 | Slave have read all relay log; Waiting For more Updates | NULL |

5. | 2 |           System user | | NULL | Connect | 29923 | Waiting for a event from Coordinator | NULL |

6. | 3 |           System user | | NULL | Connect | 29923 | Waiting for a event from Coordinator | NULL |

7. | 4 |           System user | | NULL | Connect | 29923 | Waiting for a event from Coordinator | NULL |

Question 2:

MySQL 5.6 Concurrent replication based on schema level can resolve when the table of business data is placed under different database libraries, but in actual production, most or all of the business data tables are placed in the same schema, even if the Slave_parallel_ The workers>0 setting also fails to execute the main library submission data recorded in relay log concurrently. In the case of high concurrency, the main standby delay is still caused by the inability of slave to execute the business data tables under the same schema concurrently.

Think 2:

So if slave can simultaneously execute all the business data tables under a schema simultaneously in multi-threaded way, it will be able to greatly improve the slave node execution Ralay log in the main library commit transaction to synchronize with the main library data, to achieve this function we need to solve what problem?

• 1, mentioned earlier in order to ensure the consistency of the main database data, the master node writes the binary log log in accordance with the database logical time sequence and the slave node execution relay log in the main Library commit transactions must be in a consistent order Failure to do so will result in inconsistent primary and standby data.
• 2, since to achieve Scehma under all business data tables can be executed concurrently, then slave must know that concurrent execution relay log in the main library committed transactions can not affect each other and the results must be consistent with the main library.

Implementation 2:

MySQL 5.7 introduces enhanced muti-threaded slaves when slave is configured slave_parallel_workers>0 and Global.slave_parallel_ Type= ' Logical_clock ', can support a schema ,slave_parallel_workers a The worker thread concurrently executes the transaction committed by the main library in relay log . However, in order to achieve the above functions, it is necessary to mark the commit transaction in binary log on the master machine which can be executed concurrently . Although MySQL 5.6 has introduced binary log group commit, it does not mark transactions that can be executed concurrently.

We use orders MYSQLBINLOG-VVV mysqlbinlog.0000003 | Grep-i last_committed can be seen on the MySQL 5.7 master Machine last_committed and Sequence_number

1. #151223 15:11:28 server ID 15102 end_log_pos 14623 CRC32 0x767a33fa GTID last_committed=18 sequence_numb Er=26

2.

3. #151223 15:11:28 server ID 15102 end_log_pos 15199 CRC32 0x7dd1bf05 GTID last_committed=26 Sequence_numbe R=27

4.

5. #151223 15:11:28 Server ID 15102 end_log_pos 15773 CRC32 0xb01dc76e GTID last_committed=26 Sequence_numbe R=28

6.

7. #151223 15:11:28 Server ID 15102 end_log_pos 16347 CRC32 0x7a8e0ee8 GTID last_committed=26 Sequence_numbe r=29

8.

9. #151223 15:11:28 Server ID 15102 end_log_pos 16921 CRC32 0x92516d17 GTID last_committed=26 Sequence_numbe R=30

10.

#151223 15:11:28 server ID 15102 end_log_pos 17495 CRC32 0xeb14a51e GTID last_committed=26 Sequence_numbe R=31

12.

#151223 15:11:28 server ID 15102 end_log_pos 18071 CRC32 0x750667d0 GTID last_committed=26 Sequence_numbe R=32

14.

#151223 15:11:28 server ID 15102 end_log_pos 18645 CRC32 0xcaed6159 GTID last_committed=26 Sequence_numbe R=33

16.

#151223 15:11:28 server ID 15102 end_log_pos 19219 CRC32 0x62408408 GTID last_committed=26 Sequence_numbe R=34

18.

#151223 15:11:28 server ID 15102 end_log_pos 19793 CRC32 0x5cf46239 GTID last_committed=33 Sequence_numbe R=35

slave of the Machine Relay Log in last_committed the same transaction (Sequence_num different) can be executed concurrently . From the information above, we can see that there are 8 last_committed=26 transactions: from Sequence_number=27~24. Suppose that when slave_parallel_workers=7, the Coordinator thread (SQL thread) allocates this set of transactions to the worker queue to execute. As you can see, increasing the number of transactions in the binary log group commit of Master Library can increase the number of concurrent processing transactions for slave machines, MySQL5.7 introduce Binlog_group_commit_sync_delay and Binlog The _group_commit_sync_no_delay_count parameter increases the number of binary log group submissions concurrently. MySQL waits binlog_group_commit_sync_delay milliseconds until the number of transactions is Binlog_group_commit_sync_no_delay_count, and a group commit is made.

Summarize:

MySQL 5.7 GA version introduced the enhanced multi-threaded slaves feature, completely resolved the previous version of the main backup data replication delay problem, the function parameters are as follows:

1. # Slave machine

2. Slave-parallel-type=logical_clock

3. #slave-parallel-type=database #兼容MySQL 5.6 Concurrent replication based on schema level

4. Slave-parallel-workers=16 # Turn on multi-threaded replication

5. master_info_repository=table

6. relay_log_info_repository=table

7. Relay_log_recovery=on

MySQL Concurrent replication Series two: Multi-threaded Replication 2016