目录
一、MySQL8.0之前原生的Seconds_Behind_Master
在MySQL8.0之前我们可以通过 show slave status 提供的 Seconds_Behind_Master来观测主从复制之间的延迟情况，以下是官方文档对该参数的释义
- 该字段记录的是当从库IO和SQL线程正常运行时，
从库当前服务器主机的时间戳与 主库发送过来的binlog日志(relay log)中记录的时间戳的差异 - 如果
没有任何事务执行，该值默认为0 - 如果计算的延迟值为
负数，该值也会重置为0- 如主库是2020年12月01日(1606752000),从库是2020年11月01日(1604160000),则由于从库比主库时间早,转换为时间戳相减得到负数,则会被自动转换为0
- 1604160000-1606752000=-2592000
- 如主库是2020年12月01日(1606752000),从库是2020年11月01日(1604160000),则由于从库比主库时间早,转换为时间戳相减得到负数,则会被自动转换为0
- 当从库没有任何需要处理的更新时，如果I/O和SQL线程状态都为Yes，则此字段显示为0，如果
有任意一个线程状态不为Yes，则此字段显示为NULL - 这个字段是
度量从库SQL线程和I/O线程之间的时间差，单位为秒- 如果主备之间的网络非常快，那么从库的I/O线程读取的主库binlog会与主库中最新的binlog非常接近，所以这样计算得来得值就可以作为主备之间的数据延迟时间
- 如果主备之间的网络非常慢，可能导致从库SQL线程正在重放的主库binlog 非常接近从库I/O线程读取的主库binlog，而I/O线程因为网络慢的原因可能读取的主库binlog远远落后于主库最新的binlog，此时，这么计算得来的值是不可靠的，尽管这个时候有可能该字段显示为0，但实际上可能从库已经落后于主库非常多了。所以，
对于网络比较慢的情况，该值并不可靠
- 如果主库与从库的服务器的时间不一致，那么，只要从库复制线程启动之后，没有做过任何时间变更，那么这个字段的值也可以正常计算，但是如果
修改了服务器的的时间，则可能导致时钟偏移，从而导致这个计算值不可靠- 计算公式：从库服务器时间戳-从库SQL线程正在执行的event的时间戳-主库与从库之间的时间差(该时间差只会在从库I/O线程启动时计算一次,每次重启I/O线程时该值会重新计算)
clock_of_slave - last_timestamp_executed_by_SQL_thread - clock_diff_with_master
- 计算公式：从库服务器时间戳-从库SQL线程正在执行的event的时间戳-主库与从库之间的时间差(该时间差只会在从库I/O线程启动时计算一次,每次重启I/O线程时该值会重新计算)
The difference in seconds between the clock of the master and the clock of
the slave (second - first). It must be signed as it may be <0 or >0.
clock_diff_with_master is computed when the I/O thread starts; for this the
I/O thread does a SELECT UNIX_TIMESTAMP() on the master.
"how late the slave is compared to the master" is computed like this:
clock_of_slave - last_timestamp_executed_by_SQL_thread - clock_diff_with_master
- 当SQL线程重放大事务时，SQL线程的时间戳更新相当于被暂停了,此时，根据计算公式可以得出，无论主库是否有新的数据写入,所以就会出现主库停止写入之后，从库复制延迟逐渐增大到某个最高值之后突然变为0的情况
- 主机时间的修改会直接影响数据库中时间函数获取的时间以及binlog的时间戳
clock_diff_with_master的获取
clock_diff_with_master作用是排除主从时间不同步导致的延迟误差，在IO线程第一次启动时会获取主库的系统时间戳，与从库系统时间戳做对比，在计算主从延迟时会将系统时间的误差减去，从而排除系统时间不同步的干扰得到真正的延迟，但该操作只会在IO线程第一次启动时触发，在IO线程启动后对系统时间做修改则会导致延迟的计算出现误差。
## 从库在执行了start slave io_thread后，主库的general日志中的信息
2020-03-10T10:36:05.394751+08:00 634 Connect repl@10.186.61.22 on using TCP/IP
2020-03-10T10:36:05.395346+08:00 634 Query SELECT UNIX_TIMESTAMP()
2020-03-10T10:36:05.395881+08:00 634 Query SELECT @@GLOBAL.SERVER_ID
2020-03-10T10:36:05.396288+08:00 634 Query SET @master_heartbeat_period= 30000001024
2020-03-10T10:36:05.398045+08:00 634 Query SET @master_binlog_checksum= @@global.binlog_checksum
2020-03-10T10:36:05.398401+08:00 634 Query SELECT @master_binlog_checksum
2020-03-10T10:36:05.398743+08:00 634 Query SELECT @@GLOBAL.GTID_MODE
2020-03-10T10:36:05.399078+08:00 634 Query SELECT @@GLOBAL.SERVER_UUID
2020-03-10T10:36:05.399364+08:00 634 Query SET @slave_uuid= '09235915-5c54-11ea-9666-02000aba3d16'
2020-03-10T10:36:05.400822+08:00 634 Binlog Dump GTID Log: '' Pos: 4 GTIDs: '56929ffe-5d09-11ea-bb4e-02000aba3da2:1-237069'
- 日志显示从库634线程连接到主库后执行了SELECT UNIX_TIMESTAMP()操作来获取系统时间，从库获取这个时间与自己的服务器时间做对比来消除主从服务器时间不一致的问题。
- 该操作只在IO线程启动时触发，如果从库IO线程启动后对主库的时间做了修改，如:将主库时间调慢，则即使主从不存在延迟，Seconds_Behind_Master也仍会记录有一个系统时间差的延迟(
由于主库修改了了系统时间影响了主库中binlog记录的时间戳导致)
Seconds_Behind_Master无法判断主从延迟的场景
- 搭建主从复制，压测部分数据，使用临时iptables断开主库网络(与从库不通即可)
- kill掉主库dump线程
- 观察从库的show slave status是否有变化
- 恢复主库网络，在主库上做压测模拟数据变更
- 查看从库的show slave status是否有变化
## 主库压测部分数据
sysbench /usr/local/share/sysbench/oltp_read_write.lua --db-ps-mode=disable --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sbtest --tables=1 --table-size=10000000 --report-interval=1 --time=600 --threads=1 run
## 主库使用iptables断开与从库的网络连接
#!/bin/bash
iptables -A OUTPUT -d 10.186.61.22 -j DROP
iptables -A INPUT -s 10.186.61.22 -j DROP
sleep 120
iptables -F
## 在主库上kill掉dump线程
## 在这期间主库已经产生大量事务，但从库由于网络中断未接收到binlog日志，Seconds_Behind_Master指标失效，如果10分钟内网络恢复，则IO线程能继续连上主库同步binlog，如果不能则报错主库无法连接
net_retry_count = 10 ## 重试10次
slave_net_timeout = 60 ## 每次60秒超时
dump线程是推数据还是拉数据
MySQL 的复制是“推”的，而不是“拉”的。
- “拉”是指 MySQL 的备库不断的循环询问主库是否有数据更新，这种方式资源消耗多，并且效率低。
- “推”是指 MySQL 的主库在自己有数据更新的时候推送这个变更给备库，这种方式只有在数据有变更的时候才会发生交互，资源消耗少。
- 实际上备库在向主库申请数据变更记录的时候，需要指定从主库Binlog 的哪个文件 ( MASTER_LOG_FILE ) 的具体多少个字节偏移位置 ( MASTER_LOG_POS ),对应的，主库会启动一个 Binlog dump 的线程，将变更的记录从这个位置开始一条一条的发给备库。备库一直监听主库过来的变更，接收到一条，才会在本地应用这个数据变更。
二、Percona pt-heartbeat
pt-heartbeat是percona公司开发用来更为精确的检测主从延迟的小工具，以下是其工作原理介绍
- 首先通过pt-heartbeat在主库创建一张heartbeat表，并以--interval(seconds)参数指定的频率写入timestamp类型数据(heartbeat record)
- 然后在从库回放该记录时，通过与从库的系统时间做比对计算出时间差，得出主从延迟的具体数值，这样当主从延迟或复制中断，延迟值仍会不断增加
参数解释
## 连接相关
--database 指定heartbeat表所在的数据库
--ask-pass 是否通过非命令行的方式输入密码
--host 指定数据库的IP
--password 指定数据库的密码
--port 指定数据库的端口
--socket 指定数据库的套接字文件
--table 指定创建的heartbeat表名称，默认就叫做heartbeat
--user 指定数据库的用户
--charset 指定连接的字符集，默认utf8
## 初始化心跳表
--create-table 指定该参数则自动创建heartbeat表
--create-table-engine 指定heartbeat表的存储引擎，默认InnoDB，也可设置为memory
--check 检测一次从库的延迟后退出,还可指定--recurse找到所有从库延迟
--check-read-only 如果数据库是read-only的，check时不会做任何insert操作
--read-only-interval read-only状态的检测频率
## pt-heartbeat运行相关
--run-time 指定pt-heartbeat工具运行的时间
--stop 可以指定创建哨兵文件来停止pt-heartbeat运行
--sentinel 指定一个哨兵文件，如果没指定--run-time，则如果哨兵文件存
--daemonize 将心跳检测命令后台运行
--file 将最后一条心跳数据输出到文件，可以结合--daemonize使用
--log 当开启--daemonize模式时，将运行日志保存到该参数指定的文件
--interval heartbeat表更新的频率，默认1s
--pid 指定pt-heartbeat工具运行时的pid文件
--config 指定参数配置文件，可以把多个参数指定为一个配置文件
--dbi-driver 默认为mysql，也支持pg
--defaults-file 读取的MySQL连接信息文件，需要提供绝对路径
--frames 显示1m,5m,15m的延迟情况
--[no]insert-heartbeat-row 是否默认对heartbeat表插入一条心跳数据，默认yes
--master-server-id 明确指定主库的server-id
--monitor 持续监测主从的数据延迟
--print-master-server-id 在监测输出时一同打印主库的server-id
--recurse 递归的方式检测关联的所有从库
--recursion-method 递归的方式：processlist或hosts,[show processlist|show slave status]
--replace 对heartbeat表不使用普通的update，而是用replace 方式替换数据
--update 对heartbeat表使用普通的update更新
--slave-user 如果从库有不同的账户时明确指定
--slave-password 如果从库有不同的密码时明确指定
--set-vars 设置pt-heartbeat连接MySQL时session级别的变量
--skew 延迟检测的频率，默认0.5s
使用示例
## 连接主库在percona数据库中创建heartbeat表并将延迟检测的程序后台运行
## 每秒钟对heartbeat表做replace更新操作
pt-heartbeat --host=10.186.61.162 --user=zhenxing --port=3306 --password=zhenxing --database=percona --table=heartbeat --create-table --update --replace --daemonize --log=/tmp/pt-heartbeat.log --interval=1
## 连接从库实时检测主从延迟情况
pt-heartbeat --host=10.186.61.22 --user=zhenxing --port=3306 --password=zhenxing --database=percona --table=heartbeat --print-master-server-id --monitor
heartbeat表相关信息
-- 表结构
-- 其中relay_master_log_file和exec_master_log_pos字段通过show slave status获取，且只有数据库既是主又是从时才有值，也就是级联复制的场景
root@localhost[percona]> desc heartbeat;
+-----------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------+-------+
| ts | varchar(26) | NO | | NULL | |
| server_id | int(10) unsigned | NO | PRI | NULL | |
| file | varchar(255) | YES | | NULL | |
| position | bigint(20) unsigned | YES | | NULL | |
| relay_master_log_file | varchar(255) | YES | | NULL | |
| exec_master_log_pos | bigint(20) unsigned | YES | | NULL | |
+-----------------------+---------------------+------+-----+---------+-------+
-- 数据示例
root@localhost[percona]> select * from heartbeatG
*************************** 1. row ***************************
ts: 2020-03-10T16:54:50.001070
server_id: 33
file: mysql-bin.000058
position: 218470087
relay_master_log_file: NULL
exec_master_log_pos: NULL
1 row in set (0.00 sec)
-- replace方式更新语句
REPLACE INTO `percona`.`heartbeat` (ts, server_id, file, position, relay_master_log_file, exec_master_log_pos) VALUES ('2020-03-10T16:55:42.001460', '33', 'mysql-bin.000058', '218500663', NULL, NULL)
-- update方式更新语句
UPDATE `percona`.`heartbeat` SET ts='2020-03-10T16:56:50.001400', file='mysql-bin.000058', position='218534058', relay_master_log_file=NULL, exec_master_log_pos=NULL WHERE server_id='33'
三、MySQL8.0之后P_S库下replication相关状态表
WL#7319 和 WL#7374 共同完善了复制延迟观测
WL#7319 Infrastructure for GTID based delayed replication and replication lag monitoring 在binlog 的 gtid_log_event （启用 GTID）和 anonymous_gtid_log_event（未启用 GTID）新增事务提交时间戳。将事务原始提交时间写在 binlog 中，提交时间在复制链路上传递，使得 slave 可以计算事务延迟。
- original_commit_timestamp 事务在 master 提交 binlog 的时间戳（微秒），该时间戳每个节点都是一样的。
- immediate_commit_timestamp 事务在 slave（包括中继节点）提交 binlog 的时间戳（微秒），该时间戳在 relay log 中与 original_commit_timestamp 一样，在 slave 的 binlog 是完成回放的时间戳。
WL#7374 Performance schema tables to monitor replication lags and queue 为 performance_schema 复制相关表新增观测点。
- replication_connection_status 记录事件接收线程（IO Thread）工作状态
- replication_applier_status_by_coordinator 记录启用并行回放的协调线程工作状态
- replication_applier_status_by_worker 记录事件回放线程（SQL Thread）工作状态
-- 查看从库回放与主库事务提交之间的延迟(也就是主从延迟)
-- 实际如果主库没数据产生，该值计算会有偏差
SELECT WORKER_ID,THREAD_ID,SERVICE_STATE,LAST_ERROR_NUMBER,LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP - LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP as MasterToSlaveDelayed,LAST_ERROR_MESSAGE FROM performance_schema.replication_applier_status_by_worker;
四、总结对比
Seconds_Behind_Master
- 并不能反应真实的业务事务同步或回放延迟。
- 在网络异常时延迟的数据不准确
pt-heartbeat
- 观测粒度只能达到秒级，精度不够
- 心跳进程单点风险，心跳进程不可用则延迟检测失效
- 污染 binlog，大量心跳事件占据 binlog，更多空间占用，干扰排查和日志恢复
MySQL8.0 P_S库replication相关表
- 粒度更细，支持微妙级别
- 支持不同阶段的延迟检测
- 可以检测IO进程是否存在延迟
- 可以检测调度进程是否存在延迟
- 可以检测SQL进程是否存在延迟
- 可以总体检测主库事务提交到从库SQL线程回放之间的延迟
五、参考链接
Seconds_Behind_Master官方文档解释
pt-heartbeat官方使用手册
MySQL8.0 对performance_schema复制相关表的解释
- https://dev.mysql.com/doc/refman/8.0/en/replication-connection-status-table.html
- https://dev.mysql.com/doc/refman/8.0/en/replication-applier-status-by-coordinator-table.html
- https://dev.mysql.com/doc/refman/8.0/en/replication-applier-status-by-worker-table.html
其他参考链接