mysql怎么监控延迟 mysql延迟查询

如何检查MySQL数据库的主从延时？

MySQL数据库主从延时如何去判断呢？本文我们介绍了两种判断方法：1. Seconds_Behind_Master vs 2. mk-heartbeat，接下来我们就分别介绍这些内容。 日常工作中，对于MySQL主从复制检查，一方面我们要保证复制的整体结构是否正常，另一方面需要检查主从数据是否保持一致。对于前者我们可以通过监控复制线程是否工作正常以及主从延时是否在容忍范围内，对于后者则可以通过分别校验主从表中数据的md5码是否一致，来保证数据一致，可以使用Maatkit工具包中的mk-table- checksum工具去检查。 方法1： 通过监控show slave status\G命令输出的Seconds_Behind_Master参数的值来判断，是否有发生主从延时。其值有这么几种： NULL — 表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No，而非Yes。 0 — 该值为零，是我们极为渴望看到的情况，表示主从复制良好，可以认为lag不存在。 正值— 表示主从已经出现延时，数字越大表示从库落后主库越多。 负值— 几乎很少见，我只是听一些资深的DBA说见过，其实，这是一个BUG值，该参数是不支持负值的，也就是不应该出现。 show slave status\G，该命令的输出结果非常丰厚，给我们的监控提供了很多有意义的参数，比如：Slave_IO_Running该参数可作为 io_thread的监控项，Yes表示io_thread的和主库连接正常并能实施复制工作，No则说明与主库通讯异常，多数情况是由主从间网络引起的问题；Slave_SQL_Running该参数代表sql_thread是否正常，具体就是语句是否执行通过，常会遇到主键重复或是某个表不存在。下面就说到今天的重点Seconds_Behind_Master，该值作为判断主从延时的指标，那么它又是怎么得到这个值的呢，同时，它为什么又受到很多人的质疑？ Seconds_Behind_Master是通过比较sql_thread执行的event的timestamp和 io_thread复制好的event的timestamp（简写为ts）进行比较，而得到的这么一个差值。我们都知道的relay-log和主库的 bin-log里面的内容完全一样，在记录sql语句的同时会被记录上当时的ts，所以比较参考的值来自于binlog，其实主从没有必要与NTP进行同步，也就是说无需保证主从时钟的一致。 你也会发现，其实比较真正是发生在io_thread与sql_thread之间，而io_thread才真正与主库有关联，于是，问题就出来了，当主库I/O负载很大或是网络阻塞，io_thread不能及时复制binlog（没有中断，也在复制），而 sql_thread一直都能跟上io_thread的脚本，这时Seconds_Behind_Master的值是0，也就是我们认为的无延时，但是，实际上不是，你懂得。这也就是为什么大家要批判用这个参数来监控数据库是否发生延时不准的原因，但是这个值并不是总是不准，如果当io_thread与 master网络很好的情况下，那么该值也是很有价值的。 之前，提到Seconds_Behind_Master这个参数会有负值出现，我们已经知道该值是io_thread的最近跟新的ts与sql_thread执行到的ts差值，前者始终是大于后者的，唯一的肯能就是某个event的ts发生了错误，比之前的小了，那么当这种情况发生时，负值出现就成为可能。 方法2： mk-heartbeat，Maatkit万能工具包中的一个工具，被认为可以准确判断复制延时的方法。 mk-heartbeat的实现也是借助timestmp的比较实现的，它首先需要保证主从服务器必须要保持一致，通过与相同的一个NTP server同步时钟。它需要在主库上创建一个heartbeat的表，里面至少有id与ts两个字段，id为server_id，ts就是当前的时间戳 now()，该结构也会被复制到从库上。 表建好以后，会在主库上以后台进程的模式去执行一行更新操作的命令，定期去向表中的插入数据，这个周期默认为1 秒，同时从库也会在后台执行一个监控命令，与主库保持一致的周期去比较，复制过来记录的ts值与主库上的同一条ts值，差值为0表示无延时，差值越大表示延时的秒数越多。 我们都知道复制是异步的ts不肯完全一致，所以该工具允许半秒的差距，在这之内的差异都可忽略认为无延时。这个工具就是通过实打实的复制，巧妙的借用timestamp来检查延时，非常好用！ 关于检查MySQL数据库的主从延时的两种方法就介绍到这里了，希望本次的介绍能够对您有所收获！

个旧ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为创新互联公司的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：028-86922220（备注：SSL证书合作）期待与您的合作！

MySQL主从延迟Seconds_Behind_Master计算方式

这样计算的问题：

1、 首先这样的计算方法本身是不对的，如果本地时间有问题会导致event时间戳不准，从而出现误差，所以计算公式中会记录一下主从库的当前时间差

公式如下：

注意：这个时间差只会在主从线程启动时计算一次，所以start slave后如果主从本地时间出现异常，Seconds_Behind_Master也是不准的，需要重启复制线程重新计算主从本地时间差异（如果最终计算结果是负数，会归零）

2、 如果IO线程出现延迟，此时这个值是有误差的，Seconds_Behind_Master可能显示为0，但实际和主库是有延迟的，容易出现误差

提示：所以只拿这个值来做复制延迟的监控是不准的，一般都会配合心跳表的时间戳来判断当前延迟

每次进行show slave status时都会进行一次计算

源码如下：

这里可以看到判断SQL线程是否应用所有的event

注意：此时如果IO线程有延迟则即使Seconds_Behind_Master=0但是还是有延迟

关键词解释

单线程复制和并行复制对于last_master_timestamp这个的计算也是不同的

ev-when.tv_sec表示事件的开始时间。exec_time指事件在主库的执行时间,GTID_EVENT和XID_EVENT才会统计exec_time

总结：事务执行中的event时间都是事务开始时begin的时间戳，GTID_EVENT和XID_EVENT则是事务提交的时间，所以如果存在事务长时间未提交就会出现延迟突然增大然后正常的情况

先了解一下并行复制的流程

这导致并行复制和单线程复制之间复制延迟会有差异，差异是slave_checkpoint_period + 事务在备库执行的时间，这就是有时单线程无延迟，换到并行复制反而有轻微延迟的原因

另外DDL的时间戳计算其实是事务执行时间+事务开始时间

本文主要讲述了seconds_behind_master在不同模式下的计算方式，很多情况会导致这个参数不准确，所以也建议大家还是结合心跳表配合监控延迟比较准确,如有理解偏差欢迎随时指正

本文参考：

MySQL 主从，5 分钟带你掌握

MySQL 主从一直是面试常客，里面的知识点虽然基础，但是能回答全的同学不多。

比如楼哥之前面试小米，就被问到过主从复制的原理，以及主从延迟的解决方案，因为回答的非常不错，给面试官留下非常好的印象。你之前面试，有遇到过哪些 MySQL 主从的问题呢？

所谓 MySQL 主从，就是建立两个完全一样的数据库，一个是主库，一个是从库， 主库对外提供读写的操作，从库对外提供读的操作 ，下面是一主一从模式：

对于数据库单机部署，在 4 核 8G 的机器上运行 MySQL 5.7 时，大概可以支撑 500 的 TPS 和 10000 的 QPS， 当遇到一些活动时，查询流量骤然，就需要进行主从分离。

大部分系统的访问模型是读多写少，读写请求量的差距可能达到几个数量级，所以我们可以通过一主多从的方式， 主库只负责写入和部分核心逻辑的查询，多个从库只负责查询，提升查询性能，降低主库压力。

MySQL 主从还能做到服务高可用，当主库宕机时，从库可以切成主库，保证服务的高可用，然后主库也可以做数据的容灾备份。

整体场景总结如下：

MySQL 的主从复制是依赖于 binlog 的，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上二进制日志文件。

主从复制就是将 binlog 中的数据从主库传输到从库上，一般这个过程是异步的，即主库上的操作不会等待 binlog 同步的完成。

详细流程如下：

当主库和从库数据同步时，突然中断怎么办？因为主库与从库之间维持了一个长链接，主库内部有一个线程，专门服务于从库的这个长链接的。

对于下面的情况，假如主库执行如下 SQL，其中 a 和 create_time 都是索引：

我们知道，数据选择了 a 索引和选择 create_time 索引，最后 limit 1 出来的数据一般是不一样的。

所以就会存在这种情况：在 binlog = statement 格式时，主库在执行这条 SQL 时，使用的是索引 a，而从库在执行这条 SQL 时，使用了索引 create_time，最后主从数据不一致了。

那么我们改如何解决呢？

可以把 binlog 格式修改为 row，row 格式的 binlog 日志记录的不是 SQL 原文，而是两个 event:Table_map 和 Delete_rows。

Table_map event 说明要操作的表，Delete_rows event用于定义要删除的行为，记录删除的具体行数。 row 格式的 binlog 记录的就是要删除的主键 ID 信息，因此不会出现主从不一致的问题。

但是如果 SQL 删除 10 万行数据，使用 row 格式就会很占空间的，10 万条数据都在 binlog 里面，写 binlog 的时候也很耗 IO。但是 statement 格式的 binlog 可能会导致数据不一致。

设计 MySQL 的大叔想了一个折中的方案，mixed 格式的 binlog，其实就是 row 和 statement 格式混合使用， 当 MySQL 判断可能数据不一致时，就用 row 格式，否则使用就用 statement 格式。

有时候我们遇到从数据库中获取不到信息的诡异问题时，会纠结于代码中是否有一些逻辑会把之前写入的内容删除，但是你又会发现，过了一段时间再去查询时又可以读到数据了，这基本上就是主从延迟在作怪。

主从延迟，其实就是“从库回放” 完成的时间，与 “主库写 binlog” 完成时间的差值， 会导致从库查询的数据，和主库的不一致 。

谈到 MySQL 数据库主从同步延迟原理，得从 MySQL 的主从复制原理说起：

总结一下主从延迟的主要原因 ：主从延迟主要是出现在 “relay log 回放” 这一步，当主库的 TPS 并发较高，产生的 DDL 数量超过从库一个 SQL 线程所能承受的范围，那么延时就产生了，当然还有就是可能与从库的大型 query 语句产生了锁等待。

我们一般会把从库落后的时间作为一个重点的数据库指标做监控和报警，正常的时间是在毫秒级别，一旦落后的时间达到了秒级别就需要告警了。

解决该问题的方法，除了缩短主从延迟的时间，还有一些其它的方法，基本原理都是尽量不查询从库。

具体解决方案如下：

在实际应用场景中，对于一些非常核心的场景，比如库存，支付订单等，需要直接查询从库，其它非核心场景，就不要去查主库了。

两台机器 A 和 B，A 为主库，负责读写，B 为从库，负责读数据。

如果 A 库发生故障，B 库成为主库负责读写，修复故障后，A 成为从库，主库 B 同步数据到从库 A。

一台主库多台从库，A 为主库，负责读写，B、C、D为从库，负责读数据。

如果 A 库发生故障，B 库成为主库负责读写，C、D负责读，修复故障后，A 也成为从库，主库 B 同步数据到从库 A。

如何监控MySQL

首先介绍下 pt-stalk，它是 Percona-Toolkit 工具包中的一个工具，说起 PT 工具包大家都不陌生，平时常用的 pt-query-digest、 pt-online-schema-change 等工具都是出自于这个工具包，这里就不多介绍了。

pt-stalk 的主要功能是在出现问题时收集 OS 及 MySQL 的诊断信息，这其中包括：

1. OS 层面的 CPU、IO、内存、磁盘、网络等信息；

2. MySQL 层面的行锁等待、会话连接、主从复制，状态参数等信息。

而且 pt-stalk 是一个 Shell脚本，对于我这种看不懂 perl 的人来说比较友好，脚本里面的监控逻辑与监控命令也可以拿来参考，用于构建自己的监控体系。

三、使用

接着我们来看下如何使用这个工具。

pt-stalk 通常以后台服务形式监控 MySQL 并等待触发条件，当触发条件时收集相关诊断数据。

触发条件相关的参数有以下几个：

function：

∘ 默认为 status，代表监控 SHOW GLOBAL STATUS 的输出；

∘ 也可以设置为 processlist，代表监控 show processlist 的输出；

variable：

∘ 默认为 Threads_running，代表 监控参数，根据上述监控输出指定具体的监控项；

threshold：

∘ 默认为 25，代表 监控阈值，监控参数超过阈值，则满足触发条件；

∘ 监控参数的值非数字时，需要配合 match 参数一起使用，如 processlist 的 state 列；

cycles：

∘ 默认为 5，表示连续观察到五次满足触发条件时，才触发收集；

连接参数：host、password、port、socket。

其他一些重要参数：

iterations：该参数指定 pt-stalk 在触发收集几次后退出，默认会一直运行。

run-time：触发收集后，该参数指定收集多长时间的数据，默认 30 秒。

sleep：该参数指定在触发收集后，sleep 多久后继续监控，默认 300 秒。

interval：指定状态参数的检查频率，判断是否需要触发收集，默认 1 秒。

dest：监控数据存放路径，默认为 /var/lib/pt-stalk。

retention-time ：监控数据保留时长，默认 30 天。

daemonize：以后台服务运行，默认不开启。

log：后台运行日志，默认为 /var/log/pt-stalk.log。

collect：触发发生时收集诊断数据，默认开启。

∘ collect-gdb：收集 GDB 堆栈跟踪，需要 gdb 工具。

∘ collect-strace：收集跟踪数据，需要 strace 工具。

∘ collect-tcpdump：收集 tcpdump 数据，需要 tcpdump 工具。

如何监控mysql主从之间的延迟

　可以通过监控show slave status\G命令输出的Seconds_Behind_Master参数的值来判断，是否有发生主从延时。

其值有这么几种：

NULL - 表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No,而非Yes.

0 - 该值为零，是我们极为渴望看到的情况，表示主从复制良好，可以认为lag不存在。

正值 - 表示主从已经出现延时，数字越大表示从库落后主库越多。

负值 - 几乎很少见，只是听一些资深的DBA说见过，其实，这是一个BUG值，该参数是不支持负值的，也就是不应该出现。

zabbix 如何监控 mysql 主从同步 延迟差多少时间

正值 - 表示主从已经出现延时.

0 - 该值为零，是我们极为渴望看到的情况，也就是该线程的Running状态是No，这是一个BUG值，该参数是不支持负值的可以通过监控show slave status\G命令输出的Seconds_Behind_Master参数的值来判断，表示主从复制良好，可以认为lag不存在，是否有发生主从延时。

其值有这么几种，数字越大表示从库落后主库越多。

负值 - 几乎很少见：

NULL - 表示io_thread或是sql_thread有任何一个发生故障,而非Yes，只是听一些资深的DBA说见过，其实