今天MySQL又遇備庫延遲案例，順便總結一下

案例現象

mysql>show slave status\G

Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.13.20
Master_User: rpl_user
Master_Port: 3310
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 48207531
Relay_Log_File: relay-bin-rpl1.000023
Relay_Log_Pos: 1153
Relay_Master_Log_File: mysql-bin.000013
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: 940
Relay_Log_Space: 48208449
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: 65708
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: 2155887676
Master_UUID: 000c1822-5be5-11ec-be70-a8ca7b94d69c
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: 000c1822-5be5-11ec-be70-a8ca7b94d69c:20-2141
Executed_Gtid_Set: 000c1822-5be5-11ec-be70-a8ca7b94d69c:1-2136,
6feab22c-5be4-11ec-b641-1047800760df:1-16

分析

1.查看目前執行的事務

Seconds_Behind_Master的數值不斷在變大，首先看看目前正在跑的是什么事務，
查看Relay_Master_Log_File: mysql-bin.000013和Exec_Master_Log_Pos: 940
在主庫：

mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000013  | grep -A '100'  940

發現目前卡在執行gtid:000c1822-5be5-11ec-be70-a8ca7b94d69c:2137上，這是一個大事務，批量刪除delete from procu1 limit 6000000。因為BINLOG設置的ROW格式，主庫在執行了一條delete sql;在binlog會生成六百萬條delete記錄，到了備庫要執行六百萬次。

2.查看事務表

在備庫：
mysql>select * from information_schema.innodb_trx\G

trx_id:25389

trx_state:running

start:2020-12-11 10:33:41

requested_lock_id:null

waite_started:null

weight:107178

mysql_thread_id:94

tables_in_use:1

tables_locked:1

lock_structs:692

lock_memory_bytes:90320

rows_locked:212972

rows_modified:106486

concurrency_tickets:0

isolation_leve:read commited

unique_checks:1

foreign_key_checks:1

foreign_key_error:null

沒有具體的sql語句trx_query,可以看到rows_locked和rows_modified在增加。rows_modified這個值要一直增加到六百萬才結束，而且因為autocommit=off，數據目前都是在undo上

3.查看innodb引擎信息

在備庫：
mysql>show engine innodb status\G
摘要:

---TRANSACTION **25389**, ACTIVE 58424 sec
mysql tables in use 1, locked 1
18740 lock struct(s), heap size 3137744, 11885860 row lock(s), undo log entries 5942930
MySQL thread id 65, OS thread handle 139944933123840, query id 415 Executing event

4.查看會話信息

在備庫：
mysql>show processlist

| 64 | system user | | NULL | Connect | 58362 | Waiting for dependent transaction to commit | NULL |
| 65 | system user | | NULL | Connect | 66073 | Executing event | NULL |

解決

備庫復制線程是在后臺執行，若是kill掉線程，會暫停slave同步，無法解決。
可以嘗試讓備庫資源寬裕，加快執行，然后耐心等待大事務完成，追上最新數據。
例如一些優化操作：

SET GLOBAL innodb_buffer_pool_size=增大值;
SET GLOBAL slave_pending_jobs_size_max=增大值;
SET GLOBAL foreign_key_checks=0;
SET GLOBAL unique_checks=0;
SET GLOBAL innodb_flush_log_at_trx_commit=0;
SET GLOBAL sync_binlog=0;

若是考慮需要等候時間太久，人工干預方案：

• 跳過事務

在備庫手動執行跑批操作，不做binlog記錄，再跳過gtid

stop slave;
set sql_log_bin=0;
set global super_read_only=0;
delete from procu1 limit 6000000;
commit;
set global super_read_only=1;
set sql_log_bin=1;
SET @@SESSION.GTID_NEXT= '000c1822-5be5-11ec-be70-a8ca7b94d69c:1-2137';
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;
START SLAVE;

• 重做備庫

在主庫全量備份，恢復到備庫

總結

一、常見主備延遲場景

• 備庫所在機器的性能要比主庫所在的機器性能差
• 備庫的查詢壓力大
• 大事務
• 大表DDL
• 備庫的并行復制能力
• 隨機重放
• 主庫并發高
• 鎖等待

二、常見主備延遲優化

1.主庫優化

• DML大事務，分拆小事務，小量分批多次執行
• DDL大表操作，第三方工具pt-online-schema-change，業務低谷執行

2.備庫優化

• 增大從庫參數innodb_buffer_pool_size的值，可以緩存更多數據，減少由于轉換導致的IO壓力。
• 增大參數innodb_log_file_size、innodb_log_files_in_group的值，減少buffer pool的刷盤IO,提升寫入性能。
• 修改參數innodb_flush_method為O_DIRECT,提升寫入性能。
• 關掉從庫Binlog日志或者關掉參數log_slave_updates.
• 修改參數innodb_flush_log_at_tr_commit為0或2.
• 如果binlog沒有關掉（gtid開啟，需要binlog日志打開），修改sync_binlog參數為0或者一個很大的值，減少磁盤IO壓力。
• 如果binlog_format為row模式并且被修改表沒有主鍵，則需要加上主鍵
• 如果binlog_format為row模式，則可以在備庫總刪除一些不必要的索引(同步完成之后再加上)。
• 了解清楚寫庫上操作內容，適當地在備庫中預熱一些數據，可以減少在復制時等待的時間。
• 修改參數master_info_repository、relay_log_info_repository為table，減少直接IO導致的磁盤壓力。
• 可以打開多線程復制。slave_parallel_type=‘logical_clock’ slave_parallel_workers=32
• 升級mysql 版本
• 升級硬件