How do I troubleshoot an 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 space full, check the total space that's used on your DB instance to identify what uses space. 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 (if you use a read replica)
• InnoDB tablespace
• General logs, slow query logs, and error logs

After you check your storage space and identify what is using the space, you can reclaim space. Then, you can monitor the FreeStorageSpace 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 are performed by each connection. 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 steps:

Check the total space used on your MySQL DB instance

Identify the size of each user-created database

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;

Check the size of each table for a user database that you specify:
Note: Replace example-database-name with your database name.

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

Check the total space that's used on your MariaDB instance

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

Check the size of each table for a user database that you specify:
Note: Replace example-database with the database name and example-table with the table name.

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

Check your temporary tables

InnoDB user created temporary tables and on-disk internal temporary tables are created 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 can use only MySQL versions 5.7 and later or MySQL versions 8.0 and later to query the file sizes of the InnoDB tablespace.

Identify 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%';

To reclaim disk space that's occupied by a global temporary tablespace data file, 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 can't be removed because of a query. 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.

Identify the internal temporary tables:

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

Identify 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%';

Note: The previous query is supported on MySQL versions 5.7 and later or MySQL versions 8.0 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. 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:
Note: Replace example-table-name with the table that you want to optimize.

mysql> OPTIMIZE TABLE example-table-name;

(Optional) To rebuild the table, run the following command:
Note: Replace example-table-name with the table that you want to optimize.

mysql> ALTER TABLE example-table-name ENGINE=INNODB;

Check your binary logs

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

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

To view the number of hours that a binary log is retained, run the mysql.rds_show_configuration command:

CALL mysql.rds_show_configuration;

To reduce the amount of space that the binary logs use, reduce number of hours that a binary log is retained. A value of NULL removes logs immediately.

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

If there are purging or replication issues, then binary logs can accumulate over time and consume additional 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 check Relay_Log_Space to view the size of the relay logs:

SHOW SLAVE STATUS\G

Check your MySQL logs (general logs, slow query logs, and error 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 to manage the table-based MySQL logs.

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

mysql> CALL mysql.rds_rotate_slow_log;mysql> 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.

Monitor and scale your Amazon RDS DB instance

To monitor and scale your Amazon RDS instance, take the following actions:

• Use Amazon CloudWatch to monitor your Amazon RDS DB instance.
• Set up CloudWatch alarms on the FreeStorageSpace metric to receive alerts when your storage space decreases to a specified threshold value. For more information, see How can I create CloudWatch alarms to monitor the Amazon RDS free storage space and prevent storage full issues?
• Use the Amazon RDS storage autoscaling feature to automatically manage capacity.