Earlier today I ran into a problem making a Drupal 8 database backup. I ran the usual mysqldump backup command, and when it kept running for a long time I decided to kill it, and then began investigating the problem. I knew that I had recently deleted all of the log records, and the Drupal cron task was running correctly, so something else was going on. In short, I found that my Drupal 8 database was huge.
I found this out by moving to the Drupal 8 MySQL database data directory, which on an Ubuntu Linux system is something like this:
/var/lib/mysql/drupal8
The last part of that path is the name of your database, so in this example my database is named drupal8. When I listed the files in this directory in order sorted by filesize, I noticed that a few of the files had grown huge. Here’s an abbreviated version of that directory listing:
108M search_index.ibd 120M cache_entity.ibd 268M cache_data.ibd 1.6G cache_dynamic_page_cache.ibd 3.4G cache_render.ibd
As you can see, the Drupal 8 cache-related database tables had grown huge, with cache_dynamic_page_cache.ibd and cache_render.ibd being the two largest files by a large margin.
The short story is that I ran the drush cr command to clear all of the caches, then ran the du -sh command in the same directory, and it showed that the total size of the files had been reduced to about 500 MB. That still seems a little large, as a database backup with mysqldump is about 95 MB, but it’s much better than the 5.8 GB it was before I ran the drush command. (I haven’t looked into the reasons for this difference yet.)
How to look at MySQL database table size
I later learned that another way you can look at your MySQL database table size is with this MySQL command:
show table status;
If you’re using the mysql command line client you’ll need to select your database first:
use drupal8;
After that, the show table status command will show how large each table is in the data_length column output.
Summary
In summary, if your Drupal 8 database is huge, I hope this information is helpful. As I have learned, the things to do are (a) make sure your report logs aren’t huge, (b) make sure your cron task is running properly, and (c) check the size of the Drupal 8 cache-related tables. Actually, you might want to check those things in the reverse order, but those are the usual things that make Drupal database tables large.
