---

注意：本文是《深入理解MySQL主從原理 32節》的第28節

訂閱參考：
https://www.jianshu.com/p/d636215d767f

---

到這裡本系列已經接近尾聲了，是時候對常見引起主從延遲的情形進行一個總結了。我想如果我一開始就把這些情形拿出來也許大家對具體的原因不是那麼清楚，但是經過本系列的學習，我相信當我說起這些情形的時候大家都很清楚它的原因了。當然如果還有其他造成延遲的情形也歡迎大家一起討論。

###一、總結

有了前面的知識我們就能夠從本質上了解造成延遲的可能有哪些，我先來總結一下這些可能，我將其分為兩類：

#####（1）第一類：

這一類延遲情況可能造成伺服器有較高的負載，可能是CPU/IO的負載。因為從庫在實際執行Event，如果我們伺服器的負載比較高應該考慮這幾種情況，關於如何檢視執行緒的負載可以參考29節。

- 大事務造成的延遲，其延遲會不會從0開始增加，而是直接從主庫執行了多久開始。比如主庫執行這個事務花費的20秒，要麼延遲就會從20開始，可以自己細心觀察一下很容易看到。這是因為Query Event中沒有準確的執行時間，這個在上一節的計算公式中詳細描述過了 ，可以參考第8節和第27節。

- 大表DDL造成的延遲，其延遲會從0開始增加，因為Query Event記錄了準確的執行時間。這個在上一節的計算公式中也詳細描述過了，可以參考第8節和第27節。

- 表沒有合理的使用主鍵或者唯一鍵造成的延遲。這種情況不要以為設定slave\_rows\_search\_algorithms引數為 INDEX\_SCAN,HASH\_SCAN就可以完全解決問題，原因我們在第24節進行了描述。

- 由於引數sync\_relay\_log，sync\_master\_info，sync\_relay\_log\_info不合理導致，特別是sync\_relay\_log會極大的影響從庫的效能。原因我們在第26節進行過描述，因為sync\_relay\_log設定為1會導致大量relay log刷盤操作。

- 是否從庫開啟了記錄binary log功能即log\_slave\_updates引數開啟，如果不是必要可以關閉掉。這種情況我遇到很多次了。

#####（2）第二類：

這一類延遲情況往往不會造成伺服器有較高的負載。它們要麼沒有實際的執行Event，要麼就是做了特殊的操作造成的。

- 長期未提交的事務可能造成延遲瞬間增加，因為GTID\_EVENT和XID\_EVENT是提交時間其他Event是命令發起的時間。這個我們在第27節中舉例描述過了。

- Innodb層的行鎖造成的延遲，這種是在從庫有修改操作並且和SQL執行緒修改的資料有衝突的情況下造成的，因為我們前面23節說過SQL執行緒執行Event也會開啟事務和獲取行鎖，下面我們進行測試。

- MySQL層的MDL LOCK造成的延遲，這種情況可能是由於SQL執行緒執行某些DDL操作但是從庫上做了鎖表操作造成，原因我們已經在23節描述過了，下面我們進行測試。

- MTS中不合理的設定引數slave\_checkpoint\_period引數導致，這個在第27節已經測試過了。

- 在從庫執行期間手動改大了從庫伺服器時間，這個也在第27節已經測試過了。

更多主從同步相關可以參考我的《深入理解MySQL主從原理 32節》專欄：

![KTA2LA6$`_9Z0B2~P)FULPL.png](https://upload-images.jianshu.io/upload_images/7398834-fb074567e6620f57.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

###二、相關測試

因為上面的延遲情形很多我們都已經測試和講述過了。下面我們測試鎖造成的延遲情形。

#####（1）Innodb層的行鎖造成的延遲

這個很容測試，我只要先在從庫做一個事務和SQL執行緒修改的資料相同即可以出現，大概測試如下：

```
從庫：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from tmpk;
Query OK, 4 rows affected (0.00 sec)
不要提交

主庫執行同樣的語句
mysql> delete from tmpk;
Query OK, 4 rows affected (0.30 sec)
```

這個時候你會觀察到延遲如下：

![image.png](https://upload-images.jianshu.io/upload_images/7398834-c59fa473edc510fa.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

如果檢視sys.innodb\_lock\_waits能看到如下的結果：

![image.png](https://upload-images.jianshu.io/upload_images/7398834-05411391149f6bc4.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

當然如果檢視INNODB\_TRX也可以觀察到事務的存在，這裡就不截圖了，大家可以自己試試。

######（2）MySQL層的MDL LOCK造成的延遲

這種情況也非常容易測試，我們只需要開啟一個事務做一個select，然後主庫對同樣的表做DDL就可以出現如下：

```
從庫：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> select * from tkkk limit 1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    3 |    3 |  100 |
+------+------+------+
1 row in set (0.00 sec)

不要提交，表上MDL LOCK就不會釋放

主庫執行語句：

mysql> alter table tmpk add testc int ;
Query OK, 0 rows affected (1.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

```

這個時候你將會看到如下的資訊：

![image.png](https://upload-images.jianshu.io/upload_images/7398834-5fff17d4bcb14241.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

我們可以透過state看到這是等待MDL lock獲取而導致的延遲，關於MDL lock的詳情可以參考我的文章：

http://blog.itpub.net/7728585/viewspace-2143093/

###三、總結

透過整個系列，我們應該清楚了Seconds\_Behind\_Master計算的方法，同時如果出現了延遲，我們首先檢視從庫是否有負載，根據是否有負載進行區別對待，注意這裡的負載一定要使用**top -H**檢視io/sql/woker執行緒的負載。我曾不止一次的遇到朋友問我延遲問題，當我問他負載如何的時候他告訴我負載不高啊整體負載也就不到2，這裡我們應該注意的是對於一個執行緒只能使用到一個CPU核，雖然整體負載不到2但是可能io/sql/worker執行緒已經跑滿了，實際上負載已經很高了，我們來看下面的這個截圖就是sql執行緒負載高的截圖如下：

![image.png](https://upload-images.jianshu.io/upload_images/7398834-da06248b603ab097.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

這個截圖我們發現雖然整體負載不高在1多一點，但是Lwp號20092的執行緒已經跑滿了，這個執行緒就是我們的sql執行緒，這個時候出現延遲是很可能的，這個截圖正是來自一個沒有合理使用主鍵或者唯一鍵造成的延遲的案例，案例如下：

https://www.jianshu.com/p/56e8ca2223a0

我們檢視CPU負載應該使用**top -H**去檢視，檢視io負載可以使用iotop，iostat等工具。我需要強調一下看MySQL負載的時候我們必須用**執行緒**的眼光去看，第29節將讓你獲得這種能力。

到這裡整個系列接近尾聲，大家會發現主從的原理的還是比較複雜的，這可能顛覆了以前我們的認知，以前我們認為主從無非就是搭建起來能跑同時知道有io/sql執行緒就可以了（這確實很簡單）。整個系列結論很簡單，我們無非就是想配置出安全高效的從庫同時知道延遲是怎麼導致的，出現延遲後我們如何處理，我自認為本系列還是將這些問題講解得很清楚了。當然如果本系列的原理部分都能夠理解得很好，那麼工作中解決主從問題一定會更加得心應手。

---

第28節結束

第28節從庫Seconds_Behind_Master延遲總結

相關文章

第28節 從庫Seconds_Behind_Master延遲總結

相關文章

第28節從庫Seconds_Behind_Master延遲總結