Picture this: You’re monitoring your Upsun database container, and you notice you have a 10GB database using nearly all of its allocated storage. Concerned about space, you take a database dump to assess the situation and discover the dump is only 100MB. Where did the other 9.9GB go?
This is a common question we see in support tickets, and it reveals a fascinating quirk about how databases actually work. Let’s dive into the mystery of the shrinking database dump.
The Dump Doesn’t Tell the Whole Story
First, let’s clarify what a database dump actually is. When you run mysqldump, you’re not creating a byte-for-byte copy of your database files. Instead, you’re generating a set of SQL instructions that tell the database how to recreate your data.
Think of it like the difference between a photograph of a building and the architectural blueprints. The dump is the blueprint: a series of INSERT statements, CREATE TABLE commands, and index definitions. A single line like CREATE INDEX idx_user_email ON users(email); might be tiny in the dump file, but when executed on a large table, it creates an index that consumes substantial disk space.
While dump size generally correlates with actual data volume, they’re definitely not the same thing. Understanding why requires a look under the hood at how databases store data on disk.
How InnoDB Stores Data (And Why It Matters)
On MySQL and MariaDB, the default storage engine is InnoDB, which we commonly use on Upsun. InnoDB uses a file-per-table approach where each table has its own data file (or multiple files, but let’s keep it simple).
Here’s where things get interesting: When you insert 10,000 records, the table file grows. But when you delete row 5,000, the table file doesn’t shrink. InnoDB can’t simply shift 5,000 records upward to compress the file as that would be prohibitively expensive. Instead, the deleted row is zeroed out, leaving an empty gap in the file.
If you continue this pattern - inserting records, deleting records, inserting more records - you end up with fragmentation. The table file fills with empty spaces that were once occupied by deleted data, and the file keeps growing even though the actual data volume remains constant or even shrinks.
InnoDB can reclaim some of this space for new records, but only if the new data fits in the gaps left by deletions. If you delete a 100-character row and insert a 101-character row, it won’t fit in that space, so it gets appended to the end of the file. Over time, you accumulate a file that’s much larger than the actual data it contains.
Sound familiar? If you remember defragmentation tools from the Windows XP days (or FAT32 file systems), this is essentially the same problem, just at the database level instead of the filesystem level.
The Usual Suspects: Drupal’s Watchdog and Key-Value Tables
Certain database usage patterns make this fragmentation problem dramatically worse. In Drupal sites, for example, two tables are notorious offenders: watchdog and key_value_expire.
The watchdog table logs everything your application does. Many sites run cron jobs to clear old log entries to prevent the table from growing indefinitely. The problem? These cleanup scripts typically use DELETE statements, not TRUNCATE. They delete old records row by row, then new log entries get written. The table grows continuously despite the cleanup efforts.
Similarly, key_value_expire stores data that gets created and deleted very often. On high-traffic sites, you get a constant churn of inserts and deletes, leading to severe fragmentation.
These tables often show the largest discrepancy between actual data size and on-disk size.
Seeing the Fragmentation Yourself
Before we dive into solutions, let’s look at how you can diagnose fragmentation in your own database. There’s a handy SQL query you can run to see exactly which tables are fragmented and by how much:
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
WHERE data_free > 1048576*10
ORDER BY data_free DESC;
data_length and index_length to get total table size, but they miss data_free, which is the key number that reveals your fragmentation problem.
Run this query, and you’ll likely see your watchdog and key_value_expire tables near the top with significant amounts of allocated-but-unused space. That’s your smoking gun.
Here is an example of what you could see:
+-------------------------+---------+----------------------+
| table_name | size_MB | Allocated_but_unused |
+-------------------------+---------+----------------------+
| key_value_expire | 10.17 | 10728.00 |
| watchdog | 280.06 | 180.00 |
| file_managed | 22.78 | 25.00 |
| webform_submission_data | 222.00 | 16.00 |
| webform_submission | 17.08 | 11.00 |
+-------------------------+---------+----------------------+
DELETE vs TRUNCATE: A Critical Distinction
Speaking of DELETE versus TRUNCATE, this distinction is crucial. Many developers assume they’re functionally equivalent, especially when running DELETE without a WHERE clause (deleting all rows). But they behave very differently.
DELETE removes data row by row. InnoDB iterates through the entire table, zeroing out each row individually. The table file remains the same size, full of empty space. You don’t reclaim the disk space.
TRUNCATE, on the other hand, drops the entire table file and recreates the table structure from scratch. It’s a clean slate. The disk space is immediately reclaimed.
For tables where you don’t need to preserve data - like old migration tables or temporary staging tables - TRUNCATE is your friend. For log tables like watchdog, you might consider truncating them during off-peak hours (though be aware that logs can serve audit purposes, so this isn’t always appropriate).
Defragmenting When You Need Your Data
But what if you actually need to keep your data? You can’t just truncate everything. The solution is to copy the data to a new table, drop the old fragmented table, and rename the new one. That’s a lot of manual steps, and frankly, nobody wants to do that.
Fortunately, InnoDB provides commands that handle this process automatically through what’s called a “table recreate.”
Option 1: OPTIMIZE TABLE
The OPTIMIZE TABLE command tells InnoDB to examine a table and recreate it if fragmentation is significant:
This works about 75% of the time. Sometimes InnoDB decides the table is “good enough” and doesn’t bother optimizing. When that happens, you need to force the issue.
Option 2: ALTER TABLE ENGINE (The Force Method)
You can trick InnoDB into recreating a table by running:
ALTER TABLE watchdog ENGINE = InnoDB;
- Creates a new table structure
- Copies only the non-deleted rows (actual data) to the new table
- Drops the old fragmented table
- Renames the new table to replace the old one
All of this happens internally. The process is relatively quick if your actual data is small, though it locks the table during the operation. If you have millions of rows of real data (not counting deleted rows), this can take a while and block operations on that table.
For that reason, you’ll want to run these maintenance operations during low-traffic periods—Sunday mornings are popular.
The Catch-22: You Need Space to Reclaim Space
Here’s the awkward part: This table recreation process requires duplicating your data temporarily. You need free disk space to be able to reclaim disk space.
If you’re already at 95% disk usage when you discover the fragmentation problem, you might not have enough room to recreate your largest tables. This is a real catch-22.
The workaround is to start small: Run ALTER TABLE ENGINE = InnoDB on your smallest fragmented tables first. Each small table you defragment frees up a bit more space. Work your way up from smallest to largest, and by the time you reach your biggest table, you’ll hopefully have freed enough space to handle it.
If you’re completely out of space—say, at 100% disk usage—MySQL won’t even start. The database becomes unreachable. At this point, you’ll need to temporarily increase your container’s disk size, start MySQL, run the defragmentation procedures, then shrink the disk back down after you’ve reclaimed space. This process takes only a few seconds once you’re in, but it requires that emergency disk expansion first.
Rather than manually running ALTER TABLE commands on every fragmented table, Upsun provides a CLI tool that automates this entire workflow:
This command examines all tables in your database, calculates the unallocated space (those zeroed-out deleted rows we discussed), and identifies tables with more than 10% unallocated space. You can then choose to defragment all of them automatically, and the tool handles the process for you.
One More Caveat: The Global InnoDB File
Everything we’ve discussed so far applies to individual table files, but there’s one more piece: InnoDB maintains a global storage file containing metadata about all your tables and their structures. This file grows indefinitely over time as tables are created, altered, and dropped.
Usually, this isn’t a problem as most of your storage is in the table files themselves. But on very old databases with extensive schema changes, this global file can balloon.
The only way to shrink it? Dump your entire database, recreate the MySQL server, and re-import the dump. It’s not a process you want to undertake lightly, but it’s worth knowing about if you encounter an unusually large InnoDB system tablespace.
What About PostgreSQL?
PostgreSQL faces similar fragmentation issues, but it has a solution: the VACUUM command. PostgreSQL’s VACUUM performs defragmentation similar to MySQL’s OPTIMIZE TABLE.
Better yet, PostgreSQL enables autovacuum by default—a background process that automatically identifies and defragments tables on a regular schedule.
Why doesn’t MySQL do this? The challenge is table locking. PostgreSQL’s architecture allows autovacuum to run with minimal disruption (it only locks DDL commands such as ALTER TABLE, but not queries on the table’s data), while MySQL’s InnoDB engine needs to lock tables during recreation. The length of that lock depends on the size of the actual data, which can range from milliseconds for a small table to minutes for a table with millions of rows. That unpredictability makes it risky to run automatically in production without full knowledge of the application’s purpose.
Wrapping Up the Mystery
So there you have it: Your 100MB dump and your 10GB database can both be accurate representations of the same data. The dump contains only the instructions to recreate the actual rows and indexes, while the database on disk includes all that data plus the invisible ghosts of millions of deleted rows, fragmented across table files.
The next time you see this discrepancy, you’ll know exactly what’s happening, and more importantly, what to do about it. Start monitoring your table fragmentation, schedule regular maintenance with OPTIMIZE TABLE or upsun db:size-cleanup, and keep an eye on those high-churn tables.
Your database (and your disk space alerts) will thank you. Last modified on April 14, 2026
