How do I resolve the storage full issue on my RDS for MySQL instance or my RDS for MariaDB instance?

Short description

Storage space on your Amazon RDS for MySQL instance or for your Amazon RDS for MariaDB instance is used for the following:

• User-created databases
• Temporary tables
• Binary logs or MySQL standby instance relay logs (if you use a read replica)
• InnoDB system tablespace
• General logs, slow query logs, and error logs

When your Amazon RDS for MySQL or MariaDB instance indicates that storage is full, you must analyze the total space. After you identify how your space is being used, reclaim storage space as required. To prevent future occurrences of storage full, monitor the FreeStorageSpace metric.

Note: If there's a sudden decrease in available storage, run the SHOW FULL PROCESSLIST command to check ongoing queries at the DB instance level. To review the transactions that were active for a long time, run the INFORMATION_SCHEMA.INNODB_TRX or SHOW ENGINE INNODB STATUS command, and then review the output.

Resolution

User-created databases

1.    Find the size of each 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;

2.    Check the size of each table for a specific database in your DB instance:

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

Note: For more accurate table sizes consumed at the storage level in MySQL version 5.7 or 8.0, use the information_schema.files command. This command isn't applicable to MariaDB engines.

mysql> SELECT file_name, ROUND(SUM(total_extents * extent_size)/1024/1024/1024,2) AS "TableSizeinGB" from information_schema.files where file_name like '%/database_name/%';

3.    Retrieve complete storage details and the approximate fragmented space at the database and table level:

Note: This command doesn't apply to tables that reside in a shared tablespace.

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;

4.    Record the database sizes from steps 2 and 3. Compare them to the Amazon CloudWatch metrics in Amazon RDS, and then confirm that the storage full issue is because of data usage.

Temporary tables

When you run queries that use temporary tables and your storage space is full, the MySQL server shows a "No space left on device" error.

The online DDL operations use temporary log files for these processes:

• Recording concurrent DML
• Creating temporary sort files when an index is created
• Creating temporary intermediate tables files when tables are rebuilt

Note: For MySQL version 5.7, InnoDB user-created temporary tables and on-disk internal temporary tables are created in a temporary tablespace file named ibtmp1.

1.    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%';

2.    Restart your MySQL DB instance to reclaim disk space that's in use by a global temporary tablespace (ibtmp1). For more information, see The temporary tablespace on the MySQL website.

Note: For MySQL version 8.0, the internal_tmp_mem_storage_engine parameter decides the setting for session level InnoDB temporary tables. The allowable values are TempTable (default) and MEMORY. Both of these settings consume storage until the session is active and releases the storage when the session closes. For more information, see internal_tmp_mem_storage_engine on the MySQL website.

Binary logs or MySQL standby instance relay logs

If you activated automated backups on your Amazon RDS instance, then binary logs are automatically activated on your DB instance. These binary logs are removed at every retention configuration. They are also removed immediately because the binlog retention value was set to NULL.

To resolve your storage availability issues due to binary logs, complete these steps:

• Set your binary log retention period in Amazon RDS for MySQL as required to avoid low storage availability. Run this mysql.rds_show_configuration command to review the number of hours that a binary log is retained:

CALL mysql.rds_show_configuration;

Note: To reduce the amount of space that logs use, reduce the binary log retention value. A NULL value indicates that logs are removed immediately.

• If there's a standby instance for the active instance, monitor the ReplicaLag metric on the standby instance. The ReplicaLag metric indicates any delays during the binary log removal on the active instance and relay log on the standby instance.
• Run the SHOW BINARY LOGS command to check the number of binary logs on an instance and their file sizes. Binary logs can accumulate over time and consume additional disk space. For more information, see Show binary logs statement on the MySQL website.
• If a DB instance acts as a replication standby instance, check the size of the relay logs (Relay_Log_Space) value of this query output:

SHOW SLAVE STATUS\G

InnoDB system tablespace

1.    Run the following command to find the InnoDB system tablespace. For more information, see The System Tablespace on the MySQL website.

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 preceding command runs on MySQL version 5.7 and higher or MySQL 8.0 and higher.

2.    To create storage space and decrease your system tablespace, dump your InnoDB tables and import them 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.

3.    Run the OPTIMIZE TABLE command to reclaim space from a table that uses Innodb_file_per_table:

Note: Make sure that you replace instances of example strings with your required values.

mysql> OPTIMIZE TABLE <example-table-name>

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 enough available disk space before you run this command. For more information, see Optimize table statement on the MySQL website.

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

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

General logs, slow query logs, and error logs

1.    View and list your database log files to check the sizes of slow queries, general logs with FILE type, and error logs. For more information, see Monitoring Amazon RDS log files.

2.    Manually rotate the log tables to manage the table-based MySQL logs that contain slow query logs and general log tables that use excessive storage. Run this command to remove old data and reclaim disk space:

Note: Run these commands twice in succession.

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

3.    Modify the log_output value for the slow_log and general_log parameters from FILE to TABLE. For more information, see Working with parameter groups.

Note: It's a best practice to monitor your Amazon RDS DB instance using Amazon CloudWatch. Make sure that you set up alarms for the FreeStorageSpace metric to receive alerts when your storage space reaches a minimal 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?

Note: To auto-manage storage capacity, use the Amazon RDS storage autoscaling feature. For more information, see Working with storage for Amazon RDS DB instances.

Related information

Why is my Amazon RDS for MySQL DB instance using more storage than expected?