RDS for MySQL磁盘过载定位及处理建议

数据空间增长

表碎片率增加

• 场景1：DRS全量迁移阶段并行迁移导致

  DRS在全量迁移阶段，为了保证迁移性能和传输的稳定性，采用了行级并行的迁移方式。当源端数据紧凑情况下，迁移后目标库表产生碎片，使得表空间会比源库的表空间大。

• 场景2：大量删除操作后在表空间留下碎片

  当删除数据时，MySQL并不会回收被删除数据占据的存储空间，而只做标记删除，尝试供后续复用，等新的数据来填补相应空间，如果没有数据来及时填补这些空间，就造成了表空间膨胀，形成大量碎片。

SQL执行产生临时文件

• 场景1：DDL操作期间产生的临时文件

  MySQL原生DDL copy/inplace 算法执行期间(需要重建表的场景), 会产生临时文件，存放在操作表的相同目录下，该文件大小和原表大小相近，当DDL操作执行完成后才会清理。

• 场景2：大事务操作产生的临时文件

  在MySQL中，当一个大事务开始执行时，服务器会创建一个临时文件来存储该事务的Binlog事件。这些临时文件通常以ML开头，当事务提交时完全写入Binlog文件后才会清理。

• 场景3：查询语句排序产生的临时文件

  当MySQL无法直接利用索引的有序性来获取排好序的数据（例如：ORDER BY列缺少索引、ORDER BY 的列顺序与索引列顺序不匹配等情况），则需要额外进行一次排序操作，若待排序数据超过sort_buffer_size，则会通过产生临时文件的方式完成排序，当查询语句完成后才会清理。

Binlog空间增长

写业务增加产生大量Binlog日志

当业务高峰期产生Binlog速度超过Binlog日志上传与清理速度时，则会在本地积累的Binlog日志。

本地Binlog日志无法清理

• 场景1：只读或备节点存在复制延迟或者节点异常

  为防止主节点Binlog日志清理后出现只读或备节点无法正常复制的情况，该场景不会清理本地Binlog日志。

• 场景2：Binlog日志设置保留策略

  控制台可以根据客户偏好设置本地Binlog保留策略，满足保留策略的Binlog不会清理。

• 场景3：第三方Binlog订阅工具拉取Binlog日志占用本地文件

  基于MySQL主从复制协议的Binlog订阅工具在拉取Binlog时，会模拟一个MySQL 备节点，MySQL 服务器为了保证数据一致性，不会Purge正在被拉取的Binlog文件。只要工具的连接不断开，它正在拉取的Binlog文件就受到保护，无法被清理。

临时空间增长

执行数据库操作时，创建临时对象导致临时空间增长。

• 场景1：用户临时表

  使用CREATE TEMPORARY TABLE语法创建临时表。

• 场景2：查询优化器创建的内部临时表

  包含 GROUP BY、DISTINCT、UNION 的复杂查询。如果临时结果集太大，无法在内存中完成，就需要在磁盘上创建临时表。包含 TEXT 或 BLOB 等大字段的查询，通常也会直接使用磁盘临时表。

• 场景3：在线DDL操作

  如 ALTER TABLE ... ALGORITHM=INPLACE 时（仅需要修改元数据或仅重建索引场景），InnoDB会在临时表空间中构建新的索引或表结构。

其他空间增长

其他空间增长的主要来源是ibdata空间增长。

ibdata1是InnoDB的系统表空间，主要包括：多版本并行事务控制（MVCC）相关的数据（undolog、Innodb表的元数据如数据字典）、change buffer/double write buffer等。

其中，undolog是ibdata1增大的最主要原因，而undolog过大的主要原因如下：

• 长事务长时间未提交导致undolog purge被阻塞。
• 高并发写入生成大量的undolog导致purge速度跟不上。

规格不足

存储规格不足

• 场景1：业务高峰或突发流量

  常规业务高峰（如早晚高峰）；秒杀、抢购、大型促销活动；新功能上线，吸引大量用户访问导致读写请求激增，磁盘IO达到实例存储规格上限。

• 场景2：业务量持续增加

  随着业务持续增长，当前的数据库实例的磁盘上限已无法满足日常需求。

  不同存储类型对应的IO上限参考数据库实例存储类型。

计算规格不足

内存资源不足导致访问缓冲池无法获取热数据，当需要访问的数据不在缓冲池中时，就需要从磁盘读取，这会导致产生大量磁盘读IO，甚至达到实例规格上限。

慢查询

慢查询是导致带宽打满的最常见原因，具体为SQL语句对大量数据进行磁盘扫描或者写入临时文件所产生磁盘IO负载。

• 全表扫描：查询缺乏有效索引，导致MySQL需要扫描整个表的数据。
• 排序/分组操作：ORDER BY， GROUP BY 子句如果无法利用索引，需要在磁盘上创建临时表进行排序，消耗大量磁盘带宽。
• 大字段查询：频繁查询TEXT， BLOB等大字段类型。
• 非批量的DML操作：在循环中执行单条INSERT/UPDATE/DELETE，而不是使用批量操作，导致IOPS增加。
• DDL操作：DDL操作期间需要拷贝数据，消耗大量磁盘带宽。

数据库表与日志写入频繁

Binlog（二进制日志）和InnoDB的Redo Log（重做日志）写入，主要消耗磁盘带宽。

• 大事务：一次性更新/删除大量数据（例如DELETE FROM large_table），会产生大量的日志写入。

• 批量数据导入：如使用LOAD DATA或一次性插入大量数据。

• 长事务：未及时提交的事务，可能导致Purge线程无法清理旧的Undo Log，间接增加IO负担。

• 高并发写入：业务本身就是写密集型，如日志记录系统、高频交易系统。

备机不满足备份条件导致主机备份

备份期间涉及全量数据的读取，会占用大量磁盘带宽。

• 单机实例： 单机实例备份触发后，从主库备份数据并以压缩包的形式存储在对象存储服务上。

• 备机故障、备机复制时延超过1天：为保证备份满足数据恢复的一致性，该场景会在主机触发备份。

• DRS同步任务的目标库并开启快速清理Binlog功能：同步任务由全量转增量阶段会关闭快速清理，此时会在主机触发全量备份保证后续增量备份连续。

过载异常前

数据生命周期管理

• 定期归档和清理：制定策略定期将冷数据归档（如归档到对象存储），并从生产库中删除。
• 使用分区表（Partitioning）：按时间（如天、月）对大数据表进行分区，定期DROP PARTITION来删除历史数据。

SQL与表结构优化

• 避免产生大临时文件：优化SQL语句，为排序、分组字段添加索引。
• 审查大字段使用：优先为表中的每一列选择符合存储需要的最小的数据类型。优先考虑数字类型，其次为日期或二进制类型，最后是字符类型。列的字段类型越大，建立索引占据的空间就越大。
• 定期表碎片清理

  对碎片化的表执行 OPTIMIZE TABLE table_name; 或 ALTER TABLE table_name ENGINE=InnoDB; 来重建表回收空间（建议在业务低峰期操作且预留1.5倍的操作表空间）。表碎片率高的排查方法与解决方案参考表碎片率过高可能导致的问题。

• 调整innodb_temp_data_file_path 参数

  innodb_temp_data_file_path 是 MySQL 中用于配置 InnoDB 全局临时表空间（通常命名为 ibtmp1）的系统变量，默认配置为autoextend。建议根据业务实际运行情况合理设置autoextend的max上限，避免由于临时表增长导致磁盘满。若全局临时表增长达到上限后会影响临时表查询性能与创建，建议提前调整参数的上限或清理临时表。

关注本地Binlog日志增长情况

• 合理设置RDS for MySQL本地Binlog日志清理
• 监控主备节点复制状态

  复制延迟和节点异常及时处理。

  • 复制延迟处理请参见MySQL主备复制延迟场景及解决方案。
  • 节点异常请提交工单处理。
• 监控第三方工具拉取Binlog情况

  排查工具占用Binlog方法：

  1. 控制台查看错误日志。
  2. 日志中搜索“was not purged”可以查看到被占用Binlog的工具会话ID。

     例如：file <Binlog文件名> was not purged because it was being readby thread number <会话ID>

监控告警配置与预防策略

• 设置监控报警：在云监控上设置磁盘利用率报警阈值（如80%重要、90%紧急），提前设置告警规则。
• 关注磁盘空间变化趋势：空间概况模块展示了当前实例磁盘的空间使用率、剩余可用空间以及磁盘总空间大小、近一周日均增长量、预计可用天数等信息，可快速了解实例空间的整体情况。
• 设置自动扩容策略： 根据磁盘增长情况，提前规划设置磁盘自动扩容策略。

过载异常中

整体空间增长

磁盘扩容：在云控制台上对实例磁盘进行磁盘扩容，如果原有规格的磁盘已是最大，请先升级规格。

数据空间增长

清理历史数据：

1. 磁盘满导致实例只读的场景下请先解除只读。
2. 解除只读后，如果业务允许，立即清理部分可删除的历史数据（如临时表、近期日志表）。注意：不要用DELETE，因为它会产生Undo日志且不立即释放空间。应使用 TRUNCATE TABLE table_name; 清空表或 DROP TABLE table_name; 删除表。

Binlog空间增长

检查Binlog保留策略与复制延迟：

1. 由于Binlog保留时长导致未清理场景建议检查Binlog保留策略。
2. 由于复制延迟导致无法清理Binlog场景建议通过控制台实例概览/智能异常诊断功能重建节点。
3. 由于第三方工具拉取Binlog无法清理场景建议检查工具运行情况，通过管理实时会话功能kill会话后Binlog可以正常清理。

临时空间增长

Kill慢会话或重启实例：

• MySQL 5.7版本：临时表空间存放在ibtmp1文件中，只能在重启实例时释放空间。
• MySQL 8.0版本：实现了会话级的临时表空间，通过管理实时会话功能kill会话，会话结束时自动删除文件。

过载异常前

监控告警配置与预防策略

• 设置监控告警

  在云监控上为IOPS、硬盘读吞吐量、硬盘写吞吐量、硬盘读耗时、硬盘写耗时等指标配置监控告警，详见设置告警规则。

• 监控主备节点复制状态

  生产数据库的实例类型请选择主备类型，复制延迟和节点异常及时处理。

  • 复制延迟处理请参见MySQL主备复制延迟场景及解决方案。
  • 节点异常请提交工单处理。

定期巡检日志

定期进行慢SQL巡检：定期检查慢查询、索引使用情况，防患于未然，详见管理慢日志。

性能压测

测试环境提前压测：在新功能上线或大促前，在测试环境对数据库进行压力测试，了解其IO瓶颈所在。

过载异常中

扩容实例

• 扩容规格

  由于业务增加导致当前存储规格IO达到上限，建议通过变更存储类型功能扩容规格。

• 扩容内存

  若由于内存不足导致硬盘读增加，建议通过扩容内存资源减少磁盘读IO。

慢SQL治理

• SQL限流与kill会话

  通过实时会话管理可以对慢会话SQL限流与kill会话。

• 慢查询优化
  • 针对扫描行数多、执行时间长的SQL进行优化。
  • 为查询条件列（WHERE）、连接列（JOIN）、排序/分组列（ORDER BY/GROUP BY）创建合适索引，避免全表扫描。
• 控制批量操作规模

  大数据量的导入/更新操作，应分批进行，并安排在业务低峰期。

检查主备复制