Where is my database storage going to?

I’ve followed the following article to check my database storage usage:

But the size it reports seems wrong to me.

I’ve imported a 3GB sized database backup. But when I check platform db:size it is reporting 9GB.

Checking database service db...

+----------------+-----------------+--------+
| Allocated disk | Estimated usage | % used |
+----------------+-----------------+--------+
| 14.6 GiB       | 9.4 GiB         | ~ 64%  |
+----------------+-----------------+--------+

Why the big difference?

  • There is some overhead in operating databases so that accounts for some disk space. (e.g. on mysql information_schema/mysql, etc…)
  • If you just measured the file that you import, be aware that the space used in the file is always smaller than when it is unpacked into the database. Databases are faster and more flexible than a flat file. But they create additional data to be able to do this.
  • The platform db:size makes a good estimate on how much is used, but it can deviate by a few percentages.
  • InnoDB does not reclaim removed space. So everything you once inserted and removed, is still there. This is especially true for cache tables (which get a lot of insert/deletes).

db:size actually has a trick to help you identify wasted space and clean those up.

platform db:size --cleanup -p YOUR_PROJECT_ID -e master
Checking database service db...

+----------------+-----------------+--------+
| Allocated disk | Estimated usage | % used |
+----------------+-----------------+--------+
| 14.6 GiB       | 9.4 GiB         | ~ 64%  |
+----------------+-----------------+--------+

Warning
This is an estimate of the database's disk usage. It does not represent its real size on disk.

You can save space by running the following commands during a maintenance window:

ALTER TABLE `main`.`cache_page` ENGINE="InnoDB";
ALTER TABLE `main`.`xmlsitemap` ENGINE="InnoDB";
ALTER TABLE `main`.`cache_block` ENGINE="InnoDB";
ALTER TABLE `main`.`queue` ENGINE="InnoDB";
ALTER TABLE `main`.`cache_advagg_aggregates` ENGINE="InnoDB";
ALTER TABLE `main`.`sessions` ENGINE="InnoDB";
ALTER TABLE `main`.`batch` ENGINE="InnoDB";
ALTER TABLE `main`.`field_revision_field_address_line_3` ENGINE="InnoDB";
ALTER TABLE `main`.`field_data_field_paragraph_background` ENGINE="InnoDB";
ALTER TABLE `main`.`field_data_field_bs_comms` ENGINE="InnoDB";

Warning: Running these may lock up your database for several minutes.
Only run these when you know what you're doing.

That should clean up some storage, and you can even run those queries in a cron once a week to keep it nice and clean (Sunday early morning for example).

Recommendations

  • Move cache tables to a dedicated Redis instance that can take the load off your database.
  • Make sure you have less than 80% disk used on databases. Databases don’t work well with little disk space.