承蒙大家的支持，剛上市的《MySQL實戰》已經躍居京東自營資料庫圖書熱賣榜第 1 名，收到的反饋也普遍不錯，對該書感興趣的童鞋可通過右邊的鏈接購買，目前，京東自營有活動，只需 5 折，

主從延遲作為 MySQL 的痛點已經存在很多年了，以至于大家都有一種錯覺：有 MySQL 復制的地方就有主從延遲，

對于主從延遲的原因，很多人將之歸結為從庫的單執行緒重放，

但實際上，這個說法比較片面，因為很多場景，并行復制方案也解決不了，譬如從庫 SQL 執行緒被阻塞了，從庫磁盤 IO 存在瓶頸等，

很多童鞋在分析此類問題時缺乏一個系統的方法論，以致無法準確地定位出主從延遲的根本原因，

下面就如何分析主從延遲做一個系統、全面的總結，

本文主要包括以下兩方面的內容，

如何分析主從延遲，
主從延遲的常見原因及解決方法，

下一篇文章會介紹如何監控主從延遲，包括如何解讀 Seconds_Behind_Master、Seconds_Behind_Master 的局限性、pt-heartbeat 及 MySQL 8.0 原生的解決方案，敬請留意，

如何分析主從延遲

分析主從延遲一般會采集以下三類資訊，

從庫服務器的負載情況

為什么要首先查看服務器的負載情況呢？因為軟體層面的所有操作都需要系統資源來支撐，

常見的系統資源有四類：CPU、記憶體、IO、網路，對于主從延遲，一般會重點關注 CPU 和 IO ，

分析 CPU 是否達到瓶頸，常用的命令是 top，通過 top 我們可以直觀地看到主機的 CPU 使用情況，以下是 top 中 CPU 相關的輸出，

Cpu(s):  0.2%us,  0.2%sy,  0.0%ni, 99.5%id,  0.0%wa,  0.0%hi,  0.2%si,  0.0%st

下面我們看看各個指標的具體含義，

us：處理用戶態（ user ）任務的 CPU 時間占比，
sy：處理內核態（ system ）任務的 CPU 時間占比，
ni：處理低優先級行程用戶態任務的 CPU 時間占比，

行程的優先級由 nice 值決定，nine 的范圍是 -20 ～ 19 ，值越大，優先級越低，其中，1 ～ 19 稱之為低優先級，
id：處于空閑狀態（ idle ）的 CPU 時間占比，
wa：等待 IO 的 CPU 時間占比，
hi：處理硬中斷（ irq ）的 CPU 時間占比，
si：處理軟中斷（ softirq ）的 CPU 使用率，
st：當系統運行在虛擬機中的時候，被其它虛擬機占用（ steal ）的 CPU 時間占比，

一般來說，當 CPU 使用率（ 1 - 處于空閑狀態的 CPU 時間占比）超過 90% 時，需引起足夠關注，畢竟，對于資料庫應用來說，CPU 很少是瓶頸，除非有大量的慢 SQL ，

接下來看看 IO，

查看磁盤 IO 負載情況，常用的命令是 iostat ，

# iostat -xm 1
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           4.21    0.00    1.77    0.35    0.00   93.67

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
sdb               0.00     0.00  841.00 3234.00    13.14    38.96    26.19     0.60    0.15    0.30    0.11   0.08  32.60

命令中指定了 3 個選項，其中，

-x：列印擴展資訊，
-m：指定吞吐量的單位是 MB/s ，默認是 KB/s ，
1：每隔 1s 列印一次，

下面看看輸出中各指標的具體含義，

rrqm/s：每秒被合并的讀請求的數量，
wrqm/s：每秒被合并的寫請求的數量，
r/s：每秒發送給磁盤的讀請求的數量，
w/s：每秒寫入磁盤的寫請求的數量，注意，這里的請求是合并后的請求，r/s + w/s 等于 IOPS ，
rMB/s：每秒從磁盤讀取的資料量，
wMB/s：每秒寫入磁盤的資料量，rMB/s + wMB/s 等于吞吐量，
avgrq-sz：I/O 請求的平均大小，單位是扇區，扇區的大小是 512 位元組，一般而言，I/O 請求越大，耗時越長，
avgqu-sz：佇列里的平均 I/O 請求數量，
await：I/O 請求的平均耗時，包括磁盤的實際處理時間及佇列中的等待時間，單位 ms ，

其中，r_await 是讀請求的平均耗時，w_await 是寫請求的平均耗時，
svctm：I/O 請求的平均服務時間，單位 ms ，注意，這個指標已棄用，在后續版本會移除，
%util：磁盤飽和度，反映了一個采樣周期內，有多少時間在做 I/O 操作，

一般來說，我們會重點關注 await 和 %util，

對于只能串行處理 I/O 請求的設備來說，%util 接近 100% ，就意味著設備飽和，但對于 RAID、SSD 等設備，因為它能并行處理，故該值參考意義不大，即使達到了 100% ，也不意味著設備出現了飽和，至于是否達到了性能上限，需參考性能壓測下的 IOPS 和吞吐量，

主從復制狀態

對于主庫，執行 SHOW MASTER STATUS ，

mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000004 |  1631495 |              |                  | bd6b3216-04d6-11ec-b76f-000c292c1f7b:1-5588 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

SHOW MASTER STATUS 的輸出中重點關注 File 和 Position 這兩個指標的值，

對于從庫，執行 SHOW SLAVE STATUS ，

mysql> show slave status\G
*************************** 1. row ***************************
              ...
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1631495
          ...
        Relay_Master_Log_File: mysql-bin.000004
          ...
          Exec_Master_Log_Pos: 1631495
          ...

SHOW SLAVE STATUS 的輸出中重點關注 Master_Log_File，Read_Master_Log_Pos，Relay_Master_Log_File，Exec_Master_Log_Pos 這四個指標的值，

接下來，重點比較以下兩對值，

第一對：( File , Position ) & ( Master_Log_File , Read_Master_Log_Pos )

這里面，

( File , Position ) 記錄了主庫 binlog 的位置，
( Master_Log_File , Read_Master_Log_Pos ) 記錄了 IO 執行緒當前正在接收的二進制日志事件在主庫 binlog 中的位置，

如果 ( File , Position ) 大于 ( Master_Log_File , Read_Master_Log_Pos ) ，則意味著 IO 執行緒存在延遲，

第二對：( Master_Log_File , Read_Master_Log_Pos ) & ( Relay_Master_Log_File , Exec_Master_Log_Pos )

這里面，( Relay_Master_Log_File, Exec_Master_Log_Pos ) 記錄了 SQL 執行緒當前正在重放的二進制日志事件在主庫 binlog 的位置，

如果 ( Relay_Master_Log_File, Exec_Master_Log_Pos ) < ( Master_Log_File, Read_Master_Log_Pos ) ，則意味著 SQL 執行緒存在延遲，

主庫 binlog 的寫入量

主要是看主庫 binlog 的生成速度，比如多少分鐘生成一個，

主從延遲的常見原因及解決方法

下面分別從 IO 執行緒和 SQL 執行緒這兩個方面展開介紹，

IO 執行緒存在延遲

下面看看 IO 執行緒出現延遲的常見原因及解決方法，

網路延遲，

判斷是否為網路帶寬限制，如果是，可開啟 slave_compressed_protocol 引數，啟用 binlog 的壓縮傳輸，或者從 MySQL 8.0.20 開始，通過 binlog_transaction_compression 引數開啟 binlog 事務壓縮，
磁盤 IO 存在瓶頸，

可調整從庫的雙一設定或關閉 binlog，

注意，在 MySQL 5.6 中，如果開啟了 GTID ，則會強制要求開啟 binlog ，MySQL 5.7 無此限制，
網卡存在問題，

這種情況不多見，但確實碰到過，當時是一主兩從的架構，發現一臺主機上的所有從庫都延遲了，但這些從庫對應集群的其它從庫卻沒有延遲，后來通過 scp 遠程拷貝檔案進一步確認了該臺主機的網路存在問題，最后經系統組確認，網卡存在問題，

一般情況下，IO 執行緒很少存在延遲，

SQL 執行緒存在延遲

下面看看 SQL 執行緒出現延遲的常見原因及解決方法，

主庫寫入量過大，SQL 執行緒單執行緒重放

具體體現如下：

從庫磁盤 IO 無明顯瓶頸，
Relay_Master_Log_File , Exec_Master_Log_Pos 也在不斷變化，
主庫寫入量過大，如果磁盤使用的是 SATA SSD，當 binlog 的生成速度快于 5 分鐘一個時，從庫重放就會有瓶頸，

這個是 MySQL 軟體層面的硬傷，要解決該問題，可開啟 MySQL 5.7 引入的基于 LOGICAL_CLOCK 的并行復制，

關于 MySQL 并行復制方案，可參考：MySQL 并行復制方案演進歷史及原理分析

STATEMENT 格式下的慢 SQL

具體體現，在一段時間內 Relay_Master_Log_File , Exec_Master_Log_Pos 沒有變化，

看下面這個示例，對 1 張千萬資料的表進行 DELETE 操作，表上沒有任何索引，在主庫上執行用了 7.52s，觀察從庫的 Seconds_Behind_Master，發現它最大達到了 7s ，

mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest.sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.41 sec)

mysql> show create table sbtest.sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> delete from sbtest.sbtest1 where id <= 100;
Query OK, 100 rows affected (7.52 sec)

對于這種執行較慢的 SQL ，并行復制實際上也是無能為力的，此時只能優化 SQL，

在 MySQL 5.6.11 中，引入了引數 log_slow_slave_statements ，可將 SQL 重放程序中執行時長超過 long_query_time 的操作記錄在慢日志中，

表上沒有任何索引，且二進制日志格式為 ROW

同樣，在一段時間內，Relay_Master_Log_File , Exec_Master_Log_Pos 不會變化，

如果表上沒有任何索引，對它進行操作，在主庫上只是一次全表掃描，但在從庫重放時，因為是 ROW 格式，對于每條記錄的操作都會進行一次全表掃描，

還是上面的表，同樣的操作，只不過二進制日志格式為 ROW ，在主庫上執行用了 7.53s ，但 Seconds_Behind_Master 最大卻達到了 723s ，是 STATEMENT 格式下的 100 倍，

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> delete from sbtest.sbtest1 where id <= 100;
Query OK, 100 rows affected (7.53 sec)

如果因為表上沒有任何索引，導致主從延遲過大，常見的優化方案如下：

在從庫上臨時創建個索引，加快記錄的重放，注意，盡量選擇一個區分度高的列添加索引，列的區分度越高，重放的速度就越快，
將引數 slave_rows_search_algorithms 設定為 INDEX_SCAN,HASH_SCAN ，

設定后，對于同樣的操作，Seconds_Behind_Master 最大只有 53s ，

大事務

這里的大事務，指的是二進制日志格式為 ROW 的情況下，操作涉及的記錄數較多，

還是上面的測驗表，只不過這次 id 列是自增主鍵，執行批量更新操作，更新操作如下，其中，N 是記錄數，M 是一個隨機字符，每次操作的字符均不一樣，

update sbtest.sbtest1 set c=repeat(M,120) where id<=N

接下來我們看看不同記錄數下對應 Seconds_Behind_Master 的最大值，

記錄數	主庫執行時長(s)	Seconds_Behind_Master最大值(s)
50000	0.76	1
200000	3.10	8
500000	17.32	39
1000000	63.47	122

可見，隨著記錄數的增加，Seconds_Behind_Master 也是不斷增加的，

所以對于大事務操作，建議分而治之，每次小批量執行，

判斷一個 binlog 是否存在大事務，可通過我之前寫的一個 binlog_summary.py 的工具來分析，該工具的具體用法可參考：Binlog分析利器-binlog_summary.py

從庫上有查詢操作

從庫上有查詢操作，通常會有兩方面的影響：

1. 消耗系統資源，

2. 鎖等待，

常見的是從庫的查詢操作堵塞了主庫的 DDL 操作，看下面這個示例，

mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+----------------------------------+----------------------------------------+
| Id | User            | Host            | db   | Command | Time | State                            | Info                                   |
+----+-----------------+-----------------+------+---------+------+----------------------------------+----------------------------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  | 2239 | Waiting on empty queue           | NULL                                   |
| 17 | root            | localhost       | NULL | Query   |    0 | init                             | show processlist                       |
| 18 | root            | localhost       | NULL | Query   |   19 | User sleep                       | select id,sleep(1) from sbtest.sbtest1 |
| 19 | system user     | connecting host | NULL | Connect |  243 | Waiting for source to send event | NULL                                   |
| 20 | system user     |                 |      | Query   |   13 | Waiting for table metadata lock  | alter table sbtest.sbtest1 add c2 int  |
+----+-----------------+-----------------+------+---------+------+----------------------------------+----------------------------------------+
5 rows in set (0.00 sec)

從庫上存在備份

常見的是備份的全域讀鎖阻塞了 SQL 執行緒的重放，看下面這個示例，

mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+----------------------------------+----------------------------------------+
| Id | User            | Host            | db   | Command | Time | State                            | Info                                   |
+----+-----------------+-----------------+------+---------+------+----------------------------------+----------------------------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  | 4177 | Waiting on empty queue           | NULL                                   |
| 17 | root            | localhost       | NULL | Query   |    0 | init                             | show processlist                       |
| 18 | root            | localhost       | NULL | Query   |   36 | User sleep                       | select id,sleep(1) from sbtest.sbtest2 |
| 19 | system user     | connecting host | NULL | Connect | 2181 | Waiting for source to send event | NULL                                   |
| 20 | system user     |                 |      | Query   |    2 | Waiting for global read lock     | alter table sbtest.sbtest1 add c1 int  |
| 28 | root            | localhost       | NULL | Query   |   17 | Waiting for table flush          | flush tables with read lock            |
+----+-----------------+-----------------+------+---------+------+----------------------------------+----------------------------------------+
6 rows in set (0.00 sec)

磁盤 IO 存在瓶頸

這個時候可調整從庫的雙一設定或關閉 binlog，

總結

綜合上面的分析，主從延遲的常見原因及解決方法如下圖所示，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/550513.html

標籤：MySQL

上一篇：8款資料遷移工具選型，主流且實用！

下一篇：MySQL 主從延遲的常見原因及解決方法