- The original content of GreatSQL community cannot be used without authorization. Please contact Xiaobian and indicate the source for reprint.
1. Problem description
Occasionally encountered gtid during MGR testing_ The executed transaction ID is discontinuous, but it does not affect the normal operation of the database. The phenomenon is as follows
GreatDB Cluster[sysbench]> select @@gtid_executed; +-----------------------------------------------------------------+ | @@gtid_executed | +-----------------------------------------------------------------+ | 5cd1a64d-7358-11ec-b349-080027fa2d35:1-1286:2052-2290:3052-3277 | +-----------------------------------------------------------------+ 1 row in set (0.00 sec)
2. Confirm the reason
Check the official documentation and find the following parameters:
group_replication_gtid_assignment_block_size
The following is the translation and understanding of the official documents
group_replication_gtid_assignment_block_size the number of consecutive gtids reserved for each member. Each member consumes it and obtains more gtids when it needs to (similar to the global sequence in distributed transactions, the value set by this system variable indicates the range of continuous numbers each member obtains from the global sequence as the GTID number of its own write transaction).
This system variable is a group wide configuration setting. It must set the same value on all group members. Modification is not allowed during group replication. To make the modified value effective, you need to completely reboot the group (use the system variable group_replication_bootstrap_group= on to reboot the group).
Global variable, dynamic variable and integer type. The default value is 1000000. The value range is 1 ~ 4294967295 for 32-bit platform and 1 ~ 9223372036854775807 for 64 bit platform. It is introduced in MySQL version 5.7.17.
Official document address: https://dev.mysql.com/doc/ref...
It is learned from the document that MGR will assign a continuous GTID value to each instance node, so it is suspected that MGR has a master-slave switch, resulting in discontinuous transaction ID s of gtids
2.1 recurrence problem
Modify the group for the convenience of testing_ replication_ gtid_ assignment_ block_ Size is 1000 and restart MGR
GreatDB Cluster[(none)]> set persist group_replication_gtid_assignment_block_size=1000; Query OK, 0 rows affected (0.00 sec) GreatDB Cluster[(none)]> set global group_replication_bootstrap_group=on; Query OK, 0 rows affected (0.00 sec) GreatDB Cluster[(none)]> start group_replication; Query OK, 0 rows affected (2.45 sec) GreatDB Cluster[(none)]> set global group_replication_bootstrap_group=off; Query OK, 0 rows affected (0.00 sec)
Switch 82 to the master node, and then simulate the service through sysbench
GreatDB Cluster[sysbench]> select @@gtid_executed;
+---------------------------------------------+
| @@gtid_executed |
+---------------------------------------------+
| 5cd1a64d-7358-11ec-b349-080027fa2d35:1-5445 |
+---------------------------------------------+
1 row in set (0.00 sec)
GreatDB Cluster[sysbench]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | cf43d5d7-7354-11ec-af9a-080027fa2d35 | 172.16.50.81 | 4444 | ONLINE | PRIMARY | 8.0.25 |
| group_replication_applier | cf520b3b-7354-11ec-b785-08002792d155 | 172.16.50.82 | 4444 | ONLINE | SECONDARY | 8.0.25 |
| group_replication_applier | cf85763c-7354-11ec-898d-0800276e4bea | 172.16.50.83 | 4444 | ONLINE | SECONDARY | 8.0.25 |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
GreatDB Cluster[sysbench]> SELECT group_replication_set_as_primary('cf520b3b-7354-11ec-b785-08002792d155');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('cf520b3b-7354-11ec-b785-08002792d155') |
+--------------------------------------------------------------------------+
| Primary server switched to: cf520b3b-7354-11ec-b785-08002792d155 |
+--------------------------------------------------------------------------+
1 row in set (1.38 sec)
GreatDB Cluster[sysbench]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
| group_replication_applier | cf43d5d7-7354-11ec-af9a-080027fa2d35 | 172.16.50.81 | 4444 | ONLINE | SECONDARY | 8.0.25 |
| group_replication_applier | cf520b3b-7354-11ec-b785-08002792d155 | 172.16.50.82 | 4444 | ONLINE | PRIMARY | 8.0.25 |
| group_replication_applier | cf85763c-7354-11ec-898d-0800276e4bea | 172.16.50.83 | 4444 | ONLINE | SECONDARY | 8.0.25 |
+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)Simulate business write business in the new master node
# sysbench ./oltp_read_write.lua --mysql-db=sysbench --mysql-host=172.16.50.82 --mysql-port=4444 --mysql-user=greatdb --mysql-password=greatdb --tables=3 --table_size=1000000 --report-interval=2 --threads=3 --db-driver=mysql --skip-trx=off --db-ps-mode=disable --create-secondary=off --time=10 --mysql-ignore-errors=9001,9002,9000,1062,8530,8532 run
Check the current gtid_executed
GreatDB Cluster[sysbench]> select @@gtid_executed; +-------------------------------------------------------+ | @@gtid_executed | +-------------------------------------------------------+ | 5cd1a64d-7358-11ec-b349-080027fa2d35:1-5445:6053-6268 | +-------------------------------------------------------+ 1 row in set (0.00 sec)
2.2 impact of cavity on actual use
The simulation service continues to write services at node 82
sysbench ./oltp_read_write.lua --mysql-db=sysbench --mysql-host=172.16.50.82 --mysql-port=4444 --mysql-user=greatdb --mysql-password=greatdb --tables=3 --table_size=1000000 --report-interval=2 --threads=3 --db-driver=mysql --skip-trx=off --db-ps-mode=disable --create-secondary=off --time=100 --mysql-ignore-errors=9001,9002,9000,1062,8530,8532 run
Check the current gtid_executed, gtid after a period of time_ Executed resume continuous
GreatDB Cluster[sysbench]> select @@gtid_executed; +-------------------------------------------------------+ | @@gtid_executed | +-------------------------------------------------------+ | 5cd1a64d-7358-11ec-b349-080027fa2d35:1-5451:6053-6659 | +-------------------------------------------------------+ 1 row in set (0.00 sec) GreatDB Cluster[sysbench]> select @@gtid_executed; +---------------------------------------------+ | @@gtid_executed | +---------------------------------------------+ | 5cd1a64d-7358-11ec-b349-080027fa2d35:1-6665 | +---------------------------------------------+ 1 row in set (0.01 sec)
Analyze binlog and gtid generation time
SET @@SESSION.GTID_NEXT= '5cd1a64d-7358-11ec-b349-080027fa2d35:6268'/*!*/; # at 173167217 #220112 15:12:29 server id 1 end_log_pos 173167287 Query thread_id=19 exec_time=0 error_code=0 SET @@SESSION.GTID_NEXT= '5cd1a64d-7358-11ec-b349-080027fa2d35:6269'/*!*/; # at 173169472 #220112 15:14:29 server id 1 end_log_pos 173169542 Query thread_id=19 exec_time=1 error_code=0 SET @@SESSION.GTID_NEXT= '5cd1a64d-7358-11ec-b349-080027fa2d35:6659'/*!*/; # at 174048922 #220112 15:14:50 server id 1 end_log_pos 174048992 Query thread_id=19 exec_time=0 error_code=0 SET @@SESSION.GTID_NEXT= '5cd1a64d-7358-11ec-b349-080027fa2d35:6660'/*!*/; # at 175419962 #220112 15:15:04 server id 1 end_log_pos 175420032 Query thread_id=19 exec_time=0 error_code=0 SET @@SESSION.GTID_NEXT= '5cd1a64d-7358-11ec-b349-080027fa2d35:5445'/*!*/; # at 172680137 #220112 15:06:15 server id 1 end_log_pos 172680207 Query thread_id=19 SET @@SESSION.GTID_NEXT= '5cd1a64d-7358-11ec-b349-080027fa2d35:5446'/*!*/; # at 174051177 #220112 15:14:50 server id 1 end_log_pos 174051247 Query thread_id=19
The analysis shows that the transaction id first increases from 6268 to 6659, and then starts to fill the hole. After filling, it continues to increase from 6659
3. Summary
group_replication_gtid_assignment_block_size the number of consecutive gtids reserved for each member. Each member consumes it and obtains more gtids when it needs to (similar to the global sequence in distributed transactions, the value set by this system variable indicates the range of continuous numbers each member obtains from the global sequence as the GTID number of its own write transaction).
For example, there are two nodes in the cluster, group_ replication_ gtid_ assignment_ block_ If the size is 1000, the gtid assigned to node A_ Set is group_name:1-1000, gtid assigned by node B_ Set is group_name:1001-2000.
Then group_name:1-1000 and group_name:1001-2000 as gtid respectively_ Save set in member_ On gtids. After the transaction T1 authentication of node A passes, the gtid is assigned as group_name:1, then assign group to node A transaction T2_ Name: 2, and then the transaction of node B enters the authentication module. After the authentication is passed, assign A group to it_ Name: 1001, every time gtid is assigned, gtids_assigned_in_blocks_counter increases by one. When master-slave switching occurs, node B will record gtid from 1001, so it will cause gtid of MGR_ Executed is sometimes A discontinuous multi segment, such as aaaaaa AAAA AAAA AAAA AAAA AAAA AAAA: 1-2:1001-1005
If the number of assignments is gtids_assigned_in_blocks_counter has reached gtid_assignment_block_size, then compute is required_ group_available_gtid_intervals() recalculate. Based on member_uuid finds the available gtid range of the member. If the member has not been assigned a gtid, reserve is called_ gtid_ Block(). It should be noted that reserve_gtid_block() is the most allocated, not necessarily allocated block_ Is the gtid sequence of size equal to block_size depends on Group_ available_ gtid_ Is the size of the first available contiguous gtid sequence of intervals equal to or greater than block_size.
Enjoy GreatSQL :)
Article recommendation:
GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6...
Wan Da #12, how can the MGR cluster automatically select the master without manual intervention
https://mp.weixin.qq.com/s/07...
"2021 data technology Carnival · ON LINE": evolution and practice of MySQL high availability architecture
https://mp.weixin.qq.com/s/u7...
Packet capture analysis of where an sql statement is slow
https://mp.weixin.qq.com/s/AY...
Wan Da #15, what are the conditions that may cause the MGR service to fail to start
https://mp.weixin.qq.com/s/in...
Technology sharing | why MGR consistency mode does not recommend AFTER
https://mp.weixin.qq.com/s/rN...
About GreatSQL
GreatSQL is a MySQL branch maintained by Wanli database. It focuses on improving the reliability and performance of MGR and supports the parallel query feature of InnoDB. It is a branch version of MySQL suitable for financial applications.
Gitee:
https://gitee.com/GreatSQL/Gr...
GitHub:
https://github.com/GreatSQL/G...
Bilibili:
https://space.bilibili.com/13...
Wechat & QQ group:
You can search and add GreatSQL community assistant wechat friends, send verification information "add group" to join GreatSQL/MGR communication wechat group
QQ group: 533341697
Wechat assistant: wanlidbc
This article is composed of blog one article multi posting platform OpenWrite release!