成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看

我們一起聊聊如何加速無索引表引起的主從延遲數據回放

作者：飛魚過天
數據庫 其他數據庫
加速無索引表的回放主要是針對該表上進行 DELETE? 或 UPDATE 操作時有效，而且我們操作的時候要注意是否能記錄binlog日志，保證主從數據一致性，當從節點作為數據節點即存儲節點的角色時，要關注計算節點即代理層元數據與存儲節點元數據的一致性或者是符合計算節點關于該表的定義規則。

一、場景

由于某些原因，客戶現場存在一張 8千萬 的大表，而且該表上無任何索引（也無主鍵），平時該表上 UPDATE 或 DELETE 只操作幾條數據。忽然有一天業務進行了某種操作，DELETE 2萬 條數據，悲劇發生了，當在主庫上執行了之后，傳到從庫上之后一直回放，當時評估了下可能會回放10天，后來在經過業務同意之后，對表進行操作，用于加速回放日志，處理該問題。

二、處理思路

停掉復制線程，關閉 Binlog 日志記錄，添加索引，重啟復制線程。

三、復現步驟

1.準備數據：

創建庫：

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

準備數據，準備一張8千萬數據的表：

$ 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
sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 80000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

主節點修改表結構，將表的索引去掉

greatsql> USE qj
Database changed
greatsqll> ALTER TABLE sbtest1 MODIFY id int not null;

Query OK, 80000000 rows affected (36 min 29.13 sec)
Records: 80000000  Duplicates: 0  Warnings: 0

greatsql> ALTER TABLE sbtest1 DROP key `k_1`;
Query OK, 0 rows affected (0.96 sec)
Records: 0  Duplicates: 0  Warnings: 0

greatsql> ALTER TABLE sbtest1 DROP PRIMARY KEY;

Query OK, 80000000 rows affected (14 min 29.31 sec)
Records: 80000000  Duplicates: 0  Warnings: 0

2.模擬延遲：

01.主節點：

模擬刪除數據

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

02.從節點：

等待時間超過DELETE語句的時間，觀察延遲情況，確認從節點可讀可寫

greatsql> SELECT sleep(300);
+------------+
| sleep(300) |
+------------+
|          0 |
+------------+
1 row in set (5 min 0.01 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.000078
          Read_Master_Log_Pos: 953750186
               Relay_Log_File: relaylog.000231
                Relay_Log_Pos: 949954084
        Relay_Master_Log_File: binlog.000078
             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: 949953874
              Relay_Log_Space: 953750683
              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: 1345
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: Replica has read all relay log; waiting for more updates
           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-163235
            Executed_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-163234
                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 GLOBAL  VARIABLES LIKE 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

03.通過操作從節點加速日志回放

找到回放線程，KILL掉回放線程，關閉當前會話binlog日志記錄，添加索引，重新啟動復制回放線程

greatsql> SHOW PROCESSLIST;
+--------+-----------------+----------------------+------+---------+---------+----------------------------------------------------------+------------------+------------+-----------+---------------+
| Id     | User            | Host                 | db   | Command | Time    | State                                                    | Info             | Time_ms    | Rows_sent | Rows_examined |
+--------+-----------------+----------------------+------+---------+---------+----------------------------------------------------------+------------------+------------+-----------+---------------+
|      5 | event_scheduler | localhost            | NULL | Daemon  | 2369454 | Waiting on empty queue                                   | NULL             | 2369454039 |         0 |             0 |
|     17 | system user     | connecting host      | NULL | Connect | 2369447 | Waiting for source to send event                         | NULL             | 2369446555 |         0 |             0 |
| 543448 | greatsql         | 172.17.136.93:48298  | NULL | Sleep   |     110 |                                                          | NULL             |     109832 |         0 |             0 |
| 543500 | greatsql         | 172.17.139.230:35198 | NULL | Sleep   |     586 |                                                          | NULL             |     585885 |         0 |             0 |
| 543588 | greatsql         | 172.17.136.93:57948  | NULL | Sleep   |      50 |                                                          | NULL             |      49878 |         0 |             0 |
| 543663 | greatsql         | 172.17.139.230:35726 | NULL | Query   |       0 | init                                                     | show processlist |          0 |         0 |             0 |
| 543666 | greatsql         | 172.17.136.93:58908  | NULL | Sleep   |     290 |                                                          | NULL             |     289861 |         0 |             0 |
| 543708 | system user     |                      | NULL | Query   |     477 | Replica has read all relay log; waiting for more updates | NULL             |     476520 |         0 |             0 |
| 543709 | system user     |                      | qj   | Query   |    1383 | Applying batch of row changes (delete)                   | NULL             |      26102 |         0 |             0 |
| 543710 | system user     |                      | NULL | Connect |     477 | Waiting for an event from Coordinator                    | NULL             |     476570 |         0 |             0 |
| 543711 | system user     |                      | NULL | Connect |     477 | Waiting for an event from Coordinator                    | NULL             |     476569 |         0 |             0 |
| 543712 | system user     |                      | NULL | Connect |     477 | Waiting for an event from Coordinator                    | NULL             |     476568 |         0 |             0 |
| 543714 | greatsql         | 172.17.136.93:33582  | NULL | Sleep   |     470 |                                                          | NULL             |     469798 |         0 |             0 |
+--------+-----------------+----------------------+------+---------+---------+----------------------------------------------------------+------------------+------------+-----------+---------------+
13 rows in set (0.00 sec)

greatsql> KILL 543709;
Query OK, 0 rows affected (0.00 sec)

greatsql> SHOW PROCESSLIST;
+--------+-----------------+----------------------+------+---------+---------+----------------------------------+------------------+------------+-----------+---------------+
| Id     | User            | Host                 | db   | Command | Time    | State                            | Info             | Time_ms    | Rows_sent | Rows_examined |
+--------+-----------------+----------------------+------+---------+---------+----------------------------------+------------------+------------+-----------+---------------+
|      5 | event_scheduler | localhost            | NULL | Daemon  | 2369473 | Waiting on empty queue           | NULL             | 2369473241 |         0 |             0 |
|     17 | system user     | connecting host      | NULL | Connect | 2369466 | Waiting for source to send event | NULL             | 2369465757 |         0 |             0 |
| 543448 | greatsql         | 172.17.136.93:48298  | NULL | Sleep   |     129 |                                  | NULL             |     129034 |         0 |             0 |
| 543500 | greatsql         | 172.17.139.230:35198 | NULL | Sleep   |     605 |                                  | NULL             |     605087 |         0 |             0 |
| 543588 | greatsql         | 172.17.136.93:57948  | NULL | Sleep   |       9 |                                  | NULL             |       9132 |         0 |             0 |
| 543663 | greatsql         | 172.17.139.230:35726 | NULL | Query   |       0 | init                             | show processlist |          0 |         0 |             0 |
| 543666 | greatsql         | 172.17.136.93:58908  | NULL | Sleep   |     309 |                                  | NULL             |     309064 |         0 |             0 |
| 543714 | greatsql         | 172.17.136.93:33582  | NULL | Sleep   |     489 |                                  | NULL             |     489000 |         0 |             0 |
+--------+-----------------+----------------------+------+---------+---------+----------------------------------+------------------+------------+-----------+---------------+
8 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.000078
          Read_Master_Log_Pos: 953750186
               Relay_Log_File: relaylog.000231
                Relay_Log_Pos: 949954084
        Relay_Master_Log_File: binlog.000078
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1317
                   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.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 949953874
              Relay_Log_Space: 953750683
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1317
               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.
  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: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 241105 19:06:59
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-163235
            Executed_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-163234
                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> SET sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

greatsql> ALTER TABLE qj.sbtest1 ADD index tmp_k(k);
Query OK, 0 rows affected (4 min 51.92 sec)
Records: 0  Duplicates: 0  Warnings: 0

greatsql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.02 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.000078
          Read_Master_Log_Pos: 953750186
               Relay_Log_File: relaylog.000231
                Relay_Log_Pos: 953750396
        Relay_Master_Log_File: binlog.000078
             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: 953750186
              Relay_Log_Space: 953750683
              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: 0
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: Replica has read all relay log; waiting for more updates
           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-163235
            Executed_Gtid_Set: 1d0963e9-85d9-11ef-80e1-00163e28e06a:1-163235
                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)

四、思考

01.如何保持主從一致性

02.正在回放數據時，STOP SLAVE 是否會有問題

03.如果主從結構作為數據節點，上層還有計算節點該如何保持元數據的一致性即計算節點記錄的表的結構和數據節點的表結構要一致

關于以上三個問題我的處理方案如下：

1.如何保持主從一致性

#為保持主從表結構一致，主節點添加索引
greatsql> SET sql_log_bin=0;
greatsql> ALTER TABLE qj.sbtest1 ADD index tmp_k(k);

2.正在回放數據時，STOP SLAVE是否會有問題

greatsql> STOP SLAVE --當該GTID回放很長時間時，stop可能會很長時間，我們可以采用 kill 線程方式結束回放
greatsql> SHOW PROCESSLIST; --找到會話ID
greatsql> KILL 會話號
greatsql> SHOW SLAVE STATUS  \G 
-- 添加索引即可

3.如果主從結構作為數據節點，上層還有計算節點該如何保持元數據的一致性即計算節點記錄的表的結構和數據節點的表結構要一致

-- 當從節點添加上索引之后，在計算節點即代理層給表添加索引，當然該索引名不要與臨時添加索引名重復
-- 添加上索引之后，在將從節點上臨時添加的索引刪除即可
greatsql> STOP SLAVE;
greatsql> SHOW SLAVE STATUS \G 

greatsql> SET sql_log_bin=0;
greatsql> ALTER table qj.sbtest1 DROP index tmp_k;

greatsql> START SLAVE;
greatsql> SHOW SLAVE STATUS \G

五、總結

加速無索引表的回放主要是針對該表上進行 DELETE 或 UPDATE 操作時有效，而且我們操作的時候要注意是否能記錄binlog日志，保證主從數據一致性，當從節點作為數據節點即存儲節點的角色時，要關注計算節點即代理層元數據與存儲節點元數據的一致性或者是符合計算節點關于該表的定義規則。

責任編輯：武曉燕 來源： GreatSQL社區
數據DELETE?日志
相關推薦

2024-05-11 07:29:48

Redis延遲隊列優化

2024-01-03 09:03:40

MySQL索引數據庫

2024-04-03 09:46:03

無服務架構Web

2023-08-10 08:28:46

網絡編程通信

2023-08-04 08:20:56

DockerfileDocker工具

2023-06-30 08:18:51

敏捷開發模式

2022-05-24 08:21:16

數據安全API

2023-09-10 21:42:31

2022-10-08 00:00:05

SQL機制結構

2024-11-27 16:07:45

2024-09-30 09:33:31

2023-04-26 07:30:00

promptUI非結構化

2024-02-20 21:34:16

循環GolangGo

2021-08-27 07:06:10

IOJava抽象

2024-09-09 00:00:00

編寫技術文檔

2023-04-03 00:09:13

2025-01-24 00:00:00

數據RoaringBitmap

2024-10-15 08:08:13

2024-12-10 00:00:25

2021-12-10 07:45:48

字節音頻視頻
點贊
收藏

51CTO技術棧公眾號

主站蜘蛛池模板： 中文在线视频观看 | 99精品视频一区二区三区 | 一区二区三区四区不卡 | 久久在线免费 | 国产91在线观看 | 免费小视频在线观看 | 毛片免费看 | 久久精品久久久 | 国家aaa的一级看片 h片在线看 | 麻豆成人在线视频 | 国产人成精品一区二区三 | 国产精品 欧美精品 | 欧美一区二区在线看 | 国产精品久久久久一区二区三区 | 精品一区二区三区视频在线观看 | 在线观看三级av | 国产精品美女久久久久久免费 | 精品蜜桃一区二区三区 | 黄色毛片一级 | 中文字幕精品一区二区三区精品 | 99精品网 | jizz在线看片 | 欧美不卡| 久久久精 | 永久免费在线观看 | 日韩国产精品一区二区三区 | 亚洲一区二区在线视频 | 精品成人一区二区 | 久久99深爱久久99精品 | 91在线观看免费视频 | 日韩成人精品一区 | 一区二区三区免费 | 亚洲精品9999 | 国产精品久久久久久久久久久免费看 | 91欧美精品 | 一区在线视频 | 亚洲人人 | 欧美激情综合五月色丁香小说 | 91欧美激情一区二区三区成人 | 久久久久久亚洲欧洲 | 久久国产一区二区 |