16

I have an Amazon (AWS) Aurora DB cluster, and every day, its [Billed] Volume Bytes Used is increasing.

VolumeBytesUsed CloudWatch metric over time

I have checked the size of all my tables (in all my databases on that cluster) using the INFORMATION_SCHEMA.TABLES table:

SELECT ROUND(SUM(data_length)/1024/1024/1024) AS data_in_gb, ROUND(SUM(index_length)/1024/1024/1024) AS index_in_gb, ROUND(SUM(data_free)/1024/1024/1024) AS free_in_gb FROM INFORMATION_SCHEMA.TABLES;
+------------+-------------+------------+
| data_in_gb | index_in_gb | free_in_gb |
+------------+-------------+------------+
| 30         | 4           | 19         |
+------------+-------------+------------+

Total: 53GB

So why an I being billed almost 75GB at this time?

I understand that provisioned space can never be freed, in the same way that the ibdata files on a regular MySQL server can never shrink; I'm OK with that. This is documented, and acceptable.

My problem is that every day, the space I'm billed increases. And I'm sure I am NOT using 75GB of space temporarily. If I were to do something like that, I'd understand. It's as if the storage space I am freeing, by deleting rows from my tables, or dropping tables, or even dropping databases, is never re-used.

I have contacted AWS (premium) support multiple times, and was never able to get a good explanation on why that is.
I've received suggestions to run OPTIMIZE TABLE on the tables on which there is a lot of free_space (per the INFORMATION_SCHEMA.TABLES table), or to check the InnoDB history length, to make sure deleted data isn't still kept in the rollback segment (ref: MVCC), and restart the instance(s) to make sure the rollback segment is emptied.
None of those helped.

Guillaume Boudreau
  • 634
  • 1
  • 5
  • 13

3 Answers3

24

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.

Guillaume Boudreau
  • 634
  • 1
  • 5
  • 13
  • 1
    This is a great answer, and *yowch*, those are some major caveats. Glad I saw this. – ceejayoz Sep 14 '17 at 16:24
  • Ditto. Noticed one DB server was up to 300 GB, for a database with MySQL-reported size of 54 GB... if space is never reclaimed, that's a good example of what happens when you have a lot of frequently-written-to tables (e.g. log tables, index tables, etc.). – geerlingguy Feb 16 '18 at 16:31
3

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/

Dan Hook
  • 131
  • 2
  • 1
    Indeed, a very good step in the right direction. Of note: "Thus, it applies to SQL statements such as DROP TABLE, DROP DATABASE, and TRUNCATE TABLE. It doesn't apply to deleting rows using the DELETE statement. If you delete a large number of rows from a table, you can run the Aurora MySQL `OPTIMIZE TABLE` statement or the Aurora PostgreSQL `VACUUM` statement afterward to reorganize the table and dynamically resize the cluster volume." [ref](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Managing.Performance.html#Aurora.Managing.Performance.StorageScaling) – Guillaume Boudreau Nov 02 '20 at 20:09
  • 1
    I just tested this, and it works as advertised! Upgraded to Aurora MySQL 1.23, and `VolumeBytesUsed` as reported by CloudWatch went from 170 GB to 20 GB within 5 minutes! I also issued about 20 minutes-worth of `OPTIMIZE TABLE`s afterwards, but didn't yet see any change in `VolumeBytesUsed` following those. – Guillaume Boudreau Nov 02 '20 at 21:00
1

When Aurora data is removed, such as by dropping a table or partition, the overall allocated space remains the same. The free space is reused automatically when data volume increases in the future. https://docs.amazonaws.cn/en_us/AmazonRDS/latest/AuroraUserGuide/Aurora.Managing.Performance.html

Arinjay
  • 11
  • 1