MySQL主從復制延遲原因以及解決方案

來源：公眾號「神諭的暗影長廊」

在異步或半同步的復制結構中，從庫出現(xiàn)延遲是一件十分正常的事。
雖出現(xiàn)延遲正常，但是否需要關注，則一般是由業(yè)務來評估。
如：從庫上有需要較高一致性的讀業(yè)務，并且要求延遲小于某個值，那么則需要關注。

簡單概述一下復制邏輯：

1、主庫將對數(shù)據(jù)庫實例的變更記錄到binlog中。
2、主庫會有binlog dump線程實時監(jiān)測binlog的變更并將這些新的events推給從庫（Master has sent all binlog to slave; waiting for more updates）
3、從庫的IO Thread接收這些events，并將其記錄入relaylog。
4、從庫的SQL Thread讀取relaylog的events，并將這些events應用（或稱為重放）到從庫實例。

上述為默認的異步復制邏輯，半同步復制又有些許不同，此處不再贅述。

此外，判斷從庫有延遲是十分簡單的一件事：
在從庫上通過SHOW SLAVE STATUS
檢查Seconds_Behind_Master值即可。

產(chǎn)生延遲的原因及處理思路

〇 主庫DML請求頻繁（tps較大）

即主庫寫請求較多，有大量insert、delete、update并發(fā)操作，短時間產(chǎn)生了大量的binlog。

【原因分析】

主庫并發(fā)寫入數(shù)據(jù)，而從庫SQL Thread為單線程應用日志，很容易造成relaylog堆積，產(chǎn)生延遲。

【解決思路】

做sharding，通過scale out打散寫請求。或考慮升級到MySQL 5.7+，開啟基于邏輯時鐘的并行復制。

〇 主庫執(zhí)行大事務

比如大量導入數(shù)據(jù)，INSERT INTO $tb1 SELECT * FROM $tb2、LOAD DATA INFILE等
比如UPDATE、DELETE了全表等
Exec_Master_Log_Pos一直未變，Slave_SQL_Running_State為Reading event from the relay log
分析主庫binlog，看主庫當前執(zhí)行的事務也可知曉。

【原因分析】

假如主庫花費200s更新了一張大表，在主從庫配置相近的情況下，從庫也需要花幾乎同樣的時間更新這張大表，此時從庫延遲開始堆積，后續(xù)的events無法更新。

【解決思路】

拆分大事務，及時提交。

〇 主庫對大表執(zhí)行DDL語句

現(xiàn)象和主庫執(zhí)行大事務相近。
檢查Exec_Master_Log_Pos一直未動，也有可能是在執(zhí)行DDL。
分析主庫binlog，看主庫當前執(zhí)行的事務也可知曉。

【原因分析】

1、DDL未開始，被阻塞，SHOW SLAVE STATUS檢查到Slave_SQL_Running_State為waiting for table metadata lock，且Exec_Master_Log_Pos不變。
2、DDL正在執(zhí)行，SQL Thread單線程應用導致延遲增加。Slave_SQL_Running_State為altering table，Exec_Master_Log_Pos不變

【解決思路】

通過processlist或information_schema.innodb_trx來找到阻塞DDL語句的查詢，干掉該查詢，讓DDL正常在從庫執(zhí)行。
DDL本身造成的延遲難以避免，建議考慮：
① 業(yè)務低峰期執(zhí)行
② set sql_log_bin=0后，分別在主從庫上手動執(zhí)行DDL（此操作對于某些DDL操作會造成數(shù)據(jù)不一致，請務必嚴格測試）

〇 主庫與從庫配置不一致：

【原因分析】

硬件上：主庫實例服務器使用SSD，而從庫實例服務器使用普通SAS盤、cpu主頻不一致等
配置上：如RAID卡寫策略不一致，OS內(nèi)核參數(shù)設置不一致，MySQL落盤策略不一致等

【解決思路】

盡量統(tǒng)一DB機器的配置（包括硬件及選項參數(shù)）
甚至對于某些OLAP業(yè)務，從庫實例硬件配置高于主庫等

〇 表缺乏主鍵或唯一索引

binlog_format=row的情況下，如果表缺乏主鍵或唯一索引，在UPDATE、DELETE的時候可能會造成從庫延遲驟增。
此時Slave_SQL_Running_State為Reading event from the relay log。
并且SHOW OPEN TABLES WHERE in_use=1的表一直存在。
Exec_Master_Log_Pos不變。
mysqld進程的cpu幾近100%（無讀業(yè)務時），io壓力不大

【原因分析】

做個極端情況下的假設，主庫更新一張500w表中的20w行數(shù)據(jù)，該update語句需要全表掃描
而row格式下，記錄到binlog的為20w次update操作，此時SQL Thread重放將特別慢，每一次update可能需要進行一次全表掃描

【解決思路】

檢查表結構，保證每個表都有顯式自增主鍵，并建立合適索引。

〇 從庫自身壓力過大

【原因分析】

從庫執(zhí)行大量select請求，或業(yè)務大部分select請求被路由到從庫實例上，甚至大量OLAP業(yè)務，或者從庫正在備份等。
此時可能造成cpu負載過高，io利用率過高等，導致SQL Thread應用過慢。

【解決思路】

建立更多從庫，打散讀請求，降低現(xiàn)有從庫實例的壓力。

〇 MyISAM存儲引擎

此時從庫Slave_SQL_Running_State為Waiting for table level lock

【原因分析】

MyISAM只支持表級鎖，并且讀寫不可并發(fā)操作。
主庫在設置@@concurrent_insert對應值的情況下，能并發(fā)在select時執(zhí)行insert，但從庫SQL Thread重放時并不可并發(fā)，有興趣可以再去看看myisam這塊的實現(xiàn)。

【解決思路】

當然是選擇原諒它了，既然選擇了MyISAM，那么也應該要有心理準備。（還存在其他場景，也不推薦MyISAM在復制結構中使用）
改成InnoDB吧。

總結：

通過SHOW SLAVE STATUS與SHOW PROCESSLIST查看現(xiàn)在從庫的情況。（順便也可排除在從庫備份時這種原因）
若Exec_Master_Log_Pos不變，考慮大事務、DDL、無主鍵，檢查主庫對應的binlog及position即可。
若Exec_Master_Log_Pos變化，延遲逐步增加，考慮從庫機器負載，如io、cpu等，并考慮主庫寫操作與從庫自身壓力是否過大。

如果上述原因都沒有，那么請教請教DBA大佬們吧。

當然，Seconds_Behind_Master也不一定準確，存在在少部分場景下，雖Seconds_Behind_Master為0，但主從數(shù)據(jù)不一致的情況。
這將是另一篇博文了。

全文完。

以上就是MySQL主從復制延遲原因以及解決方案的詳細內(nèi)容，更多關于MySQL主從復制延遲的資料請關注腳本之家其它相關文章！