一、現象
凌晨對線上一張表添加索引,表數據量太大(1億+數據,數據量50G以上),造成主從延遲幾個小時,各個依賴從庫的系統無法查詢數據,最終影響業務。
現在就梳理下主從延遲的原理。
二、原理
根據 MySQL 官方文檔 MySQL Replication Implementation Details 中的描述,MySQL 主從復制依賴于三個線程:master一個線程(Binlog dump thread),slave兩個線程(I/O thread和SQL thread)。主從復制流程如下圖:

master 服務器和 slave 服務器連接時,創建Binlog dump thread以發送bin log數據:
Binlog dump thread對應一個 slave 服務器;Binlog dump thread從bin log獲取數據時會加鎖,獲取到數據后,立即釋放鎖。 當 slave 服務器收到 START_SLAVE 命令時,會創建I/O thread和SQL thread:
I/O thread以拉的方式,從 master 讀取事件,并存儲到 slave 服務器的relay log中;SQL thread從relay log中讀取事件并執行;slave可以按照自己的節奏讀取和更新數據,也可以隨意操作復制進程(啟動和停止)。 注: START_SLAVE命令成功啟動線程后,如果后面I/O thread或SQL thread因為某些原因停止,則不會有任何的警告,業務方無法感知??梢酝ㄟ^查看 slave 的 error 日志,或者通過 SHOW SLAVE STATUS 查看 slave 上的線程狀態。
通過 SHOW PROCESSLIST 可查看線程狀態:
Binlog dump thread:
mysql> SHOW PROCESSLIST/G*************************** 1. row *************************** Id: 2 User: root Host: localhost:32931 db: NULLCommand: Binlog Dump Time: 94 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL
I/O thread 和 SQL thread:
mysql> SHOW PROCESSLIST/G*************************** 1. row *************************** Id: 10 User: system user Host: db: NULLCommand: Connect Time: 11 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 11 User: system user Host: db: NULLCommand: Connect Time: 11 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL
三、分析
根據上面的原理,由于slave是單線程(I/O thread)讀取數據,單線程(SQL thread)更新數據,而master是多線程寫入,那么只要master寫入的頻率大于slave讀取更新的頻率,就有可能出現主從延遲的情況,如:
master寫入tps較高,大于slave更新速度;slave執行某些語句耗時較長,如持有鎖等;master執行某些DDL語句時,執行的時間較長,在slave也執行相同的時間; 此處創建了索引,咨詢 DBA,產生的bin log文件有100多G,數據量太大,導致從庫I/O thread一直讀取DDL操作產生的bin log事件,而影響到正常的業務DML事件的更新,從而表現為主從同步延遲。
四、解決方案
從主從延遲的原因來看,解決方案可以從以下幾個方向入手:
DDL操作盡量選擇凌晨等業務量較小的時候總結
以上就是這篇文章的全部內容了,希望本文的內容對大家的學習或者工作具有一定的參考學習價值,謝謝大家對VeVb武林網的支持。
新聞熱點
疑難解答