当云数据库 MySQL 版实例遇到主备延迟时，您可以根据本文排查数据库实例问题。

背景信息

在 MySQL 中，主备延迟指的是主数据库和备份数据库之间数据同步的延迟时间，这种延迟会导致备份数据库上的数据滞后于主数据库上的数据。

问题排查

数据延迟可能由于 DML QPS 过高、DML 操作表缺少主键或唯一键、DDL 操作、大事务或锁阻塞等因素导致，您可以根据以下操作指引排查分析具体导致数据延迟的原因。

可能原因一：DML QPS 过高

当 DML QPS 过高时，可能会致使数据库的性能降低甚至崩溃，进而造成数据延迟。您可以对比分析全量 SQL 洞察在不同时间段内写分析的 DML 执行次数、DML 平均执行耗时、DML 最大执行耗时、影响行数等各项指标，判断查询实例的 DML QPS 是否过高。您可以通过以下操作查看是否是 DML QPS 过高导致的主备延迟。

1. 登录 DBW 控制台。

2. 在顶部菜单栏，切换地域。

3. 在左侧导航栏，选择运维管理 > 观测诊断。

4. 在运维观测页面左上角，单击下拉图标，选择目标实例。

5. 在目标实例页面，单击全量 SQL 洞察。

6. 在全量 SQL 洞察页面，单击写分析，

7. 在写分析页签，选择查询时间、数据库和表，最长查询时间的跨度不能超过 24 小时。

   查看 DML 执行次数、DML 平均执行耗时、DML 最大执行耗时、影响行数等指标项在不同时间段内是否有增长趋势，如果有增长趋势，您可以在全量 SQL 洞察页签勾选时间对比，对比不同时间段内各项指标的变化趋势，进而判断实例的 DML QPS 是否过高。

可能原因二：DML 操作表缺少主键或唯一键

在 DML 操作过程中，如果表缺少主键或唯一键，那么可能会导致数据库性能降低、影响数据完整性，同时也无法唯一识别表中的每一行记录等问题，从而影响主备延迟。您可以通过以下方法查询是否是 DML 操作表缺少主键或唯一键导致的主备延迟。

1. 登录 DBW 控制台。

2. 在顶部菜单栏，切换地域。

3. 在左侧导航栏，选择运维管理 > 观测诊断。

4. 在运维观测页面左上角，单击下拉图标，选择目标实例。

5. 在目标实例页面，单击全量 SQL 洞察。

6. 在全量 SQL 洞察页签的 SQL 模板列表区域，查看 SQL 耗时较长的 DML 语句对应的表是否有主键或唯一键。

可能原因三：DDL 操作

DDL 操作通常执行时间较长，尤其是在表数据量巨大时。通常情况下，只读节点或备机回放一个 DDL 操作所花费的时间与主库基本相同，因此，当主机对大表执行 DDL 操作后，备机和只读节点在回放该 DDL 期间，复制时间必然会一致地大幅增加，从而导致主备延迟增加。您可以通过以下操作查看是否是 DDL 操作导致的主备延迟。

1. 登录 DBW 控制台。

2. 在顶部菜单栏，切换地域。

3. 在左侧导航栏，选择运维管理 > 观测诊断。

4. 在运维观测页面左上角，单击下拉图标，选择目标实例。

5. 在目标实例页面，单击全量 SQL 洞察。

6. 在写分析页签，查看 DDL 最大执行时间指标。

可能原因四：大事务

在数据库中，大事务通常是一个事务中包含大量的数据操作，例如上千次甚至上万次的 INSERT、UPDATE 或 DELETE 操作，或一条 SQL 语句影响了大量的数据行。大事务在执行过程中占用了较多的系统资源，执行时间较长，从而影响数据库的性能或并发性。
当主实例执行大事务后，会产生大量的 Binlog 日志，备机或只读节点拉取这些 Binlog 所耗费的时间比一般事务更长，并且至少需要花费与主实例相同的时间来对这些事务的更新进行回放，进而导致备机或只读节点出现复制延迟。 您可以通过以下操作查看是否是大事务导致主备延迟。

• 方式一：通过 DBW 的全量 SQL 洞察查询是否存在大事务导致主备延迟

  • 登录 DBW 控制台。

  • 在顶部菜单栏，切换地域。

  • 在左侧导航栏，选择运维管理 > 观测诊断。

  • 在运维观测页面左上角，单击下拉图标，选择目标实例。

  • 在目标实例页面，单击全量 SQL 洞察。

  • 在全量 SQL 洞察页签的 SQL 模版列表区域，查看 SQL 语句的 平均TRXCommitTime（即事务提交总耗时）和 SQL 耗时。

• 方式二：执行 SQL 命令查询是否有大事务导致主备延迟

  执行以下命令，查看实例上是否存在长时间执行的事务：

  SELECT 
      t.*, 
      TO_SECONDS(NOW()) - TO_SECONDS(t.trx_started) AS idle_time 
  FROM 
      INFORMATION_SCHEMA.INNODB_TRX t;
  

可能原因五：锁阻塞

锁阻塞通过影响主从事务延迟提交、影响 Binlog 的生成和写入、从库在处理 Binlog 时受锁争用等导致主备延迟。您可以通过以下方式查看是否是锁阻塞导致主备延迟。

1. 登录 DBW 控制台。

2. 在顶部菜单栏，切换地域。

3. 在左侧导航栏，选择运维管理 > 观测诊断。

4. 在运维观测页面左上角，单击下拉图标，选择目标实例。

5. 在目标实例页面，单击全量 SQL 洞察。

6. 在写分析页签，查看包括 DML 平均 innodb 行锁等待时间、DML 平均 MDL 锁等待时间、DML 最大执行耗时、DDL 平均 innodb 行锁等待时间、DDL 平均 MDL 锁等待时间分析是否因为锁阻塞导致主备延迟。

同时，您也可以在实例上执行以下命令查看当前数据库是否有 MDL 锁导致锁阻塞：

select * from  information_schema.metadata_lock_info;