AWS Aurora MySQL Purging & Archival Approach

For purging and archiving large amounts of transactional data in Aurora MySQL with complex relationships, I recommend a carefully planned approach that minimizes impact on your high-traffic system.

Since your tables are not partitioned and have tight relationships across 6-7 tables with approximately 100 million records each, here are some recommended strategies:

For Archiving Unpartitioned Tables:

1. Consider using the Percona Toolkit's pt-archiver tool, which is designed to archive records from large tables to other tables or files without impacting your OLTP workload. This tool has the advantage of deleting data from the source table after archiving it, eliminating the need for separate deletion management.

2. You can install pt-archiver locally or on an EC2 instance and use it to move data to archive tables in the same database or a different server.

3. For scheduling regular archiving jobs, AWS Batch can be used to automate the process.

4. After archiving to corresponding tables in an archive schema, you can move the data to Amazon S3 for long-term storage and cost optimization.

For Purging Related Data:

1. Define clear purging criteria to identify which data should be deleted across your related tables.

2. Since your tables have tight relationships, consider enabling foreign keys for cascading deletion to maintain referential integrity during the purge process.

3. Instead of using DELETE statements which generate undo logs and can impact performance, consider alternative approaches:

• Create new tables with the data you want to keep
• Copy the data over
• Drop the old tables
• This approach avoids generating undo logs that can slow down your system

4. If you must use DELETE, break down the deletion into smaller chunks to minimize impact on your production system.

Performance Considerations:

1. Avoid long-running SELECT queries during high-volume DELETE operations as they can hold undo logs and block purge operations.

2. Consider setting max_execution_time to automatically stop SELECT queries that run too long.

3. You might want to switch the transaction isolation level from REPEATABLE READ to READ COMMITTED during purge operations to reduce the scope of read views and lower the chances of blocking undo logs from being purged.

4. Don't leave transactions open for long periods as this can consume more memory and impact performance.

Regular archiving will result in a smaller set of live data, leading to faster reads and writes and improved application performance. It also helps with cost optimization by moving older data to Amazon S3 and cleaning up your Aurora MySQL cluster.

Before implementing in production, thoroughly test your chosen approach in a staging environment to ensure it meets your performance requirements and doesn't impact your high-traffic system.
Sources
Achieve a high-speed InnoDB purge on Amazon RDS for MySQL and Amazon Aurora MySQL | AWS Database Blog
Archiving data in Amazon RDS for MySQL, Amazon RDS for MariaDB, and Aurora MySQL-Compatible - AWS Prescriptive Guidance
Archiving data from unpartitioned tables - AWS Prescriptive Guidance
Reclaiming storage space by vacuuming - Amazon Aurora
Scaling Amazon RDS for MySQL performance for Careem’s digital platform on AWS | AWS Database Blog

After checking multiple options, I think this we can use:

• If you need to keep old data, export it safely before deleting.
• Aurora MySQL supports exporting query results or snapshots directly to S3.
• Never run a massive "DELETE" — instead use batch deletes (e.g., 2k–5k rows).
• Add a small "SLEEP" (e.g., 200ms) between batches to reduce load.
• Always delete from child tables first, then from the parent to avoid foreign key issues.
• Wrap this logic in a stored procedure or cron script for reusability.

Step-by-Step: Setting Up Cron for Purge

1. Connect to Your EC2 or jump server

2. Create the Purge SQL File

Make a file /home/ec2-user/purge_old_data.sql:

-- Purge from child table
DELETE c
FROM child_table c
JOIN parent_table p ON p.id = c.parent_id
WHERE p.created_at < '2023-01-01'
LIMIT 5000;

-- Sleep briefly to reduce load
DO SLEEP(0.2);

-- Purge from parent table
DELETE FROM parent_table
WHERE created_at < '2023-01-01'
LIMIT 5000;

Notes:

• Replace "parent_table", "child_table", and "created_at" with your real schema.
• Keep "LIMIT" small (2k–5k) to minimize locking.

3. Test It Manually

Run the SQL to confirm it works as expected:

mysql -h <aurora-cluster-endpoint> -u <username> -p<password> <dbname> < /home/ec2-user/purge_old_data.sql

4. Create a Shell Script Wrapper

Make a script "/home/ec2-user/purge.sh":

#!/bin/bash
mysql -h <aurora-cluster-endpoint> -u <username> -p'<password>' <dbname> < /home/ec2-user/purge_old_data.sql

Make it executable:

chmod +x /home/ec2-user/purge.sh

5. Add to Cron

Open cron editor:

crontab -e

Schedule purge to run every night at 2 AM:

0 2 * * * /home/ec2-user/purge.sh >> /home/ec2-user/purge.log 2>&1

This means:

• Runs daily at 02:00 AM.
• Logs output & errors into "purge.log".

6. Verify Cron Job

Check your cron jobs:

crontab -l

Check purge logs after the first run:

tail -f /home/ec2-user/purge.log

7. Monitor While Running

• Watch Aurora CPU, replication lag, and deadlocks in CloudWatch.
• If system load spikes, lower the "LIMIT" or increase "SLEEP".