Why is my "volume bytes used" always increasing on my Amazon Aurora cluster?

Solution 1:

There are multiple things at play here...

1. Each table is stored in its own tablespace

   By default, the parameter group for Aurora clusters (named default.aurora5.6) defines innodb_file_per_table = ON. That means each table is stored in a separate file, on the Aurora storage cluster. You can see which tablespace is used for each of your tables using this query:

   SELECT name, space FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES;

   Note: I have not tried to change innodb_file_per_table to OFF. Maybe that would help..?

2. Storage space freed by deleting tablespaces is NOT re-used

   Quoting AWS premium support:

   Due to the unique design of the Aurora Storage engine to increase its performance and fault tolerance Aurora does not have a functionality to defragment file-per-table tablespaces in the same way as standard MySQL.

   Currently Aurora unfortunately does not have a way to shrink tablespaces as standard MySQL does and all fragmented space are charged because it is included in VolumeBytesUsed.
   The reason that Aurora cannot reclaim the space of a dropped table in the same way as standard MySQL is that the data for the table is stored in a completely different way to a standard MySQL database with a single storage volume.

   If you drop a table or row in Aurora the space is not then reclaimed on Auroras cluster volume due to this complicated design.
   This inability to reclaim small amounts of storage space is a sacrifice made to get the additional performance gains of Auroras cluster storage volume and the greatly improved fault tolerance of Aurora.

   But there is some obscure way to re-use some of that wasted space...
   Again, quote AWS premium support:

   Once your total data set exceeds a certain size (approximately 160 GB) you can begin to reclaim space in 160 GB blocks for re-use e.g. if you have 400 GB in your Aurora cluster volume and DROP 160 GB or more of tables Aurora can then automatically re-use 160 GB of data. However it can be slow to reclaim this space.
   The reason for the large amount of data required to be freed at once is due to Auroras unique design as an enterprise scale DB engine unlike standard MySQL which cannot be used on this scale.

3. OPTIMIZE TABLE is evil!

   Because Aurora is based on MySQL 5.6, OPTIMIZE TABLE is mapped to ALTER TABLE ... FORCE, which rebuilds the table to update index statistics and free unused space in the clustered index. Effectively, along with innodb_file_per_table = ON, that means running an OPTIMIZE TABLE creates a new tablespace file, and deletes the old one. Since deleting a tablespace file doesn't free up the storage it was using, that means OPTIMIZE TABLE will always result in more storage being provisioned. Ouch!

   Ref: https://dev.mysql.com/doc/refman/5.6/en/optimize-table.html#optimize-table-innodb-details

4. Using temporary tables

   By default, the parameter group for Aurora instances (named default.aurora5.6) defines default_tmp_storage_engine = InnoDB. That means every time I am creating a TEMPORARY table, it is stored, along with all my regular tables, on the Aurora storage cluster. That means new space is provisioned to hold those tables, thus increasing the total VolumeBytesUsed.
   The solution for this is simple enough: change the default_tmp_storage_engine parameter value to MyISAM. This will force Aurora to create the TEMPORARY tables on the instance's local storage.
   Of note: the instances' local storage is limited; see the Free Local Storage metric on CloudWatch to see how much storage your instances have. Larger (costlier) instances have more local storage.

   Ref: none yet; the current Amazon Aurora documentation doesn't mention this. I asked the AWS support team to update the documentation, and will update my answer if/once they do.

Solution 2:

Thankfully this won't be an issue for much longer. AWS has announced dynamic resizing for storage space: https://aws.amazon.com/about-aws/whats-new/2020/10/amazon-aurora-enables-dynamic-resizing-database-storage-space/