如何解决 Amazon RDS MySQL 数据库实例使用的存储超出预期的问题?

上次更新时间:2019 年 11 月 14 日

我在 MySQL 上的 Amazon Relational Database Service (Amazon RDS) 数据库实例使用的空间超出预期。为什么会发生这种情况,我该如何优化磁盘存储?

解决方法

您可以使用 FreeStorageSpace Amazon CloudWatch 指标监控 RDS 数据库实例的可用存储空间,但该指标不说明数据库实例具体占用存储的情况。

可使用以下策略回收存储空间:

运行 OPTIMIZE TABLE

表占用的一些空间并未使用,但仍会分配给表。如果启用了 innodb_file_per_table (默认情况下处于启用状态),则可以使用 OPTIMIZE TABLE 回收这些空间。OPTIMIZE TABLE 适用于 InnoDB、MyISAM 和 ARCHIVE 表,而当 Amazon RDS 接受 OPTIMIZE TABLE 命令时,Amazon RDS 将执行 ALTER TABLE...FORCE。当发生此情况时,您可以接受与以下内容类似的警告消息:“表不支持优化,进行重新创建 + 分析替代之。” 有关更多信息,请参阅 MySQL 文档中的 OPTIMIZE TABLE

要检查碎片,请运行如下类似查询:

SELECT
	table_name,
	data_length,
	max_data_length,
	index_length,
	data_free
FROM
	information_schema.tables 
WHERE table_schema='schema_name'
;

data_free 列突出显示分配给表但并未使用的可用空间量。如果按照 Amazon RDS 默认 innodb_file_per_table 配置设置在单独的表空间中创建了表,则可以使用 OPTIMIZE TABLE 回收这些空间。 有关更多信息,请参阅 MySQL 文档中的 File-Per-Table Tablespaces

减少应用程序表存储

要查看数据库实例上的应用程序表使用的存储,请运行如下类似查询:

SELECT 
	table_schema,
	SUM(data_length + index_length + data_free)/1024/1024 AS total_mb,
	SUM(data_length)/1024/1024 AS data_mb,
	SUM(index_length)/1024/1024 AS index_mb,
	SUM(data_free)/1024/1024 AS free_mb,
	COUNT(*) AS tables,
	CURDATE() AS today 
FROM 
	information_schema.tables
	GROUP BY table_schema
	ORDER BY 2 DESC
;

要找到数据库实例上的最大应用程序表,请运行如下类似查询:

SELECT 
	table_schema,
	table_name,
	(data_length + index_length + data_free)/1024/1024 AS total_mb,
	(data_length)/1024/1024 AS data_mb,
	(index_length)/1024/1024 AS index_mb,
	(data_free)/1024/1024 AS free_mb,
	CURDATE() AS today
FROM 
	information_schema.tables
	ORDER BY 3 DESC
;

注意:如果数据库包含的表具有长度可变的列,且列长度超过 768 个字节(例如,BLOB、TEXT、VARCHAR 或 VARBINARY),则无法计算单个数据库和表使用的总存储。

减少二进制日志存储

添加只读副本会导致主实例的二进制日志使用额外存储。要查明主实例上的二进制日志使用的存储空间,请查看 BinLogDiskUsage CloudWatch 指标。大量增长可能表明一个或多个只读副本未同步。有关更多信息,请参阅访问 MySQL 二进制日志

减少或禁用常规日志和慢速查询日志存储

启用常规日志和慢速查询日志参数会导致数据库实例开始存储并备份这些日志。要轮换这些文件并控制磁盘使用量,请参阅 mysql.rds_rotate_general_logmysql.rds_rotate_slow_log

注意:为避免潜在的性能和磁盘使用问题,请在不使用常规日志和慢速查询日志排查问题时禁用这些日志。

管理或减少 InnoDB 系统表空间大小

系统表空间包含 InnoDB 数据字典和撤消空间,初始值为 10MB。分配空间后,文件将始终至少是这个大小,不过长时间运行的事务可能会占用更多的可用存储。

默认情况下,Amazon RDS 将 innodb_file_per_table 设置为 1,这意味着每个表空间的数据存储在自己的 .ibd 文件中。如需回收标记为可供相关表重用的空间,可以使用 OPTIMIZE TABLE 命令调整每个表的表空间文件大小或者删除表。

如果 innodb_file_per_table 设置为 0,则会将所有表分配到系统表空间。删除表或索引,或者删除或截断分配到系统表空间的表中的数据,会将以前占用的空间标记为可重用。但是,该命令不会将任何空间释放到文件系统。

由于无法就地缩减系统表空间,因此请导出当前数据库的数据,然后再将数据导入新实例。为减少停机时间,请将新的 MySQL 实例配置为源 Amazon RDS 主实例的副本。当副本与源 Amazon RDS 主实例同步时,切换到新实例。有关手动复制的信息,请参阅利用 Amazon RDS 外部运行的 MySQL 或 MariaDB 实例进行复制

注意:从快照还原或创建只读副本不会帮助您从系统表空间中回收空间。这是因为这两种方法均使用包含系统表空间的源实例存储卷的快照。