Handling large deletes in MySQL

Last night, a simple delete query brought screener.in down. This is how the things went.

• One of the tables in the database had around 80 million rows.
  
• I ran a MySQL query to delete around 60 million rows.
  
• I went off to sleep and my remote shell got disconnected.
  
• 7 hours later, I woke to see the website performing very slowly.
  
• The disk utilisation showed writes of 80MB/s for the last 7+ hours.
  

After an hour of debugging, this is what solved it.

SET PERSIST innodb_redo_log_capacity = 4294967296;   -- 4 GiB

Why did this happen?

I later learned that any modifications anywhere in the DB are first made to InnoDB’s buffer-pool pages—“an area in main memory where InnoDB caches table and index data as it is accessed.” Those pages become dirty because they’re not yet saved to disk, and are later flushed (written) to the data files.

Each modification also creates a redo log. Redo logs are for recovery. In case of a crash, the database can replay changes that were logged but not yet flushed by reading them from redo logs.

By default, MySQL allocates 100MB to innodb_redo_log_capacity. When redo logs reach near the allocated capacity, it triggers a more aggressive flushing of dirty pages to advance the checkpoint and free redo space.

In my case, the huge DELETE generated lots of undo and redo; the rollback then generated even more redo. That kept the redo log near full, forcing aggressive dirty-page flushing and saturating disk I/O.

Increasing the size of innodb_redo_log_capacity to 4GiB reduced the checkpoint pressure and allowed the rollback to complete.

Correct Way?

I never thought that running a delete query can stall the db.

Better way to do large deletes is to create a new table with the selected rows and then rename that table.

Something like this:

-- 1) Create a duplicate table with same schema
CREATE TABLE big_table_new LIKE big_table;

-- 2) Copy the keepers (you can do this in chunks too)
INSERT /*+ SET_VAR(innodb_flush_log_at_trx_commit=2) */
INTO big_table_new
SELECT *
FROM big_table
WHERE <keep_condition>;

-- 3) Atomic swap (FKs must be identical on the new table)
RENAME TABLE big_table TO big_table_old, big_table_new TO big_table;

-- 4) Drop the old data when you’re happy
DROP TABLE big_table_old;