加快无索引表引起的主从延长数据回放

加快无索引表引起的主从延长数据回放

一、场景

由于某些缘故原由，客户现场存在一张 8千万 的大表，而且该表上无任何索引（也无主键），平时该表上 UPDATE 或 DELETE 只操作几条数据。忽然有一天业务进行了某种操作，DELETE 2万 条数据，悲剧发生了，当在主库上执行了之后，传到从库上之后一直回放，当时评估了下可能会回放10天，后来在经过业务同意之后，对表进行操作，用于加快回放日志，处理该问题。
二、处理思路

停掉复制线程，关闭 Binlog 日志记载，添加索引，重启复制线程。
三、复现步骤

1、准备数据：

创建库：

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

复制代码

准备数据，准备一张8千万数据的表：

1. $ sysbench --db-driver=mysql --mysql-host=192.168.139.230 --mysql-port=3307 --mysql-user=greatsql --mysql-password=GreatSQL@2024 --mysql-db=qj --table_size=80000000 --tables=1 oltp_write_only  prepare
2. sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)
4. Creating table 'sbtest1'...
5. Inserting 80000000 records into 'sbtest1'
6. Creating a secondary index on 'sbtest1'...

复制代码

主节点修改表布局，将表的索引去掉

1. greatsql> USE qj
2. Database changed
3. greatsqll> ALTER TABLE sbtest1 MODIFY id int not null;
5. Query OK, 80000000 rows affected (36 min 29.13 sec)
6. Records: 80000000  Duplicates: 0  Warnings: 0
8. greatsql> ALTER TABLE sbtest1 DROP key `k_1`;
9. Query OK, 0 rows affected (0.96 sec)
10. Records: 0  Duplicates: 0  Warnings: 0
12. greatsql> ALTER TABLE sbtest1 DROP PRIMARY KEY;
14. Query OK, 80000000 rows affected (14 min 29.31 sec)
15. Records: 80000000  Duplicates: 0  Warnings: 0

复制代码

2、模仿延长：

01、主节点：
模仿删除数据

1. greatsql> DELETE FROM qj.sbtest1 WHERE  k<20000;
2. Query OK, 19894 rows affected (4 min 20.40 sec)

复制代码

03、通过操作从节点加快日志回放
找到回放线程，KILL掉回放线程，关闭当前会话binlog日志记载，添加索引，重新启动复制回放线程

1. greatsql> SELECT sleep(300);
2. +------------+
3. | sleep(300) |
4. +------------+
5. |          0 |
6. +------------+
7. 1 row in set (5 min 0.01 sec)
9. greatsql> SHOW SLAVE STATUS \G
10. *************************** 1. row ***************************
11.                Slave_IO_State: Waiting for source to send event
12.                   Master_Host: 172.17.139.230
13.                   Master_User: greatsql
14.                   Master_Port: 3307
15.                 Connect_Retry: 60
16.               Master_Log_File: binlog.000078
17.           Read_Master_Log_Pos: 953750186
18.                Relay_Log_File: relaylog.000231
19.                 Relay_Log_Pos: 949954084
20.         Relay_Master_Log_File: binlog.000078
21.              Slave_IO_Running: Yes
22.             Slave_SQL_Running: Yes
23.               Replicate_Do_DB:
24.           Replicate_Ignore_DB:
25.            Replicate_Do_Table:
26.        Replicate_Ignore_Table:
27.       Replicate_Wild_Do_Table:
28.   Replicate_Wild_Ignore_Table:
29.                    Last_Errno: 0
30.                    Last_Error:
31.                  Skip_Counter: 0
32.           Exec_Master_Log_Pos: 949953874
33.               Relay_Log_Space: 953750683
34.               Until_Condition: None
35.                Until_Log_File:
36.                 Until_Log_Pos: 0
37.            Master_SSL_Allowed: No
38.            Master_SSL_CA_File:
39.            Master_SSL_CA_Path:
40.               Master_SSL_Cert:
41.             Master_SSL_Cipher:
42.                Master_SSL_Key:
43.         Seconds_Behind_Master: 1345
44. Master_SSL_Verify_Server_Cert: No
45.                 Last_IO_Errno: 0
46.                 Last_IO_Error:
47.                Last_SQL_Errno: 0
48.                Last_SQL_Error:
49.   Replicate_Ignore_Server_Ids:
50.              Master_Server_Id: 1000403307
51.                   Master_UUID: 1d0963e9-85d9-11ef-80e1-00163e28e06a
52.              Master_Info_File: mysql.slave_master_info
53.                     SQL_Delay: 0
54.           SQL_Remaining_Delay: NULL
55.       Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
56.            Master_Retry_Count: 86400
57.                   Master_Bind:
58.       Last_IO_Error_Timestamp:
59.      Last_SQL_Error_Timestamp:
60.                Master_SSL_Crl:
61.            Master_SSL_Crlpath:
62.            Retrieved_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-163235
63.             Executed_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-163234
64.                 Auto_Position: 1
65.          Replicate_Rewrite_DB:
66.                  Channel_Name:
67.            Master_TLS_Version:
68.        Master_public_key_path:
69.         Get_master_public_key: 0
70.             Network_Namespace:
71. 1 row in set, 1 warning (0.00 sec)
73. greatsql> SHOW GLOBAL  VARIABLES LIKE 'read_only';
74. +---------------+-------+
75. | Variable_name | Value |
76. +---------------+-------+
77. | read_only     | OFF   |
78. +---------------+-------+
79. 1 row in set (0.01 sec)

复制代码

四、思索

01、如何保持主从划一性
02、正在回放数据时，STOP SLAVE 是否会有问题
03、如果主从布局作为数据节点，上层还有计算节点该如何保持元数据的划一性即计算节点记载的表的布局和数据节点的表布局要划一
关于以上三个问题我的处理方案如下：
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  | 2369454 | Waiting on empty queue                                   | NULL             | 2369454039 |         0 |             0 |
6. |     17 | system user     | connecting host      | NULL | Connect | 2369447 | Waiting for source to send event                         | NULL             | 2369446555 |         0 |             0 |
7. | 543448 | greatsql         | 172.17.136.93:48298  | NULL | Sleep   |     110 |                                                          | NULL             |     109832 |         0 |             0 |
8. | 543500 | greatsql         | 172.17.139.230:35198 | NULL | Sleep   |     586 |                                                          | NULL             |     585885 |         0 |             0 |
9. | 543588 | greatsql         | 172.17.136.93:57948  | NULL | Sleep   |      50 |                                                          | NULL             |      49878 |         0 |             0 |
10. | 543663 | greatsql         | 172.17.139.230:35726 | NULL | Query   |       0 | init                                                     | show processlist |          0 |         0 |             0 |
11. | 543666 | greatsql         | 172.17.136.93:58908  | NULL | Sleep   |     290 |                                                          | NULL             |     289861 |         0 |             0 |
12. | 543708 | system user     |                      | NULL | Query   |     477 | Replica has read all relay log; waiting for more updates | NULL             |     476520 |         0 |             0 |
13. | 543709 | system user     |                      | qj   | Query   |    1383 | Applying batch of row changes (delete)                   | NULL             |      26102 |         0 |             0 |
14. | 543710 | system user     |                      | NULL | Connect |     477 | Waiting for an event from Coordinator                    | NULL             |     476570 |         0 |             0 |
15. | 543711 | system user     |                      | NULL | Connect |     477 | Waiting for an event from Coordinator                    | NULL             |     476569 |         0 |             0 |
16. | 543712 | system user     |                      | NULL | Connect |     477 | Waiting for an event from Coordinator                    | NULL             |     476568 |         0 |             0 |
17. | 543714 | greatsql         | 172.17.136.93:33582  | NULL | Sleep   |     470 |                                                          | NULL             |     469798 |         0 |             0 |
18. +--------+-----------------+----------------------+------+---------+---------+----------------------------------------------------------+------------------+------------+-----------+---------------+
19. 13 rows in set (0.00 sec)
21. greatsql> KILL 543709;
22. Query OK, 0 rows affected (0.00 sec)
24. greatsql> SHOW PROCESSLIST;
25. +--------+-----------------+----------------------+------+---------+---------+----------------------------------+------------------+------------+-----------+---------------+
26. | Id     | User            | Host                 | db   | Command | Time    | State                            | Info             | Time_ms    | Rows_sent | Rows_examined |
27. +--------+-----------------+----------------------+------+---------+---------+----------------------------------+------------------+------------+-----------+---------------+
28. |      5 | event_scheduler | localhost            | NULL | Daemon  | 2369473 | Waiting on empty queue           | NULL             | 2369473241 |         0 |             0 |
29. |     17 | system user     | connecting host      | NULL | Connect | 2369466 | Waiting for source to send event | NULL             | 2369465757 |         0 |             0 |
30. | 543448 | greatsql         | 172.17.136.93:48298  | NULL | Sleep   |     129 |                                  | NULL             |     129034 |         0 |             0 |
31. | 543500 | greatsql         | 172.17.139.230:35198 | NULL | Sleep   |     605 |                                  | NULL             |     605087 |         0 |             0 |
32. | 543588 | greatsql         | 172.17.136.93:57948  | NULL | Sleep   |       9 |                                  | NULL             |       9132 |         0 |             0 |
33. | 543663 | greatsql         | 172.17.139.230:35726 | NULL | Query   |       0 | init                             | show processlist |          0 |         0 |             0 |
34. | 543666 | greatsql         | 172.17.136.93:58908  | NULL | Sleep   |     309 |                                  | NULL             |     309064 |         0 |             0 |
35. | 543714 | greatsql         | 172.17.136.93:33582  | NULL | Sleep   |     489 |                                  | NULL             |     489000 |         0 |             0 |
36. +--------+-----------------+----------------------+------+---------+---------+----------------------------------+------------------+------------+-----------+---------------+
37. 8 rows in set (0.00 sec)
39. greatsql> SHOW SLAVE STATUS \G
40. *************************** 1. row ***************************
41.                Slave_IO_State: Waiting for source to send event
42.                   Master_Host: 172.17.139.230
43.                   Master_User: greatsql
44.                   Master_Port: 3307
45.                 Connect_Retry: 60
46.               Master_Log_File: binlog.000078
47.           Read_Master_Log_Pos: 953750186
48.                Relay_Log_File: relaylog.000231
49.                 Relay_Log_Pos: 949954084
50.         Relay_Master_Log_File: binlog.000078
51.              Slave_IO_Running: Yes
52.             Slave_SQL_Running: No
53.               Replicate_Do_DB:
54.           Replicate_Ignore_DB:
55.            Replicate_Do_Table:
56.        Replicate_Ignore_Table:
57.       Replicate_Wild_Do_Table:
58.   Replicate_Wild_Ignore_Table:
59.                    Last_Errno: 1317
60.                    Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '1d0963e9-85d9-11ef-80e1-00163e28e06a:163235' at master log binlog.000078, end_log_pos 950241265. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
61.                  Skip_Counter: 0
62.           Exec_Master_Log_Pos: 949953874
63.               Relay_Log_Space: 953750683
64.               Until_Condition: None
65.                Until_Log_File:
66.                 Until_Log_Pos: 0
67.            Master_SSL_Allowed: No
68.            Master_SSL_CA_File:
69.            Master_SSL_CA_Path:
70.               Master_SSL_Cert:
71.             Master_SSL_Cipher:
72.                Master_SSL_Key:
73.         Seconds_Behind_Master: NULL
74. Master_SSL_Verify_Server_Cert: No
75.                 Last_IO_Errno: 0
76.                 Last_IO_Error:
77.                Last_SQL_Errno: 1317
78.                Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '1d0963e9-85d9-11ef-80e1-00163e28e06a:163235' at master log binlog.000078, end_log_pos 950241265. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
79.   Replicate_Ignore_Server_Ids:
80.              Master_Server_Id: 1000403307
81.                   Master_UUID: 1d0963e9-85d9-11ef-80e1-00163e28e06a
82.              Master_Info_File: mysql.slave_master_info
83.                     SQL_Delay: 0
84.           SQL_Remaining_Delay: NULL
85.       Slave_SQL_Running_State:
86.            Master_Retry_Count: 86400
87.                   Master_Bind:
88.       Last_IO_Error_Timestamp:
89.      Last_SQL_Error_Timestamp: 241105 19:06:59
90.                Master_SSL_Crl:
91.            Master_SSL_Crlpath:
92.            Retrieved_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-163235
93.             Executed_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-163234
94.                 Auto_Position: 1
95.          Replicate_Rewrite_DB:
96.                  Channel_Name:
97.            Master_TLS_Version:
98.        Master_public_key_path:
99.         Get_master_public_key: 0
100.             Network_Namespace:
101. 1 row in set, 1 warning (0.00 sec)
103. greatsql> SET sql_log_bin=0;
104. Query OK, 0 rows affected (0.00 sec)
106. greatsql> ALTER TABLE qj.sbtest1 ADD index tmp_k(k);
107. Query OK, 0 rows affected (4 min 51.92 sec)
108. Records: 0  Duplicates: 0  Warnings: 0
110. greatsql> START SLAVE;
111. Query OK, 0 rows affected, 1 warning (0.02 sec)
112. greatsql> SHOW SLAVE STATUS \G
113. *************************** 1. row ***************************
114.                Slave_IO_State: Waiting for source to send event
115.                   Master_Host: 172.17.139.230
116.                   Master_User: greatsql
117.                   Master_Port: 3307
118.                 Connect_Retry: 60
119.               Master_Log_File: binlog.000078
120.           Read_Master_Log_Pos: 953750186
121.                Relay_Log_File: relaylog.000231
122.                 Relay_Log_Pos: 953750396
123.         Relay_Master_Log_File: binlog.000078
124.              Slave_IO_Running: Yes
125.             Slave_SQL_Running: Yes
126.               Replicate_Do_DB:
127.           Replicate_Ignore_DB:
128.            Replicate_Do_Table:
129.        Replicate_Ignore_Table:
130.       Replicate_Wild_Do_Table:
131.   Replicate_Wild_Ignore_Table:
132.                    Last_Errno: 0
133.                    Last_Error:
134.                  Skip_Counter: 0
135.           Exec_Master_Log_Pos: 953750186
136.               Relay_Log_Space: 953750683
137.               Until_Condition: None
138.                Until_Log_File:
139.                 Until_Log_Pos: 0
140.            Master_SSL_Allowed: No
141.            Master_SSL_CA_File:
142.            Master_SSL_CA_Path:
143.               Master_SSL_Cert:
144.             Master_SSL_Cipher:
145.                Master_SSL_Key:
146.         Seconds_Behind_Master: 0
147. Master_SSL_Verify_Server_Cert: No
148.                 Last_IO_Errno: 0
149.                 Last_IO_Error:
150.                Last_SQL_Errno: 0
151.                Last_SQL_Error:
152.   Replicate_Ignore_Server_Ids:
153.              Master_Server_Id: 1000403307
154.                   Master_UUID: 1d0963e9-85d9-11ef-80e1-00163e28e06a
155.              Master_Info_File: mysql.slave_master_info
156.                     SQL_Delay: 0
157.           SQL_Remaining_Delay: NULL
158.       Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
159.            Master_Retry_Count: 86400
160.                   Master_Bind:
161.       Last_IO_Error_Timestamp:
162.      Last_SQL_Error_Timestamp:
163.                Master_SSL_Crl:
164.            Master_SSL_Crlpath:
165.            Retrieved_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-163235
166.             Executed_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-163235
167.                 Auto_Position: 1
168.          Replicate_Rewrite_DB:
169.                  Channel_Name:
170.            Master_TLS_Version:
171.        Master_public_key_path:
172.         Get_master_public_key: 0
173.             Network_Namespace:
174. 1 row in set, 1 warning (0.00 sec)

复制代码

2、正在回放数据时，STOP SLAVE是否会有问题

1. #为保持主从表结构一致，主节点添加索引
2. greatsql> SET sql_log_bin=0;
3. greatsql> ALTER TABLE qj.sbtest1 ADD index tmp_k(k);

复制代码

3、如果主从布局作为数据节点，上层还有计算节点该如何保持元数据的划一性即计算节点记载的表的布局和数据节点的表布局要划一

1. greatsql> STOP SLAVE --当该GTID回放很长时间时，stop可能会很长时间，我们可以采用 kill 线程方式结束回放
2. greatsql> SHOW PROCESSLIST; --找到会话ID
3. greatsql> KILL 会话号
4. greatsql> SHOW SLAVE STATUS  \G
5. -- 添加索引即可

复制代码

五、总结

加快无索引表的回放重要是针对该表上进行 DELETE 或 UPDATE 操作时有用，而且我们操作的时候要注意是否能记载binlog日志，保证主从数据划一性，当从节点作为数据节点即存储节点的角色时，要关注计算节点即代理层元数据与存储节点元数据的划一性或者是符合计算节点关于该表的定义规则。

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