How to Shrink a Bloated Database Without Breaking Your Site

To shrink a bloated MySQL database, you first measure where the space actually goes, then remove or relocate rows that no longer earn their keep, and only then reclaim the freed space. The order matters, because deleting data does not return disk space to the operating system on its own, and doing any of it without a backup or a plan is how sites get corrupted.

Start by measuring, not deleting

Before you touch a single row, find out which tables are large and why. The information_schema database holds the answer. This query reports data size, index size and approximate row counts per table, sorted by total size:

SELECT table_name,
       ROUND(data_length / 1024 / 1024) AS data_mb,
       ROUND(index_length / 1024 / 1024) AS index_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema = 'your_db'
ORDER BY data_length + index_length DESC
LIMIT 20;

Two things surprise people here. First, table_rows is an estimate for InnoDB tables, not a count, so treat it as a hint rather than a fact. Second, index size often rivals data size, and indexes are rebuilt when you rebuild the table, so a table that looks bloated may simply be over-indexed.

For an exact count on a table you suspect, run SELECT COUNT(*) FROM some_table;. On a very large table that itself takes time and I/O, so run it off-peak.

Also check the filesystem view, because a table can be fragmented or hold free space internally that the query above does not show:

du -sh /var/lib/mysql/your_db
ls -lh /var/lib/mysql/your_db | sort -k5 -h | tail -20

If the directory is far larger than the sum of the tables, you likely have free space trapped inside the tablespace files or leftover files from failed operations.

Which rows are inflating the database

Bloat almost always comes from a handful of offenders. Log tables, session tables, cache tables, and anything with a timestamp column are the usual suspects. If your application stores sessions in the database rather than in memory, that table grows with traffic and never shrinks on its own unless something prunes it.

Find the oldest and newest rows in a candidate table to see how far back it reaches and whether anything is still writing to it:

SELECT MIN(created_at), MAX(created_at), COUNT(*)
FROM some_log_table;

Then ask the harder question: does the application read this data? A table nobody reads is not a table you need to keep online. A table that is read but only for recent data is a candidate for archiving rather than deletion. A table that is read across its whole history needs a different answer, usually a separate archive database or object storage, not a purge.

Watch for orphaned rows too. In many schemas, child tables keep rows whose parent has been deleted, because the foreign key was never enforced or the application deletes only the parent. Comparing counts between parent and child tables reveals this quickly.

The safe deletion protocol for how to shrink a bloated MySQL database

Never delete a large number of rows in one statement. A single DELETE touching millions of rows holds locks, fills the undo log, and can stall the site for minutes or hours. It also cannot be interrupted cleanly once it is deep into its work.

Delete in bounded batches instead, with a small sleep between them so replication and other queries can keep up:

DELETE FROM some_log_table
WHERE created_at < '2020-01-01'
ORDER BY id
LIMIT 1000;

Run that repeatedly from a shell loop or a scheduled job, and check the affected row count each time. When it returns zero, you are done. Take a logical backup first with mysqldump restricted to the tables you plan to touch, and verify that the dump restores into a scratch database before you delete anything in production.

If the table is a log or event table that only ever needs recent data, PARTITION BY RANGE on the timestamp column is a far better long term answer. Dropping a partition is close to instant and does not generate the undo and redo volume that row-by-row deletion does. The tradeoff is that partitioning must be designed before the table grows, and not every schema suits it.

Archiving is usually the right move rather than deletion. Copy the rows you want to keep into an archive table or a separate database, confirm the copy, then delete from the live table. That gives you a rollback path if the application turns out to need older data after all.

Reclaiming the space after deletion

Deleting rows marks space as reusable inside the tablespace. It does not hand it back to the filesystem. For InnoDB, the way to return it is to rebuild the table, which you can do in place:

ALTER TABLE some_log_table ENGINE=InnoDB;

That statement copies the table, rebuilds the indexes and swaps the new file in. It needs free disk space roughly equal to the table size, so check df -h before running it. On a busy server, use an online schema change tool that copies in chunks and keeps the table writable, rather than a blocking ALTER. If your server uses a shared tablespace file, per-table rebuilds will not shrink it; you need innodb_file_per_table enabled, and for tables created before that setting changed, a rebuild is what moves them into their own file.

After the rebuild, run ANALYZE TABLE so the optimizer has fresh statistics, and re-run the size query from the top of this article to confirm the change. If the directory is still large, look for binary logs, which are a common and overlooked consumer of disk. The expire_logs_days or binlog_expire_logs_seconds setting controls how long they are kept, and they are safe to purge once replication and point-in-time recovery no longer need them.

What to do next

Pick the single largest table and do nothing to it yet. Take a backup, measure it properly with the queries above, and work out whether the application reads all of it, some of it, or none of it. That answer decides whether you archive, partition or purge. Then rehearse the whole sequence on a copy of the database first, including the rebuild, and only repeat it in production once you have seen it finish cleanly and the site still serves traffic.