Background
Starting from December 5th, a production instance began experiancing daily replica lag alerts. After investigation, the TPS during the lag period was not high, with only about 1K DML operations on the clickstream_events table. However, this table had grown to 60GB, far exceeding the recommended maximum single table size of 10GB.
This instance serves as a clickstream database, primarily recording user link visit behavior. After discussing with the development team, it was decided to archive historical data from this table while retaining only the most recent month's data.
Operation Steps
Step 1: Confirm Data Archive Conditions
The archive operation is based on the non-primary key column server_time. Since pt-archiver requires primary key conditions, we need to map time-based conditions to primary key values first.
SHOW CREATE TABLE clickstream_events\G
SELECT MAX(event_id) FROM clickstream_events WHERE event_time = '2018-04-30 23:59:59';
SELECT MAX(event_id) FROM clickstream_events WHERE event_time = '2018-05-31 23:59:59';
SELECT MAX(event_id) FROM clickstream_events WHERE event_time = '2018-06-30 23:59:59';
SELECT MAX(event_id) FROM clickstream_events WHERE event_time = '2018-07-31 23:59:59';
SELECT MAX(event_id) FROM clickstream_events WHERE event_time = '2018-08-31 23:59:59';
SELECT MAX(event_id) FROM clickstream_events WHERE event_time = '2018-09-30 23:59:59';
SELECT MAX(event_id) FROM clickstream_events WHERE event_time = '2018-10-31 23:59:59';
Step 2: Create Monthly Archive Tables
Since the source table is large, archiving by month facilitates both operation and subsequent data queries. Create separate archive tables for each month in the target database:
CREATE TABLE events_archived_04 LIKE clickstream_events;
CREATE TABLE events_archived_05 LIKE clickstream_events;
CREATE TABLE events_archived_06 LIKE clickstream_events;
CREATE TABLE events_archived_07 LIKE clickstream_events;
CREATE TABLE events_archived_08 LIKE clickstream_events;
CREATE TABLE events_archived_09 LIKE clickstream_events;
CREATE TABLE events_archived_10 LIKE clickstream_events;
Step 3: Prepare Archive Commands
Configure the pt-archiver commands for each month's data migration:
# April archive
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=clickstream_events \
--charset 'UTF8' --dest h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=events_archived_04 \
--no-version-check --where "event_id <= 4383363" --statistics --no-delete \
--bulk-insert --progress 5000 --limit=500 --txn-size=100 \
>> archive--clickstream--events_04.log &
# May archive
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=clickstream_events \
--charset 'UTF8' --dest h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=events_archived_05 \
--no-version-check --where "event_id <= 26473975 AND event_id > 4383363" --statistics --no-delete \
--bulk-insert --progress 5000 --limit=500 --txn-size=100 \
>> archive--clickstream--events_05.log &
# June archive
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=clickstream_events \
--charset 'UTF8' --dest h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=events_archived_06 \
--no-version-check --where "event_id <= 51504119 AND event_id > 26473975" --statistics --no-delete \
--bulk-insert --progress 5000 --limit=500 --txn-size=100 \
>> archive--clickstream--events_06.log &
# July archive
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=clickstream_events \
--charset 'UTF8' --dest h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=events_archived_07 \
--no-version-check --where "event_id <= 75811899 AND event_id > 51504119" --statistics --no-delete \
--bulk-insert --progress 5000 --limit=500 --txn-size=100 \
>> archive--clickstream--events_07.log &
# August archive
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=clickstream_events \
--charset 'UTF8' --dest h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=events_archived_08 \
--no-version-check --where "event_id <= 121711398 AND event_id > 75811899" --statistics --no-delete \
--bulk-insert --progress 5000 --limit=500 --txn-size=100 \
>> archive--clickstream--events_08.log &
# September archive
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=clickstream_events \
--charset 'UTF8' --dest h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=events_archived_09 \
--no-version-check --where "event_id <= 150953368 AND event_id > 121711398" --statistics --no-delete \
--bulk-insert --progress 5000 --limit=500 --txn-size=100 \
>> archive--clickstream--events_09.log &
# October archive
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=clickstream_events \
--charset 'UTF8' --dest h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=events_archived_10 \
--no-version-check --where "event_id <= 206555065 AND event_id > 150953368" --statistics --no-delete \
--bulk-insert --progress 5000 --limit=500 --txn-size=100 \
>> archive--clickstream--events_10.log &
Step 4: Execute Archive Commands
Run the prepared archive commands to migrate historical data month by month.
Step 5: Verify Data Integrity
After archiving completes, verify the row counts match between source and archive tables:
-- April verification
SELECT COUNT(*) FROM clickstream_events WHERE event_id <= 4383363;
SELECT COUNT(*) FROM events_archived_04 WHERE event_id <= 4383363;
-- May verification
SELECT COUNT(*) FROM clickstream_events WHERE event_id <= 26473975 AND event_id > 4383363;
SELECT COUNT(*) FROM events_archived_05 WHERE event_id <= 26473975 AND event_id > 4383363;
-- June verification
SELECT COUNT(*) FROM clickstream_events WHERE event_id <= 51504119 AND event_id > 26473975;
SELECT COUNT(*) FROM events_archived_06 WHERE event_id <= 51504119 AND event_id > 26473975;
-- Continue verification for remaining months...
Step 6: Export Archive Table Backups
After confirming data consistency, export the archive tables for backup:
mysqldump -S /var/lib/mysql/mysql.sock --single-transaction --master-data=2 \
--set-gtid-purged=OFF --no-create-info analytics events_archived_04 \
> /data/backup/analytics--events_archived_04.sql.bak
mysqldump -S /var/lib/mysql/mysql.sock --single-transaction --master-data=2 \
--set-gtid-purged=OFF --no-create-info analytics events_archived_05 \
> /data/backup/analytics--events_archived_05.sql.bak
# Continue for remaining archive tables...
Step 7: Prepare Delete Commands
Create the command to purge archived data from the source table:
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=clickstream_events \
--charset UTF8 --no-version-check --where="event_id <= 206555065" \
--progress=5000 --limit=500 --purge --bulk-delete --commit-each --sleep=1 --statistics \
>> archive--clickstream--purge_10.log &
Step 8: Alternative Deletion Method Using Script
Alternatively, use a bash script for incremental deletion:
#!/bin/bash
USER="admin"
PASS="pass123"
SOCKET="/var/lib/mysql/mysql.sock"
DATABASE="analytics"
TABLE="clickstream_events"
MAX_ID=206555065
BATCH_SIZE=1000
PRIMARY_KEY="event_id"
MYSQL_CLIENT="/usr/local/bin/mysql -u${USER} -p${PASS} -S${SOCKET} ${DATABASE}"
for ((i=0; i<207; i++)); do
${MYSQL_CLIENT} -e "DELETE FROM ${TABLE} WHERE ${PRIMARY_KEY} <= ${MAX_ID} LIMIT ${BATCH_SIZE};"
sleep 0.2
echo "Batch $i completed"
done
Step 9: Execute Data Deletion
After backup are completed, execute the deletion command to remove historical data from the source table.
Tool Introduction
The pt-archiver tool is part of the Percona Toolkit collection. It is primarily designed for data deletion, archiving, and migration scenarios. It efficiently moves rows from one table to another while optionally deleting them from the source, making it ideal for purging historical data from large tables without causing significant performance impact.
Tool Parameters
Connection Parameters
| Parameter | Description |
|---|---|
| --source | DSN specifying the source table to archive from (required) |
| --dest | DSN specifying the destination table to archive to |
| -h, --host | MySQL server hostname |
| -P, --port | Port number for MySQL connection |
| -S, --socket | Socket file path for connection |
| -u, --user | Username for login |
| -p, --password | Password for connection |
| -D, --database | Database name |
| -t | Table name to archive from/to |
| -A, --charset | Character set for connection |
| -F | Read options only from specified config file |
| -L | Explicitly enable LOAD DATA LOCAL INFILE |
Common Operation Parameters
| Parameter | Description | Example |
|---|---|---|
| --[no]version-check | Check for latest Percona Toolkit and MySQL versions (default: yes) | --no-version-check (required for cloud RDS) |
| --where | WHERE clause to limit which rows to archive (required) | --where="event_id <= 100000" |
| --statistics | Collect and print timing statistics | Outputs execution details and final statistics |
| --no-delete | Do not delete rows from source after archiving | Preserves source data |
| Use LOAD DATA INFILE for bulk inserts (implies --bulk-delete --commit-each) | Faster insert performance on destination | |
| --progress | Print progress information every X rows | --progress=5000 |
| --limit | Number of rows to fetch per statement (default: 1) | --limit=500 |
| --txn-size | Number of rows per transaction (default: 1) | --txn-size=100 |
| --sleep | Sleep time between fetches (in seconds) | --sleep=1 |
| --bulk-delete | Delete each chunk with single DELETE statement | Batch deletion from source |
| --replace | Use REPLACE instead of INSERT for destination | Handles duplicate key conflicts |
| --file | File path for exporting data (supports DATE_FORMAT) | --file '/tmp/export.csv' |
| --purge | Delete matching rows from source instead of archiving | Used for data cleanup |
| --header | Print column headers in output file | Works with --file |
| --[no]check-columns | Verify source and dest have same columns (default: yes) | Auto-aborts if schemas differ |
| --check-interval | Pause interval when slave lag is detected (default: 1s) | --check-interval=5s |
| --local | Do not write OPTIMIZE/ANALYZE to binlog | Prevents replication lag |
| --retries | Number of retries for timeouts/deadlocks (default: 1) | --retries=3 |
| --analyze | Run ANALYZE TABLE on source/dest after operation | --analyze=ds |
Usage Examples
Example A: Archive Data to Another Table Without Deleting Source
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=clickstream_events \
--charset 'UTF8' --dest h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=events_archived_04 \
--no-version-check --where="event_id <= 4383363" \
--statistics --no-delete --bulk-insert --progress=5000 --limit=500 --txn-size=100 \
>> archive--clickstream--events_04.log &
Example B: Delete Old Data from Source Table
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=clickstream_events \
--charset 'UTF8' --no-version-check --where "event_id <= 4383363" \
--progress=5000 --limit=500 --purge --bulk-delete --commit-each --sleep=1 --statistics \
>> archive--clickstream--purge_04.log &
Example C: Export Data to File
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=clickstream_events \
--where "event_id <= 4383363" \
--file '/tmp/2018-04-export.txt' \
--statistics --no-delete --bulk-insert --progress=5000 --limit=500 --txn-size=100 \
>> archive--clickstream--export_04.log &
Example D: Export to File and Delete from Database
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='pass123',D=analytics,t=clickstream_events \
--charset 'UTF8' --no-version-check --where "event_id <= 4383363" \
--file '/tmp/2018-04-export.txt' \
--progress=5000 --limit=500 --purge --bulk-delete --commit-each --sleep=1 --statistics \
>> archive--clickstream--export_purge_04.log &