MySQL的从库Seconds_Behind_Master延迟总结

MySQL从库Seconds_Behind_Master延迟总结

一、延迟分类

延迟我们可将其分为两类：

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&gt; begin;Query OK, 0 rows affected (0.00 sec) mysql&gt; delete from tmpk;Query OK, 4 rows affected (0.00 sec)不要提交 主库执行同样的语句mysql&gt; delete from tmpk;Query OK, 4 rows affected (0.30 sec)

如果查看sys.innodb_lock_waits能看到如下的结果：

当然如果查看INNODB_TRX也可以观察到事务的存在，这里就不截图了，大家可以自己试试。

2、MySQL层的MDL LOCK造成的延迟

这种情况也非常容易测试，我们只需要开启一个事务做一个select，然后主库对同样的表做DDL就可以出现如下：

从库：mysql&gt; begin;Query OK, 0 rows affected (0.00 sec) mysql&gt;mysql&gt;mysql&gt; select * from tkkk limit 1;+------+------+------+| a    | b    | c    |+------+------+------+|    3 |    3 |  100 |+------+------+------+1 row in set (0.00 sec) 不要提交，表上MDL LOCK就不会释放 主库执行语句： mysql&gt; alter table tmpk add testc int ;Query OK, 0 rows affected (1.14 sec)Records: 0  Duplicates: 0  Warnings: 0

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

https://www.jb51.net/article/221412.htm

三、总结

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

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

https://www.jb51.net/article/221396.htm

我们查看CPU负载应该使用top -H去查看，查看io负载可以使用iotop，iostat等工具。我需要强调一下看MySQL负载的时候我们必须用线程的眼光去看