Posted inMySQL

Cleaning Up Million-Row MySQL Tables: Don’t Let DELETE Commands Crash Your System

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

The Nightmare Named DELETE FROM table_name

In 2018, I almost “lost it all” over a seemingly easy task: cleaning up old orders to free up disk space. At the time, the orders table had hit 40 million rows. I confidently executed a single command:

DELETE FROM orders WHERE created_at < '2022-01-01';

Just 10 seconds later, disaster struck. System latency spiked from 50ms to 10s. Server CPU hit 100%, the connection queue grew endlessly, and my boss called asking why the website was down. That was a painful lesson in handling big data in MySQL.

The issue lies in the operational mechanism. When executing a large DELETE command, MySQL must lock data rows and write to the Undo Log for rollback purposes. It also simultaneously updates all related indexes. With millions of rows, this combination exhausts I/O and creates an immediate system bottleneck.

Cleanup Strategy: Don’t Just Delete, Think About a Way Out

In a production environment, old data might still be needed for reconciliation or reporting later. Instead of wiping it out entirely, I usually apply a safe 3-step process:

  1. Archive: Copy old data to an intermediate table or a backup database.
  2. Verify: Ensure the record counts on both sides match.
  3. Purge: Proceed with deletion from the main table in small batches (Batching).

Technique 1: Batching – Divide and Conquer

This is the simplest way to save the situation. Instead of deleting 1 million rows at once, I break it down into batches of about 5,000 rows. Between intervals, I let the script sleep for 1-2 seconds. This gives the server some breathing room to handle real user requests.

Here is the procedure I often use for automated cleanup:

DELIMITER //

CREATE PROCEDURE PurgeOldOrders()
BEGIN
    DECLARE rows_affected INT DEFAULT 1;
    
    WHILE rows_affected > 0 DO
        -- Delete in small batches to avoid long table locks
        DELETE FROM orders 
        WHERE created_at < '2022-01-01' 
        LIMIT 5000;
        
        SET rows_affected = ROW_COUNT();
        
        -- Sleep for 1 second to reduce load on I/O and Replication
        SELECT SLEEP(1);
    END WHILE;
END //

DELIMITER ;

Note: If you use MySQL Replication, batch deletion is mandatory. If you delete too much on the Master, the Slave will lag for hours as it has to re-execute that massive command.

Technique 2: Partitioning – Delete Data in a Flash

For tables exceeding 100 million records, I prioritize Table Partitioning. Instead of treating the table as one large block, MySQL splits it into small physical files based on a time column.

For example, partitioning the logs table by year:

ALTER TABLE logs PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

When you need to clean up 2022 data, you don’t need a DELETE command. Just a single DROP command:

ALTER TABLE logs DROP PARTITION p2022;

This operation is almost instantaneous. MySQL simply deletes the corresponding .ibd file on the disk, consuming no CPU resources and causing no row locks.

Technique 3: The Heavy Weapon – pt-archiver

If you don’t want to write your own scripts, use pt-archiver from the Percona Toolkit. This is the standard tool for professional DBAs.

It automates copying data to an archive table and deleting it from the original table using a “nibbling” mechanism. A real command I often use:

pt-archiver --source h=localhost,D=my_db,t=orders \
            --dest h=archive_server,D=archive_db,t=orders_history \
            --where "created_at < '2022-01-01'" \
            --limit 1000 --commit-each --sleep 1

The smart thing about pt-archiver is that it automatically pauses if it detects Slave lag or server overload.

Three Vital Notes to Avoid Server Hangs

1. Always Prioritize Indexes

Before running a delete command based on the created_at column, make sure that column is indexed. Without an index, MySQL will perform a Full Table Scan. In that case, your server will definitely crash before the deletion can complete.

2. Solving the Disk Space Paradox

In MySQL (InnoDB), a DELETE command doesn’t shrink the .ibd file immediately. It creates fragmented space (gaps). To reclaim space, you need to run OPTIMIZE TABLE. However, this command locks the table completely, so only perform it during off-peak hours.

3. Check Foreign Key Constraints

Be careful with ON DELETE CASCADE. Deleting a row in a parent table can trigger a chain reaction of deleting thousands of rows in child tables. This domino effect is often the main reason I/O spikes to 100% without the developer knowing why.

Lessons Learned

Handling big data doesn’t need to be fast; it needs to be stable. Instead of trying to sweep everything at once, choose the safest approach. Always batch the data, closely monitor the charts, and always have a backup before performing any cleanup operations.

I hope these insights help you feel more confident when handling massive MySQL tables. Good luck with your system optimization!

Share:
Tags: