Changing Millions of Rows Safely

One UPDATE that touches ten million rows seems simple. On a live system it is dangerous. This lesson shows the safe approach.

Why one big statement hurts

• A long transaction. InnoDB locks every row it changes until the transaction ends. Other sessions that need those rows wait, and may time out.
• A huge undo log. MySQL must keep the old versions of all changed rows. The undo log grows, and every other query that reads those rows slows down.
• Replication lag. The replicas apply the whole change as one block, and they fall behind.
• No way back. If it fails after an hour, the rollback takes about as long again.

The approach: small batches by primary key

Process a range of the primary key at a time. Each batch is a short transaction. Let us build a table and see the idea at a small scale.

1
2
3
4
5
6
7
8
9
10
11
12

SET SESSION cte_max_recursion_depth = 20000;

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status VARCHAR(10) NOT NULL,
    payload VARCHAR(20) NOT NULL
);
INSERT INTO events (status, payload)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 10000)
SELECT 'old', CONCAT('payload ', i) FROM n;

SELECT MIN(id) AS first_id, MAX(id) AS last_id FROM events;

1
2
3
4
5
6

+----------+---------+
| first_id | last_id |
+----------+---------+
|        1 |   10000 |
+----------+---------+
1 row in set

Update 2,500 rows at a time by id range:

1
2
3
4

UPDATE events SET status = 'archived' WHERE id BETWEEN 1 AND 2500 AND status = 'old';
SELECT ROW_COUNT() AS changed_in_batch_1;
UPDATE events SET status = 'archived' WHERE id BETWEEN 2501 AND 5000 AND status = 'old';
SELECT ROW_COUNT() AS changed_in_batch_2;

1
2
3
4
5
6
7
8
9
10
11
12
13

+--------------------+
| changed_in_batch_1 |
+--------------------+
|               2500 |
+--------------------+
1 row in set

+--------------------+
| changed_in_batch_2 |
+--------------------+
|               2500 |
+--------------------+
1 row in set

An application loops over the ranges (1-2500, 2501-5000, …) until it passes the maximum id. The range uses the primary key, so each batch reads only its own rows. Add a short pause (a fraction of a second) between batches on a busy server.

The approach: LIMIT in a loop

When the rows to change are identified by a condition and not a range, use LIMIT and repeat until zero rows are affected:

1
2

UPDATE events SET status = 'archived' WHERE status = 'old' LIMIT 3000;
SELECT ROW_COUNT() AS changed;

1
2
3
4
5
6

+---------+
| changed |
+---------+
|    3000 |
+---------+
1 row in set

Repeat until the affected row count is 0. This works well when the condition is indexed (status here is not, so each round scans). For deletes it looks like DELETE FROM events WHERE ... LIMIT 5000. Add ORDER BY id to make the batches predictable.

Which batch size?

Aim for batches that take under a second, often 1,000 to 10,000 rows depending on row size and indexes. Measure. Smaller batches keep locks short but need more round trips. Watch:

• Lock waits of other sessions (SHOW PROCESSLIST, performance_schema.data_lock_waits).
• Replication lag (SHOW REPLICA STATUS): pause if the lag grows.
• Disk and I/O load.

Option: build a new table and swap

For a very large change that rewrites most rows (a type change, a restructure), it is often easier to build the result into a new table and swap the names atomically (Rename and Swap):

1
2
3
4

CREATE TABLE events_new LIKE events;
INSERT INTO events_new (id, status, payload)
SELECT id, IF(status = 'old', 'archived', status), payload FROM events WHERE id <= 100;
SELECT COUNT(*) AS copied FROM events_new;

1
2
3
4
5
6

+--------+
| copied |
+--------+
|    100 |
+--------+
1 row in set

For a live table, new rows arrive while you copy, so you would also copy the changes made during the process. Tools such as gh-ost and pt-online-schema-change automate this safely.

Option: partitions or a rolling delete

Deleting old data is the easiest when the table is partitioned by date (Partitioning): drop the oldest partition, which is instant, instead of deleting millions of rows.

Option: avoid the change

Sometimes the best plan is not to rewrite old rows at all. Add a new column with a default, and fill it only when a row is touched.

Checklist

• [ ] Backup, and a rollback plan.
• [ ] Test on a copy of the real size.
• [ ] Batches by primary key or LIMIT, committing between batches.
• [ ] An index that serves the condition.
• [ ] Pause between batches; watch locks and replica lag.
• [ ] Run during a quiet period.
• [ ] Verify with counts afterwards.