如何查看什么在使用运行 MySQL 或 MariaDB 的 Amazon RDS 数据库实例中的存储空间?

上次更新时间:2020 年 3 月 13 日

如何查看什么在使用运行 MySQL 或 MariaDB 的 Amazon Relational Database Service (Amazon RDS) 数据库实例中的储存空间?

简短描述

分析数据库实例中已使用的总空间。数据库实例上的空间用于以下方面:

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

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

解决方案

分析数据库实例上已使用的总空间

运行以下查询以查找每个用户创建的数据库的大小:

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

注意:如果您使用的数据库引擎版本高于 MySQL 5.7 或 MariaDB 10.2.1,则可以在 information_schema 内使用以下查询检索更准确的数据库/表大小:

mysql> SELECT SUBSTRING_INDEX(it.name, '/', 1) AS table_schema,
ROUND(SUM(its.allocated_size)/1024/1024/1024, 2) "size in GB" FROM
information_schema.innodb_sys_tables it INNER JOIN information_schema.innodb_sys_tablespaces its ON it.space =
its.space GROUP BY 1 ORDER BY 2 DESC;
mysql> SELECT SUBSTRING_INDEX(it.name, '/', 1) AS table_schema, ROUND(SUM(its.allocated_size)/1024/1024/1024,2) "size in GB",
ROUND(SUM(t.data_free)/1024/1024/1024,2) "fragmented size in GB"  FROM
information_schema.innodb_sys_tables it INNER
JOIN  information_schema.innodb_sys_tablespaces
its ON it.space = its.space INNER JOIN information_schema.innodb_sys_tablestats istat ON istat.table_id =
it.table_id INNER JOIN information_schema.tables t ON t.table_schema = SUBSTRING_INDEX(it.name,
'/', 1) AND t.table_name = SUBSTRING_INDEX(it.name, '/', -1) GROUP BY 1 ORDER BY 2 DESC;
mysql> SELECT SUBSTRING_INDEX(it.name, '/', 1) AS
table_schema, t.table_name, ROUND(its.allocated_size/1024/1024/1024,2)
"size in GB", ROUND(t.data_free/1024/1024/1024,2) "fragmented
size in GB"  FROM information_schema.innodb_sys_tables
it INNER JOIN information_schema.innodb_sys_tablespaces its ON it.space = its.space INNER JOIN
information_schema.innodb_sys_tablestats istat ON istat.table_id = it.table_id INNER JOIN information_schema.tables t
ON t.table_schema = SUBSTRING_INDEX(it.name, '/', 1) AND t.table_name =
SUBSTRING_INDEX(it.name, '/', -1)  WHERE
t.table_schema NOT IN ('performance_schema', 'mysql', 'information_schema')
ORDER BY 4 DESC;

运行以下命令以检索数据库实例上可用的每个二进制日志文件的名称和大小:

SHOW MASTER LOGS;

使用 Amazon RDS 控制台或 AWS 命令行界面 (AWS CLI) 检查慢速查询、常规和错误日志的大小。

注意:默认情况下,慢速查询日志和常规日志存储在 MySQL 数据库中的 slow_log general_log 表里。这些表不能为您提供日志的准确文件大小。修改参数,使 slow_loggeneral_loglog_output 值为文件,而不是

在某些情况下,MySQL 会创建内部临时表,该表在查询正在进行时无法删除。这些临时表不是 information_schema 中名为“tables”的表的一部分。有关更多信息,请参阅 MySQL 文档中的 MySQL 中的内部临时表使用。运行以下查询以查找这些内部临时表:

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

在 MySQL 5.5 和 5.6 版本上无法查询 InnoDB 表空间的文件大小,但可以在 MySQL 5.7 及更高版本中查询文件大小。运行以下查询以查找 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%';

运行以下查询以查找 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%';

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

SHOW SLAVE STATUS\G

回收存储空间

要从用户数据库和表的碎片中回收空间,请优化 InnoDB 表。对于 InnoDB 表,OPTIMIZE TABLE 将映射至 ALTER TABLE ... FORCE,这将会重建表,以更新索引统计数据并释放聚集索引中的未使用空间。有关更多信息,请参阅 MySQL 文档中的 OPTIMIZE TABLE 语句。请参阅以下 OPTIMIZE TABLE 示例命令:

mysql> OPTIMIZE TABLE foo;

或者,您可以通过运行以下命令重建表:

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

重要提示:此命令使用 COPY ALGORITHM 创建与原始表大小相同的临时表。在运行此命令之前,请确认您有足够的可用磁盘空间。

在 MySQL 5.7 及更高版本上,如果临时表 (ibtmp1) 使用过多存储空间,则重新启动数据库实例以释放空间。

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

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

防止未来发生低存储空间问题

二进制日志保留期决定二进制日志在数据库实例上存储的时间。要查看当前的二进制日志保留期,请运行以下命令:

mysql> CALL mysql.rds_show_configuration;

您还可以降低此值以缩短保留日志的时间,从而减少日志使用的空间大小。NULL 值表示尽快清除日志。如果活动实例存在一个备用示例,则监控备用实例上的 ReplicaLag 指标,因为这会减慢活动实例上的二进制日志处理和备用实例上的中继日志处理。

如果可用的存储空间骤减,请通过在数据库上运行 SHOW FULL PROCESSLIST; 命令来检查数据库实例级别正在进行的查询。运行此命令可获得所有活动连接的详细信息,其中包括每个连接执行的查询以及查询处于当前状态的时间。要查看长时间处于活动状态的事务,请运行 SHOW ENGINE INNODB STATUS; SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX 命令。然后查看输出。

最后,通过设置 Amazon CloudWatch 警报来监控 FreeStorageSpace 指标,以便在数据库实例的可用空间不足时收到通知。


这篇文章对您有帮助吗?

我们可以改进什么?


需要更多帮助?