Group Submission and parallel replication of MySQL5.7, and mysql5.7 commit in parallel

Group Submission and parallel replication of MySQL5.7, and mysql5.7 commit in parallel
MySQL and later versions introduce the mechanism of parallel replication, which is a very important feature of MySQL. MySQL and later versions support schema-based parallel replication. That is, if the binlog row event operates on objects in different schemas, if no DDL or foreign key dependency is determined, you can achieve parallel replication. The Community also introduced a version of parallel replication that uses tables as the dimension or records as the dimension. Whether it is schema, table, or record, it is an event created in the slave database slave to parse row format in real time for judgment, ensure that parallel distribution is implemented without conflict. Multi-threaded slave (MTS) is the parallel replication of MySQL5.7. It is expected to maximize the degree of parallelism in the master database. The implementation method is to add necessary information in binlog event, so that the slave node can perform parallel replication based on the information. The parallel replication of MySQL 5.7 is based on group commit. All statements that can complete prepared on the master database indicate that there is no data conflict, and you can perform parallel replication on the slave node. For group submission of MySQL5.7, we should look at the following parameters:

mysql> show global variables like '%group_commit%';+-----------------------------------------+-------+| Variable_name | Value |+-----------------------------------------+-------+| binlog_group_commit_sync_delay | 0 || binlog_group_commit_sync_no_delay_count | 0 |+-----------------------------------------+-------+2 rows in set (0.00 sec)

 

The binlog_group_commit_sync_delay parameter controls the waiting time for log submission before flushing the disk. The default value is 0, which means flushing the disk immediately after submission. When it is set to 0 or above, this allows log colleagues of multiple transactions to submit disk flushing, that is, group commit. Group commit is the basis of parallel replication. If we set this value to greater than 0, the group commit function is enabled. The maximum value can only be set to 1000000. Group commit is a fun method. We can see what the group commit is based on MySQL's binlog:

[root@mxqmongodb2 log]# mysqlbinlog mysql-bin.000005 |grep last_committed#170607 11:24:57 server id 353306 end_log_pos 876350 CRC32 0x92093332 GTID last_committed=654 sequence_number=655#170607 11:24:58 server id 353306 end_log_pos 880406 CRC32 0x344fdf71 GTID last_committed=655 sequence_number=656#170607 11:24:58 server id 353306 end_log_pos 888700 CRC32 0x4ba2b05b GTID last_committed=656 sequence_number=657#170607 11:24:58 server id 353306 end_log_pos 890675 CRC32 0xf8a8ad64 GTID last_committed=657 sequence_number=658#170607 11:24:58 server id 353306 end_log_pos 892770 CRC32 0x127f9cdd GTID last_committed=658 sequence_number=659#170607 11:24:58 server id 353306 end_log_pos 894757 CRC32 0x518abd93 GTID last_committed=659 sequence_number=660#170607 11:37:46 server id 353306 end_log_pos 895620 CRC32 0x99174f95 GTID last_committed=660 sequence_number=661#170607 11:37:51 server id 353306 end_log_pos 895897 CRC32 0xb4ffc341 GTID last_committed=661 sequence_number=662#170607 11:38:00 server id 353306 end_log_pos 896174 CRC32 0x6bcbc492 GTID last_committed=662 sequence_number=663#170607 11:39:40 server id 353306 end_log_pos 896365 CRC32 0x1fe16c7c GTID last_committed=663 sequence_number=664

 

The preceding figure shows that the group commit is not enabled. We can see that the binlog contains two parameters: last_committed and sequence_number. We can see that after the group commit is configured in the master database, add the following parameter to the slave Database: last_committed is always equal to the sequence_number of the previous transaction. This is easy to understand, because things are submitted in order, so it is not surprising to understand. Let's take a look at the group commit mode:

[root@mxqmongodb2 log]# mysqlbinlog mysql-bin.000008|grep last_commit#170609 10:11:07 server id 353306 end_log_pos 75629 CRC32 0xd54f2604 GTID last_committed=269 sequence_number=270#170609 10:13:03 server id 353306 end_log_pos 75912 CRC32 0x43675b14 GTID last_committed=270 sequence_number=271#170609 10:13:24 server id 353306 end_log_pos 76195 CRC32 0x4f843438 GTID last_committed=270 sequence_number=272

 

We can see that the last_committed of the last two things is the same. What does it mean that the two things are submitted as a group, the two things get the same last_committed in perpare truncation and do not affect each other. They are committed as a group. This is the so-called group commit.

# MTSslave-parallel-type = LOGICAL_CLOCKslave-parallel-workers = 8 # Too many threads will increase the overhead of synchronization between threads, we recommend that you use 4-8 slave threads master_info_repository = feature = ONslave-parallel-type with two of them: DATABASE and LOGICAL_CLOCK. The default value is "DATABASE". compatible with 5.6 parallel replication in schema dimension: mySQL 5.7 adopts a group-based Concurrent replication mechanism.

In general, the parallel replication of MySQL5.7 is based on the group commit of the master database and the configuration of the following parameters of the slave database: mysql> show variables like '% slave_para % ';

+------------------------+---------------+| Variable_name | Value |+------------------------+---------------+| slave_parallel_type | LOGICAL_CLOCK || slave_parallel_workers | 8 |+------------------------+---------------+2 rows in set (0.01 sec)

To use the parallel replication of MySQL5.7, you must first set binlog_group_commit_sync_delay to greater than 0 in the master database, and then set the number of threads and related methods in the slave database.