为什么我的 Amazon RDS for MySQL 或 MariaDB 实例显示为存储已满？

简短描述

要解决存储空间已满问题，必须首先分析数据库实例上使用的总空间。数据库实例上的空间用于以下方面：

• 用户创建的数据库
• 临时表
• 二进制日志或者 MySQL 备用实例中继日志（如果使用读取副本）
• InnoDB 表空间
• 常规日志、慢速查询日志和错误日志

在确定存储空间的使用情况后，可以回收存储空间。然后，监控 FreeStorageSpace 指标，以避免再次耗尽空间。

**注意：**如果可用存储空间突然减少，请运行 SHOW FULL PROCESSLIST 命令，检查数据库实例级别正在进行的查询。SHOW FULL PROCESSLIST 命令会提供有关所有活动连接和每个连接执行的查询的信息。要查看长期处于活动状态的事务，请运行 INFORMATION_SCHEMA.INNODB_TRX 或 SHOW ENGINE INNODB STATUS 命令。然后，查看输出。

解决方法

分析数据库实例（用户创建的数据库）上使用的总空间

要查找每个用户创建的数据库的大小，请运行以下查询：

mysql> SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024/1024,2) "size in GB" FROM information_schema.tables GROUP BY 1 ORDER BY 2 DESC;

要检查（数据库实例中的）特定数据库的每个表的大小，请运行以下查询：

mysql> 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';

要在 MySQL 5.7 及更高版本或者 MySQL 8.0 及更高版本中获得更准确的表大小，请使用以下查询：
**注意：**information_schema.files 查询不适用于 MariaDB 引擎。

mysql> 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/%';

要获取完整的存储详细信息以及数据库级别和表级别的大致碎片空间，请运行下列查询：
注意： 此查询不适用于驻留在共享表空间中的表。

mysql> SELECT table_schema AS "DB_NAME", SUM(size) "DB_SIZE", SUM(fragmented_space) APPROXIMATED_FRAGMENTED_SPACE_GB FROM (SELECT table_schema, table_name, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) AS size, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2)
    AS fragmented_space FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ) AS TEMP GROUP BY DB_NAME ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

mysql> SELECT table_schema DB_NAME, table_name TABLE_NAME, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) SIZE_GB, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2) APPROXIMATED_FRAGMENTED_SPACE_GB from information_schema.tables
    WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

记录从这两个查询获得的数据库大小，并将它们与 Amazon RDS 中的 Amazon CloudWatch 指标进行比较。然后，可以确认是否由于数据使用而导致存储空间已满。

临时表

InnoDB 用户创建的临时表和磁盘上的内部临时表是在名为 ibtmp1 的临时表空间文件中创建的。有时，临时表空间文件甚至可以扩展到 MySQL 数据目录中的 ibtmp2。

**提示：**如果临时表（ibtmp1）使用的存储空间过多，请重启数据库实例以释放空间。

在线 DDL 操作使用临时日志文件来执行以下操作：

• 记录并发 DML
• 创建索引时创建临时排序文件
• 重建表时创建临时中间表文件（这样临时表可能会占用存储空间）

**注意：**InnoDB 表空间的文件大小只能使用 MySQL 5.7 及更高版本或 MySQL 8.0 及更高版本进行查询。

要查找 InnoDB 临时表空间，请运行以下查询：

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibtmp%';

要回收全局临时表空间数据文件占用的磁盘空间，请重启 MySQL 服务器或重启数据库实例。有关更多信息，请参阅 MySQL 网站上的 The temporary tablespace。

InnoDB 表空间

有时，MySQL 会创建内部临时表，该表在查询正在进行时无法删除。这些临时表不是 information_schema 中名为“tables”的表的一部分。有关更多信息，请参阅 MySQL 网站上的 Internal temporary table use in MySQL。

运行以下查询来查找这些内部临时表：

mysql> SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE '%#%';

InnoDB 系统表空间是 InnoDB 数据字典的存储区域。除了数据字典之外，双写缓冲区、更改缓冲区和撤消日志也位于 InnoDB 系统表空间中。此外，如果表是在系统表空间（而不是 file-per-table 或通用表空间）中创建的，则表空间可能包含索引和表数据。

运行以下查询，查找 InnoDB 系统表空间：

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibdata%';

**注意：**此查询在 MySQL 5.7 及更高版本或 MySQL 8.0 及更高版本上运行。

系统表空间的大小增加后无法减少。但是，您可以转储所有 InnoDB 表，并将这些表导入到一个新的 MySQL 数据库实例中。为避免出现较大的系统表空间，请考虑使用 file-per-table 表空间。有关更多信息，请参阅 MySQL 网站上的 File-per-table tablespaces。

如果确实启用了 Innodb_file_per_table，则每个表都会将数据和索引存储在自身的表空间文件中。通过在该表上运行 OPTIMIZE TABLE，您可以（从数据库和表的碎片中）回收空间。OPTIMIZE TABLE 命令会创建表的新空副本。然后，旧表中的数据将逐行复制到新表中。在此过程中，将创建一个新的 .ibd 表空间并回收空间。有关此过程的更多信息，请参阅 MySQL 网站上的 OPTIMIZE TABLE statement。

**重要提示：**OPTIMIZE TABLE 命令会使用 COPY 算法创建与原始表大小相同的临时表。在运行此命令之前，请确认您有足够的可用磁盘空间。

要优化表，请运行以下命令语法：

mysql> OPTIMIZE TABLE <tablename>;

或者，也可以通过运行以下命令来重建表：

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

二进制日志

如果在 Amazon RDS 实例上激活了自动备份，则二进制日志也会在数据库实例上自动激活。这些二进制日志存储在磁盘上并占用存储空间，但在每次配置二进制日志保留时都会被清除。实例的默认 binlog 保留值也设置为“Null”，这意味着文件将立即删除。

为避免存储空间不足问题，请在 Amazon RDS for MySQL 中设置适当的二进制日志保留期。您可以使用 mysql.rds_show_configuration 命令语法查看二进制日志的保留小时数：

CALL mysql.rds_show_configuration;

您也可以降低此值来缩短日志保留时间，从而减少日志使用的空间大小。NULL 值表示尽快清除日志。如果活动实例有备用实例，则监控备用实例上的 ReplicaLag 指标。ReplicaLag 指标表示在对活动实例上的二进制日志或备用实例上的中继日志进行处理期间出现的任何延迟。

如果活动实例有备用实例，则监控备用实例上的 ReplicaLag 指标。ReplicaLag 指标表示清除活动实例上的二进制日志和备用实例上的中继日志期间发生的任何延迟。如果存在清除或复制问题，则这些二进制日志可能会随着时间的推移而累积，从而消耗额外的磁盘空间。要检查实例上的二进制日志数量和文件大小，请使用 SHOW BINARY LOGS 命令。有关更多信息，请参阅 MySQL 网站上的 SHOW BINARY LOGS statement。

如果数据库实例充当复制备用实例，则使用以下命令检查中继日志（Relay_Log_Space）值的大小：

SHOW SLAVE STATUS\G

MySQL 日志（常规日志、慢速查询日志和错误日志）

Amazon RDS for MySQL 提供了可用于监控数据库的日志（例如常规日志、慢速查询日志和错误日志）。默认情况下，错误日志处于活动状态。但是，可以使用 RDS 实例上的自定义参数组激活常规日志和慢速查询日志。激活慢速查询日志和常规日志后，它们将自动存储在 MySQL 数据库的 slow_log 和 general_log tables 表中。要检查任何慢速查询、常规日志（“FILE”类型）和错误日志的大小，请查看并列出数据库日志文件。

如果慢速查询日志和常规日志表使用的存储空间过多，则通过手动轮换日志表来管理基于表的 MySQL 日志。要完全删除旧数据并回收磁盘空间，请连续两次调用以下命令：

mysql> CALL mysql.rds_rotate_slow_log;
mysql> CALL mysql.rds_rotate_general_log;

**注意：**这些表不能为您提供日志的准确文件大小。修改参数，使 slow_log 和 general_log 的 log_output 值为“File”而不是“Table”。

最好还使用 Amazon CloudWatch 来监控 Amazon RDS DB 实例。您可以在 FreeStorageSpace 指标上设置 CloudWatch 警报，以便在存储空间降至特定阈值以下时接收警报。最后，通过设置 CloudWatch 警报来监控 FreeStorageSpace 指标，以便在数据库实例的可用空间不足时接收通知。有关更多信息，请参阅如何创建 CloudWatch 告警来监控 Amazon RDS 的可用存储空间并防止出现存储空间已满问题？

此外，您可以使用 Amazon RDS 存储自动扩展功能来自动管理容量。有了存储自动扩展功能，您不必手动纵向扩展数据库存储空间。有关 Amazon RDS 存储自动扩展的更多信息，请参阅使用 Amazon RDS 数据库实例的存储。

---

相关信息

如何解决 Amazon RDS for MySQL 数据库实例使用的存储空间超过预期的问题？