mysql同步(複製)延遲的原因及解決方案

willcat發表於2019-05-13
MySql

同步延遲的本質

當從庫跟不上主庫的更新進度時就會出現同步(複製)延遲，這時在從庫裡，未同步的修改在relay_log裡出現堆積，資料的版本也會漸漸跟主庫差別越來越大。

同步延遲的原因

為了確定延遲的原因，我們需要確定是哪個複製執行緒出現問題了。在mysql中，一對主從同步的連線依賴三個不同的執行緒，其中兩個由從庫建立，一個由主庫建立。

  • 從庫的I/o執行緒：當你在從庫通過Start Slave命令配置了主庫同步資訊後，這個執行緒就會被從庫建立。用來請求主庫binlog日誌的備份
  • 主庫的Bin log Dump執行緒: 當從庫連上主庫後，這個執行緒就會被建立，然後會將其binlog傳送給從庫。
  • 從庫Slave SQL 執行緒： 從庫建立這個執行緒，然後從獲取的binlog裡讀取內容並應用。

同步延遲的解決方案

當I/O執行緒或SQL執行緒無法處理對其提出的要求時，會導致複製延遲。

I/O執行緒可能出現的問題

  1. 網路較慢
  2. 傳輸資料量較大
  3. 一個主從對中，binlog的讀是序列的，而寫是並行的

I/O執行緒可能出現的問題解決方案

  1. 針對1.2.開啟slave_compressed_protocol選項進行資料壓縮
  2. 針對3.庫級別並行讀

SQL執行緒可能出現的問題

  1. 語句沒有優化或者不合適造成從庫同步很慢，如有很多長時事務或太多IO活動
  2. 使用Row/Mixed格式時，從庫的表沒有primary key也會有問題。

SQL執行緒可能出現的問題解決方案

  1. 減少長時事務或IO
  2. 檢查主、從庫是否都有primary key

如何檢視哪個執行緒出現了問題

binlog檔名和位置的同步方式

此種同步方式下使用Show master status和show slave statue命令可以檢視你需要的資訊，比如: Show master status命令看到的：

  • 通過postion-Read_Master_Log_Pos獲得你的IO執行緒落後主庫日誌的位元組數
  • 通過postion-Exec_Master_Log_Pos獲得你的SQL執行緒落後主庫日誌的位元組數
  • 通過Read_Master_Log_Pos-Exec_Master_Log_Pos獲得你的SQL執行緒落後IO的位元組數 Show slave status命令看到的：
  • seconds_behind_master可以參考落後主庫的秒數，不過不要過度依賴它，因為它的統計方式不是很準確

GTIDs同步方式

診斷所用指標(show slave status)

  • Executed GTIDs： 主從庫上都能看到，返回執行的事務的id。也可以通過查詢全域性變數gtid_executed來獲取最近執行的事務id
  • Retrieved GTIDs：只有從庫上可以看到，用來返回IO執行緒獲取的事務id
  • Purged GTIDs：顯示從binlog清除了哪些已經執行完的事務。一般你只會對從庫中的這個日誌感興趣

獲得GTID後，你可以使用GTID_SUBTRACT()函式計算從庫與主庫的差異。例如，以下對從庫查詢顯示從二進位制日誌中讀取的尚未應用的GTID（SQL執行緒延遲）:

slave> SELECT GTID_SUBTRACT('96985d6f-2ebc-11e7-84df-08002715584a:5-133', 
        '96985d6f-2ebc-11e7-84df-08002715584a:26-132') AS MissingGTIDs;
+-----------------------------------------------+
| MissingGTIDs                                  |
+-----------------------------------------------+
| 96985d6f-2ebc-11e7-84df-08002715584a:5-25:133 |
+-----------------------------------------------+
1 row in set (0.00 sec)
複製程式碼

GIIDs方式簡介

通過GTIDs【global transaction identifiers】,可以標識每一個事務，並且可以在其一旦提交追蹤並應用於任何一個Slave上；這樣 就不需要像BinaryLog複製依賴Log file 和位置。GTIDs完全基於事務，只要在Master提交的所有事務都在Slave上進行了Commit，那麼就能保證Master和Slave之間的資料 一致性。你可以使用基於SBR或RBR的GTIDs來實現。推薦使用RBR【Row-based replication】.

參考

What Causes Replication Lag?

相關文章