MySQL主從複製延遲原因及處理思路

在非同步或半同步的複製結構中，從庫出現延遲是一件十分正常的事。
雖出現延遲正常，但是否需要關注，則一般是由業務來評估。
如：從庫上有需要較高一致性的讀業務，並且要求延遲小於某個值，那麼則需要關注。

首先，簡單概述一下複製邏輯：
1、主庫將對資料庫例項的變更記錄到binlog中。  
2、主庫會有binlog dump執行緒實時監測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值即可。


產生延遲的原因及處理思路
〇 主庫DML請求頻繁（tps較大）

即主庫寫請求較多，有大量insert、delete、update併發操作，短時間產生了大量的binlog。

【原因分析】
主庫併發寫入資料，而從庫SQL Thread為單執行緒應用日誌，很容易造成relaylog堆積，產生延遲。

【解決思路】
做sharding，透過scale out打散寫請求。或考慮升級到MySQL 5.7+，開啟基於邏輯時鐘的並行複製。

〇 主庫執行大事務

比如大量匯入資料，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，看主庫當前執行的事務也可知曉。

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

【解決思路】
拆分大事務，及時提交。

〇 主庫對大表執行DDL語句

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

【原因分析】
1、DDL未開始，被阻塞，SHOW SLAVE STATUS檢查到Slave_SQL_Running_State為waiting for table metadata lock，且Exec_Master_Log_Pos不變。
2、DDL正在執行，SQL Thread單執行緒應用導致延遲增加。Slave_SQL_Running_State為altering table，Exec_Master_Log_Pos不變

【解決思路】
透過processlist或information_schema.innodb_trx來找到阻塞DDL語句的查詢，幹掉該查詢，讓DDL正常在從庫執行。
DDL本身造成的延遲難以避免，建議考慮：
① 業務低峰期執行 
② set sql_log_bin=0後，分別在主從庫上手動執行DDL（此操作對於某些DDL操作會造成資料不一致，請務必嚴格測試）

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

【原因分析】
硬體上：主庫例項伺服器使用SSD，而從庫例項伺服器使用普通SAS盤、cpu主頻不一致等
配置上：如RAID卡寫策略不一致，OS核心引數設定不一致，MySQL落盤策略不一致等

【解決思路】
儘量統一DB機器的配置（包括硬體及選項引數）
甚至對於某些OLAP業務，從庫例項硬體配置高於主庫等

〇 表缺乏主鍵或唯一索引

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%（無讀業務時），io壓力不大

【原因分析】
做個極端情況下的假設，主庫更新一張500w表中的20w行資料，該update語句需要全表掃描
而row格式下，記錄到binlog的為20w次update操作，此時SQL Thread重放將特別慢，每一次update可能需要進行一次全表掃描

【解決思路】
檢查表結構，保證每個表都有顯式自增主鍵，並建立合適索引。

〇 從庫自身壓力過大

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

【解決思路】
建立更多從庫，打散讀請求，降低現有從庫例項的壓力。

〇 MyISAM儲存引擎

此時從庫Slave_SQL_Running_State為Waiting for table level lock

【原因分析】
MyISAM只支援表級鎖，並且讀寫不可併發操作。
主庫在設定@@concurrent_insert對應值的情況下，能併發在select時執行insert，但從庫SQL Thread重放時並不可併發，有興趣可以再去看看myisam這塊的實現。

【解決思路】
當然是選擇原諒它了，既然選擇了MyISAM，那麼也應該要有心理準備。（還存在其他場景，也不推薦MyISAM在複製結構中使用）
改成InnoDB吧。

總結

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

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

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

作者微信公主號：