MySQL複制延遲優化的方法論

一、MySQL為什麼會延遲

資料延遲： 是指master執行了N個事務，slave卻隻執行了N-M個事務，說明master和slave之間産生了延遲

延遲原因：延遲的原因很多種，大部分情況下是 slave的處理能力跟不上master導緻

接下來，我們從各種角度分析下延遲的原因

1.1 MySQL複制的架構

通過架構圖，可以直覺的看到資料延遲的點有哪些，當然也就可以知道如何優化了

1.2 大事務導緻的延遲

大家都知道，binlog的寫入時機是在commit的時候，redo的寫入時機是在事務執行階段就開始。

Oracle是通過實體複制，我們姑且認為是redo的複制，因為redo是事務執行階段就開始寫入的，是以，oracle的複制幾乎沒有延遲

MySQL是基于binlog複制的，如果有一個非常大的事務，如果需要1個小時，那麼master在1小時候後才會生成binlog，而此時，slave就比master慢了至少1個小時，還不算是binlog傳輸時間

這是第一種延遲原因，破解方法後面說

PS: DDL雖然不是事務，但是特性跟大事務一樣，都是在master上執行了一個巨大無比的操作才寫的binlog

1.3 IO線程導緻的延遲

根據複制的架構，Master寫完binlog後，需要通過網絡傳輸給slave（這部分我們需要網絡的支援）

然後呢，IO thread會将binlog寫到slave的relay log中，這部分工作由IO thread完成

好了，這裡我們分析下瓶頸：

1. io thread 是單線程的
2. io thread 寫入 relay log的速度

經過分析以及大量的實戰，IO thread并不是我們的瓶頸，因為relay log是順序的寫入，非常快，幾乎碰不到瓶頸

1.4 SQL線程導緻的延遲

master 上面的事務是可以進行并發的，然後binlog傳輸到slave後，slave是卻以單線程的模式讀取和執行relay log

這是典型的消費能力不足

1.5 網絡問題導緻的延遲

網絡問題不用多說了吧，如果要複制良好，一個穩定的網路環境是在所難免的

1.6 硬體問題導緻的延遲

如果master是SSD，但是slave還是機械硬碟，這樣的架構存在延遲也不足為奇

二、延遲場景的解決方案

2.1 DDL

2.1.1 ddl的最佳實踐

1. 通過pt-osc 或者 gh-ost 來讓ddl拆分成一個個小事務，并且還有流控功能
2. 在slave上先ddl，然後master-slave切換，然後再old master上進行ddl，進而完美的解決了這個問題

2.2 大事務

2.2.1 大事務拆小事務

如果說大事務對于binlog的産生有極大的影響，那麼我們認為定義小事務，大事務不允許執行

有大事務的監控，可以基于時間，可以基于資料量，監控到不符合規範的trx自動kill

2.3 大量并發事務

2.3.1 調整安全參數

sync_binlog = 0 && innodb_flush_trx_commit = 0 可以極大的提高事務處理的吞吐量，因為IO fsync的次數變少了，可以非常有效的降低資料延遲

風險：如果slave挂了，需要重做slave

2.3.2 MTS(enhanced multi-threaded slave)

之前有深入讨論過MTS的文章，它主要的功能就是讓slave擁有比master更快的并行能力，進而有效的讓延遲縮短，甚至無延遲

終極大招

半同步： 半同步可以讓延遲為0，但是半同步有自動切換為異步複制的可能

全同步： MySQL的group replication 就是這類的代表，這個話題以後再聊

最後，以上就是關于MySQL延遲優化的方法，幾乎涵蓋了90%的方案，如果大家還有更好的方案，不妨拿出來大家一起探讨