You need to enable JavaScript to run this app.
导航
如何解决主从复制延迟过高的问题
最近更新时间:2025.01.08 17:01:40首次发布时间:2025.01.08 17:01:40

使用云数据库 MySQL 版时,倘若实例的写入流量较大或者存在表结构方面的问题,或许会致使主节点与只读节点的数据延迟有所升高。在主从延迟升高以后,从业务的角度会观察到路由至只读节点的读请求无法访问到写入的数据,亦或读到旧数据的情况。对于使用 binlog 复制的备节点[1]而言,可能会引发主备切换失败,对高可用产生影响。

问题确认

查看复制延迟的监控项,确认实例是否存在较大主从延迟。

  1. 实例列表页面,单击目标实例的实例名称,进入实例信息页。

  2. 在页面上方,单击监控告警页签。

  3. 部署监控分类下,查看复制延迟

    说明

    可按照节点维度查看实例各节点的复制延迟情况。

快速止损

如果有已知的较大写入负载,可以暂停写入。调整写入流量后再恢复写入。若无法确定,可继续阅读本文提供的方法定位并处理。

原因定位及解决

负载原因

  • 原因分析

    MySQL 的从节点使用了并行复制回放的方式,一般情况下不会有很大的延迟。如果数据库正在执行某些对资源占用较大的负载,如大表重建 DDL、大事务或者较重的写入负载,会造成从节点回放不及时从而影响主从延迟

    • 请求级别分析 在部署监控分类下,查看引擎监控 > 访问下的各监控项,查看是否有较多的写请求(更新,插入,删除,覆盖)。

    • 行级别分析

      部署监控分类下,查看引擎监控 > I****nnodb 查看是否有较多的行修改。重点关注 Innodb 行插入量Innodb 行删除量Innodb 行更新量


  • 解决办法

    通过数据库工作台的全量 SQL 洞察功能确定是哪些 SQL 有较多写入,并通过 SQL 限流来限制 SQL 的执行频率,防止主从时延上升。

    说明

    全量 SQL 洞察和 SQL 限流当前为数据库工作台的邀测功能,如需使用请提交工单申请。

    • 定位 SQL

      1. SQL 洞察 进行 SQL 洞察的操作路径:登录数据库工作台 > 观测诊断 > 全量 SQL 洞察 > 写分析。更详细信息,请参见全量 SQL 洞察

      2. DML语句:重点关注平均耗时DML 平均扫描行以及DML 平均影响行数

      3. DDL语句:大表的重建 DDL 也会造成主从延迟,重点关注 DDL 平均扫描行以及 DDL 平均影响行数

    • 处理 SQL

      定位到 SQL 后,可通过 SQL 限流或 KILL 会话的方式进行处理。

      1. SQL 限流

      定位到问题 SQL 后,可以通过 SQL 限流限制 SQL 的执行频率。 操作路径:

      1. 登录 数据库工作台 > 观测诊断 > 会话管理 > SQL限流 > 打开SQL限流功能 > 创建任务

      2. 在创建 SQL 限流任务窗口中,输入问题 SQL,调整最大并发数(最大并发数为 0 时会完全屏蔽该 SQL),单击关键字生成和校验拆分关键词,关键词会用波浪线(~)分隔。

      1. 单击确定提交任务。

        说明

        更多详细信息,请参见SQL 限流管理

      2. KILL 会话 操作路径:

        • 登录 数据库工作台 > 观测诊断 > 会话管理

        • 实时会话区域,关注会话的 TimeState。如果某会话的 State 为 updating,但 Time 的数值已很大,则有可能是大的写入事务,需要关注处理。

表结构原因

  • 原因分析

    MySQL 的使用中推荐在表中使用主键索引,如果表不含有主键或唯一键,从库在回放时会消耗更多时间寻找需要修改的数据,造成主从延迟增加。无主键表在有大量删除(delete)和修改(update)时会有明显的延迟上升情况。

    • 查看写入负载对的表结构

      SHOW CREATE TABLE example_table;
      
    • 分析表结构,如果不包含 PRIMARY KEY或者UNIQUE KEY,则回放时可能会用到过滤较差的索引(有时甚至需要全表扫描),在表数据较多时,检索耗时过大会导致时延上升。

    • 无主键表示例:

      CREATE TABLE `example_table` (
        `column1` int(11) DEFAULT NULL,
        `column2` varchar(255) DEFAULT NULL,
        `column3` date DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
      
  • 解决办法

    • 如果有唯一列,可变更表结构,将其作为唯一键:

      • 方法 1:CREATE INDEX

        CREATE UNIQUE INDEX unique_col1 ON example_table (column1);
        
      • 方法 2:ALTER TABLE

        ALTER TABLE example_table ADD UNIQUE (column1);
        
    • 添加自增主键: 对于无主键表,其数据顺序由存储引擎的 row_id 决定,主从节点上的 row_id 可能不同,导致数据顺序不同。给这种表添加自增主键时,自增主键值按数据顺序初始化,致使同样的数据对应的自增主键值不同,用相同自增主键值在主备节点查询的数据也不同。
      为已有数据的表添加自增列时,请先创建相同表结构的新表,再在新表上添加自增列,将原表数据导入(导入数据时,请尽量保持原表无写入操作,否则会造成原表与新表数据不一致)。
      按照如下步骤解决主备节点查询数据不一致问题。

      • 在主节点上创建一个与无主键表(称之为原无主键表t1)相同的新表t1_new,并为新表中添加自增主键

        CREATE TABLE t1_new LIKE t1;
        ALTER TABLE t1_new ADD id INT AUTO_INCREMENT PRIMARY KEY;
        
      • 将原无主键表的数据全部插入到新表t1_new

        INSERT INTO t1_new(column1, column2) 
            SELECT column1, column2 
            FROM t1 
            ORDER BY column1, column2;
        
      • 删除原无主键表t1,并将新表重命名为原无主键表名

        DROP TABLE t1;
        RENAME TABLE t1_new TO t1;
        

提升实例规格

经过以上手段进行排查和解决后,主从延迟仍然很高,则可能是业务压力导致的从库处理不过来情况,此时可考虑通过常规变配或临时升配提高实例规格,增加 CPU 核数可提升从节点处理能力从而减少主从时延。
关于如何对实例进行常规变配或临时升配,请参见以下文档:

说明

临时升配当前为云数据库 MySQL 版的邀测功能,如需使用,请提交工单申请。

附录

名词释义
云数据库 MySQL 版有两种通过 binlog 复制的节点类型:

  • 只读节点:承接用户读流量。
  • 备节点:热备,负责高可用,不承接读流量。

常见的配置:一主一备,一主一备多只读节点。