Archiving Old Rows with pt-archiver

Nobody ever writes the cleanup job for log tables, audit trails and session stores, so they just grow. I've watched a table reach 300 GB, the nightly backup crawl to four hours, and a plain DELETE FROM events WHERE created_at < '2018-01-01' would have held locks long enough to take the application down. That's what pt-archiver from Percona Toolkit is for. It deletes or moves rows in small primary-key-ordered chunks, backs off when the server is busy, and can write the rows to another table or a file so nothing is lost.

Here I move a year-old slice of an events table into an archive database on the same server, then schedule a nightly job. It's Percona Toolkit 3.1 on MySQL 5.7, and it works on 8.0 and MariaDB too. You need Percona Toolkit (yum install percona-toolkit from the Percona repo on CentOS 7, apt-get install percona-toolkit on Ubuntu 18.04), a table with a primary key or unique index (pt-archiver won't work safely without one), and an index on the column you filter by, or every chunk scans the whole table.

1. Destination table

The archive table must have the same columns as the source. Create it in a separate schema so backups can treat it differently:

CREATE DATABASE archive;
CREATE TABLE archive.events LIKE appdb.events;
ALTER TABLE archive.events ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

Compression is optional but archive data is rarely read, so trading CPU for disk usually makes sense. Check that the filter column is indexed on the source:

SHOW INDEX FROM appdb.events WHERE Column_name = 'created_at';

If it is not, add one with ALGORITHM=INPLACE, LOCK=NONE before continuing.

2. Dry run

--dry-run prints the statements the tool would execute without touching data. I never skip this.

pt-archiver --source h=localhost,D=appdb,t=events,u=dba,p=secret \
  --dest h=localhost,D=archive,t=events \
  --where "created_at < NOW() - INTERVAL 365 DAY" \
  --limit 1000 --commit-each --dry-run

You should see a SELECT ... FORCE INDEX(PRIMARY) ... LIMIT 1000 that walks the primary key, an INSERT INTO archive.events and a DELETE FROM appdb.events WHERE ... keyed on the primary key. If the SELECT doesn't mention your index, go back and fix the index first.

Use a .my.cnf or --defaults-file for the credentials rather than p=secret on the command line in real use.

3. The initial archive

pt-archiver --source h=localhost,D=appdb,t=events \
  --dest h=localhost,D=archive,t=events \
  --where "created_at < NOW() - INTERVAL 365 DAY" \
  --limit 1000 --txn-size 1000 --commit-each \
  --sleep-coef 1.0 --check-slave-lag h=replica1 --max-lag 5 \
  --progress 100000 --statistics --no-check-charset

What the options do:

• --limit 1000 --txn-size 1000 --commit-each: move 1000 rows per transaction and commit after each batch, keeping lock durations tiny.
• --sleep-coef 1.0: after each batch, sleep for as long as the batch took, so the tool never uses more than about half the server's capacity.
• --check-slave-lag h=replica1 --max-lag 5: pause whenever the named replica falls more than 5 seconds behind. This is essential; pt-archiver deletes generate a lot of binlog.
• --progress 100000: print a line every 100,000 rows.
• --statistics: summarise time spent in select, insert, delete and sleep at the end.

The first pass over a large backlog may take hours. It is safe to interrupt with Ctrl-C; the current transaction rolls back and everything committed so far stays archived.

4. Purging when you don't need the data

If the rows have already been shipped elsewhere and you just want them gone, drop --dest and add --purge. I still add --file as a safety net. It writes rows to a file in LOAD DATA INFILE format before deleting them:

pt-archiver --source h=localhost,D=appdb,t=sessions \
  --where "last_seen < NOW() - INTERVAL 30 DAY" \
  --file '/var/backups/archive/%Y-%m-%d-%D.%t' \
  --limit 500 --commit-each --sleep-coef 1.0 --purge

Add --bulk-delete to delete each chunk with a single ranged statement instead of one DELETE per row; it is much faster on large chunks but takes a gap lock across the range, so keep --limit modest on busy tables.

5. Nightly run

Once the backlog is gone, a nightly job moves one day's worth of rows and finishes in minutes. In root's crontab:

0 3 * * * /usr/bin/pt-archiver --source h=localhost,D=appdb,t=events --dest h=localhost,D=archive,t=events --where "created_at < NOW() - INTERVAL 365 DAY" --limit 1000 --commit-each --sleep-coef 1.0 --check-slave-lag h=replica1 --max-lag 5 --why-quit >> /var/log/pt-archiver.log 2>&1

--why-quit writes a line explaining why the tool stopped, which is useful when the log shows a short run: it should say there were no more rows to archive.

Did the rows move?

Compare counts across the boundary:

SELECT COUNT(*) FROM appdb.events WHERE created_at < NOW() - INTERVAL 365 DAY;
SELECT COUNT(*), MIN(created_at), MAX(created_at) FROM archive.events;

The first should be zero (or near zero if rows arrived during the run). Check that the source table's space was actually released. InnoDB does not shrink .ibd files after deletes; with innodb_file_per_table=1 you reclaim space by rebuilding:

OPTIMIZE TABLE appdb.events;

This is an online rebuild on 5.7+, but budget time and disk for a second copy of the table.

Things that bit me

Cannot find an ascendable index in table means the source has no primary key or unique index. Add one; there is no safe way to chunk without it.

Character set mismatch: --source DSN uses utf8mb4, table uses latin1. The tool checks that its connection charset matches the table. Either set A=latin1 in the DSN or use --no-check-charset if you are certain the data round-trips.

Archiving is very slow with SELECT dominating --statistics output. The WHERE column is not indexed or the optimizer is ignoring the index. Confirm with EXPLAIN on the printed SELECT, and consider --source ... ,i=idx_created_at to force a specific index.

Slave lag is too high, waiting repeating for a long time. Your replica cannot keep up with the delete volume. Lower --limit, raise --sleep-coef, or enable parallel replication (slave_parallel_workers) on the replica.

Small chunks, a lag check, and a dry run first. That's the whole recipe.