When Disk Space is Not Enough: A Lesson in Challenging MySQL Database Cleansing

Source: Dev.to

When Disk Space Is Not Enough: A Lesson in Challenging MySQL Database Cleansing

Database maintenance is rarely a walk in the park, especially when dealing with high‑volume transactional data. Recently, we were tasked with cleansing a MySQL database that had ballooned to 220 GB. The mission was simple: remove data from 2018‑2023 across four tables to reclaim space and improve performance.

However, as any dev knows, “simple” tasks often hide complex traps. Here’s how we navigated storage limits and technical hurdles to get the job done.

The Challenge: The 200 GB “Elephant” in the Room

Out of the 220 GB total size, a single table accounted for 200 GB. It was heavy with transactional records and—​the real culprit—BLOB attachments. Our goal was to keep only the data from 2024 onward.

Phase 1: The “Clone and Swap” Strategy (The Ideal Plan)

1. Clone – Create a copy of the existing tables with new names.
2. Filter – Insert only the 2024‑present data into the new tables.
3. Swap – Rename the original tables and replace them with the new ones.
4. Cleanup – Drop the old tables once verification is complete.

Pros: Safe; the original data remains untouched as a checkpoint.

Result in Dev: Flawless. Despite the time required for backup and restore, it worked exactly as expected.

The Production Reality Check

When we moved to production, we hit a brick wall. The production server had a 500 GB total capacity, but only 100 GB of free space remained. Cloning a 200 GB table into a 100 GB hole is mathematically impossible. We needed a Plan B.

Phase 2: The “Chunked Delete” Strategy (The Safe Bet?)

We pivoted to a more granular approach: deleting data directly from the existing tables. To prevent long‑running queries or locking the database, we followed these steps:

• Delete data in year‑by‑year ranges (2018, 2019, etc.).
• Use Bash scripts to automate chunked deletions with LIMIT clauses, keeping the system responsive and avoiding “hanging” the DB.

The Trap: The OPTIMIZE TABLE Paradox

The deletions worked, but MySQL doesn’t automatically shrink the file size after a DELETE (because of the “high water mark” and data fragmentation). To actually reclaim the 100 GB+ of space, we needed to run OPTIMIZE TABLE.

Critical oversight: OPTIMIZE TABLE creates a temporary copy of the table. Even after deleting half the data, the operation still requires enough free space to rewrite the entire table. Our 100 GB of free space was again the bottleneck. We were stuck.

Phase 3: The “Off‑Site Surgery” (The Final Solution)

Desperate times call for creative architecture. We performed the heavy lifting on a separate environment.

1. Export – Dump the massive 200 GB table and move it to a secondary server with ample storage.
2. Cleanse & Shrink – On the secondary server, delete the unwanted rows and run OPTIMIZE TABLE.
3. Result – The optimized table dropped significantly below 100 GB.
4. Import – Transfer the now‑compacted table back to production under a new name.
5. Final Swap – Because the new table now fit within the 100 GB free space, we swapped it with the original and dropped the old 200 GB giant.

Key Takeaways for Us

• Free Space Is Your Best Friend – Always check df -h before planning a migration. Most MySQL maintenance operations (like OPTIMIZE or ALTER) require at least 2× the size of your largest table in free space.
• Optimize With Caution – Remember that OPTIMIZE TABLE is not a “free” operation; it needs temporary storage equal to the table’s current size.
• Chunked Operations Save the Day – Deleting data in small batches prevents long locks and keeps the DB responsive.
• Off‑Site Processing Can Be a Lifesaver – When on‑prem storage is insufficient, off‑loading heavy transformations to a larger environment can unblock you without downtime.

Written by Taufiq Abdullah.

An “in‑place” fix; it is a rebuild.

**Think Outside the Box:**  
If your local disk is full, use a secondary staging server to process the data before bringing it back home.

Database cleansing is as much about managing infrastructure as it is about writing SQL. Have you ever faced a storage “deadlock” like this? I’d love to hear how you handled it in the comments!