主从复制中定位回放慢涉及的表

主从复制中定位回放慢涉及的表

一、前提

天下千奇百怪，每个人都有自己独立的思想，有些事变即使你附耳告知，也可能如风般吹过，进而消失，为了性能为了不延迟，表要加索引嘛，然而在某业务场景，业务表数千张，无索引的表几百张，这些表都是上百万的数据。
二、征象

在 GreatSQL 主从架构中，某天在体系资源富足的环境下，主从忽然延迟，而且持续增长，我们通过SHOW PROCESSLIST 和 SHOW SLAVE STATUS 观察是由于回放 DELETE 事件造成的，但是 GTID`` 在不断地增长，不外增长的非常缓慢，但是平时的时间是没有云云缓慢的，我们该如何快速的定位这些回放缓慢的 GTID 的涉及表呢，接下来就在测试环境演示下如何定位。
三、模拟测试

1、主节点创建库

1. greatsql> CREATE DATABASE fcmark;
2. Query OK, 1 row affected (0.03 sec)

复制代码

2、准备数据

1. $ sysbench --db-driver=mysql --mysql-host=192.168.139.230 --mysql-port=3307 --mysql-user=greatsql --mysql-password=greatsql@2024 --mysql-db=fcmark --table_size=10000000 --tables=5 oltp_write_only  prepare
2. sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
4. Creating table 'sbtest1'...
5. Inserting 10000000 records into 'sbtest1'
6. Creating a secondary index on 'sbtest1'...
7. Creating table 'sbtest2'...
8. Inserting 10000000 records into 'sbtest2'
9. Creating a secondary index on 'sbtest2'...
10. Creating table 'sbtest3'...
11. Inserting 10000000 records into 'sbtest3'
12. Creating a secondary index on 'sbtest3'...
13. Creating table 'sbtest4'...
14. Inserting 10000000 records into 'sbtest4'
15. Creating a secondary index on 'sbtest4'...
16. Creating table 'sbtest5'...
17. Inserting 10000000 records into 'sbtest5'
18. Creating a secondary index on 'sbtest5'...

复制代码

3、主节点修改表布局，将表修改为无索引表

1. greatsql> ALTER TABLE sbtest1 MODIFY id int not null;
2. Query OK, 10000000 rows affected (3 min 2.62 sec)
3. Records: 10000000  Duplicates: 0  Warnings: 0
5. greatsql> ALTER TABLE sbtest1 DROP key `k_1`;
6. Query OK, 0 rows affected (0.15 sec)
7. Records: 0  Duplicates: 0  Warnings: 0
9. greatsql> ALTER TABLE sbtest1 DROP PRIMARY KEY;
10. Query OK, 10000000 rows affected (1 min 50.19 sec)
11. Records: 10000000  Duplicates: 0  Warnings: 0
13. greatsql> ALTER TABLE sbtest2 MODIFY id int not null;
14. Query OK, 10000000 rows affected (3 min 2.62 sec)
15. Records: 10000000  Duplicates: 0  Warnings: 0
17. greatsql> ALTER TABLE sbtest2 DROP key `k_2`;
18. Query OK, 0 rows affected (0.15 sec)
19. Records: 0  Duplicates: 0  Warnings: 0
21. greatsql> ALTER TABLE sbtest2 DROP  PRIMARY KEY;
22. Query OK, 10000000 rows affected (1 min 50.19 sec)
23. Records: 10000000  Duplicates: 0  Warnings: 0
25. greatsql> ALTER TABLE sbtest3 MODIFY id int not null;
26. Query OK, 10000000 rows affected (3 min 2.72 sec)
27. Records: 10000000  Duplicates: 0  Warnings: 0
29. greatsql> ALTER TABLE sbtest3 DROP key `k_3`;
30. Query OK, 0 rows affected (0.15 sec)
31. Records: 0  Duplicates: 0  Warnings: 0
33. greatsql> ALTER TABLE sbtest3 DROP PRIMARY KEY;
34. Query OK, 10000000 rows affected (1 min 52.19 sec)
35. Records: 10000000  Duplicates: 0  Warnings: 0
37. greatsql> ALTER TABLE sbtest4 MODIFY id int not null;
38. Query OK, 10000000 rows affected (3 min 3.64 sec)
39. Records: 10000000  Duplicates: 0  Warnings: 0
41. greatsql> ALTER TABLE sbtest4 DROP key `k_4`;
42. Query OK, 0 rows affected (0.15 sec)
43. Records: 0  Duplicates: 0  Warnings: 0
45. greatsql> ALTER TABLE sbtest4 DROP PRIMARY KEY;
46. Query OK, 10000000 rows affected (1 min 53.19 sec)
47. Records: 10000000  Duplicates: 0  Warnings: 0
49. greatsql> ALTER TABLE sbtest5 MODIFY id int not null;
50. Query OK, 10000000 rows affected (3 min 5.62 sec)
51. Records: 10000000  Duplicates: 0  Warnings: 0
53. greatsql> ALTER TABLE sbtest5 DROP key `k_5`;
54. Query OK, 0 rows affected (0.15 sec)
55. Records: 0  Duplicates: 0  Warnings: 0
57. greatsql> ALTER TABLE sbtest5 DROP PRIMARY KEY;
58. Query OK, 10000000 rows affected (1 min 51 sec)
59. Records: 10000000  Duplicates: 0  Warnings: 0

复制代码

4、主库操作数据模拟延迟

1. greatsql> DELETE FROM sbtest1 WHERE k<200;
2. Query OK, 214 rows affected (41.14 sec)
4. greatsql> DELETE FROM sbtest2 WHERE k<200;
5. Query OK, 193 rows affected (41.58 sec)
7. greatsql> DELETE FROM sbtest3 WHERE k<200;
8. Query OK, 177 rows affected (40.00 sec)
10. greatsql> DELETE FROM sbtest4 WHERE k<200;
11. Query OK, 184 rows affected (39.38 sec)
13. greatsql> DELETE FROM sbtest5 WHERE k<200;
14. Query OK, 212 rows affected (37.56 sec)
16. greatsql> DELETE FROM sbtest1 WHERE k<400;
17. Query OK, 182 rows affected (39.09 sec)
19. greatsql> DELETE FROM sbtest2 WHERE k<400;
20. Query OK, 193 rows affected (38.21 sec)
22. greatsql> DELETE FROM sbtest3 WHERE k<400;
23. Query OK, 215 rows affected (34.45 sec)
25. greatsql> DELETE FROM sbtest4 WHERE k<400;
26. Query OK, 219 rows affected (37.45 sec)
28. greatsql> DELETE FROM sbtest5 WHERE k<400;
29. Query OK, 224 rows affected (34.63 sec)
31. greatsql> DELETE FROM sbtest1 WHERE k<600;
32. Query OK, 185 rows affected (34.11 sec)
34. greatsql> DELETE FROM sbtest2 WHERE k<600;
35. Query OK, 245 rows affected (35.99 sec)
37. greatsql> DELETE FROM sbtest3 WHERE k<600;
38. Query OK, 173 rows affected (36.08 sec)
40. greatsql> DELETE FROM sbtest4 WHERE k<600;
41. Query OK, 230 rows affected (36.18 sec)
43. greatsql> DELETE FROM sbtest5 WHERE k<600;
44. Query OK, 186 rows affected (38.00 sec)

复制代码

6、分析数据

主节点操作，根据从节点观察的信息，在主节点上观察 binlog 信息，回放内容是关于 fcmark.sbtest4`` 表的多个DELETE事件，观察该表布局是无索引，并且数据量接近 1万万

1. greatsql> SHOW PROCESSLIST;
2. +--------+-----------------+----------------------+--------+---------+---------+---------------------------------------------+------------------+------------+-----------+---------------+
3. | Id     | User            | Host                 | db     | Command | Time    | State                                       | Info             | Time_ms    | Rows_sent | Rows_examined |
4. +--------+-----------------+----------------------+--------+---------+---------+---------------------------------------------+------------------+------------+-----------+---------------+
5. |      5 | event_scheduler | localhost            | NULL   | Daemon  | 2356467 | Waiting on empty queue                      | NULL             | 2356467469 |         0 |             0 |
6. |     17 | system user     | connecting host      | NULL   | Connect | 2356460 | Waiting for source to send event            | NULL             | 2356459985 |         0 |             0 |
7. |     18 | system user     |                      | NULL   | Query   |      99 | Waiting for dependent transaction to commit | NULL             |      99081 |         0 |             0 |
8. |     19 | system user     |                      | fcmark | Query   |     697 | Applying batch of row changes (delete)      | NULL             |      32977 |         0 |             0 |
9. |     20 | system user     |                      | NULL   | Query   |    3244 | Waiting for an event from Coordinator       | NULL             |    3242579 |         0 |             0 |
10. |     21 | system user     |                      | NULL   | Connect | 2356460 | Waiting for an event from Coordinator       | NULL             | 2356459981 |         0 |             0 |
11. |     22 | system user     |                      | NULL   | Connect | 2356460 | Waiting for an event from Coordinator       | NULL             | 2356459980 |         0 |             0 |
12. | 540493 | greatsql         | 172.17.136.93:34298  | NULL   | Sleep   |      83 |                                             | NULL             |      83365 |         0 |             0 |
13. | 540593 | greatsql         | 172.17.139.230:51514 | NULL   | Query   |       0 | init                                        | show processlist |          0 |         0 |             0 |
14. | 540660 | greatsql         | 172.17.136.93:46212  | NULL   | Sleep   |     383 |                                             | NULL             |     383304 |         0 |             0 |
15. | 540730 | greatsql         | 172.17.136.93:51128  | NULL   | Sleep   |     143 |                                             | NULL             |     143319 |         0 |             0 |
16. | 540758 | greatsql         | 172.17.136.93:53112  | NULL   | Sleep   |      23 |                                             | NULL             |      23205 |         0 |             0 |
17. | 540763 | greatsql         | 172.17.139.230:52204 | NULL   | Sleep   |       1 |                                             | NULL             |       1246 |         0 |             0 |
18. +--------+-----------------+----------------------+--------+---------+---------+---------------------------------------------+------------------+------------+-----------+---------------+
19. 13 rows in set (0.00 sec)
21. greatsql> SHOW SLAVE STATUS \G
22. *************************** 1. row ***************************
23.                Slave_IO_State: Waiting for source to send event
24.                   Master_Host: 172.17.139.230
25.                   Master_User: greatsql
26.                   Master_Port: 3307
27.                 Connect_Retry: 60
28.               Master_Log_File: binlog.000064
29.           Read_Master_Log_Pos: 714784459
30.                Relay_Log_File: relaylog.000189
31.                 Relay_Log_Pos: 714314261
32.         Relay_Master_Log_File: binlog.000064
33.              Slave_IO_Running: Yes
34.             Slave_SQL_Running: Yes
35.               Replicate_Do_DB:
36.           Replicate_Ignore_DB:
37.            Replicate_Do_Table:
38.        Replicate_Ignore_Table:
39.       Replicate_Wild_Do_Table:
40.   Replicate_Wild_Ignore_Table:
41.                    Last_Errno: 0
42.                    Last_Error:
43.                  Skip_Counter: 0
44.           Exec_Master_Log_Pos: 714314051
45.               Relay_Log_Space: 714784956
46.               Until_Condition: None
47.                Until_Log_File:
48.                 Until_Log_Pos: 0
49.            Master_SSL_Allowed: No
50.            Master_SSL_CA_File:
51.            Master_SSL_CA_Path:
52.               Master_SSL_Cert:
53.             Master_SSL_Cipher:
54.                Master_SSL_Key:
55.         Seconds_Behind_Master: 700
56. Master_SSL_Verify_Server_Cert: No
57.                 Last_IO_Errno: 0
58.                 Last_IO_Error:
59.                Last_SQL_Errno: 0
60.                Last_SQL_Error:
61.   Replicate_Ignore_Server_Ids:
62.              Master_Server_Id: 1000403307
63.                   Master_UUID: 1d0963e9-85d9-11ef-80e1-00163e28e06a
64.              Master_Info_File: mysql.slave_master_info
65.                     SQL_Delay: 0
66.           SQL_Remaining_Delay: NULL
67.       Slave_SQL_Running_State: Waiting for dependent transaction to commit
68.            Master_Retry_Count: 86400
69.                   Master_Bind:
70.       Last_IO_Error_Timestamp:
71.      Last_SQL_Error_Timestamp:
72.                Master_SSL_Crl:
73.            Master_SSL_Crlpath:
74.            Retrieved_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-133182
75.             Executed_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-133170
76.                 Auto_Position: 1
77.          Replicate_Rewrite_DB:
78.                  Channel_Name:
79.            Master_TLS_Version:
80.        Master_public_key_path:
81.         Get_master_public_key: 0
82.             Network_Namespace:
83. 1 row in set, 1 warning (0.00 sec)
85. greatsql> SHOW SLAVE STATUS \G
86. *************************** 1. row ***************************
87.                Slave_IO_State: Waiting for source to send event
88.                   Master_Host: 172.17.139.230
89.                   Master_User: greatsql
90.                   Master_Port: 3307
91.                 Connect_Retry: 60
92.               Master_Log_File: binlog.000064
93.           Read_Master_Log_Pos: 714784459
94.                Relay_Log_File: relaylog.000189
95.                 Relay_Log_Pos: 714314261
96.         Relay_Master_Log_File: binlog.000064
97.              Slave_IO_Running: Yes
98.             Slave_SQL_Running: Yes
99.               Replicate_Do_DB:
100.           Replicate_Ignore_DB:
101.            Replicate_Do_Table:
102.        Replicate_Ignore_Table:
103.       Replicate_Wild_Do_Table:
104.   Replicate_Wild_Ignore_Table:
105.                    Last_Errno: 0
106.                    Last_Error:
107.                  Skip_Counter: 0
108.           Exec_Master_Log_Pos: 714314051
109.               Relay_Log_Space: 714784956
110.               Until_Condition: None
111.                Until_Log_File:
112.                 Until_Log_Pos: 0
113.            Master_SSL_Allowed: No
114.            Master_SSL_CA_File:
115.            Master_SSL_CA_Path:
116.               Master_SSL_Cert:
117.             Master_SSL_Cipher:
118.                Master_SSL_Key:
119.         Seconds_Behind_Master: 704
120. Master_SSL_Verify_Server_Cert: No
121.                 Last_IO_Errno: 0
122.                 Last_IO_Error:
123.                Last_SQL_Errno: 0
124.                Last_SQL_Error:
125.   Replicate_Ignore_Server_Ids:
126.              Master_Server_Id: 1000403307
127.                   Master_UUID: 1d0963e9-85d9-11ef-80e1-00163e28e06a
128.              Master_Info_File: mysql.slave_master_info
129.                     SQL_Delay: 0
130.           SQL_Remaining_Delay: NULL
131.       Slave_SQL_Running_State: Waiting for dependent transaction to commit
132.            Master_Retry_Count: 86400
133.                   Master_Bind:
134.       Last_IO_Error_Timestamp:
135.      Last_SQL_Error_Timestamp:
136.                Master_SSL_Crl:
137.            Master_SSL_Crlpath:
138.            Retrieved_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-133182
139.             Executed_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-133170
140.                 Auto_Position: 1
141.          Replicate_Rewrite_DB:
142.                  Channel_Name:
143.            Master_TLS_Version:
144.        Master_public_key_path:
145.         Get_master_public_key: 0
146.             Network_Namespace:
147. 1 row in set, 1 warning (0.00 sec)

复制代码

四、总结

利用SHOW PROCESSLIST和SHOW SLAVE STATUS观察是数据库正在回放数据，找到正在回放的主库的binlog位点以及正在回放的GTID，在主库上利用 SHOW BINLOG EVENTS IN binlog文件 FROM 位点 LIMIT 步长，来找到对应的表布局和事件类型，进而观察表布局，该表无索引且数据量大，以是导致从库回放的时间比较慢，至于处理办法，可以暂时KILL 掉回放线程，然后加上索引，在重启回放线程，详细细节就不详细展开了。

Enjoy GreatSQL
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。