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

 

posted on   太白金星有点烦  阅读(34)  评论(0编辑  收藏  举报

相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
历史上的今天:
2022-03-18 2.oracle性能日常查看

导航

< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8
点击右上角即可分享
微信分享提示