分析数据库Seconds

共计 2414 个字符，预计需要花费 7 分钟才能阅读完成。

本篇内容主要讲解“分析数据库 Seconds_Behind_Master 延迟的原因”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“分析数据库 Seconds_Behind_Master 延迟的原因”吧!

一、总结（1）第一类：

这一类延迟情况可能造成服务器有较高的负载，可能是 CPU/IO 的负载。因为从库在实际执行 Event，如果我们服务器的负载比较高应该考虑这几种情况。

大事务造成的延迟，其延迟会不会从 0 开始增加，而是直接从主库执行了多久开始。比如主库执行这个事务花费的 20 秒，要么延迟就会从 20 开始，可以自己细心观察一下很容易看到。这是因为 Query Event 中没有准确的执行时间。

大表 DDL 造成的延迟，其延迟会从 0 开始增加，因为 Query Event 记录了准确的执行时间

表没有合理的使用主键或者唯一键造成的延迟。这种情况不要以为设置 slave_rows_search_algorithms 参数为 INDEX_SCAN,HASH_SCAN 就可以完全解决问题。

由于参数 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 操作但是从库上做了锁表操作造成。

MTS 中不合理的设置参数 slave_checkpoint_period 参数导致。

在从库运行期间手动改大了从库服务器时间。

二、相关测试

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

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

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

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

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

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

我们可以通过 state 看到这是等待 MDL lock 获取而导致的延迟

三、总结

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

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

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

到此，相信大家对“分析数据库 Seconds_Behind_Master 延迟的原因”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！