数据库>>云数据库 RDS>>技术运维问题>>MYSQL使用

1. 只读实例产生延迟的原因

1.1 只读实例规格过小

这类延迟场景经常出现在只读实例规格和主实例规格相差较大，而且只读实例上负载较重，比如只读实例上 IOPS 打满。

分析：

只读节点的数据为了和主节点保持同步，采用了 MySQL 原生的 binlog 复制技术，由一个 IO 线程和一个 SQL 线程来完成。IO 线程负责将主库的 binlog 拉取到只读节点，SQL 线程负责消费这些 binlog 日志应用到只读实例。这两个线程会消耗只读节点的 IO 资源，所以当只读节点 IOPS 配置不够的时候，则会导致只读节点的数据出现延迟。

比如下面的例子：只读实例的 IOPS 被查询打满，导致和主实例的数据同步出现延迟。

建议：

对于这样的情况，建议用户升级只读实例规格，避免由于只读实例规格较小导致数据延迟。RDS 推荐只读实例的配置大于或者等于主实例的配置。

1.2 主实例的 TPS（Transaction Per Second）过高

主实例的 TPS （Insert、Update、Delete）过高导致只读节点延迟。

分析：

由于只读节点与主实例同步采用的是单线程同步，而主实例的压力是并发多线程写入，这样在主实例高并发 DML TPS 情况下非常容易出现只读实例的数据延迟，可以通过观察只读实例节点的TPS与主实例的 TPS 性能数据来完成判断。

主实例的 TPS ：

只读实例的 TPS：

只读实例的延迟：

建议：

排查主实例的写入压力是否正常；如果正常则需要对业务进行优化或者拆分，保证主实例的 TPS 不会导致只读实例出现延迟。

1.3 主实例的大事务

主实例执行一个大事务导致延迟。

分析：

比如在主实例执行一个涉及数据量非常大的 update、delete、insert...select、replace...select 等事务操作，生成大量的 binlog 数据传送到只读实例。只读实例需要花费与主实例相若的时间来完成该事务，进而导致了只读实例的同步延迟。

比如下面的例子，在主实例上执行一个持续 80 秒的删除操作，会导致只读实例上出现数据延迟。

只读实例数据延迟：

在只读实例出现大事务导致延迟时，通过 show slave status \G 命令，可以看到 Seconds_Behind_Master 不断变化，而 Exec_Master_Log_Pos 却保持不变，这样可以判断只读实例的 SQL 线程在执行一个大的事务或者DDL 操作。

建议：

建议考虑将大事务拆分为小事务（比如在 delete 语句中增加 where 条件子句，限制每次删除的数据量，将一次删除操作拆分为多次数据量较小的删除操作进行），这样只读实例可以迅速的完成事务的执行，不会造成数据的延迟。

1.4 主实例的 DDL 语句

主实例的 DDL（alter、drop、repair、create）导致只读实例延迟。

分析：

只读实例和主实例数据同步是串行进行的，如果 DDL 操作在主实例执行时间很长，那么同样在只读实例也会消耗同样的时间。比如对一张 500 万行的表添加一个字段耗费了 10 分钟，那么在只读实例上同样也会耗费10 分钟。所以只读实例会延迟 600 秒。其他常见操作比如 create index、repair table、alter table add column 等。

主实例：

只读实例：

只读实例上执行的查询或未完成的事务阻塞来自主实例的 DDL 执行。在只读实例上执行 show processlist 命令查看 SQL 线程的状态为：waiting for table metadata lock.

只读实例：

建议：

对于DDL直接引起的只读实例延迟，建议这些 DDL 在业务低峰期执行。

对于来自主实例的DDL在只读实例上被阻塞的情况，需要 kill 掉只读实例上引起阻塞的会话（通常是运行时间长的查询）来恢复只读实例和主实例的数据同步。

注：对于表原数据锁等待的原因和处理，请参考 RDS MySQL 表上Metadata lock 的产生和处理。

1.5 只读实例 MyISAM 引擎表

只读实例上针对 MyISAM 引擎表的长时间查询，阻塞来自主实例对该表的数据同步，导致只读实例数据延迟。

分析：

MyISAM 引擎表读写相互冲突，同一时间读写不能并发操作，且仅支持表级锁。因此表上的长时间查询（比如 SQL 注入）会阻塞SQL线程应用来自主实例的该表数据变化，导致只读实例数据延迟。

主实例：对 MyISAM 引擎表 large_tab_02插入数据。

只读实例：存在对表 large_tab_02 的长时间查询，使 SQL 应用线程等待 large_tab_02 表级锁，导致数据步发生延迟。

建议：

如果使用只读实例，建议业务低峰期将主实例上的 MyISAM 引擎表转换为 InnoDB 引擎表（只读实例上会相应进行转换）。

1.6 其他

其他只读实例出现延迟的情况：

比如只读实例的主机 IO 压力出现异常，或者对无主键的表进行删除（RDS目前已经支持对表添加隐式主键，但是对于以前历史创建的表需要进行重建才能支持隐式主键）。

2. 综述

综上所述，把目前RDS只读实例出现延迟的场景都进行了分析，可以简单归纳一下：

当只读实例出现延迟后，

一看只读节点 IOPS 定位是否存在资源瓶颈；

二看只读节点的 binlog 增长量定位是否存在大事务；

三看只读节点的 ComDML 性能指标，对比主节点的 ComDML 定位是否是主库写入压力过高导致；

四看只读节点执行 show slave status \G，判断是否有 Waiting for table metadata lock；同时在主实例控制台 =》SQL明细中排查下是否有 alter，repair，create等 DDL 操作；

五看只读节点执行 show slave status \G，判断是否有 Waiting for table level lock; 同时通过 show full processlist; 或者 DMS =》实例信息 =》实例会话检查下是否有长时间对 MyISAM 引擎表的查询。

如果上述五看都还没有发现问题，那么在最后再检查一下只读节点是否存在 无主键表 的删除或者更新操作，可以通过在只读节点执行：show engine innodb status\G 或者 show open tables 状态为 in_use 为1的表：

mysql> show open tables;
+-------------------+-----------------------+-------------+------------------+
| Database          | Table                 | In_use      | Name_ locked     |
+-------------------+-----------------------+-------------+------------------+
| aixuedai_web      | sd_repay              |         4   |               0  |
| mysql             | slow_log              |         0   |               0  |
| dw                | dw_acc_sd_expectpay   |         1   |               0  |

如问题还未解决,请联系售后技术支持。