Implementation and advantages and disadvantages of MySQL parallel replication in different versions

MySQL parallel replication is already a commonplace. I started to deal with this issue online since 2010. I have been happy to share it for the first two or three years. Now I will mention this topic to avoid the suspicion of "stir-fried rice.
 
Recently, this topic was triggered again because some people felt that "5.7 of parallel replication finally completely solved the replication concurrency problem" and it was necessary to analyze it. Everyone said there was no silver bullet, but they expected it again ..
 
Now that we want to talk about 5.7 of parallel replication, we can simply explain the parallel replication of each version. This is the original intention of this sharing.
 
Background]
In one sentence, because there have been too many such articles over the past few years, that is, MySQL has always used single-thread apply for standby database replication.
 
[Basic solution]
Change to multi-thread replication.
 
The slave database has two threads related to replication: io_thread takes the binlog from the master database and writes it to relaylog. SQL _thread reads and executes relaylog.

Ps: // logs "/>

 
The idea of multithreading is to convert SQL _thread into a distribution thread, which is then executed by a group of worker_threads.
Almost all parallel replication is based on this idea. The difference lies in the distribution policy of SQL _thread.

 
These policies fall into two categories: using the traditional binlog format and modifying binlog.
 
The traditional binlog format is used. Because the information in binlog is the same, it can only be divided by granularity, that is, by database, by table, or by row.
 
Two other policies modify the binlog format and add other information in binlog to reflect the submission group.
 
Next we will introduce several parallel replication implementations.
 
[5.5]
 
MySQL 5.5 does not support parallel replication. However, the year in which Alibaba's business needs to be replicated in parallel is not supported by the official version, so you have to implement it on your own. In terms of compatibility, the binlog format is not modified. Therefore, the traditional binlog format is used for transformation.
 
Alibaba version supports two distribution policies: By table and by row.
 
As described above, because MySQLbinlog is also required by other systems, Alibaba's binlog format is "row", which reduces the difficulty of parallel replication.
 
Table-based distribution policy: binlog in row format. Each DML is preceded by a Table_map event. Therefore, it is easy to get the database name/table name. A simple idea is that updates to different tables do not need to be in strict order.
Therefore, according to the table name hash, the hash key is the database name + table name, and the updates of the same table are placed on the same worker. This ensures the update sequence of the same table, which is the same as that of the master database.
 
Application scenario: it is particularly effective for multi-table update scenarios. The disadvantage is that, if the hotspot table is updated, this policy is invalid. In addition, due to the maintenance of the hash table, the performance is reduced.
 
Row-based distribution policy: in the row-format binlog, it is not difficult to obtain the primary key ID. Some people say what if there is no primary key, the answer is "starting, who has no primary key yet :)". Well, the answer is that this policy is not supported without a primary key.
Similarly, we think that the updates of different rows can be unordered and concurrently executed. You only need to ensure that the data in the same row is updated, and the order on the slave database is the same as that on the master database.
Therefore, the hash key is longer, and must be the database name, table name, and primary key id. Updates of the same row are placed on the same worker.
 
It should be noted that the above description seems to be an operation on a single event, but it is actually not! Because the slave database may accept reads, the atomicity of the transaction must be ensured. That is to say, for a transaction involving multiple update operations, each time it is used for decision-making, it is not a hash key, but a group.
Application scenario: hotspot table update. Disadvantage: the cost of hash key conflicts is high. Especially for large transactions, the cpu consumption and memory consumption of hash keys are high. This requires the business DBA to determine the gains and losses.
 
[5.6]
Official 5.6 supports database-based distribution. With the above background, we all know that after this feature came out, there was no response in China.
 
However, this policy also has advantages:
 
1. For scenarios that can be distributed by table, you can apply this policy by migrating tables to different databases. This policy is operable.
 
2. The speed is faster, because the hash key is a database name.
 
3. The binlog format is not required. You know that database names can be easily obtained in either row or statement format.
So it is not completely useless. Or habits.
 
 
[MariaDB]
 
MariaDB's parallel replication policy seems to have several options. However, the default value CONSERVATIVE is available in production.
 
Because maraiaDB supports multi-master replication, a domain_id field is used to indicate the transaction source. If they come from different masters, they can naturally be parallel (this is also a general concept and must be determined by the business DBA ).
 
For binlogs from the same primary database, use commit_id to determine the group.
 
The idea is as follows: Set the transaction committed at the same time on the master database to the same commit_id. When applying on the slave database, the same commit_id can be executed in parallel, because this means that there is no line conflict between these transactions (otherwise it is impossible to commit at the same time ).
 
This idea is the same as changing from a single thread to multiple threads. I personally think it is epoch-making.
 
But it does not solve all the problems. The most frightening thing about this strategy is to hold down transactions.
 
Imagine this scenario. If a database is performing a large number of small update transactions (for example, updating a row for each transaction), it will be a pleasure in parallel in the slave database.
 
Then suddenly, in the same instance, another database, or another table in the same database that has nothing to do with the current update, a delte operation suddenly deletes 10 million rows.
 
When the delete transaction is committed, it is considered the same as the transaction committed at that time. Assume N.
 
The commit group commit_id of the minor transaction update is N + 1.
 
When applying to the slave database, you will find that all other small transactions in the N group are completed, and the thread enters the idle state, but you cannot continue to execute the transaction of the N + 1 commit_id, because there is still a large transaction in N that has not been completed, we think it is a drag-and-drop.
 
The preceding three policies based on the traditional binlog format do not solve this problem. As long as the policy can determine that there is no conflict, the large transaction itself has a thread to run, and other transactions continue to run in parallel.
 
[5.7]
 
MySQL official version 5.7 is also followed up in a timely manner, first introduced the above MariaDB policy. Of course, from copyright security, oracle does not allow direct port code.
 
In fact, the policy of segmentation by group is slightly rough. In fact, the transaction commit is not a point, but a stage. At least we can divide it into: Prepare for submission, submitting, and submitting.
 
These three phases are all completed in the transaction's main operation logic and entered the commit state.
 
The same commit_id is included in the "submitting" status at the same time. but in fact, at any time, the transaction in the "prepare to commit" can be parallel with the transaction in the "commit. But obviously they will be divided into two different commit_id.
 
This means that there is still room for this policy to improve concurrency.
 
Let's take a look at the differences between the two strategies.
 
Assume that the master database is like the transaction sequence shown below. Each transaction commit process is considered as two phases, with different numbers given by prepare... commit. The number corresponding to commit is the natural number increasing, sequence_no. The prepare corresponds to X + 1, which indicates the sequence_no already submitted.
Trx1 1 ..... 2
Trx2 1 ............. 3
Trx3 1 ......................... 4
Trx4 2 ............................... 5
Trx5 3 ...................................... 6
Trx6 3 ...................................................... 7
Trx7 6 .......................... 8
 
Analysis:
In the MariaDB policy, the concurrent execution sequence is as follows:
Trx1, trx2, trx3 ---- group 1
Trx4 ----- group 2
Trx 5, trx6 ---- group 3
Trx 7 ---- group 4
After each group is executed, the next group can start.
The complete execution time is the sum of the maximum transaction time of each group, namely, trx3 + trx4 + trx6 + trx7.
Therefore, if a group contains a large transaction, the execution time of the entire sequence will be prolonged.
 
Let's take a look at the 5.7 Improvement Strategy:
Although group1 is started first, after trx1 is completed, trx4 can be started;
Similarly, trx7 can be executed after the trx4 execution is complete, and is concurrent with trx5 and trx6.
Therefore, in the above example, the slave database apply process completely achieves the concurrency of the master database.
However, for large transactions, such as trx2 commit, the problem still persists.

Supplement:

Principles of MySQL 5.7 parallel replication
MySQL 5.7 group-based concurrent replication
MySQL 5.7 can be called true parallel replication, the most important reason is that the playback of the server load balancer is consistent with that of the host, that is, how the server load balancer runs concurrently on the master server and how to perform parallel playback. There are no database-based parallel replication restrictions, and there are no special requirements on the binary log format (library-based parallel replication is not required ).
From the official perspective of MySQL, its original plan for parallel replication is to support parallel table-level replication and ROW-level parallel replication. ROW-level parallel replication is completed by parsing binary logs in ROW format, WL #4648. But in the end, it is called MTS: Prepared transactions slave parallel applier in the development plan, which is shown as WL #6314. This idea of parallel replication was first proposed by Kristain of MariaDB and has appeared in MariaDB 10. I believe that one of the most important features of many partners who choose MariaDB is parallel replication.
The idea of MySQL 5.7 parallel replication is simple and easy to understand. Simply put, a group of committed transactions can be played back in parallel, because these transactions have entered the prepare stage of the transaction, it indicates that there is no conflict between transactions (otherwise it is impossible to commit ).
To be compatible with MySQL 5.6 database-based parallel replication, MySQL 5.7 introduces the new variable slave-parallel-type, which can be configured with the following values:
DATABASE: default value. DATABASE-based parallel replication
LOGICAL_CLOCK: Group-based concurrent replication
GTID supporting parallel replication
How to know whether a transaction is in a group is another problem, because the original MySQL does not provide such information. In MySQL 5.7, it is designed to store group submitted information in GTID. If the GTID function is not enabled, will the gtid_mode parameter be set to OFF? Therefore, MySQL 5.7 introduces the binary log event type called Anonymous_Gtid, such:

Mysql> show binlog events in 'MySQL-bin.000006 ';
+ ------------------ + ----- + ---------------- + ----------- + ------------- + ----------------------------------------------- +
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+ ------------------ + ----- + ---------------- + ----------- + ------------- + ----------------------------------------------- +
| Mysql-bin.000006 | 4 | Format_desc | 88 | 123 | Server ver: 5.7.7-rc-debug-log, Binlog ver: 4 |
| Mysql-bin.000006 | 123 | previus_gtids | 88 | 194 | f11232f7-ff07-11e4-8fbb-00ff55e152c6: 1-2 |
| Mysql-bin.000006 | 194 | Anonymous_Gtid | 88 | 259 | SET @ SESSION. GTID_NEXT = 'Anonymous' |
| Mysql-bin.000006 | 259 | Query | 88| 330 | BEGIN |
| Mysql-bin.000006 | 330 | Table_map | 88 | 373 | table_id: 108 (aaa. t) |
| Mysql-bin.000006 | 373 | Write_rows | 88 | 413 | table_id: 108 flags: STMT_END_F |
.....
This means that even if GTID is not enabled in MySQL 5.7, an Anonymous_Gtid exists before each transaction starts, and the group commit information exists in the GTID.
LOGICAL_CLOCK
However, through show binlog events, we did not find any information about group submission. However, with mysqlbinlog, you can find the internal information submitted by the group:

 

Root @ localhost :~ # Mysqlbinlog mysql-bin.0000006 | grep last_committed
#150520 14:23:11 server id 88 end_log_pos 259 CRC32 0x4ead9ad6 GTID last_committed = 0 sequence_number = 1
#150520 14:23:11 server id 88 end_log_pos 1483 CRC32 0xdf94bc85 GTID last_committed = 0 sequence_number = 2
#150520 14:23:11 server id 88 end_log_pos 2708 CRC32 0x0914697b GTID last_committed = 0 sequence_number = 3
#150520 14:23:11 server id 88 end_log_pos 3934 CRC32 0xd9cb4a43 GTID last_committed = 0 sequence_number = 4
#150520 14:23:11 server id 88 end_log_pos 5159 CRC32 0x06a6f531 GTID last_committed = 0 sequence_number = 5
#150520 14:23:11 server id 88 end_log_pos 6386 CRC32 0xd6cae930 GTID last_committed = 0 sequence_number = 6
#150520 14:23:11 server id 88 end_log_pos 7610 CRC32 0xa1ea531c GTID last_committed = 6 sequence_number = 7
...
It can be found that last_committed and sequence_number are more than the original binary log Content. last_committed indicates the number of the last transaction submitted when the transaction is committed. If the transaction has the same last_committed, indicates that all these transactions are in a group and can be played back in parallel. For example, if the preceding last_committed value is 0, there are 6 transactions, indicating that the group commits 6 transactions, and these 6 transactions can be played back in parallel on the slave machine.
The preceding last_committed and sequence_number represent the so-called LOGICAL_CLOCK. Let's take a look at the definition of LOGICAL_CLOCK in the source code:

 

Class Logical_clock
 {
Private:
Int64 state;
/*
Offset is subtracted from the actual "absolute time" value
Logging a replication event. That is the event holds logical
Timestamps in the "relative" format. They are meaningful only in
The context of the current binlog.
The member is updated (incremented) per binary log rotation.
*/
Int64 offset;
......
State is an auto-increment value. offset is updated in each binary log rotate, and the state value in rotate is recorded. In fact, the state and offset records the global count value, while the binary log only records the relative value of the current file. LOGICAL_CLOCK is used in the following scenarios:

 

Class MYSQL_BIN_LOG: public TC_LOG
 {
...
Public:
/* Committed transactions timestamp */
Logical_clock max_committed_transaction;
/* "Prepared" transactions timestamp */
Logical_clock transaction_counter;
...
We can see that two Logical_clock variables are defined in the MYSQL_BIN_LOG class:
Max_c ommitted_transaction: record the logical_clock when the last group was submitted, representing the last_committed in the above mysqlbinlog
Transaction_counter: records the logcial_clock of each transaction in the current group commit, representing the sequence_number in mysqlbinlog.
Parallel replication test
The following figure shows the QPS of the slave server after MTS is enabled. The test tool is sysbench's single-table full update test. The test results show that the performance of 16 threads is the best, and the QPS of the slave machine can reach more than 25000, further increasing the number of parallel execution threads to 32 does not bring a higher level. The QPS of the original single-thread playback is only about 4000. It can be seen that the performance of MySQL 5.7 MTS is improved. Because the test is a single table, the MTS mechanism of MySQL 5.6 is completely powerless.
Mysql

Mysql 5.7 parallel replication

Configuration and optimization of parallel replication
Master_info_repository
After MTS is enabled, you must set the master_info_repostitory parameter to TABLE, so that the performance can be 50% ~ 80% improvement. This is because after the parallel replication is enabled, the updates to the file master.info will be greatly improved, and the competition for resources will also increase. In earlier InnoSQL versions, parameters were added to control the frequency of refreshing the master.info file, or even not to refresh the file. Because it is unnecessary to refresh this file, that is, recovery itself is unreliable according to the master-info.log file. In MySQL 5.7, we recommend that you set master_info_repository to TABLE to reduce the overhead.
Slave_parallel_workers
If slave_parallel_workers is set to 0, MySQL 5.7 degrades to the original single-thread replication, but slave_parallel_workers is set to 1, the SQL thread function is converted to the coordinator thread, however, there is only one worker thread for playback, which is also a single-thread replication. However, there are some differences between the two types of performance. Because the coordinator thread is used for forwarding, the slave_parallel_workers = 1 performance is worse than 0, in the test of Inside Jun, the performance drops by about 20%, as shown in the following figure:
Mysql
Mysql 5.7 parallel replication
Another problem is introduced here. If the load on the host is not large, the group commit efficiency is not high. It is very likely that only one transaction is committed in each group, in the replay of the slave machine, although parallel replication is enabled, the performance will be worse than the previous single thread, that is, the latency will increase. Smart Friends, have you ever thought about optimizing this?
Enhanced Multi-Threaded Slave configuration
To enable the enhanced multi-threaded slave function, you only need to set it as follows:

 

# Slave
Slave-parallel-type = LOGICAL_CLOCK
Slave-parallel-workers = 16
Master_info_repository = TABLE
Relay_log_info_repository = TABLE
Relay_log_recovery = ON
Parallel replication monitoring
Replication Monitoring can still be performed through show slave status \ G, but MySQL 5.7 has the following metadata tables in the performance_schema architecture, allowing you to perform more detailed monitoring:

Mysql> show tables like 'replication % ';
+ --------------------------------------------- +
| Tables_in_performance_schema (replication %) |
+ --------------------------------------------- +
| Replication_applier_configuration |
| Replication_applier_status |
| Replication_applier_status_by_coordinator |
| Replication_applier_status_by_worker |
| Replication_connection_configuration |
| Replication_connection_status |
| Replication_group_member_stats |
| Replication_group_members |
+ --------------------------------------------- +
8 rows in set (0.00 sec)
 
[Summary]

The three policies are divided by granularity, from coarse to fine, by database, by table, by row.
In these three comparisons, the degree of parallelism is getting bigger and bigger, and the additional loss is also. Unrelated large transactions do not affect the concurrency.
 
According to the two commit_id policies, the application scope is wider and the additional consumption is low.
5.7 improves the concurrency of policies. However, large transactions may be slowed down.
 
In addition, it is very important that 5.7 of the policy aims to "simulate Master Database Concurrency", so it does not accelerate single-threaded updates to the master database. However, if the first three policies based on the conflict meet the concurrency conditions, the execution speed of the slave database is faster than that of the master database. This requirement may be triggered in the scenario of slave database or delayed replication.
 
In fact, the choice of strategy depends on the application scenario, which is one of the architect's work.
 
PS: specific 5.7 implementation principle can refer to our team @ infeng blog http://mysqllover.com /? P = 1370 (the case in the last example is also excerpted here)