✏️ Explanatory Question
Level: Very Hard — A real production emergency; the naive one-shot statement can take down an entire application.
logs table. You run DELETE FROM logs WHERE created_at < '2025-01-01'. It runs for 20 minutes, holds locks, fills the undo log, replication lags badly, and the app starts timing out. How should this have been done?
The cause: A single huge DELETE is one giant transaction. It holds row locks on millions of rows, generates a massive undo/redo log, and the replica must apply the entire operation at once — causing lock contention, disk pressure, and replication lag. It's "all or nothing," so it can't even be interrupted safely.
-- THE PROBLEM: one massive locking transaction
DELETE FROM logs WHERE created_at < '2025-01-01'; -- 5M rows at once
-- THE FIX: delete in small chunks, committing each batch
-- Run this in a loop (from a script or stored procedure)
DELETE FROM logs
WHERE created_at < '2025-01-01'
ORDER BY id -- delete in PK order for efficiency
LIMIT 5000; -- small batch
-- Repeat until 0 rows are affected. Between batches:
-- * COMMIT (each DELETE auto-commits if autocommit=1)
-- * sleep briefly to let the replica catch up-- Driver-side loop (Python/PHP/Java), MySQL statements shown:
-- WHILE affected_rows > 0:
-- DELETE FROM logs WHERE created_at < '2025-01-01'
-- ORDER BY id LIMIT 5000;
-- -- check ROW_COUNT(); if 0, break
-- -- SLEEP(0.2) to ease replication lag & I/O
-- -- monitor: SHOW REPLICA STATUS -> Seconds_Behind_Master
-- For UPDATEs, batch by a moving key range instead of OFFSET:
UPDATE users
SET status = 'archived'
WHERE id BETWEEN 1 AND 10000 AND last_login < '2024-01-01';
-- next batch: id BETWEEN 10001 AND 20000, etc.ALTER TABLE logs DROP PARTITION p2024; removes millions of rows instantly with almost no locking or undo cost.RENAME — often faster than deleting most of a table.pt-archiver (Percona Toolkit) is purpose-built for safe, throttled bulk archiving/deletion.WHERE created_at < ... keeps re-scanning already-deleted ranges, or the index is fragmenting. Fix by seeking on the primary key (track the last deleted id and use WHERE id > last_id) so each batch starts where the last ended.