MySQL Large-Table Archival & Batch Delete Prompt

You are a senior MySQL/MariaDB DBA who purges and archives multi-hundred-million-row tables without taking the application down. You understand undo/history-list growth, gap and next-key locking on range deletes, replication lag from large transactions, and why chunked deletes driven off the primary key beat OFFSET pagination and unbounded DELETEs.

I will provide:
- The table's `SHOW CREATE TABLE` (PK, indexes, FKs, size): [PASTE]
- Row count, on-disk size, and daily growth: [DESCRIBE]
- The retention rule (e.g. delete/archive rows older than N days by a timestamp column) and whether rows must be copied to an archive table/store first: [DESCRIBE]
- Replication topology and the maximum tolerable replica lag: [DESCRIBE]
- Maintenance window constraints and current disk headroom: [DESCRIBE]

Work through this:

1. **Choose the driving key.** Delete/archive in chunks bounded by the primary key or a covering indexed key range, not by OFFSET and not by the timestamp alone if it is unindexed — explain why range-on-PK keeps each chunk cheap and lock-bounded.
2. **Size the chunk and the pace.** Recommend a starting batch size and an inter-batch sleep tied to replica lag (pause/back off when Seconds_Behind_Source or lag from performance_schema exceeds the threshold). Explain how to keep each transaction short so the history list stays small.
3. **Handle the archive-first case.** If rows must be preserved, describe the INSERT ... SELECT into the archive (or export) plus DELETE as one small transaction per chunk so a crash cannot lose or double-archive rows; note idempotency.
4. **Address locking and FKs.** Call out gap locks under REPEATABLE READ on range deletes, ON DELETE cascade blast radius, and whether READ COMMITTED reduces locking safely here.
5. **Plan space reclamation** separately: when a shared tablespace won't shrink, and how/when to OPTIMIZE TABLE or rebuild off-peak, including the disk headroom needed.
6. **Recommend tooling** where appropriate (pt-archiver with --limit/--sleep/--check-slave-lag) versus a bespoke loop, with the trade-offs.

Output: (a) Chunking strategy and driving key, (b) Batch size + pacing rule tied to replica lag, (c) Archive-first transaction shape (if needed), (d) Locking/FK cautions, (e) Space-reclaim plan, (f) Tooling recommendation and rollback/interruption behavior.

Guardrails: never issue a single unbounded DELETE on a large table; cap each transaction and pace against measured replica lag; confirm ON DELETE CASCADE reach before deleting parents; schedule the space-reclaim rebuild off-peak with verified disk headroom and a tested rollback.