We recently migrated 23GB of data from an EC2 MySQL database server to RDS. During the migration, we noticed that the free storage on the 100GB instance was being eaten up quickly. It took some digging to find out that AWS does some configuration trickery here to make more money from unsuspecting customers, in my opinion. They set innodb_file_per_table to true by default.This creates a file per table in the database instead of one global tablespace. There’s really no benefit that I’ve found for performance by doing this. It’s a clever way for users who have file access to migrate tables individually instead of entire databases. The key here is that there is no SSH access for RDS, so, why do we need a file per table? It doesn’t make sense if we can’t even use the feature for its original purpose.

innodb_file_per_table reserves disk space for each table whether you use it or not. On smaller databases, a user might not even notice the extra storage, but it added 27GB of storage for just 1/4 of our databases that were being migrated. In our case there are about sixty-eight tables per database on average. We have over 6,000 databases this size. In order to recapture that space, you have to re-migrate those databases again by either optimizing tables or deleting the entire database, creating, and importing again. Needless to say, it was quite a few hours of work to reclaim the unused space.

To fix this, you need to create a new parameter group and assign it to your database instance. Within the parameter group search for ‘innodb_file_per_table’ and set it to 0. Once you assign it to the instance, it will give you a status of ‘pending-reboot’ which means you need to manually reboot the instance. If you’ve already filled up the instance with databases, you’ll need to re-migrate the databases, or optimize your tables. Booting a new instance will not reclaim your space.

To note, we contacted AWS about this issue, and they recommended we purchase premium support. Go figure. 🙂