RDS/Aurora MySQL 的大表操作和管理最佳实践之大表 DROP

1. 前言

Amazon Relational Database Service（Amazon RDS）是一项 Web 服务，让用户能够在云上更轻松地设置、扩展和管理关系型数据库。Amazon RDS 支持 MySQL、PostgreSQL、MariraDB、Oracle、Microsoft SQL Server 等多种引擎。Amazon Aurora 则是一款 Amazon 自研的全托管类兼容 MySQL 和 PostgreSQL 的关系型数据库产品，在 RDS MySQL 和 Aurora MySQL 的日常使用过程中，大表的使用和维护经常成为数据库使用人员一个比较关注的一件事情。本篇博客主要介绍在 RDS 和 Aurora 的 MySQL 引擎中如何优雅地进行大表 drop 操作，以及日常 drop 操作的原理和影响。

2. RDS MySQL 中的 DROP TABLE 操作

Drop Table 一般在数据库的使用过程中，随着业务的增长，数据库库表的大小也会不断扩展。当库表到达一定大小时，出于缩减空间的需求，用户会选择进行梳理并删除无用业务表的操作。也有部分用户，因业务发展，不再需要部分表，也会选择直接进行 drop 操作。一般来说，当一些表的大小超过 100GB 的时候，用户在做此类表删除动作时，便会对表删除时的耗时以及对实例其他表的影响会有一个顾虑。

基于用户常见的问题以及顾虑，本篇文章将围绕以下几个主题展开：

• Amazon RDS MySQL 中 DROP TABLE 是否影响其他表操作；
• DROP TABLE 操作在 Amazon Aurora MySQL 与 Amazon RDS MySQL 中的原理差异；
• DROP TABLE 操作在 Amazon Aurora MySQL 与 Amazon RDS MySQL 中的耗时以及 CPU 资源或 IO 资源对比。

2.1 DROP TABLE 是否影响其他表操作

Drop Table 操作从原理上我们可以认为主要分为两个阶段，一是对 buffer pool 的 drop 表相关缓存页的清理，这里又包含对自适应哈希索引AHI（Adaptive Hash Index）的索引记录的清理以及 buffer pool 中数据页的清理；另一部份则是数据文件的清理。

Drop Table 对其他表操作的影响，上述两个阶段都会有相关影响，其中对于 buffer pool 中 drop 表相关缓存页的清理这个问题社区版 MySQL 历数版本也一直在跟进优化，时至今日的 MySQL8.0 已经相对早期版本有相当大的调整和优化了。

在 MySQL5 的版本中，当删除一张表的时候，会出现数据库卡顿的现象，并且 buffer_pool_size 越大，这种现象越严重。究其原因，与 drop 操作时对 buffer pool 中的 LRU 列表的扫描和同步删除以及操作期间持有的较大粒度 mutex 密切相关。MySQL 也意识到这个问题了，在 MySQL 5.5.23 版本开始，做了数据页扫描次数以及 LRU（least recently used）列表惰性清理优化和阶段性释放粒度较大的 mutex 的改动。此后，Drop Table 操作对 LRU 列表的扫描由原来的两次扫描优化为扫描一次 flush list 来删除表的数据页以及一次为了删除 AHI 条目的一次 LRU 列表，其中 flush list 长度一般小于 buffer pool LRU 列表的长度，而不存在 flush list 中的 drop 表相关的数据页则会依然保持这些页面作为 buffer pool LRU 列表的一部分，并且当没有新的写入时，它们会随着年龄增长而从按照正常剔除逻辑 LRU 尾部被逐出列表，通过这种方式实现了减少列表扫描次数以及惰性清理的优化策略。另外优化后 buffer pool mutex 会定期释放，不再是一直持有直到扫描完 buffer pool LRU list 造成持有 mutex 的时间太长，阻塞其他请求。几项优化措施的加持下，使得 Drop Table 操作效率以及对其他业务表的影响大大减少了。MySQL 5.6 版本支持了多个 buffer pool 的拆分，5.7 版本引入多个 page cleaner 并行刷脏页，因此对于上述 DROP TABLE 的处理，又得到进一步优化，即持有大粒度 mutex 的时间越来越少，对业务的影响也就越来越少。MySQL 8.0 中对数据字典的管理的优化[1]以及在 8.0.3 中对 dict_operation_lock 和 dict_sys mutex 在 DDL 期间持有时间的优化[2] [3]，使得 DROP TABLE 操作的效率进一步提升。

经过一系列的优化后，对于现有 MySQL8.0 版本过程简化版操作逻辑可参考下图。

目前来说，现行版本 MySQL8.0 以及兼容该版本的如 Amazon RDS MySQL 在 Drop Table 操作期间无论是对其他业务表的影响还是对 CPU 资源以及磁盘 IO 资源的占用都相对来说比较小了，具体的影响可以参见下面章节测试数据。

2.2 DROP TABLE 操作在 Amazon Aurora MySQL 与 MySQL 中的原理差异

Amazon Aurora 是为云构建的兼容 MySQL 和 PostgreSQL 的关系数据库，它将传统企业数据库的性能和可用性与开源数据库的简单性和成本效益相结合。 Amazon Aurora 具有分布式、容错、自我修复的存储系统，每个数据库实例可自动扩展至 128TB。其存算分离的架构以及日志即数据库的特性决定了 Drop Table 操作与 RDS MySQL 便存在根本的差异。

基于上一章节的叙述我们了解到 MySQL 的 Drop Table 操作主要分为 buffer pool 内待删除表的数据缓存页清理以及数据文件的删除，buffer pool 内部的清理经过版本迭代以及优化后，现在整体影响还是相对客观的。那对于数据文件的清理，在 MySQL 上的大表来说，直接删除会瞬时占用大量 I/O，有可能会造成 IO 阻塞。所以部分用户会选择创建表文件的硬链接，通过这种方式避免产生较高 IO，影响数据库性能，同时因为多个文件名指向同一个 inode 时，我们再去删除其中任意一个文件名都是很快的，所以硬连接的方式让我们的 Drop Table 操作看起来快且影响小，然后再选择在一个业务低峰期，删除数据文件，将 Drop Table 的操作影响降到最低。如下列操作步骤所示。

ln drop_t.ibd  drop_t.ibd.bak
drop table drop_t;
rm  -f  drop_t.ibd.bak

对于 Aurora MySQL 来说，数据页在存储层的存储结构和 RDS MySQL 有着根本性的区别，Aurora MySQL 中的数据为 6 副本分布在 3 可用区中，并且计算层写入存储层存为 Redo Log，该部分 log 在存储层通过不断的合并为数据页，以便在计算层读取。在 Drop Table 指令发送至 Aurora MySQL 时，计算节点仍然需要清理 buffer pool 中相关表的数据页，但是存储层则会收缩表相关的可用空间并直接反馈至 Volume Bytes Used (GiB)这个监控指标中，降低该部分存储费用。而在后台则会通过异步方式再逐步清理 drop 表相关的数据页以及日志页。除了存储层的区别外，Aurora 在不同版本也融合了社区版 MySQL 对 Drop Table 操作的优化，如存在于 Aurora 2 本中对分区表 truncate/drop 操作的 mutex 粒度的优化、Aurora 3 版本结合 MySQL8.0 中原子 DDL 对数据字典互斥锁 dict_sys 的优化等，结合 Aurora 关闭 innodb_adaptive_hash_index 参数等调整，相较于 Aurora MySQL 2，Aurora 3 中执行时间得到极大的缩短，且因 Drop Table 或 truncate table 长时间持有字典互斥锁导致阻塞甚至超过 MySQL 信号量 600 秒超时触发 Crash 事件概率大大减少。具体对比数据可参照下一章节测试和对比详情。

2.3 DROP TABLE 操作在 Amazon Aurora MySQL 与 Amazon RDS MySQL 中的耗时以及 CPU 资源或 IO 资源对比

结合分别在 Amazon RDS MySQL 以及 Amazon RDS Aurora 的兼容 MySQL5.7 以及 MySQL8.0 版本的 4 个实例上进行了 460GB 大表的 DROP 测试，整体的执行时间，兼容 MySQL8.0 版本的 Amazon RDS 以及 Amazon Aurora 耗时均在秒级以内，兼容 MySQL5.7 版本的 Amazon RDS 以及 Amazon Aurora 耗时均在 20 秒以内，相较于 MySQL5.7 的兼容版本，速度提升巨大。具体数值如下表，测试数据为 2,500,000,000 行，其中表大小以及数据表行数均来自于 INFORMATION_SCHEMA 中的统计数据，与真实数据存在些许偏差。

同时 Amazon Aurora for MySQL 在 drop 操作完成之后，有近一个小时的间隔 IO 操作，表明 Aurora 在处理大表操作是，对表相关文件的 IO 操作是由后台进程操作且分散在一段持续时间，而非类似于传统磁盘数据文件清理方式，可能导致瞬时 IO 对磁盘造成阻塞等情况。而对于 Amazon RDS for MySQL 来说，测试中使用的是 Amazon EBS 通用型 SSD（gp3）卷，在清理 drop 表对应数据文件时，会产生如图所示的瞬时少量 IO，后续清理将通过异步操作进行空间覆盖或清理。相关监控如下图所示。

另外在 RDS MySQL5.7 实例上执行 Drop Table 的同时，开启另一会话执行 Create Table 操作，会发现 Create Table 操作会保持等待至 Drop Table 操作完成，在 Aurora 2 中同样存在该现象。在 Aurora 3 中，因为整体 Drop Table 执行时间已经优化到 1 秒之内，这样的等待对其他业务操作的影响将微乎其微。如下图所示。

3. 总结

通过本文的测试以及 MySQL 社区相关说明和 Aurora 相关优化资料表明，Aurora 3 以及 RDS 8.0 在表的 DROP 操作上，执行时间相较于 Aurora 2 以及 RSD 5.7 有一个极大的优化，并且 Aurora 的数据表 DROP 操作，无论涉及的数据文件有多大，相关数据文件的清理动作将在 Drop Table 操作之后逐步被清理，对数据库实例不存在一个瞬时 IO 压力，但是持续时间段对实例 CPU 以及 IO 资源都存在一定的消耗，所以大表操作还是建议尽量避开业务高峰期进行操作，且在版本选择上，现行版本均以涵盖相关优化策略，可放心选择。另外，鉴于 Aurora 2 以及 RDS MySQL5.7 现在均已停止支持，进入扩展支持阶段，建议尽快升级相关版本数据库实例至兼容 MySQL8.0 版本，这将不仅仅在 Drop Table 操作上得到相应的优化，同时还会带来诸多特性以及提升，具体可参考相关博客升级至 Amazon Aurora MySQL 版本 3（兼容 MySQL 8.0）。

本篇作者