从库Seconds_Behind_Master延迟总结

向右看齐 2024-04-18 10:04 19阅读 0赞

导读：

> 本文节选自八怪专栏《深入理解MySQL主从原理32讲》第28节

**想阅读更多内容请点击订阅专栏**

![640?wx\_fmt=png][640_wx_fmt_png]

**注意：****如果正文有图片不清晰可以将图片保存到本地查看****（本文建议横屏观看效果更佳）**

--------------------

到这里本系列已经接近尾声了，是时候对常见引起主从延迟的情形进行一个总结了。我想如果我一开始就把这些情形拿出来也许大家对具体的原因不是那么清楚，但是经过本系列的学习，我相信当我说起这些情形的时候大家都很清楚它的原因了。当然如果还有其他造成延迟的情形也欢迎大家一起讨论。

### 一、总结 ###

有了前面的知识我们就能够从本质上了解造成延迟的可能有哪些，我先来总结一下这些可能，我将其分为两类：

##### （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节已经测试过了。

### 二、相关测试 ###

因为上面的延迟情形很多我们都已经测试和讲述过了。下面我们测试锁造成的延迟情形。

##### （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)

这个时候你会观察到延迟如下：

![640?wx\_fmt=png][640_wx_fmt_png 1]

如果查看sys.innodb\_lock\_waits能看到如下的结果：

![640?wx\_fmt=png][640_wx_fmt_png 2]

当然如果查看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

这个时候你将会看到如下的信息：

![640?wx\_fmt=png][640_wx_fmt_png 3]

我们可以通过state看到这是等待MDL lock获取而导致的延迟，关于MDL lock的详情可以参考我的文章：

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

### 三、总结 ###

通过整个系列，我们应该清楚了Seconds\_Behind\_Master计算的方法，同时如果出现了延迟，我们首先查看从库是否有负载，根据是否有负载进行区别对待，注意这里的负载一定要使用**top -H**查看io/sql/worker线程的负载。我曾不止一次的遇到朋友问我延迟问题，当我问他负载如何的时候他告诉我负载不高啊整体负载也就不到2，这里我们应该注意的是对于一个线程只能使用到一个CPU核，虽然整体负载不到2但是可能io/sql/worker线程已经跑满了，实际上负载已经很高了，我们来看下面的这个截图就是sql线程负载高的截图如下：

![640?wx\_fmt=png][640_wx_fmt_png 4]

这个截图我们发现虽然整体负载不高在1多一点，但是Lwp号20092的线程已经跑满了，这个线程就是我们的sql线程，这个时候出现延迟是很可能的，这个截图正是来自一个没有合理使用主键或者唯一键造成的延迟的案例，案例如下：

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

我们查看CPU负载应该使用**top -H**去查看，查看io负载可以使用iotop，iostat等工具。我需要强调一下看MySQL负载的时候我们必须用**线程**的眼光去看，第29节将让你获得这种能力。

到这里整个系列接近尾声，大家会发现主从的原理的还是比较复杂的，这可能颠覆了以前我们的认知，以前我们认为主从无非就是搭建起来能跑同时知道有io/sql线程就可以了（这确实很简单）。整个系列结论很简单，我们无非就是想配置出安全高效的从库同时知道延迟是怎么导致的，出现延迟后我们如何处理，我自认为本系列还是将这些问题讲解得很清楚了。当然如果本系列的原理部分都能够理解得很好，那么工作中解决主从问题一定会更加得心应手。

**识别下方二维码添加作者为好友**

![640?wx\_fmt=png][640_wx_fmt_png 5]

END

--------------------

**点击下图小程序订阅**

**《深入理解MySQL主从原理32讲》专栏**

**可了解更多八怪技术文章**

![640?wx\_fmt=png][640_wx_fmt_png]

***![640?wx\_fmt=png][640_wx_fmt_png 6]***

![640?wx\_fmt=gif][640_wx_fmt_gif]

扫码加入MySQL技术Q群

**（群号：****793818397）**

![640?wx\_fmt=png][640_wx_fmt_png 7]

[640_wx_fmt_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/fbbf35f2356c4ad3af23940bd1b2d897.png
[640_wx_fmt_png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/ee98e7a597da48fda1c99176092e5857.png
[640_wx_fmt_png 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/e75f7af70933473c95335e5cd15ef31c.png
[640_wx_fmt_png 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/bc01bb3500024ca89ecb81a2e7994586.png
[640_wx_fmt_png 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/03200dd36cf8463a9fd1d9f4f864fb36.png
[640_wx_fmt_png 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/86137f2d3f664ad5ac54978ea1ef41fb.png
[640_wx_fmt_png 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/9966f3cc28044842b8bde3d88aa7f65b.png
[640_wx_fmt_gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/37d6719426e047b59db392674cb9c674.gif
[640_wx_fmt_png 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/24a72da6759a4d739083b0efdac2ac9e.png