Optimizing Aurora MySQL Storage by Deleting Unnecessary Data

A table in our Aurora MySQL database was consuming approximately 80% (around 400 GB) of the total storage. Since we were able to archive older data as CSV files, we decided to delete old records and free up the storage.

I initially thought that deleting the records would free up the storage space, but it turned out to be more complicated than expected. So, I’m documenting the detailed steps for future reference.

Checking Table Storage Usage

You can check the size of each .ibd file using the following query:

SELECT file_name, ROUND(SUM(total_extents * extent_size)/1024/1024/1024,2) AS "TableSizeinGB" 
FROM information_schema.files 
GROUP BY file_name 
ORDER BY total_extents DESC;

Reference: MySQL Documentation

Important Note

AWS re:Post recommended the following query to check table sizes, but the results for the target table were about 150 GB smaller compared to the first query.

SELECT table_schema "DB Name", table_name, 
       (data_length + index_length)/1024/1024/1024 AS "TableSizeinGB" 
FROM information_schema.tables 
WHERE table_schema = 'database_name';

When I consulted AWS Support, they confirmed that information_schema.tables provides only statistical values, which are often inaccurate. They advised using information_schema.files to get precise data.

The information regarding the table size (390 GB) was retrieved from information_schema.tables, and since this is statistical data, it is likely inaccurate. In the future, we recommend using information_schema.files for retrieving table size information.

Reference: AWS re:Post

Checking Database Storage Usage

The following query checks the overall database usage. This also uses information_schema.files for accuracy.

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/%';

Steps to Free Up Database Storage

Here are the steps for freeing up storage:

1. Delete old records.
2. Change the instance class if necessary.
3. Run OPTIMIZE TABLE ;.

Simply deleting records does not free up storage space; you need to run OPTIMIZE TABLE to release the space.

Additionally, during the OPTIMIZE TABLE (or ALTER TABLE ... FORCE) operation, temporary intermediary table files are created. In Aurora, these temporary files are stored on local storage. The amount of local storage depends on the instance class. In my case, the db.r6g.xlarge instance only has 80 GB of local storage, which wasn’t enough for the size of the deleted records. So, I temporarily scaled up to db.r6g.8xlarge (640 GB).

Reference: Optimize Table

Reference: Alter Table

Reference: InnoDB Online DDL Space Requirements

Reference: Aurora MySQL Temporary Storage

Caution When Using OPTIMIZE TABLE

After deleting around 250 GB of records, running OPTIMIZE TABLE took approximately 130 minutes (about 2 hours). Since OPTIMIZE TABLE locks the table, you may need to schedule downtime or perform this operation during off-peak hours. For reference, it took around 15 hours in total to delete all records, which I spread over several days.

The above is the detailed content of Optimizing Aurora MySQL Storage by Deleting Unnecessary Data. For more information, please follow other related articles on the PHP Chinese website!