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主從復制延遲的資料請關注腳本之家其它相關文章!
相關文章
完美解決mysql啟動后隨即關閉的問題(ibdata1文件損壞導致)
下面小編就為大家?guī)硪黄昝澜鉀Qmysql啟動后隨即關閉的問題(ibdata1文件損壞導致)。小編覺得挺不錯的,現(xiàn)在就分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2017-03-03mysql 觸發(fā)器實現(xiàn)兩個表的數(shù)據(jù)同步
本文將介紹mysql 觸發(fā)器實現(xiàn)兩個表的數(shù)據(jù)同步,需要的朋友可以參考2012-11-11MYSQL5.7 全文檢索中文無返回數(shù)據(jù)的問題解決
本文介紹了MYSQL5.7 全文檢索中文無返回數(shù)據(jù)的問題解決,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2025-01-01