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

上次更新时间:2021 年 9 月 8 日

我的 Amazon Relational Database Service (Amazon RDS) for MySQL 或 MariaDB 实例显示为存储已满。为什么会发生这种情况以及如何查看数据库实例中的存储使用情况? 

简短描述

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

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

确定使用存储空间的内容后,就可以回收存储空间了。接着,监控 FreeStorageSpace 指标以避免再次耗尽空间。

注意:如果可用的存储空间骤减,请通过运行 SHOW FULL PROCESSLIST 命令来检查数据库实例级别正在进行的查询。SHOW FULL PROCESSLIST 命令提供有关每个连接执行的所有活动连接和查询的信息。要查看长时间处于活动状态的事务,请运行 INFORMATION_SCHEMA.INNODB_TRXSHOW 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> 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 网站上的临时表空间

InnoDB 表空间

有时,MySQL 会创建内部临时表,该表在查询正在进行时无法删除。这些临时表不是 information_schema 中名为“tables”的表的一部分。有关更多信息,请参阅 MySQL 网站上的 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 表空间

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

重要提示: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 语句

如果数据库实例充当复制备用实例,则使用以下命令检查中继日志 (Relay_Log_Space) 值的大小:

SHOW SLAVE STATUS\G

MySQL 日志(常规日志、慢速查询日志和错误日志)

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

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

注意:这些表不能为您提供日志的准确文件大小。修改参数,使 slow_loggeneral_loglog_output 值为“文件”,而不是“表”。

使用 Amazon CloudWatch 监控您的 Amazon RDS 数据库实例也是一种最佳做法。您可以在 FreeStorageSpace 指标上设置 CloudWatch 警报,以便在存储空间降至特定阈值以下时接收警报。最后,通过设置 CloudWatch 警报来监控 FreeStorageSpace 指标,以便在数据库实例的可用空间不足时收到通知。有关更多信息,请参阅如何创建 CloudWatch 警报来监控 Amazon RDS 可用存储空间并防止存储已满的问题?

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


这篇文章对您有帮助吗?


您是否需要账单或技术支持?