如何诊断同步备库延迟
主从同步备库延迟(slave lags)
当使用MySQL replication复制,最初看上去主从同步完全正常,主服务器的变化很快反映到从服务器。但是,偶然也会出现一些延迟,甚至严重时,从服务器迟迟没有得到更新。
常见的备库延迟原因
CPU负载:集群过于繁忙无法即使处理复制事件
丢失索引:更新的数据库表没有主键或者如果是SBR,没有正确的索引
锁等待超时:在slave主机上,由于没有commit的会话会导致锁等待超时
常规卷写入:当站点流量或大量的删除或更新导致站点流量增长,也使得写入卷增长
数据一致性:通常slave的停止和启动会导致数据一致性问题(重复的键或者数据记录行丢失)
长时间运行聚合查询(基于SQL语句复制的SBR环境):在SBR中聚合查询将占用很长的时间来处理结果,则导致后台日志查询等待
数据分布:目标表没有分布在所有节点
网络问题
mysql的RBR和SBR:
从MySQL 5.1开始,支持基于行的复制,即关注表中发生变化的记录,而不是以前直接复制binlog。从 MySQL 5.1.2 开始可以使用三种模式实现复制:
基于SQL语句的复制(statement-based replication, SBR)
基于行的复制(row-based replication, RBR)
混合模式复制(mixed-based replication, MBR)
相应地,binlog的格式也有三种:STATEMENT,ROW,MIXED。
在slave上处理写日志可能比master慢,是因为在master主机上复制是单线程,而写查询可以并发处理。对于负载非常重的环境,使用多个binlog可能可以优化复制。
如何定位复制延迟
MySQL复制使用了2个线程:IO_THREAD
和SQL_THREAD
:
IO_THREAD
连接到master数据库,从master读取binlog事件,并将binlog复制成一个本地的名为relay log
的log文件。SQL_THREAD
则在slave数据库上读取复制后的本地日志(这个日志文件是由IO_THREAD
写入的),然后尽可能快地应用这个SQL。
当出现复制延迟,最重要的第一步就是检查slave主机上的IO_THREAD
或SQL_THREAD
。
通常,I/O线程不会导致很大的延迟,因为这线程只是从master上读取binlog。然而,网络连接和网络延迟会影响I/O线程的稳定工作。当网络带宽阻塞时,slave主机上的I/O线程会变慢。通常,当slave主机上的IO_THERAD
能够足够快速读取binlog并在slave主机上构建relay log
,就可以确定IO_THREAD
不是导致复制延迟的原因。
另一方面,当slave主机上SQL_THREAD
导致复制延迟有可能是slave主机上复制流花费了太长时间来执行。有时候这是因为主从服务器使用了不同的硬件,不同的schema索引,不同的负载。进一步,slave OLTP工作负载有可能导致复制延迟。
例如,一个长时间运行的读会阻塞MyISAM表的SQL线程,或者任何InnoDB表的回话引发的IX锁或者SQL线程的阻塞DDL。
检查
在master主机上检查
在slave主机上检查
这里可以看到,Master上的binlog文件是 mysql-bin.000002
,同样在Slave上检查可以看到 Master_Log_File: mysql-bin.000002
,这说明主备之间binlog文件是同步的(如果落后,slave文件编号会比master小,就说明有部分binlog没有传输过来)。但是,即使binlog
文件名同步了,Position
不一致的话,依然说明slave落后于master。
注意,在slave上:
由于slave上Read的日志文件位置和Exec的日志文件位置完全一致(当前都是157687292
),则说明slave上回放sql是完全及时的,只是slave上位置比master上的位置157689756
要小,就说明master和slave之间的网络连接和传输不佳。
等待一段时间,则当slave上的Pos追上master的Position,数据就同步了。
另外,对于网络连接不佳,可以参考 MySQL主从延迟原因以及解决方案 适当调整参数:
参考
Last updated