Mysql 非同步複製延遲的原因及解決方案

業餘DBA發表於2020-11-10

在非同步或半同步的複製結構中,從庫出現延遲是一件十分正常的事。雖出現延遲正常,但是否需要關注,則一般是由業務來評估。
 

複製邏輯過程


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\G ;檢查Seconds_Behind_Master值即可.

此方法的結果有一定的偏差,如果要求不是非常嚴格的話,使用是沒有問題,要求嚴格的話,可以使用pt工具檢查。

 

造成延遲的主要原因

MySQL的主從複製都是單執行緒的操作,主庫對所有DDL和DML產生的日誌寫進binlog,由於binlog是順序寫,所以效率很高。Slave的SQL Thread執行緒將主庫的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是隨即的,不是順序的,成本高很多。另一方面,由於SQL Thread也是單執行緒的,當主庫的併發較高時,產生的DML數量超過slave的SQL Thread所能處理的速度,或者當slave中有大型query語句產生了鎖等待那麼延時就產生了。(雖然5.7有並行複製功能,不過在大併發的情況下,從庫並行度肯定會比主庫低)

常見原因:Master負載過高、Slave負載過高、網路延遲、機器效能太低、MySQL配置不合理。

 

解決延遲的辦法

1.升級資料庫版本到5.7以上,開啟並行複製,加大並行複製執行緒數到適合的個數

2.避免在主庫上執行大的事務,將大事務拆分成小事務。

3.關閉從庫binlog,修改innodb_flush_log_at_trx_commit配置。

4.確保每個表都是顯示的遞增主鍵,避免從庫更新或刪除時掃描全表。

5.根據從庫上業務情況,可適當減小大表的索引個數,以減小每次操作時索引維護的開銷。

6.如果業務允許,只複製部分庫表資料。

7.減少從庫上的操作,減小從庫壓力。

8.增加從庫所在伺服器的硬體配置。

9.優化網路,儘量內網複製

 

 

 

相關文章