MySQL主從復制延遲原因是什么

本篇內容主要講解“MySQL主從復制延遲原因是什么”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“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吧。

到此，相信大家對“MySQL主從復制延遲原因是什么”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！