Aurora MySQL Storage Monitoring & Reduction

Using Aurora MySQL is a great way to host a MySQL database at low cost while providing a scalable and fault tolerant environment. Once you’ve setup your multi-zone cluster AWS takes care of much of the scaling for the database server. One of the components it manages automatically is the volume, or disk storage, up to a maximum of 128TB. However, this does not mean volume storage is fully managed.

All AWS RDS implementations have several components that can impact the monthly billing. While things like CPU and memory are based on the workload and often require application restructuring to reduce consumption, disk space — or volume storage — can often be reduced with routine maintenance.

Implementing routine maintenance to reduce volume consumption should be part of a monthly health-check for your AWS services.

Checking The Aurora MySQL Volume Size

The easiest way to check the amount of storage being used by the nodes in the data cluster is to use CloudWatch. You can follow this AWS article on How To View Storage to view the volume sizes. For monthly reviews, add a Number widget for this metric to your main Cloudwatch dashboard.

Checking MySQL Database and Table Sizes

You can check table sizes with this MySQL command:

SELECT *
FROM   (SELECT table_name, TABLE_SCHEMA, ENGINE,
           ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS `Size MB`
        FROM   information_schema.tables
        GROUP  BY table_name) AS tmp_table
ORDER  BY `Size MB` DESC;

Checking database sizes in MySQL can be done with this command:

SELECT *
FROM   (SELECT TABLE_SCHEMA,
           ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS `Size MB`
        FROM   information_schema.tables
        GROUP  BY TABLE_SCHEMA) AS tmp_table
ORDER  BY `Size MB` DESC;

Additional File & Table Size Commands

Temp files in Aurora MySQL

SELECT file_name, ROUND(SUM(total_extents * extent_size)/1024/1024/1024,2) AS "TableSizeinGB" from information_schema.files;

Table and fragmented space (GB)

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;

Reducing Volume Size

Yes, Amazon did add automatic volume reduction as part of the Aurora MySQL and PostgreSQL implementations a couple of years ago, but there are some caveats. First, deleting records from a table does not release the disk space; MySQL keeps that space for future inserts and updates. Second, Optimize Table should help but it temporarily increases volume usage as it replicates the table during the process; Also InnoDB tables — the default unless overridden should be auto-optimized so you won’t gain much if anything from this step.

This Stack Overflow post has some great details about MySQL and volume storage.

Dropping unused tables will reduce volume size IF innodb_file_per_table is on. It should be for default Aurora MySQL instances. You can check that setting with this MySQL command.

show variables like "innodb_file_per_table";

Cloning a database with dump/restore and dropping the original will reduce volume size and may be the only option to “reclaim” space if a lot of records have been dropped. You’ll be doubling volume use during the operation, but once the original source database is removed you should see a reduction in space.

You may be interested in following this AWS article on performance and scaling for Aurora DB clusters as well.

Or this article from AWS on troubleshooting storage space.