Shulou(Shulou.com)06/01 Report--

Parallel Replication Series 2: Enhanced Multi-threaded Slaves

Author: Ma Pengfei, MySQL Database Engineer, Woqu Technology

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

Master-slave replication is accomplished by three threads, binlog dump threads running on master node, I/O threads and SQL threads running on slave node

The Binlog dump thread of the master node pushes the updated binlog contents to the slave node when the slave node is properly connected to the master node.

I/O thread for slave node, which copies master binlog name and offset information to local relay log file.

SQL thread of slave node. This thread reads relay log information and plays back transactions committed on master node locally, so as to keep consistent with main database data.

Question 1:

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

Thinking 1:

So in order to reduce the synchronization delay time of the primary and standby data, because the standby database only has single thread compensation data, which causes delay, can the slave node run multiple functions like SQL thread at the same time to replay the transactions executed in the primary database? The answer, of course, is yes! But we need to solve the following problems:

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

For example:

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

Slave nodes perform T1 and T2 in reverse order:

MySQL 5.6 improvements:

MySQL version 5.6 introduces concurrent replication (schema level), which is based on the core idea of schema level concurrent replication: "Data submitted concurrently under different schemas will not affect each other, that is, slave nodes can allocate a thread similar to SQL function to different schemas in relay log to replay transactions submitted by main database in relay log, and keep data consistent with main database." See MySQL version 5.6 concurrent replication, where a schema assigns a SQL thread-like function.

Implementation 1:

Slave node enables concurrent replication (slave_parallel_workers=3). As shown in the figure below, the SQL thread of the current slave is Coordinator (coordinator), and the thread executing relay log is worker(the current SQL thread not only plays the role of coordinator, but also replays transactions submitted by the main library in relay log).

Question 2:

MySQL 5.6 schema-level concurrent replication can solve the problem that when business data tables are placed under different databases, most or all business data tables are often placed under the same schema in actual production. In this scenario, even if slave_parallel_workers>0 is set, the master database submission data recorded in relay log cannot be executed concurrently. In the case of high concurrency, since slave cannot execute business data tables under the same schema concurrently, it will still cause active and standby delays.

Thinking 2:

So if slave can execute all business data tables under a schema in a multithreaded manner at the same time, it will greatly improve the slave node's execution of the main database commit transactions recorded in the ralay log to achieve synchronization with the main database data. What problems do we need to solve to achieve this function?

1. As mentioned above, in order to ensure the consistency of the primary database data, the binary log written by the master node must be in the order of the database logical time sequence, and the transactions submitted by the primary database in the slave node execution relay log must be in the same order, otherwise the primary and backup data will be inconsistent.

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

Implementation 2:

MySQL 5.7 introduced Enhanced Muti-threaded slaves, when slave configuration slave_parallel_workers>0 and global.slave_parallel_type='LOGICAL_CLOCK', it can support multiple worker threads of slave_parallel_workers to execute transactions submitted by the main library in relay log concurrently under a schema. However, to achieve the above functionality, you need to mark the transactions committed in the 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 the transactions that can be executed concurrently.

We use the command mysqlbinlog -vvv mysqlbinlog.000003| grep -i last_committed See last_committed and sequence_number on MySQL 5.7 master

Transactions with the same last_committed (but different sequence_num) in the relay log of a slave machine can be executed concurrently. From the information intercepted above, we can see that there are 8 transactions with last_committed=26: sequence_number=27~24. Suppose that when slave_parallel_workers=7, the Coordinator thread (SQL thread) assigns this set of transactions to queue in worker for execution. It can be seen here that increasing the number of transactions in the binary log group commit group of the master library can increase the number of concurrent transactions processed by the slave machine. MySQL 5.7 introduces binlog_group_commit_sync_delay and binlog_group_commit_sync_no_delay_count parameters to increase the number of concurrent transactions submitted by the binary log group. MySQL waits for binlog_group_commit_sync_delay milliseconds until binlog_group_commit_sync_no_delay_count the number of transactions, and a group commit occurs.

Summary:

MySQL version 5.7 GA introduced Enhanced Multi-threaded Slaves function, completely solved the problem of delay of primary and standby data replication in previous versions. The parameters for enabling this function are as follows:

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.