Large Delete/Update Pathologies, Chunking, Purge Effects, and Storage Reclamation

Learning outcomes

ServiceHub must delete 400 million expired events. A single giant DELETE is syntactically simple but operationally dangerous: one long transaction can retain undo history, hold locks, generate large redo/binlog streams, delay replica apply, and create a purge tail long after the client sees COMMIT. This lesson replaces “one statement is simpler” with bounded, observable lifecycle work.

1. Why one huge DELETE can keep hurting after it finishes

2. Create a deterministic retention fixture

DROP DATABASE IF EXISTS servicehub16_l4;CREATE DATABASE servicehub16_l4;USE servicehub16_l4;CREATE TABLE ticket_events (  event_id BIGINT PRIMARY KEY AUTO_INCREMENT,  event_day DATE NOT NULL,  ticket_id BIGINT NOT NULL,  payload VARCHAR(500) NOT NULL,  KEY ix_retention(event_day,event_id)) ENGINE=InnoDB;INSERT INTO ticket_events(event_day,ticket_id,payload)SELECT DATE('2025-01-01') + INTERVAL (seq % 500) DAY,       seq % 10000,       RPAD('event',300,'x')FROM seq_1_to_200000;SELECT COUNT(*) AS total_rows,       SUM(event_day < '2026-01-01') AS expired_rowsFROM ticket_events;

The composite (event_day,event_id) index makes the retention predicate and deterministic progress key observable. Do not copy this exact index into production without checking the real workload.

3. Deliberately start the dangerous pattern—then inspect it

USE servicehub16_l4;START TRANSACTION;DELETE FROM ticket_events WHERE event_day < '2026-01-01';-- Leave the transaction open briefly for observation, then ROLLBACK this lab attempt.

SELECT trx_id,trx_started,trx_rows_locked,trx_rows_modified,trx_queryFROM INFORMATION_SCHEMA.INNODB_TRX\GSHOW ENGINE INNODB STATUS\G

The exact counters vary by release and workload. Look for an old/large active transaction, row modifications, lock symptoms, and the history/purge context in InnoDB status. Then ROLLBACK Session A. The lesson is not to manufacture a universal threshold; it is to correlate an oversized transaction with the mechanisms it stresses.

4. Repair: bounded, deterministic chunks

Chunking trades one large failure domain for many small committed units. The chunk key must make progress deterministic; do not use a LIMIT without a stable ordering/range rule that lets you prove which rows are next.

-- Find an upper event_id boundary for at most 5000 expired rows.SELECT MAX(event_id) AS chunk_endFROM (  SELECT event_id  FROM ticket_events  WHERE event_day < '2026-01-01'  ORDER BY event_id  LIMIT 5000) AS x;-- Suppose the returned boundary is :chunk_end.DELETE FROM ticket_eventsWHERE event_day < '2026-01-01'  AND event_id <= :chunk_end;COMMIT;

In a client script, record chunk_end, affected rows, duration, lag/flow-control signals, and sleep/backoff between chunks. Resume from persisted progress after interruption. Use prepared parameters rather than textual replacement.

while expired_rows_exist:    boundary = select_next_boundary(batch_size)    begin    delete_expired_up_to(boundary)    commit    record(boundary, rows, elapsed, replica_or_wsrep_health)    if latency_or_lag_above_budget: sleep_or_stop

5. Chunk size is a feedback-control variable, not a constant

A batch of 5,000 is only a lab starting point. On production data, adapt to measured transaction duration, lock waits, redo pressure, disk latency, replica lag, Galera flow control, and application latency. A smaller batch reduces individual blast radius but increases statement/commit overhead; a larger batch does the opposite.

6. Why the .ibd file may not shrink after DELETE

InnoDB can mark freed pages as reusable inside a table tablespace. That improves future reuse but does not imply the operating-system file immediately becomes smaller. Therefore “disk file unchanged” is not proof that DELETE failed.

SELECT TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA='servicehub16_l4' AND TABLE_NAME='ticket_events';

Statistics can be approximate. If the operational goal is filesystem reclamation, a table rebuild/OPTIMIZE path can reclaim a file-per-table tablespace, but that is a separate heavyweight DDL operation with disk, locking, redo/binlog, and topology consequences. Do not run OPTIMIZE TABLE reflexively after every purge.

7. Choose the mechanism that matches the real objective

8. Production judgment and cleanup

Large DML should have the same operational controls as large DDL: backup/recovery point, deterministic progress, stop thresholds, downstream/topology monitoring, and a tested abort/resume path. If the same massive retention job repeats every month, redesign the table lifecycle rather than tuning the emergency forever.

DROP DATABASE IF EXISTS servicehub16_l4;