主从复制中定位回放慢涉及的表
主从复制中定位回放慢涉及的表一、前提
天下千奇百怪，每个人都有自己独立的思想，有些事变即使你附耳告知，也可能如风般吹过，进而消失，为了性能为了不延迟，表要加索引嘛，然而在某业务场景，业务表数千张，无索引的表几百张，这些表都是上百万的数据。
二、征象
在 GreatSQL 主从架构中，某天在体系资源富足的环境下，主从忽然延迟，而且持续增长，我们通过SHOW PROCESSLIST 和 SHOW SLAVE STATUS 观察是由于回放 DELETE 事件造成的，但是 GTID`` 在不断地增长，不外增长的非常缓慢，但是平时的时间是没有云云缓慢的，我们该如何快速的定位这些回放缓慢的 GTID 的涉及表呢，接下来就在测试环境演示下如何定位。
三、模拟测试
1、主节点创建库
greatsql> CREATE DATABASE fcmark;
Query OK, 1 row affected (0.03 sec)2、准备数据
$ 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_onlyprepare
sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
Creating table 'sbtest1'...
Inserting 10000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 10000000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 10000000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 10000000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 10000000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...3、主节点修改表布局，将表修改为无索引表
greatsql> ALTER TABLE sbtest1 MODIFY id int not null;
Query OK, 10000000 rows affected (3 min 2.62 sec)
Records: 10000000Duplicates: 0Warnings: 0
greatsql> ALTER TABLE sbtest1 DROP key `k_1`;
Query OK, 0 rows affected (0.15 sec)
Records: 0Duplicates: 0Warnings: 0
greatsql> ALTER TABLE sbtest1 DROP PRIMARY KEY;
Query OK, 10000000 rows affected (1 min 50.19 sec)
Records: 10000000Duplicates: 0Warnings: 0
greatsql> ALTER TABLE sbtest2 MODIFY id int not null;
Query OK, 10000000 rows affected (3 min 2.62 sec)
Records: 10000000Duplicates: 0Warnings: 0
greatsql> ALTER TABLE sbtest2 DROP key `k_2`;
Query OK, 0 rows affected (0.15 sec)
Records: 0Duplicates: 0Warnings: 0
greatsql> ALTER TABLE sbtest2 DROPPRIMARY KEY;
Query OK, 10000000 rows affected (1 min 50.19 sec)
Records: 10000000Duplicates: 0Warnings: 0
greatsql> ALTER TABLE sbtest3 MODIFY id int not null;
Query OK, 10000000 rows affected (3 min 2.72 sec)
Records: 10000000Duplicates: 0Warnings: 0
greatsql> ALTER TABLE sbtest3 DROP key `k_3`;
Query OK, 0 rows affected (0.15 sec)
Records: 0Duplicates: 0Warnings: 0
greatsql> ALTER TABLE sbtest3 DROP PRIMARY KEY;
Query OK, 10000000 rows affected (1 min 52.19 sec)
Records: 10000000Duplicates: 0Warnings: 0
greatsql> ALTER TABLE sbtest4 MODIFY id int not null;
Query OK, 10000000 rows affected (3 min 3.64 sec)
Records: 10000000Duplicates: 0Warnings: 0
greatsql> ALTER TABLE sbtest4 DROP key `k_4`;
Query OK, 0 rows affected (0.15 sec)
Records: 0Duplicates: 0Warnings: 0
greatsql> ALTER TABLE sbtest4 DROP PRIMARY KEY;
Query OK, 10000000 rows affected (1 min 53.19 sec)
Records: 10000000Duplicates: 0Warnings: 0
greatsql> ALTER TABLE sbtest5 MODIFY id int not null;
Query OK, 10000000 rows affected (3 min 5.62 sec)
Records: 10000000Duplicates: 0Warnings: 0
greatsql> ALTER TABLE sbtest5 DROP key `k_5`;
Query OK, 0 rows affected (0.15 sec)
Records: 0Duplicates: 0Warnings: 0
greatsql> ALTER TABLE sbtest5 DROP PRIMARY KEY;
Query OK, 10000000 rows affected (1 min 51 sec)
Records: 10000000Duplicates: 0Warnings: 04、主库操作数据模拟延迟
greatsql> DELETE FROM sbtest1 WHERE k<200;
Query OK, 214 rows affected (41.14 sec)
greatsql> DELETE FROM sbtest2 WHERE k<200;
Query OK, 193 rows affected (41.58 sec)
greatsql> DELETE FROM sbtest3 WHERE k<200;
Query OK, 177 rows affected (40.00 sec)
greatsql> DELETE FROM sbtest4 WHERE k<200;
Query OK, 184 rows affected (39.38 sec)
greatsql> DELETE FROM sbtest5 WHERE k<200;
Query OK, 212 rows affected (37.56 sec)
greatsql> DELETE FROM sbtest1 WHERE k<400;
Query OK, 182 rows affected (39.09 sec)
greatsql> DELETE FROM sbtest2 WHERE k<400;
Query OK, 193 rows affected (38.21 sec)
greatsql> DELETE FROM sbtest3 WHERE k<400;
Query OK, 215 rows affected (34.45 sec)
greatsql> DELETE FROM sbtest4 WHERE k<400;
Query OK, 219 rows affected (37.45 sec)
greatsql> DELETE FROM sbtest5 WHERE k<400;
Query OK, 224 rows affected (34.63 sec)
greatsql> DELETE FROM sbtest1 WHERE k<600;
Query OK, 185 rows affected (34.11 sec)
greatsql> DELETE FROM sbtest2 WHERE k<600;
Query OK, 245 rows affected (35.99 sec)
greatsql> DELETE FROM sbtest3 WHERE k<600;
Query OK, 173 rows affected (36.08 sec)
greatsql> DELETE FROM sbtest4 WHERE k<600;
Query OK, 230 rows affected (36.18 sec)
greatsql> DELETE FROM sbtest5 WHERE k<600;
Query OK, 186 rows affected (38.00 sec)6、分析数据
主节点操作，根据从节点观察的信息，在主节点上观察 binlog 信息，回放内容是关于 fcmark.sbtest4`` 表的多个DELETE事件，观察该表布局是无索引，并且数据量接近 1万万
greatsql> SHOW PROCESSLIST;
+--------+-----------------+----------------------+--------+---------+---------+---------------------------------------------+------------------+------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |
+--------+-----------------+----------------------+--------+---------+---------+---------------------------------------------+------------------+------------+-----------+---------------+
| 5 | event_scheduler | localhost | NULL | Daemon| 2356467 | Waiting on empty queue | NULL | 2356467469 | 0 | 0 |
| 17 | system user | connecting host | NULL | Connect | 2356460 | Waiting for source to send event | NULL | 2356459985 | 0 | 0 |
| 18 | system user | | NULL | Query | 99 | Waiting for dependent transaction to commit | NULL | 99081 | 0 | 0 |
| 19 | system user | | fcmark | Query | 697 | Applying batch of row changes (delete) | NULL | 32977 | 0 | 0 |
| 20 | system user | | NULL | Query | 3244 | Waiting for an event from Coordinator | NULL | 3242579 | 0 | 0 |
| 21 | system user | | NULL | Connect | 2356460 | Waiting for an event from Coordinator | NULL | 2356459981 | 0 | 0 |
| 22 | system user | | NULL | Connect | 2356460 | Waiting for an event from Coordinator | NULL | 2356459980 | 0 | 0 |
| 540493 | greatsql | 172.17.136.93:34298| NULL | Sleep | 83 | | NULL | 83365 | 0 | 0 |
| 540593 | greatsql | 172.17.139.230:51514 | NULL | Query | 0 | init | show processlist | 0 | 0 | 0 |
| 540660 | greatsql | 172.17.136.93:46212| NULL | Sleep | 383 | | NULL | 383304 | 0 | 0 |
| 540730 | greatsql | 172.17.136.93:51128| NULL | Sleep | 143 | | NULL | 143319 | 0 | 0 |
| 540758 | greatsql | 172.17.136.93:53112| NULL | Sleep | 23 | | NULL | 23205 | 0 | 0 |
| 540763 | greatsql | 172.17.139.230:52204 | NULL | Sleep | 1 | | NULL | 1246 | 0 | 0 |
+--------+-----------------+----------------------+--------+---------+---------+---------------------------------------------+------------------+------------+-----------+---------------+
13 rows in set (0.00 sec)
greatsql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.17.139.230
Master_User: greatsql
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: binlog.000064
Read_Master_Log_Pos: 714784459
Relay_Log_File: relaylog.000189
Relay_Log_Pos: 714314261
Relay_Master_Log_File: binlog.000064
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 714314051
Relay_Log_Space: 714784956
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 700
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1000403307
Master_UUID: 1d0963e9-85d9-11ef-80e1-00163e28e06a
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Waiting for dependent transaction to commit
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-133182
Executed_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-133170
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
greatsql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.17.139.230
Master_User: greatsql
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: binlog.000064
Read_Master_Log_Pos: 714784459
Relay_Log_File: relaylog.000189
Relay_Log_Pos: 714314261
Relay_Master_Log_File: binlog.000064
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 714314051
Relay_Log_Space: 714784956
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 704
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1000403307
Master_UUID: 1d0963e9-85d9-11ef-80e1-00163e28e06a
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Waiting for dependent transaction to commit
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-133182
Executed_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-133170
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
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企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]