An Amazon RDS database instance running on the MySQL engine is usig more space than expected. Why is this extra space being used, and how can I optimize the use of disk storage allocated to my RDS MySQL DB instance?

The Amazon CloudWatch metric FreeStorageSpace can help you monitor available storage space for a given RDS DB instance, but it doesn't describe how the DB instance is consuming storage.

There are a few general strategies you can employ to reclaim storage space:

Run OPTIMIZE TABLE

Some of the space consumed by tables isn't actively being used, but is allocated to the table anyway. If innodb_file_per_table is enabled (it is enabled by default), you can reclaim that space using OPTIMIZE TABLE.

To check for fragmentation, you could use a query similar to the following:

SELECT

    table_name,

    data_length,

    max_data_length,

    index_length,

    data_free

FROM

    information_schema.tables

WHERE table_schema='schema_name'

;

The data_free column can help highlight the amount of free space allocated to a table that isn't actively being used, which you may be able to reclaim with OPTIMIZE TABLE if this was created in a separate tablespace as per RDS default innodb_file_per_table configuration setting.

Reduce application table storage

To see how much storage is being used by application tables on your DB instance, you can run a query similar to the following:

SELECT

    table_schema,

    SUM(data_length + index_length + data_free)/1024/1024 AS total_mb,

    SUM(data_length)/1024/1024 AS data_mb,

    SUM(index_length)/1024/1024 AS index_mb,

    SUM(data_free)/1024/1024 AS free_mb,

    COUNT(*) AS tables,

    CURDATE() AS today

FROM

    information_schema.tables

    GROUP BY table_schema

    ORDER BY 2 DESC

;

To locate the largest application table on your DB instance, you can run a query similar to the following:

SELECT

    table_schema,

    table_name,

    (data_length + index_length + data_free)/1024/1024 AS total_mb,

    (data_length)/1024/1024 AS data_mb,

    (index_length)/1024/1024 AS index_mb,

    (data_free)/1024/1024 AS free_mb,

    CURDATE() AS today

FROM

    information_schema.tables

    ORDER BY 3 DESC

;

Note: The total storage used by an individual database and table can't be calculated if the database includes tables with variable-length columns longer than 768 bytes (for example, BLOB, TEXT, VARCHAR, or VARBINARY).

Reduce binary log storage

Adding a Read Replica causes the master instance's binary log to use additional storage. To find out how much storage the binary log on the master instance is using, check the BinLogDiskUsage CloudWatch metric. A large amount of growth can indicate that one or more Read Replicas are out of sync. For more information, see Accessing MySQL Binary Logs.

Reduce or disable general log and slow query log storage

Enabling the general log and slow query log parameters causes your DB instance to begin storing these logs and backups of these logs. To rotate these files and control disk usage, see mysql.rds_rotate_general_log and mysql.rds_rotate_slow_log.

Note: To avoid potential performance and disk usage issues, it's a best practice to disable the general and slow query logs when you're not actively using them to troubleshoot.

Manage or reduce InnoDB system tablespace size

The system tablespace contains the InnoDB data dictionary and undo space, and starts out at 10 MB. After space has been allocated, the file will always be at least that size, though long-running transactions can consume more available storage.

By default, RDS sets innodb_file_per_table to 1, which means that data for each tablespace is stored in its own .ibd file. To recover space that is marked as reusable for related tables, use the OPTIMIZE TABLE command to resize per table tablespace files, or drop a table.

If innodb_file_per_table is set to 0, all tables are also allocated to the system tablespace. Dropping tables or indexes, or deleting or truncating data from tables allocated in the system tablespace marks the space previously occupied as reusable, but it does not free up any space to the file system.

Because it's not possible to shrink the system tablespace in place, export your current database's data and import the data into a new instance. To reduce downtime, configure your new MySQL instance as a slave of the source RDS master instance. When the slave is in sync with the source RDS master instance, switch to the new instance. For information about manual replication, see Replication with a MySQL or MariaDB Instance Running External to Amazon RDS.

Note: Restoring from a snapshot or creating a Read Replica won't help you recover space from the system tablespace, because both methods use a snapshot of the source instance storage volume that contains the system tablespace.

Amazon RDS, storage, FreeStorageSpace, BinLogDiskUsage, innodb, MySQL


Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center.

Published: 2015-11-03

Updated: 2017-03-15