实例数据同步延迟

最后更新时间:2021-06-01 16:23:51

    现象描述

    云数据库 MySQL 对应的默认备库、只读实例均采用 MySQL 原生 binlog 复制技术,当数据复制方式为异步复制或半同步复制时,都有可能发生延迟。

    故障影响

    • 备库 存在延迟,会导致主备实例无法在短时间内完成切换,进而影响业务无法在短时间内恢复正常。
    • 若读业务对数据一致性有较高要求,只读组 可以设置延迟剔除策略,当只读实例与主实例延迟时间超过阈值,对应的只读实例会被自动剔除,从而导致读业务无法正常访问只读实例。

    可能原因

    • 无主键或二级索引
      若 binlog 为 row 格式且表无主键或二级索引,当对大表进行 DML 操作(例如 delete、update、insert),在从库进行 binlog 日志应用时,会根据主键或者二级索引来检索需要更改的行,如对应表未创建主键或者二级索引,会产生大量的全表扫描进而降低了日志应用速度,从而产生数据延迟。
      处理步骤请参见 无主键或二级索引

    • 大事务
      大事务:特指对数据进行增删改的 insert,update,delete,replcae这一类语句。在一个事务中包含对数百万行数据的操作;或者是一个 SQL 语句修改百万行数据,导致执行时间超过30s。
      当主实例执行大数据量的 DML 操作,大量的 binlog 日志传送到从库时,从库需要花费与主实例相同的时间来完成相应事务,进而导致从库出现数据延迟。处理步骤请参见 大事务

    • DDL 操作
      由于只读节点上会有用户的查询在上面运行,如果只读节点上有一个执行时间非常长的查询正在执行,那么这个查询会堵塞来自主库的 DDL,直到查询结束为止,进而导致只读节点的数据延迟。处理步骤请参见 DDL 操作

    • 实例规格过小
      只读实例的规格小于主实例且负载较高,会导致只读实例的数据延迟。
      处理步骤请参见 实例规格过小

    • Waiting for table metadata lock 报错
      大事务运行,阻塞 DDL,继而阻塞所有同表的后续操作;未提交事务,阻塞 DDL,继而阻塞所有同表的后续操作。
      处理步骤请参见 Waiting for table metadata lock 报错

    处理步骤

    无主键或二级索引

    1. 登录 DBbrain 控制台,在左侧导航选择【诊断优化】,在上方选择对应数据库,然后选择【空间分析】页。
    2. 在空间分析页下方,选择【无主键表】页,单击列表的无主键表,可查看表的字段和索引信息。
      说明:

      无主键表列表支持定期扫描(频率为每天扫描1次)和手动刷新两种方式,可根据实际情况选择。

    3. 为步骤2中的无主键表创建主键,若表无法创建主键,建议选择基数高的列创建二级索引。

    大事务

    1. 登录 DBbrain 控制台,在异常告警页,选择对应数据库和地域,在“诊断项”勾选【事务导致复制延迟】,可过滤查看实例的大事务。
    2. 将大事务拆分为小事务,通过 where 条件限制每次要处理的数据量。
      说明:

      通过 DBbrain 定位耗时的大事务,将大事务拆分成为小事务进行,这样只读节点就可以迅速完成事务的执行,不会造成数据延迟。

    DDL 操作

    1. 登录 DBbrain 控制台,在异常告警页,选择对应数据库和地域,在“诊断项”勾选【DDL导致复制延迟】,可过滤查看实例对应的 DDL 操作。
    2. 在告警列表单击“操作”列的【详情】,可跳转至事件详情页进行相应处理。
      • 事件详情:包括诊断项、起止时间、风险等级、持续时长、概要等信息。
      • 现场描述:异常事件(或健康巡检事件)的外在表现现象的快照和性能趋势。
      • 智能分析:分析导致性能异常的根本原因,定位具体操作。
      • 优化建议:提供优化指导建议,包括但不限于 SQL 优化(索引建议、重写建议)、资源配置优化和参数调优。

    实例规格过小

    1. 建议只读实例规格大于等于主实例,实例规格可登录 MySQL 控制台 的实例列表查看。
    2. 若只读实例承载了大量的分析类业务导致实例负载过高,需将其实例规格升级至合适的配置或者对其性能低效的 SQL 进行优化。

    Waiting for table metadata lock 报错

    建议使用 数据库智能管家 DBbrain 对实际业务和实例进行诊断,排查慢查询等指标,来定位耗时的大事务。

    1. 登录 DBbrain 控制台,在异常告警页,选择对应数据库和地域,在“诊断项”勾选如下诊断项,来定位耗时的大事务。
    2. 对应如下不同故障场景,采取对应处理措施:
      • 大事务运行,阻塞 DDL,继而阻塞所有同表的后续操作,根据 DBbrain 的异常诊断提示找到大事务的 ID,然后 kill 掉。
      • 未提交事务,阻塞 DDL,继而阻塞所有同表的后续操作,根据 DBbrain 的异常诊断找到未提交事务的 ID,然后 kill 掉,并排查程序,及时提交事务。
      • 一个显式的事务中,对 TableA 进行了一个失败的操作(如查询了一个不存在的字段),这时事务没有开始,但是失败语句获取到的锁依然有效,没有释放,根据 DBbrain 的异常诊断找到 session 的 ID,然后 kill 掉。