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

Short description

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

Resolution

Run OPTIMIZE TABLE

RDS for MySQL allocates space to tables that aren't actively in use. When you delete rows, InnoDB leaves empty pages that it can reuse. InnoDB doesn't shrink the physical table file. If you turned on innodb_file_per_table, then 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 based on 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 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."

The ALTER TABLE...FORCE command also updates your table statistics. To estimate storage use based on the updated table statistics, run the following query:

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

Note: Replace schema_name with the name of your database schema.

To find the amount of free space that's allocated to a table that isn't actively in use, view the data_free column.

Reduce application table storage

To view how much storage the application tables use on your RDS for 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 for 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 larger than 768 bytes, then you can't calculate the individual storage that the database and tables use. This limitation includes BLOB, TEXT, VARCHAR, and VARBINARY data types.

Reduce binary log storage

If you add a read replica to an RDS for MySQL DB instance, then the binary log on the source instance uses additional storage. To determine how much storage the binary log uses, view the BinLogDiskUsage CloudWatch metric. If the binary log increasingly uses storage, then synchronize your read replicas.

Reduce or turn off general log and slow query log storage

When you turn on the general log and slow query log parameters, your instance stores the logs and the backups of the logs. It's a best practice to turn off the general and slow query logs when you aren't troubleshooting issues. To rotate the files and control disk usage, see mysql.rds_rotate_general_log and mysql.rds_rotate_slow_log.

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, 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.

To shrink the system tablespace, export the data from your current database, and then import the data into a new instance. You can't shrink the system tablespace on the instance where it exists. To reduce downtime, configure your new RDS for MySQL instance as a replica of the source instance. After you synchronize the replica with the source 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?