How do I view what is using my storage in Amazon RDS for MySQL or MariaDB?

Last updated: 2019-05-10

How can I view what is using my storage in my Amazon Relational Database Service (Amazon RDS) DB instance that is running MySQL?

Short Description

Analyze the total space used on your DB instance. Space on your DB instance is used for the following:

  • User-created databases
  • Binary logs
  • General logs, slow query logs, and error logs
  • InnoDB logs
  • InnoDB tablespace
  • MySQL slave relay logs, if you use a read replica
  • Table space

After you identify what is using storage space, you can reclaim storage space. Then, monitor the the FreeStorageSpace metric to avoid running out of space in the future.

Resolution

Analyze the total space used on the DB instance

Run the following query to find the size of each user-created database:

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;

Run the following queries to identify the approximate fragmented space at the database level and table level:

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;

Run the following command to retrieve the name and size of each binary log file available on the DB instance:

SHOW MASTER LOGS;

Check the size of the slow query, general, and error logs using the Amazon RDS console or the AWS Command Line Interface (AWS CLI).

Note: By default, slow query logs and general logs are stored in the slow_log and general_log tables inside the MySQL database. The tables don't provide you an accurate file size for the logs. Modify the parameter so that the value of log_output for slow_log and general_log is File instead of Table.

In some cases, MySQL creates internal temporary tables that can't be removed because a query is intervening. These temporary tables aren't part of the table named "tables" inside information_schema. Run the following query to find these internal temporary tables:

mysql> select * from innodb_sys_tables where name like '%#%';

The file size of the InnoDB tablespaces can't be queried on MySQL versions 5.5 and 5.6, but the file size can be queried in MySQL version 5.7 and higher. Run the following query to find the InnoDB system tablespace: 

mysql> select FILE_NAME, TABLESPACE_NAME, TABLE_NAME, ENGINE, INDEX_LENGTH, TOTAL_EXTENTS, EXTENT_SIZE from INFORMATION_SCHEMA.FILES where file_name like '%ibdata%';

Run the following query to find the InnoDB temporary tablespace:

mysql> select FILE_NAME, TABLESPACE_NAME, TABLE_NAME, ENGINE, INDEX_LENGTH, TOTAL_EXTENTS, EXTENT_SIZE from INFORMATION_SCHEMA.FILES where file_name like '%ibtmp%';

If the DB instance is acting as a replication slave, check the size of the relay logs by using the following command on the database:

SHOW SLAVE STATUS\G

Reclaim storage space

To reclaim space from fragmentation on user databases and tables, optimize InnoDB table. Because OPTIMIZE table isn't supported by InnoDB tables, Amazon RDS executes ALTER table instead.

To optimize an InnoDB table, execute the following command:

Important: This command uses the COPY ALGORITHM to create temporary tables that are the same size as the original table. Confirm that you have enough available disk space before running this command.

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

On MySQL 5.7 and higher, if temporary table (ibtmp1) uses excessive storage, reboot the DB instance to release the space.

If the slow query log and general log tables use excessive storage, Manage Table-Based MySQL Logs by manually rotating the log tables. To completely remove the old data and reclaim the disk space, call the following commands twice in succession:

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

Prevent future low storage space issues

The binary log retention period determines how long binary logs are stored on the DB instance. To check the current binary log retention period, run the following command:

mysql> call mysql.rds_show_configuration;

You can also reduce this value to retain logs for a shorter period, and therefore reduce the amount of space the logs use. A value of NULL means that logs are purged as soon as possible. If there is a slave for the master instance, monitor the ReplicaLag metric on the slave instance, because this delays the binary log processing on the master and relay log on the slave.

If general or slow query log files are consuming the storage space, reduce the storage space by disabling the general log or by reducing the number of slow query logs that are generated by increasing the long_query_time value.

If there is a sudden decrease in available storage, check ongoing queries at the DB instance level by running the SHOW FULL PROCESSLIST; command on the database. Running this command provides the details of all active connections, the queries that are executed by each connection, and how long the query has been in the current state. To review which transactions have been active for a long time, run the SHOW ENGINE INNODB STATUS; or SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX commands. Then, review the output.

Finally, monitor the FreeStorageSpace metric by setting up an Amazon CloudWatch alarm so that you receive a notification when your DB instance is low on free space.