來(lái)源：公眾號(hào)「神諭的暗影長(zhǎng)廊」

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

簡(jiǎn)單概述一下復(fù)制邏輯：

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

上述為默認(rèn)的異步復(fù)制邏輯，半同步復(fù)制又有些許不同，此處不再贅述。

此外，判斷從庫(kù)有延遲是十分簡(jiǎn)單的一件事：
在從庫(kù)上通過(guò)SHOW SLAVE STATUS
檢查Seconds_Behind_Master值即可。

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

〇 主庫(kù)DML請(qǐng)求頻繁（tps較大）

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

【原因分析】

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

【解決思路】

做sharding，通過(guò)scale out打散寫(xiě)請(qǐng)求?；蚩紤]升級(jí)到MySQL 5.7+，開(kāi)啟基于邏輯時(shí)鐘的并行復(fù)制。

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

比如大量導(dǎo)入數(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
分析主庫(kù)binlog，看主庫(kù)當(dāng)前執(zhí)行的事務(wù)也可知曉。

【原因分析】

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

【解決思路】

拆分大事務(wù)，及時(shí)提交。

〇 主庫(kù)對(duì)大表執(zhí)行DDL語(yǔ)句

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

【原因分析】

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

【解決思路】

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

〇 主庫(kù)與從庫(kù)配置不一致：

【原因分析】

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

【解決思路】

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

〇 表缺乏主鍵或唯一索引

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

【原因分析】

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

【解決思路】

檢查表結(jié)構(gòu)，保證每個(gè)表都有顯式自增主鍵，并建立合適索引。

〇 從庫(kù)自身壓力過(guò)大

【原因分析】

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

【解決思路】

建立更多從庫(kù)，打散讀請(qǐng)求，降低現(xiàn)有從庫(kù)實(shí)例的壓力。

〇 MyISAM存儲(chǔ)引擎

此時(shí)從庫(kù)Slave_SQL_Running_State為Waiting for table level lock

【原因分析】

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

【解決思路】

當(dāng)然是選擇原諒它了，既然選擇了MyISAM，那么也應(yīng)該要有心理準(zhǔn)備。（還存在其他場(chǎng)景，也不推薦MyISAM在復(fù)制結(jié)構(gòu)中使用）
改成InnoDB吧。

總結(jié)：

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

如果上述原因都沒(méi)有，那么請(qǐng)教請(qǐng)教DBA大佬們吧。

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

全文完。

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

最新評(píng)論