Running out of storage space on you RDS instance!!! Keep calm and reclaim it

If you notice that your RDS instance is taking more storage than it should,

If you notice that some of your queries are taking longer than they should, even with all the indexes in place,

You might be facing fragmented MySQL table!!

Random insertions into or deletions from a secondary index can cause the index to become fragmented. Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages, or that there are many unused pages in the 64-page blocks that were allocated to the index.

MySQL :: MySQL 5.6 Reference Manual :: 14.12.4 Defragmenting a Table

How do you identify the tables that are fragmented?

SELECT ENGINE,
       TABLE_SCHEMA AS `database`,
       TABLE_NAME AS `table`,
       ROUND(((DATA_LENGTH + INDEX_LENGTH)/1024/1024), 2) `Current Size (MB)`,
       ROUND((DATA_FREE/1024/1024), 2) `Fragmention (MB)`,
       (DATA_FREE / (DATA_LENGTH + INDEX_LENGTH)) AS fragmention_ratio
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
ORDER BY `Fragmention (MB)` DESC, fragmention_ratio DESC;

The column `Fragmention (MB)` will tell you approximately how much free space you would be able to reclaim after you defragment the table.

Oh.. that’s great.. but how do I actually reclaim the storage space now??

It’s actually pretty simple perform a `null` `ALTER TABLE` operation, which causes MySQL to rebuild the table, below are two ways to do it

ALTER TABLE tbl_name ENGINE=your_storage_engine, ALGORITHM=INPLACE, LOCK=NONE;

or

ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

Tada !!! You have reclaimed your storage space

Note: The first method mentioned above is tried and tested, second method is also known to work, however I haven’t tried it myself yet.

PS: From our 150 GB RDS storage we reclaimed about 33 GB