6. 主从复制延迟的原因
1.查看主从延迟
一般我们通过在从库上执行show slave status\G; 然后看里面有一个 Seconds_Behind_Master指标,该指标表示此时从库落后主库多少秒
2.Second_Behind_Master指标含义
This field is an indication of how “late” the replica is:
-
When the replica is actively processing updates, this field shows the difference between the current timestamp on the replica and the original timestamp logged on the source for the event currently being processed on the replica.
-
When no event is currently being processed on the replica, this value is 0.
In essence, this field measures the time difference in seconds between the replica SQL thread and the replica I/O thread. If the network connection between source and replica is fast, the replica I/O thread is very close to the source, so this field is a good approximation of how late the replica SQL thread is compared to the source. If the network is slow, this is not a good approximation; the replica SQL thread may quite often be caught up with the slow-reading replica I/O thread, so Seconds_Behind_Master
often shows a value of 0, even if the I/O thread is late compared to the source. In other words, this column is useful only for fast networks.
This time difference computation works even if the source and replica do not have identical clock times, provided that the difference, computed when the replica I/O thread starts, remains constant from then on. Any changes—including NTP updates—can lead to clock skews that can make calculation of Seconds_Behind_Master
less reliable.
In MySQL 5.7, this field is NULL
(undefined or unknown) if the replica SQL thread is not running, or if the SQL thread has consumed all of the relay log and the replica I/O thread is not running. (In older versions of MySQL, this field was NULL
if the replica SQL thread or the replica I/O thread was not running or was not connected to the source.) If the I/O thread is running but the relay log is exhausted, Seconds_Behind_Master
is set to 0.
The value of Seconds_Behind_Master
is based on the timestamps stored in events, which are preserved through replication. This means that if a source M1 is itself a replica of M0, any event from M1's binary log that originates from M0's binary log has M0's timestamp for that event. This enables MySQL to replicate TIMESTAMP
successfully. However, the problem for Seconds_Behind_Master
is that if M1 also receives direct updates from clients, the Seconds_Behind_Master
value randomly fluctuates because sometimes the last event from M1 originates from M0 and sometimes is the result of a direct update on M1.
When using a multithreaded replica, you should keep in mind that this value is based on Exec_Master_Log_Pos
, and so may not reflect the position of the most recently committed transaction.
注意:对于主从节点网络速度良好的情况下,该参数是有意义的,如果两个节点的网速很慢,该节点会一直是0,因此无任何意义。
3.为啥出现了主从延迟?
参考我之前写的博客:https://www.cnblogs.com/zmc60/p/14526252.html
4.到底怎样有效地监控主从之间的延迟?
这里建议使用percona-toolkit中pt-heartbeat
pt-heartbeat - Monitor MySQL replication delay.
1)在主库中创建心跳表,这里sbtest库要默认存在, --creat-table 选项会默认创建一个heartbeat心跳表
pt-heartbeat -S /tmp/mysql3306.sock --database sbtest --update --create-table --daemonize
2)在从库执行监控命令
pt-heartbeat -S /tmp/mysql3306.sock --master-server-id=1003306 --monitor --database sbtest
3)输出结果如下
[root@mysql02 3306]# pt-heartbeat -uroot -p123 -S mysqld_3306.sock --master-server-id=55230 -D test --monitor 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ]
延时时间 1分钟 5分钟 15分钟平均的延迟时间
参考:https://docs.percona.com/percona-toolkit/pt-heartbeat.html
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
2022-03-18 2.oracle性能日常查看