糟了，數據庫主從延遲了！

生活中所受的苦，終會以一種形式迴歸！

前言

因此，一般來說都是通過集羣主從複製（Master-Slave）的方式來同步數據，再通過讀寫分離（MySQL-Proxy）來提升數據庫的併發負載能力進行部署與實施

總結 MySQL 主從集羣帶來的作用是：

說到主從同步，離不開 binlog 這個東西，先介紹下 binlog 吧

binlog

binlog 是什麼？有什麼作用？

用於記錄數據庫執行的寫入性操作 (不包括查詢) 信息，以二進制的形式保存在磁盤中。可以簡單理解爲記錄的就是 sql 語句

binlog 是 mysql 的邏輯日誌，並且由 Server層進行記錄，使用任何存儲引擎的 mysql 數據庫都會記錄 binlog 日誌

在實際應用中， binlog 的主要使用場景有兩個：

用於主從複製，在主從結構中，binlog 作爲操作記錄從 master 被髮送到 slave，slave 服務器從 master 接收到的日誌保存到 relay log 中。
用於數據備份，在數據庫備份文件生成後，binlog 保存了數據庫備份後的詳細信息，以便下一次備份能從備份點開始。

日誌格式

binlog 日誌有三種格式，分別爲 STATMENT 、 ROW 和 MIXED

在 MySQL 5.7.7 之前，默認的格式是 STATEMENT ， MySQL 5.7.7 之後，默認值是 ROW

日誌格式通過 binlog-format 指定。

STATMENT ：基於 SQL 語句的複製，每一條會修改數據的 sql 語句會記錄到 binlog 中
ROW ：基於行的複製
MIXED ：基於 STATMENT 和 ROW 兩種模式的混合複製，比如一般的數據操作使用 row 格式保存，有些表結構的變更語句，使用 statement 來記錄

我們還可以通過 mysql 提供的查看工具 mysqlbinlog 查看文件中的內容，例如

mysqlbinlog mysql-bin.00001 | more

binlog 文件大小和個數會不斷的增加，後綴名會按序號遞增，例如mysql-bin.00002等。

主從複製原理

可以看到 mysql 主從複製需要三個線程：master（binlog dump thread）、slave（I/O thread 、SQL thread）

binlog dump 線程： 主庫中有數據更新時，根據設置的 binlog 格式，將更新的事件類型寫入到主庫的 binlog 文件中，並創建 log dump 線程通知 slave 有數據更新。當 I/O 線程請求日誌內容時，將此時的 binlog 名稱和當前更新的位置同時傳給 slave 的 I/O 線程。
I/O 線程： 該線程會連接到 master，向 log dump 線程請求一份指定 binlog 文件位置的副本，並將請求回來的 binlog 存到本地的 relay log 中。
SQL 線程： 該線程檢測到 relay log 有更新後，會讀取並在本地做 redo 操作，將發生在主庫的事件在本地重新執行一遍，來保證主從數據同步。

基本過程總結

主庫寫入數據並且生成 binlog 文件。該過程中 MySQL 將事務串行的寫入二進制日誌，即使事務中的語句都是交叉執行的。
在事件寫入二進制日誌完成後，master 通知存儲引擎提交事務。
從庫服務器上的 IO 線程連接 Master 服務器，請求從執行 binlog 日誌文件中的指定位置開始讀取 binlog 至從庫。
主庫接收到從庫的 IO 線程請求後，其上覆制的 IO 線程會根據 Slave 的請求信息分批讀取 binlog 文件然後返回給從庫的 IO 線程。
Slave 服務器的 IO 線程獲取到 Master 服務器上 IO 線程發送的日誌內容、日誌文件及位置點後，會將 binlog 日誌內容依次寫到 Slave 端自身的 Relay Log（即中繼日誌）文件的最末端，並將新的 binlog 文件名和位置記錄到master-info文件中，以便下一次讀取 master 端新 binlog 日誌時能告訴 Master 服務器從新 binlog 日誌的指定文件及位置開始讀取新的 binlog 日誌內容。
從庫服務器的 SQL 線程會實時監測到本地 Relay Log 中新增了日誌內容，然後把 RelayLog 中的日誌翻譯成 SQL 並且按照順序執行 SQL 來更新從庫的數據。
從庫在relay-log.info中記錄當前應用中繼日誌的文件名和位置點以便下一次數據複製。

並行複製

在 MySQL 5.6 版本之前，Slave 服務器上有兩個線程 I/O 線程和 SQL 線程。

I/O 線程負責接收二進制日誌，SQL 線程進行回放二進制日誌。如果在 MySQL 5.6 版本開啓並行複製功能，那麼 SQL 線程就變爲了 coordinator 線程，coordinator 線程主要負責以前兩部分的內容

上圖的紅色框框部分就是實現並行複製的關鍵所在

這意味着 coordinator 線程並不是僅將日誌發送給 worker 線程，自己也可以回放日誌，但是所有可以並行的操作交付由 worker 線程完成。

coordinator 線程與 worker 是典型的生產者與消費者模型。

不過到 MySQL 5.7 纔可稱爲真正的並行複製，這其中最爲主要的原因就是 slave 服務器的回放與主機是一致的即 master 服務器上是怎麼並行執行的 slave 上就怎樣進行並行回放。不再有庫的並行複製限制，對於二進制日誌格式也無特殊的要求。

爲了兼容 MySQL 5.6 基於庫的並行複製，5.7 引入了新的變量slave-parallel-type，其可以配置的值有：

DATABASE：默認值，基於庫的並行複製方式
LOGICAL_CLOCK：基於組提交的並行複製方式

下面分別介紹下兩種並行複製方式

能夠同一組裏提交的事務，定不會修改同一行；
主庫上可以並行執行的事務，從庫上也一定可以並行執行。

具體是如何實現的：

在同一組裏面一起提交的事務，會有一個相同的commit_id，下一組爲commit_id+1，該commit_id會直接寫到 binlog 中；
在從庫使用時，相同commit_id的事務會被分發到多個 worker 並行執行，直到這一組相同的commit_id執行結束後，coordinator 再取下一批。

更詳細內容可以去官網看看：https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html

下面開始介紹主從延時

主從延遲

主從延遲是怎麼回事？

根據前面主從複製的原理可以看出，兩者之間是存在一定時間的數據不一致，也就是所謂的主從延遲。

我們來看下導致主從延遲的時間點：

主庫 A 執行完成一個事務，寫入 binlog，該時刻記爲 T1.
傳給從庫 B，從庫接受完這個 binlog 的時刻記爲 T2.
從庫 B 執行完這個事務，該時刻記爲 T3.

那麼所謂主從延遲，就是同一個事務，從庫執行完成的時間和主庫執行完成的時間之間的差值，即 T3-T1。

我們也可以通過在從庫執行show slave status，返回結果會顯示seconds_behind_master，表示當前從庫延遲了多少秒。

seconds_behind_master 如何計算的？

每一個事務的 binlog 都有一個時間字段，用於記錄主庫上寫入的時間
從庫取出當前正在執行的事務的時間字段，跟當前系統的時間進行相減，得到的就是seconds_behind_master，也就是前面所描述的 T3-T1。

主從延遲原因

爲什麼會主從延遲？

正常情況下，如果網絡不延遲，那麼日誌從主庫傳給從庫的時間是相當短，所以 T2-T1 可以基本忽略。

最直接的影響就是從庫消費中轉日誌（relaylog）的時間段，而造成原因一般是以下幾種：

1、從庫的機器性能比主庫要差

比如將 20 臺主庫放在 4 臺機器，把從庫放在一臺機器。這個時候進行更新操作，由於更新時會觸發大量讀操作，導致從庫機器上的多個從庫爭奪資源，導致主從延遲。

不過，目前大部分部署都是採取主從使用相同規格的機器部署。

2、從庫的壓力大

按照正常的策略，讀寫分離，主庫提供寫能力，從庫提供讀能力。將進行大量查詢放在從庫上，結果導致從庫上耗費了大量的 CPU 資源，進而影響了同步速度，造成主從延遲。

對於這種情況，可以通過一主多從，分擔讀壓力；也可以採取 binlog 輸出到外部系統，比如 Hadoop，讓外部系統提供查詢能力。

3、大事務的執行

一旦執行大事務，那麼主庫必須要等到事務完成之後纔會寫入 binlog。

比如主庫執行了一條 insert … select 非常大的插入操作，該操作產生了近幾百 G 的 binlog 文件傳輸到只讀節點，進而導致了只讀節點出現應用 binlog 延遲。

因此，DBA 經常會提醒開發，不要一次性地試用 delete 語句刪除大量數據，儘可能控制數量，分批進行。

4、主庫的 DDL(alter、drop、create)

1、只讀節點與主庫的 DDL 同步是串行進行，如果 DDL 操作在主庫執行時間很長，那麼從庫也會消耗同樣的時間，比如在主庫對一張 500W 的表添加一個字段耗費了 10 分鐘，那麼從節點上也會耗費 10 分鐘。

2、從節點上有一個執行時間非常長的的查詢正在執行，那麼這個查詢會堵塞來自主庫的 DDL，表被鎖，直到查詢結束爲止，進而導致了從節點的數據延遲。

5、鎖衝突

鎖衝突問題也可能導致從節點的 SQL 線程執行慢，比如從機上有一些 select .... for update 的 SQL，或者使用了 MyISAM 引擎等。

6、從庫的複製能力

一般場景中，因偶然情況導致從庫延遲了幾分鐘，都會在從庫恢復之後追上主庫。但若是從庫執行速度低於主庫，且主庫持續具有壓力，就會導致長時間主從延遲，很有可能就是從庫複製能力的問題。

從庫上的執行，即sql_thread更新邏輯，在 5.6 版本之前，是隻支持單線程，那麼在主庫併發高、TPS 高時，就會出現較大的主從延遲。

因此，MySQL 自 5.7 版本後就已經支持並行複製了。可以在從服務上設置 slave_parallel_workers爲一個大於 0 的數，然後把slave_parallel_type參數設置爲LOGICAL_CLOCK，這就可以了

mysql> show variables like 'slave_parallel%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| slave_parallel_type    | DATABASE |
| slave_parallel_workers | 0        |
+------------------------+----------+

怎麼減少主從延遲

主從同步問題永遠都是一致性和性能的權衡，得看實際的應用場景，若想要減少主從延遲的時間，可以採取下面的辦法：

降低多線程大事務併發的概率，優化業務邏輯
優化 SQL，避免慢 SQL，減少批量操作，建議寫腳本以 update-sleep 這樣的形式完成。
提高從庫機器的配置，減少主庫寫 binlog 和從庫讀 binlog 的效率差。
儘量採用短的鏈路，也就是主庫和從庫服務器的距離儘量要短，提升端口帶寬，減少 binlog 傳輸的網絡延時。
實時性要求的業務讀強制走主庫，從庫只做災備，備份。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/GSslajNsZ0xGqwDtDHRf6g

前言