今天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 版本
- 升級硬件
最后修改時間:2022-05-06 15:24:01
「喜歡這篇文章,您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容,轉載時必須標注文章的來源(墨天輪),文章鏈接,文章作者等基本信息,否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容,歡迎發送郵件至:contact@modb.pro進行舉報,并提供相關證據,一經查實,墨天輪將立刻刪除相關內容。




