Shulou(Shulou.com)05/31 Report--

This article introduces you how to understand MySQL multithreaded replication, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Enhanced Multi-threaded Slaves

First of all, sort out the basic principles of traditional MySQL/MariaDB active / standby replication:

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

The Binlog dump thread of the master node, when the slave node is normally connected to the master, the master pushes the updated binlog content to the slave node.

The binlog O thread of the slave node, which copies it to the local relay log log file by reading the master node relay log log name and offset information.

The SQL thread of the slave node, which reads the relay log log information and plays back the transactions committed on the master node locally to achieve the purpose of being consistent with the main database data.

Question 1:

The database instance of the Master node concurrently runs multiple threads to commit transactions at the same time, and the committed transactions are written to the binary log log according to the logical time (database LSN number). The slave node writes to the local relay log log through the Imax O thread, but the slave node has only SQL single thread to execute the log information in the relay log to replay the transactions committed by the master database, resulting in a delay (lag) in the master and standby database.

Think 1:

In order to reduce the synchronization delay time of master and standby data, the delay is caused by the reason that only a single thread compensates the data in the slave database, so can the slave node run multiple functions like SQL threads at the same time to replay the transactions executed in the main database? Of course the answer is: yes! But we need to solve the following problems:

1. The local relay log of slave records the binary log log information of master, and the log information is recorded in the chronological order of transactions. In order to ensure the consistency of master and slave data, slave nodes must be executed in the same order. If the order is inconsistent, it is easy to cause the risk of data inconsistency between master and slave databases.

Such as:

T1 and T2 transactions are committed at the master node in the following order

1. State0: X = 1, y = 1

2. T1: {XRV = Read (y)

3. XPlus = xroom1

4. Write (x)

5. Commit;}

6.

State1: X = 2, y = 1

7. T2: {yvv = Read (x)

8. Y:=y+1

9. Write (y)

10. Commit;}

11.

State2: X = 2, y = 3

Slave nodes perform T1 and T2 in the opposite order:

1. State0: X = 1, y = 1

2. T2: {yvv = Read (x)

3. Y _ v = y _ y _ 1

4. Write (y)

5. Commit;}

6.

State1: X = 1, y = 2

7. T1: {XRV = Read (y)

8. X:=x+1

9. Write (x)

10. 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 core idea of concurrent replication at schema level: "when tables under different schema are submitted concurrently, the data will not affect each other, that is, slave nodes can replay transactions that have been committed by the main database in relay log by assigning a thread similar to SQL function to different schema in the relay log, so as to keep the data consistent with the main database." You can see the concurrent replication of the MySQL5.6 version, where a schema assigns a function similar to a SQL thread.

Achieve 1:

Slave node enables concurrent replication (slave_parallel_workers=3) as shown in the following figure, the current slave SQL thread is Coordinator (coordinator), and the thread that executes relay log logs is worker (the current SQL thread not only acts as a coordinator, but also replays transactions committed by the main library in relay log).

1. +- -+

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

3. +- -+

4. | 1 | system user | | NULL | Connect | 29923 | Slave has read all relay log; waiting for more updates | NULL |

5. | 2 | system user | | NULL | Connect | 29923 | Waiting for an event from Coordinator | NULL |

6. | 3 | system user | | NULL | Connect | 29923 | Waiting for an event from Coordinator | NULL |

7. | 4 | system user | | NULL | Connect | 29923 | Waiting for an event from Coordinator | NULL |

Question 2:

MySQL 5.6 concurrent replication based on schema level can solve the problem when the tables of business data are placed under different database libraries, but in actual production, most or all of the business data tables are placed under the same schema. In this scenario, even if slave_parallel_workers > 0 is set, the data submitted by the master database recorded in relay log cannot be executed concurrently. In the case of high concurrency, the delay between master and slave will still occur because slave cannot execute business data tables under the same schema concurrently.

Think 2:

Then if slave can execute all the business data tables under one schema by multithreading at the same time, it will greatly improve the slave node to execute the commit transaction of the main library recorded in ralay log to achieve the purpose of synchronizing with the main database data. What problems do we need to solve to achieve this function?

1. As mentioned earlier, in order to ensure the data consistency of the master database, the binary log logs written by the master node follow the logical chronological order of the database, and the transactions committed by the master database in the relay log must be executed by the slave node in a consistent order, otherwise it will cause inconsistency between the master and backup data.

2. Since all business data tables under scehma can be executed concurrently, slave must know that transactions committed by the main database in relay log cannot affect each other and the results must be consistent with the main database.

Implementation 2:

MySQL 5.7introduces Enhanced Muti-threaded slaves, when slave configuration slave_parallel_workers > 0 and global.slave_parallel_type='LOGICAL_CLOCK', can support one schema, slave_parallel_workers worker threads execute transactions committed by the main library in relay log concurrently. However, in order to achieve the above functions, it is necessary to mark which transactions committed in binary log in the master machine can be executed concurrently. Although binary log group commit has been introduced in MySQL 5.6, there are no transactions that can be executed concurrently.

We can see last_committed and sequence_number on the master machine of MySQL 5.7with the command mysqlbinlog-vvv mysqlbinlog.0000003 | grep-I last_committed.

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

two。

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

4.

5. # 151223 15:11:28 server id 15102 end_log_pos 15773 CRC32 0xb01dc76e GTID last_committed=26 sequence_number=28

6.

7. # 151223 15:11:28 server id 15102 end_log_pos 16347 CRC32 0x7a8e0ee8 GTID last_committed=26 sequence_number=29

8.

9. # 151223 15:11:28 server id 15102 end_log_pos 16921 CRC32 0x92516d17 GTID last_committed=26 sequence_number=30

10.

11. # 151223 15:11:28 server id 15102 end_log_pos 17495 CRC32 0xeb14a51e GTID last_committed=26 sequence_number=31

twelve。

13. # 151223 15:11:28 server id 15102 end_log_pos 18071 CRC32 0x750667d0 GTID last_committed=26 sequence_number=32

14.

15. # 151223 15:11:28 server id 15102 end_log_pos 18645 CRC32 0xcaed6159 GTID last_committed=26 sequence_number=33

16.

17. # 151223 15:11:28 server id 15102 end_log_pos 19219 CRC32 0x62408408 GTID last_committed=26 sequence_number=34

18.

19. # 151223 15:11:28 server id 15102 end_log_pos 19793 CRC32 0x5cf46239 GTID last_committed=33 sequence_number=35

Transactions with the same last_committed (different sequence_num) in the relay log of the slave machine can be executed concurrently. From the information intercepted above, we can see that last_committed=26 has a total of eight transactions: from sequence_number=27~24. Suppose that when slave_parallel_workers=7, the Coordinator thread (SQL thread) allocates this set of transactions to be queued in worker to execute. Here we can see that increasing the number of transactions in the binary log group commit group of the master library can increase the number of transactions processed concurrently by the slave machine. The introduction of binlog_group_commit_sync_delay and binlog_group_commit_sync_no_delay_count parameters by MySQL5.7 increases the concurrent number of binary log group commits. When MySQL waits for binlog_group_commit_sync_delay milliseconds until the number of binlog_group_commit_sync_no_delay_count transactions, a group commit occurs.

Summary:

The Enhanced Multi-threaded Slaves feature introduced in MySQL version 5.7 GA completely solves the problem of delay in master / slave data replication in the previous version. The parameters for enabling this feature are as follows:

1. # slave machine

2. Slave-parallel-type=LOGICAL_CLOCK

3. # slave-parallel-type=DATABASE # compatible with MySQL 5.6concurrent replication based on schema level

4. Slave-parallel-workers=16 # enables multithreaded replication

5. Master_info_repository=TABLE

6. Relay_log_info_repository=TABLE

7. Relay_log_recovery=ON

On how to understand MySQL multithreaded replication to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.