Why does my Amazon RDS for MySQL DB instance use more storage than I expect?

Resolution

To monitor the available storage space for an Amazon RDS DB instance, you can use the Amazon CloudWatch FreeStorageSpace metric. However, the FreeStorageSpace metric doesn't show what uses storage in the DB instance. To determine what's using your storage and reclaim storage space, use the following methods.

Run OPTIMIZE TABLE

Tables use only space that isn't actively in use. However, Amazon RDS still allocates space to the tables. If you turned on innodb_file_per_table, then you can run the OPTIMIZE TABLE command to reclaim the space. For more information, see OPTIMIZE TABLE statement on the MySQL website.

You can use OPTIMIZE TABLE for InnoDB, MyISAM, and ARCHIVE tables. OPTIMIZE TABLE works when you create the table in a separate tablespace according to the Amazon RDS default innodb_file_per_table configuration setting. For more information, see File-per-table tablespaces on the MySQL website.

 Amazon RDS accepts the OPTIMIZE TABLE command, but it actually runs the ALTER TABLE...FORCE command instead. When this occurs, you receive a warning message that's similar to the following:

"Table does not support optimize, doing recreate + analyze instead."

Update you statistics, and then run the following information_schema.tables query to estimate storage use based on the statistics:

SELECT    table_name,
    data_length,
    max_data_length,
    index_length,
    data_free
FROM
    information_schema.tables 
WHERE table_schema='schema_name'
;

Note: In the preceding query, add the values for your use case. 

The data_free column shows the amount of free space that's allocated to a table that isn't actively in use. 

Reduce application table storage

To view how much storage the application tables use on your RDS MySQL DB instance, run the following query:

SELECT TABLESPACE_NAME, 
ROUND(DATA_FREE/EXTENT_SIZE,0) FREE_DATA_MB,
 FREE_EXTENTS FREE_EXTENTS_MB, TOTAL_EXTENTS 
TOTAL_EXTENTS_MB FROM 
INFORMATION_SCHEMA.FILES;

To locate the largest application table on your RDS MySQL DB instance, run the following query:

SELECT TABLESPACE_NAME,  
ROUND(DATA_FREE/EXTENT_SIZE,0) FREE_DATA_MB,  
FREE_EXTENTS FREE_EXTENTS_MB, 
TOTAL_EXTENTS  TOTAL_EXTENTS_MB FROM  
INFORMATION_SCHEMA.FILES 
ORDER BY TOTAL_EXTENTS_MB DESC;

Note: If a database includes tables with variable-length columns that are longer than 768 bytes, then you can't calculate the individual storage that the database and table use. This limitation includes BLOB, TEXT, VARCHAR, and VARBINARY commands.

Reduce binary log storage

If you add a read replica to an Amazon RDS instance, then the source instance's binary log uses additional storage. To determine how much storage the binary log on the source instance uses, check the BinLogDiskUsage CloudWatch metric. If the binary log increasingly uses storage, then you might need to synchronize your read replicas.

Reduce or turn off general log and slow query log storage

When you turn off the general log and slow query log parameters, your instance begins to store the logs and the backups of the logs. To rotate the files and control disk usage, see mysql.rds_rotate_general_log and mysql.rds_rotate_slow_log.

Note: When you aren't troubleshooting, it's a best practice to turn off the general and slow query logs.

Manage or reduce InnoDB system tablespace size

The system tablespace begins with 10 MB of space and contains the InnoDB data dictionary and undo space. After you allocate the space, the file is at least 10 MB and can use more available storage.

By default, Amazon RDS sets innodb_file_per_table to 1 so that the database stores data for each tablespace in its own .ibd file. To recover reusable space for related tables, use OPTIMIZE TABLE to resize each table tablespace file. Or, you can drop a table.

If you set innodb_file_per_table to 0, then Amazon RDS allocates all tables to the system tablespace. If you drop tables or indexes, or delete or truncate data from allocated tables in the system, then the tablespace marks the space as reusable. However, innodb_file_per_table doesn't free up the space for the file system.

You can't shrink the system tablespace in the instance where it's located. Instead, export your current database's data, and then import the data into a new instance. To reduce downtime, configure your new MySQL instance as a replica of the source Amazon RDS instance. After you synchronize the replica with the source Amazon RDS instance, switch to the new instance.

Note: When you restore from a snapshot or create a read replica, you don't recover space from the system tablespace. Both methods use a snapshot of the source instance storage volume that contains the system tablespace.

Related information

Amazon RDS DB instance running out of storage

Modifying an Amazon RDS DB instance

How can I troubleshoot the error "MySQL HA_ERR_RECORD_FILE_FULL" when I use Amazon RDS for MySQL?