本文介绍常见的RDS MySQL只读实例延迟时间变长的原因及解决方案。
问题描述
由于阿里云云数据库RDS只读实例采用MySQL原生的基于日志复制技术(异步复制或半异步复制),必然会有同步延迟。延迟会导致只读实例与主实例的数据出现不一致,从而导致业务出现问题。另外,延迟也有可能引起日志堆积,导致只读实例空间被迅速消耗。
- 若主实例正产生大量的日志,有可能会使只读实例被锁定。
- 日志复制延迟是通过
show slave status
命令的second_behind_master字段透出的。
按照延迟时长可将延迟分为以下两种类型:
- 小于或等于1秒的延迟:由系统延迟计算精度、计算方法、采样时刻、监控时间粒度引起,无问题,无需关注。
- 大于1秒的延迟:由只读实例规格过小、主实例的TPS过高、主实例的大事务、主实例的DDL语句执行时间较长引起,需要排查处理。
问题原因
- 小于或等于1秒的延迟:
- 小于1秒延迟出现原因:由监控时间范围设置过长引起,实际并不存在小于1秒的延迟,无需关注。
如果设置的监控时间范围比较长,就会导致监控的时间粒度比较大。例如,如果监控的时间范围为3小时,默认的监控的时间粒度就会达到30秒级,每个时刻计算得出的数据就是30秒的平均值,就会出现小于1秒的延迟。而实际每个时刻计算出的延迟最小粒度为1秒。
如果想看到更准确的延迟,可以到RDS控制台的性能趋势页面,将监控时间范围缩小到6分钟以内,可以看到时间粒度为1秒的监控值。详情请参见性能趋势。
- 1秒延迟出现原因:由系统延迟计算精度、计算方法、采样时刻、是否存在跨秒事务引起,实际并不存在1秒的延迟,无需关注。
延迟的计算方法:延迟时长=当前时间(执行
show slave status
命令的时间)-当前备库上正在应用的事务在主库提交的时间RDS MySQL中延迟的计算精度是秒级的,会忽略“秒”这一位之后的数据。例如,00:00:00.95会按照00:00:00来计算,00:00:01.05会按照00:00:01来计算。如果采集时刻在整秒刚过(例如00:00:01.05),就可能会导致将不满1秒的延迟(例如0.15秒)计算为1秒,如下表中第4行所示。
事务 主库提交时间 备库提交时间 当前时间(show slave status命令执行的时间) 秒级精度的延迟 Trx1 00:00:00.30 00:00:00.50 00:00:00.35 0(0.35)-0(0.3)=0秒 00:00:00.45 0(0.45)-0(0.3)=0秒 Trx2 00:00:00.90 00:00:01.10 00:00:00.95 0(0.95)-0(0.9)=0秒 00:00:01.05 1(1.05)-0(0.9)=1秒 由于采集的间隔是1整秒,所以每次采集时刻都在整秒刚过,如果有业务压力、存在跨秒事务,这样每次都可能采集到1秒的延迟。而如果采集的时刻不是在整秒刚过(例如00:00:00.95),就会将延迟计算为0秒,如上表中第3行所示。
- 小于1秒延迟出现原因:由监控时间范围设置过长引起,实际并不存在小于1秒的延迟,无需关注。
- 大于1秒的延迟:
大于1秒的延迟,由以下几种可能的原因引起:
- 只读实例规格过小
这类延迟场景经常出现在只读实例规格和主实例规格相差较大,而且只读实例负载较重的情况下。例如,只读实例IOPS过高。只读实例为了和主实例保持同步,采用了MySQL原生的日志复制技术,由一个IO线程和一个SQL线程来完成。IO线程负责将主实例的日志拉取到只读实例,SQL线程负责将这些日志应用到只读实例。这两个线程会消耗只读实例的IO资源,所以当只读实例的IOPS配置不高时,会导致只读实例数据延迟。您可以登录RDS控制台,通过监控信息确认IOPS较高。
- 主实例的TPS(Transaction Per Second)过高
由于只读实例与主实例之前的同步采用的是单线程同步,若主实例并发多线程写入数据,在主实例TPS过高的情况下容易出现只读实例的数据延迟,您可以通过观察只读实例的TPS与主实例的TPS性能数据来判断。
- 主实例的大事务
主实例执行一个涉及数据量非常大的update、delete、insert…select、replace…select等事务操作时,会生成大量的日志数据并同步到只读实例。只读实例需要花费与主实例相同的时间来完成该事务,因此会导致只读实例同步延迟。例如,在主实例上执行一个持续80秒的删除操作,只读实例进行相同操作时也需要花费很长时间,于是会出现延迟情况。
- 主实例的DDL语句执行时间较长
- 只读实例和主实例数据同步是串行进行的,如果DDL操作在主实例操作的表过大,执行时间很长,或者执行大量慢查询,则会产生大量的临时表,将导致磁盘容量不足和磁盘IO增加,从而导致同步延迟。常见操作例如create index、repair table、alter table add column等。
- 只读实例上执行的查询或未完成的事务阻塞了来自主实例的DDL执行。
- 只读实例规格过小
解决方案
小于或等于1秒的延迟无问题,无需排查处理。这里介绍大于1秒的四种原因引起的延迟的解决方案。
- 如果您对实例或数据有修改、变更等风险操作,务必注意实例的容灾、容错能力,确保数据安全。
- 如果您对实例(包括但不限于ECS、RDS)等进行配置与数据修改,建议提前创建快照或开启RDS日志备份等功能。
- 如果您在阿里云平台授权或者提交过登录账号、密码等安全信息,建议您及时修改。
排查方法
当只读实例出现延迟时,可以根据以下排查方法定位问题:
- 在RDS控制台中查看监控信息,检查只读实例的IOPS,确认只读实例是否存在资源瓶颈。
- 在RDS控制台查看监控信息,检查只读实例的TPS,确认主实例TPS是否过高。
- 检查只读实例的日志增长量,确定是否存在大事务。
- 查看慢日志信息,确认是否存在alter、repair和create等DDL操作,详情请参见慢日志分析。
- 只读实例执行
show slave status \G
命令,确定是否存在元数据锁。 - 检查只读实例是否存在无主键表的删除或者更新操作,可以通过在只读实例上执行
show engine innodb status \G
语句查看,或者执行show open tables
语句后,查看输出结果的in_use列的值为1的表。
解决方法
本节列出以下四种常见的解决方法,您可以根据排查方法定位的问题原因选择对应的解决方法:
- 只读实例规格过小
建议您升级只读实例规格,使只读实例的配置大于或者等于主实例的配置,避免由于只读实例规格较小导致延迟,详情请参见变更配置。
- 主实例的TPS(Transaction Per Second)过高
确认主实例的TPS是否正常,如果TPS过高,则需要对业务进行优化或者拆分,保证主实例的TPS不会导致只读实例出现延迟。TPS相关数据可以通过自治服务的性能趋势页面查看,详情请参见性能趋势。
- 主实例的大事务
在只读实例出现大事务导致延迟时,登录数据库,执行以下SQL语句,确认Seconds_Behind_Master不断变化,而Exec_Master_Log_Pos却保持不变,说明只读实例的SQL线程在执行一个大事务或者DDL操作,然后通过
show processlist
语句定位具体的线程。
系统显示类似如下结果:show slave status \G
- 建议您将大事务拆分为小事务分别执行。例如,在delete语句中增加where条件子句,限制每次删除的数据量,将一次删除操作拆分为多次数据量较小的删除操作进行。这样只读实例可以迅速地完成事务的执行,不会造成数据的延迟。
- 主实例的DDL语句执行时间较长
- 对于DDL直接引起的只读实例延迟,建议在业务低峰期执行这些DDL。您可根据业务情况通过以下方法进行解决:
- 对于来自主实例的DDL语句在只读实例上被阻塞的情况:
- 在只读实例上执行
show processlist
语句,确认SQL线程的状态为“waiting for table metadata lock”。 - 使用kill命令终止只读实例上引起阻塞的会话,恢复只读实例和主实例的数据同步,详情请参见解决MDL锁导致无法操作数据库的问题。
- 在只读实例上执行
只读实例延迟介绍

常见问题
- Q:为什么我的实例中,部分只读实例有1秒延迟的现象?
A:每个实例的采集程序、启动时间都不一样,有的实例的采集刻凑巧在刚过整秒的时间点,就会有此现象。其他实例的采集时刻不在刚过整秒的时间点,就不会有这个现象。详情请参见小于或等于1秒的延迟。
- Q:1秒延迟对我的实例有没有影响?
A:没有影响。1秒延迟并不代表实际存在1秒的延迟。例如,当实际延迟只有0.1秒时,如果采集时间在刚过整秒的时刻,也会采集到1秒的延迟,它是由系统延迟计算精度、计算方法、采样时刻引起的。详情请参见小于或等于1秒的延迟。
建议在配置告警或者代理的读库延迟阈值时,要根据自己的实际需求与配置,并满足:读库延迟阈值>1秒。