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，但主從資料不一致的情況。
這將是另一篇博文了。

全文完。

以上就是MySQL主從複製延遲原因以及解決方案的詳細內容，更多關於MySQL主從複製延遲的資料請關注it145.com其它相關文章！