通过删除不必要的数据来优化 Aurora MySQL 存储

我们的 Aurora MySQL 数据库中的一个表消耗了总存储的大约 80%（约 400 GB）。由于我们能够将旧数据存档为 CSV 文件，因此我们决定删除旧记录并释放存储空间。

一开始我以为删除记录就能释放存储空间，但结果比想象的要复杂。所以，我记录了详细步骤以供将来参考。

检查表存储使用情况

您可以使用以下查询检查每个 .ibd 文件的大小：

SELECT file_name, ROUND(SUM(total_extents * extent_size)/1024/1024/1024,2) AS "TableSizeinGB" 
FROM information_schema.files 
GROUP BY file_name 
ORDER BY total_extents DESC;

参考：MySQL 文档

重要提示

AWS re:Post 建议使用以下查询来检查表大小，但目标表的结果比第一个查询小约 150 GB。

SELECT table_schema "DB Name", table_name, 
       (data_length + index_length)/1024/1024/1024 AS "TableSizeinGB" 
FROM information_schema.tables 
WHERE table_schema = 'database_name';

当我咨询 AWS Support 时，他们确认 information_schema.tables 仅提供统计值，而这些值通常不准确。他们建议使用 information_schema.files 来获取精确的数据。

有关表大小（390 GB）的信息是从 information_schema.tables 中检索的，由于这是统计数据，因此可能不准确。将来，我们建议使用 information_schema.files 来检索表大小信息。

参考：AWS re:Post

检查数据库存储使用情况

以下查询检查总体数据库使用情况。这也使用 information_schema.files 来保证准确性。

SELECT file_name, ROUND(SUM(total_extents * extent_size)/1024/1024/1024,2) AS "TableSizeinGB" 
FROM information_schema.files 
WHERE file_name LIKE '%/database_name/%';

释放数据库存储的步骤

以下是释放存储空间的步骤：

1. 删除旧记录。
2. 如有必要，更改实例类。
3. 运行 OPTIMIZE TABLE ;.

单纯删除记录并不会释放存储空间；您需要运行 OPTIMIZE TABLE 来释放空间。

此外，在 OPTIMIZE TABLE（或 ALTER TABLE ... FORCE）操作期间，会创建临时中间表文件。在 Aurora 中，这些临时文件存储在本地存储中。本地存储量取决于实例类。就我而言，db.r6g.xlarge 实例只有 80 GB 本地存储，这不足以容纳已删除记录的大小。因此，我暂时扩展到 db.r6g.8xlarge (640 GB)。

参考：优化表

参考：更改表

参考：InnoDB Online DDL空间要求

参考：Aurora MySQL 临时存储

使用 OPTIMIZE TABLE 时的注意事项

删除大约 250 GB 的记录后，运行 OPTIMIZE TABLE 花费了大约 130 分钟（大约 2 小时）。由于 OPTIMIZE TABLE 会锁定表，因此您可能需要安排停机时间或在非高峰时段执行此操作。作为参考，删除所有记录总共花了大约 15 个小时，我分散了好几天。

以上是通过删除不必要的数据来优化 Aurora MySQL 存储的详细内容。更多信息请关注PHP中文网其他相关文章！