• 

  Matthias Van Woensel

  October 05, 2020 14:22

  • 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).
  • You’d need to run an ALTER TABLE or OPTIMIZE TABLE on the tables involved.

  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, if your tables are small enough you can even run those queries in a cron once a week to keep it nice and clean (Sunday early morning for example).

  Warning: do not run a cleanup during production hours unless you really have to. It will lock up tables.

  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.

  More information

  • If you are curious about getting the disk space from the database yourself. You can with this query to get the top 10 tables with the most allocated but unused space:

  SELECT table_name, round((data_length+index_length)/1048576,2) as size_MB, round((data_free)/1048576,2) as Allocated_but_unused FROM information_schema.tables ORDER BY data_free DESC LIMIT 10;

  Or sorted by actual data size:
  SELECT table_name, round((data_length+index_length)/1048576,2) as size_MB, round((data_free)/1048576,2) as Allocated_but_unused FROM information_schema.tables ORDER BY data_length DESC LIMIT 10;

  Percona also has a nice article describing how InnoDB deals with storage.

  0

Please sign in to leave a comment.