My database is taking up lots of space on disk, but contains very little data. How do I fix it?

Problem

Your site's database is occupying a large amount of disk space in your database storage volume. Certain tables appear to be very large on disk, but contain relatively few records with very little actual data therein when analyzed via MySQL. Why are these tables taking up so much space, and how do you reclaim it?

Solution

Note: this solution only works for databases that use InnoDB's discrete file-per-table storage strategy (i.e: innodb_file_per_table).

Warning: the database maintenance operation described below will lock the database table it is being performed on so that MySQL cannot write to it until the operation is completed. Because of the performance impact this may cause while the table optimization is being performed, we recommend that you run this query only during low-traffic situations.

The disk utilization you're seeing in these tables is likely a product of the way that the InnoDB storage engine manages the actual disk space required to accommodate the varying size of a table. For example, let's say that a high volume of traffic to a form on your site creates a large number of entries in the cache_form table of your Drupal database. The storage engine will reserve disk space in the table's datafile (e.g: cache_form.ibd) to accommodate the size of all of those new records. However, even after those entries are deleted from the database, the storage engine will not release their disk space until manual action is taken.

Fortunately, for databases that uses InnoDB's file-per-table storage strategy, the process of reclaiming this space is relatively painless, and can be done with a simple MySQL query:

OPTIMIZE TABLE tablename;

Note: replace "tablename" in the query above with the name of the table you'd like to optimize.

This query, actually copies all of the extant data to a new table in a new file, and then replaces the old file with the new one. This effectively releases the disk space being needlessly occupied by the old file, reducing the disk utilization on your database volume.