MySQL5.7 group submission and parallel replication example tutorial

Since MySQL version 5.5, the parallel replication mechanism has been introduced, which is a very important feature of MySQL.

MySQL5.6 begins to support parallel replication with schema as the dimension. That is, if the binlog row event operates on objects of different schemas, parallel replication can be achieved if it is determined that there are no DDL and foreign key dependencies. .

The community has also introduced a version of parallel replication with tables as dimensions or records as dimensions. Whether it is schema, table or record, it is based on the standby database slave's real-time analysis of events in row format for judgment, ensuring that there is no In case of conflicts, distribution is performed to achieve parallelism.

MySQL5.7's parallel replication, multi-threaded slave, that is, MTS, is expected to maximize the parallelism of the main database. The implementation method is to add necessary information to the binlog event so that the slave node can achieve parallelism based on this information. copy.

The parallel replication of MySQL 5.7 is based on group commit. All prepared statements that can be completed on the main database indicate that there is no data conflict, and can be replicated in parallel on the slave node.

Regarding the group submission of MySQL5.7, we need to 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)

binlog_group_commit_sync_delay parameter Controls the time to wait for log submission before flushing the disk. The default is 0, which means that the disk is flushed immediately after submission. When set to above 0, log colleagues of multiple things are allowed to submit for flushing at the same time, which is what we Said group submitted. Group submission is the basis of parallel replication. If we set this value greater than 0, it means that the group submission function is turned on. The maximum value can only be set to 1000000 microseconds.

binlog_group_commit_sync_no_delay_count, this parameter indicates that within the waiting time of binlog_group_commit_sync_delay, if the number of things reaches the parameter set by binlog_group_commit_sync_no_delay_count, a group submission will be triggered. If this value is set to 0, there will be no Impact. If the time is reached but the number of transactions has not been reached, a group submission operation will also be performed.

Group submission is a more fun way. We can see what the group submission 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 above is a log without group submission enabled. We can see that there are two parameters last_committed and sequence_number in the binlog. We can see that the

of the next thing is in the main After the library is configured for group submission, we need to add the following parameters from the library: last_committed is always equal to the sequence_number of the previous thing. This is also easy to understand, because things are submitted sequentially, so it is not surprising to understand.

Let’s take a look at the things in the group submission 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 the last_committed of the last two things are the same, what does this mean? It means that 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 will eventually be submitted as a group. This is called group submission.

#MTS
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8 #太多的线程会增加线程间同步的开销，建议4-8个slave线程
master_info_repository=TABLErelay_log_info_repository=TABLErelay_log_recovery=ONslave-parallel-type有两个之，DATABASE和LOGICAL_CLOCK，DATABASE： 默认值，兼容5.6以schema维度的并行复制， LOGICAL_CLOCK： MySQL 5.7基于组提交的并行复制机制。

In summary, the parallel replication of MySQL5.7 is based on the group commit of the main library and the configuration of the following parameters of the slave library: 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)

If you want to use the parallel replication of MySQL5.7, you must first set binlog_group_commit_sync_delay greater than 0 in the main library, and then set the number of threads and related parameters in the slave library Way. What we set above is 8, and you can see from the slave library that

mysql> show processlist;+----+-------------+--------------------+------+---------+--------+--------------------------------------------------------+------------------+| Id | User        | Host               | db   | Command | Time   | State                                                  | Info             |+----+-------------+--------------------+------+---------+--------+--------------------------------------------------------+------------------+|  1 | system user |                    | NULL | Connect | 373198 | Waiting for master to send event                       | NULL             ||  2 | system user |                    | NULL | Connect |   1197 | Slave has read all relay log; waiting for more updates | NULL             ||  4 | system user |                    | NULL | Connect |   4292 | Waiting for an event from Coordinator                  | NULL             ||  5 | system user |                    | NULL | Connect | 373198 | Waiting for an event from Coordinator                  | NULL             ||  6 | system user |                    | NULL | Connect | 373198 | Waiting for an event from Coordinator                  | NULL             ||  7 | system user |                    | NULL | Connect | 373198 | Waiting for an event from Coordinator                  | NULL             ||  8 | system user |                    | NULL | Connect | 373198 | Waiting for an event from Coordinator                  | NULL             ||  9 | system user |                    | NULL | Connect | 373198 | Waiting for an event from Coordinator                  | NULL             || 10 | system user |                    | NULL | Connect | 373198 | Waiting for an event from Coordinator                  | NULL             || 11 | system user |                    | NULL | Connect | 373198 | Waiting for an event from Coordinator                  | NULL             || 16 | root        | 10.103.16.34:37263 | NULL | Query   |      0 | starting                                               | show processlist |+----+-------------+--------------------+------+---------+--------+--------------------------------------------------------+------------------+

The slave library will have eight threads waiting for things to be processed, instead of just one.

The above is the detailed content of MySQL5.7 group submission and parallel replication example tutorial. For more information, please follow other related articles on the PHP Chinese website!