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 usinginformation_schema.files
for retrieving table size information.
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:
- Delete old records.
- Change the instance class if necessary.
- Run
OPTIMIZE TABLE <table_name>;
.
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.
Top comments (1)
Uh, I didn't know you need to take extra steps to reduce the actual storage consumption in Aurora beyond deleting the records - nice article!