How do I view what is using storage in an Amazon RDS DB instance that's running MySQL or MariaDB?

Last updated: 2020-03-13

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

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

Note: If you use a DB engine version that is higher than MySQL 5.7 or MariaDB 10.2.1, you can retrieve more accurate database/table size by using the following queries inside 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;

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. For more information, see the MySQL documentation for Internal Temporary Table Use in MySQL. Run the following query to find these internal temporary tables:

mysql> SELECT * FROM information_schema.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 standby instance, you can check the size of the relay logs by using value of Relay_Log_Space in 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. For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE ... FORCE, which rebuilds the table to update index statistics and free unused space in the clustered index. For more information, see the MySQL documentation for OPTIMIZE TABLE Statement. See the following example command for OPTIMIZE TABLE:

mysql> OPTIMIZE TABLE foo;

Alternatively, you can rebuild the table by running the following command:

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

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.

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 standby instance for the active instance, monitor the ReplicaLag metric on the standby instance, because this delays the binary log processing on the active instance and relay log on the standby instance.

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.