How do I troubleshoot an Amazon RDS for MySQL or MariaDB instance that shows storage full?

7 minute read

I want to troubleshoot an Amazon Relational Database Service (Amazon RDS) for MySQL or MariaDB instance that shows storage full.

Short description

To troubleshoot an Amazon RDS for MySQL or MariaDB instance that shows storage full, check the total space used on your DB instance. Identify what uses space on your DB instance. You can use the space on your DB instance for the following objects:

User-created databases
Temporary tables
Binary logs or MySQL standby instance relay logs for read replicas
InnoDB tablespace
General logs, slow query logs, and error logs

After you check your storage space and identify what uses the space, you can reclaim space. Then, you can monitor the FreeStorageSpace Amazon CloudWatch metric to prevent further storage space issues.

Note: If there's a sudden decrease in available storage, then run the SHOW FULL PROCESSLIST command to check queries at the DB instance level. The SHOW FULL PROCESSLIST command provides information about all active connections and queries that each connection performs. To review the transactions that have been active for a long time, first run the INFORMATION_SCHEMA.INNODB_TRX or SHOW ENGINE INNODB STATUS command. Then, review the output.

Resolution

To troubleshoot an Amazon RDS for MySQL or MariaDB instance that shows storage full, complete the following sections:

Check the total space used on your MySQL DB instance

To identify the size of each user-created database, run the following command:

SELECT SUBSTRING_INDEX(TABLESPACE_NAME,"/",1) AS DATABASE_NAME, ROUND((DATA_FREE/1024/1024/1024),3) AS 'REUSABLE (GB)', ROUND(SUM((TOTAL_EXTENTS * EXTENT_SIZE)/1024/1024/1024),3) AS 'TOTAL (GB)' FROM INFORMATION_SCHEMA.FILES GROUP BY DATABASE_NAME ORDER BY 'TOTAL (GB)' DESC;

To check the size of each table for a user database that you specify, run the following command:

SELECT SUBSTRING_INDEX(TABLESPACE_NAME,"/",-1) as 'TABLE_NAME', ROUND((total_extents * extent_size)/1024/1024/1024,3) AS "TableSizeinGB" from information_schema.files WHERE FILE_NAME LIKE 'example-database-name';

Note: Replace example-database-name with your database name.

Check the total space used on your MariaDB instance

To identify the size of each user-created database, run the following command:

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;

To check the size of each table for a user database that you specify, run the following command:

SELECT table_schema "example-database", example-table,(data_length + index_length)/1024/1024/1024 AS "TableSizeinGB" from information_schema.tables where table_schema='example-database';

Note: Replace example-database with the database name and example-table with the table name.

Check your temporary tables

MySQL stores InnoDB user-created temporary tables and on-disk internal temporary tables in a temporary tablespace file named ibtmp1. Temporary tablespace files can extend to ibtmp2 in the MySQL data directory. If the temporary table ibtmp1 uses excessive storage, then reboot the DB instance to release the space.

Note: You must use only MySQL versions 5.7 and later to query the file sizes of the InnoDB tablespace.

To identify the InnoDB temporary tablespace, run the following command:

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

To reclaim disk space that a global temporary tablespace data file occupies, restart the MySQL server or reboot your DB instance. For more information, see The temporary tablespace on the MySQL website.

Check your InnoDB tablespace

MySQL might create internal temporary tables that you can't remove while a query runs. These temporary tables aren't part of the table named tables inside information_schema. For more information, see Internal temporary table use in MySQL on the MySQL website.

To identify the internal temporary tables, run one of the following commands based on your MySQL DB instance version:

For MySQL 8.0 and later DB instances, run the following command:

SELECT * FROM information_schema.innodb_tables WHERE name LIKE '%#%';

For earlier MySQL DB instances, run the following command:

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

To identify the InnoDB system tablespace, run the following command:

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

Note: The preceding query works on MySQL versions 5.7 and later.

When the size of your system tablespace increases, you can't decrease it. To work around this, you can dump your InnoDB tables and import the tables into a new MySQL DB instance. For more information, see Importing data from an external MySQL database to an Amazon RDS for MySQL DB instance.

To avoid large system tablespaces, use file-per-table tablespaces. For more information, see File-per-table tablespaces on the MySQL website.

If you turn on Innodb_file_per_table, then each table stores the data and index in its own tablespace file. To reclaim the space, run OPTIMIZE TABLE. For more information, see OPTIMIZE TABLE statement on the MySQL website.

Note: The OPTIMIZE TABLE command uses the COPY algorithm to create temporary tables that are the same size as the original table. Make sure that you have available disk space before you run OPTIMIZE TABLE.

To optimize your table, run the following command:

OPTIMIZE TABLE example-table-name;

Note: Replace example-table-name with the table that you want to optimize.

(Optional) To rebuild the table, run the following command:

ALTER TABLE example-table-name ENGINE=INNODB;

Note: Replace example-table-name with the table that you want to rebuild.

Check your binary logs

If you turn on automated backups on your Amazon RDS instance, then Amazon RDS automatically activates binary logs on your DB instance. Binary logs consume storage space on the disk, but Amazon RDS removes them at every binary log retention configuration. The default binlog retention value for your instance is Null, and Amazon RDS removes files immediately.

To avoid low storage space issues, you must set the correct value for the binary log retention period in Amazon RDS for MySQL.

To view the number of hours that Amazon RDS retains a binary log, run the following command:

CALL mysql.rds_show_configuration;

To set the number of hours that Amazon RDS retains a binary log, run the following command:

CALL mysql.rds_set_configuration('binlog retention hours', 24);

Note: Replace 24 with the number of hours that you want to retain binary logs. The maximum value is 168. A value of NULL immediately removes logs.

If there's a standby instance for the active instance, then monitor the ReplicaLag metric on the standby instance. The ReplicaLag metric shows delays that occur when the active instance applies binary logs or the standby instance applies relay logs.

If log purge or replication issues occur, then binary logs can accumulate over time and consume more disk space. To check the number of binary logs on an instance and file size, use the SHOW BINARY LOGS command. For more information, see SHOW BINARY LOGS statement on the MySQL website.

If the DB instance acts as a replication standby instance, then view the size of the relay logs. To do this, run one of the following commands and check Relay_Log_Space:

For MySQL 8.0 and later DB instances, run the following command:

SHOW REPLICA STATUS\G

For earlier MySQL DB instances, run the following command:

SHOW SLAVE STATUS\G

Check your MySQL logs

To check the sizes of slow queries, general logs of FILE type, and error logs, view and list the database log files. If the slow query log and general log tables use excessive storage, then manually rotate the log tables. For more information, see Sending MySQL log output to tables.

To remove the old data and reclaim your disk space, run the following commands twice:

CALL mysql.rds_rotate_slow_log;
CALL mysql.rds_rotate_general_log;

Note: The tables don't provide an accurate file size of the logs. Modify the parameter value for log_output to File for slow_log and general_log.