MySQL Archiving: 3 Ways to Clear the Bloat

The Silent Bloat: Managing Massive Logging Tables

Certain tables in MySQL, such as logging tables, can grow extremely large and occupy the bulk of a database. In many MySQL environments, 90% of the storage is consumed by data that is 0% useful for daily operations. Not only can they be difficult to query, these large tables can quickly affect RPO and RTO because most backup and restore time is devoted to non-critical data.

A well-designed logging system would, for instance, take advantage of MySQL table partitions. A partition can be quickly dropped with almost no overhead on the database. However, most systems start small, and the exponential growth of these tables is not accounted for.

A massive insert/delete is NOT safe:

• Undo Log Bloat
  Huge transactions require a lot of temporary space to allow for a potential rollback.
• The Rollback Trap
  If you cancel a massive delete halfway through, MySQL must undo every single row change, which is often slower than the delete itself.
• Replication Gridlock
  Replicas usually process transactions serially; one massive 30-minute delete on the primary will stop all data flow to your replicas for that same 30 minutes.

With this in mind, here are a few options to assist our customers with archiving.

Option 1: pt-archiver (Percona Toolkit)

pt-archiver is a Perl script (part of the Percona Toolkit) that "nibbles" at the data. It selects a chunk of rows, inserts them into the archive, deletes them from the source, and commits. It monitors replication lag automatically (if using native MySQL replication) and pauses if the database gets too busy.

The Process:

1. Install Percona Toolkit: sudo apt-get install percona-toolkit (or similar).

2. Create the Archive Table:

   CREATE TABLE transactions_archive LIKE transactions;
   -- Optional: Switch engine to Archive or MyISAM if you need compression and don't need updates
   -- ALTER TABLE transactions_archive ENGINE=ARCHIVE;

3. Run the Archiver:

   pt-archiver \
     --source h=localhost,D=mydb,t=transactions \
     --dest h=localhost,D=mydb,t=transactions_archive \
     --where "created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)" \
     --limit 1000 \
     --txn-size 1000 \
     --bulk-delete \
     --progress 5000

• Non-Blocking: It works in small transactions (1000 rows at a time).
• Zero Data Loss: It explicitly inserts then deletes based on the Primary Key.

Option 2: DIY

The Logic (Pseudo-code): You want to iterate through the table using the Primary Key to avoid table scans.

1. Identify the Cutoff: Find the Primary Key (ID) corresponding to 90 days ago.
2. The Loop:
   • Start Transaction.
   • Select a batch of rows (e.g., 1000) that are older than 90 days FOR UPDATE (to lock them safely).
   • Insert them into the archive table.
   • Delete them from the transactions table using their specific IDs.
   • Commit Transaction.
   • Crucial: Sleep for 1-2 seconds to let the server breathe and replication catch up.

Perl code:

$dbh->{AutoCommit} = 0;
while (1) {
    # 1. Select IDs to move (Limit locking)
    my $ids = $dbh->selectcol_arrayref(
        "SELECT id FROM transactions WHERE created_at < ? LIMIT 1000 FOR UPDATE", 
        undef, 
        $cutoff_date
    );
    last unless @$ids; # Exit if no more rows
    my $id_list = join(',', @$ids);


    # 2. Copy to Archive
    $dbh->do("INSERT INTO transactions_archive SELECT * FROM transactions WHERE id IN ($id_list)");
    # 3. Delete from Source
    $dbh->do("DELETE FROM transactions WHERE id IN ($id_list)");
    $dbh->commit;
    # 4. Safety Pause
    sleep(1); 
}

Option 3: Partitioning

Step 1: Create the New "Shadow" Table

Create your new table with the exact same schema, but add partitioning immediately.

CREATE TABLE transactions_new (
    id INT NOT NULL,
    created_at DATETIME NOT NULL,
    amount DECIMAL(10,2),
    -- ... other columns ...
    PRIMARY KEY (id, created_at) -- Partition key must be in PK
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p_old VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION p_2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p_2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    -- Always have a catch-all for future dates
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Step 2: The Migration (The "Gradual" Part)

You have two choices here depending on your uptime requirements.

Option A: The "Maintenance Window" (Safest & Easiest)
Ideal if you can afford 15-30 minutes of downtime

1. Stop the application (or pause writes).
2. Rename transactions to transactions_legacy.
3. Rename transactions_new to transactions.

4. Copy the "Hot" Data: Run a SQL script to copy only the last 90 days of data from legacy to the new table.

   INSERT INTO transactions SELECT * FROM transactions_legacy
   WHERE created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY);

5. Start the application.

Result: Your app creates new rows in the partitioned table. The old table (transactions_legacy) is now effectively your "Archive". You can drop it later or back it up to cold storage.

Option B: Zero Downtime (Double Writes)
Ideal if you cannot stop the business

1. Modify your application code to write new transactions to BOTH transactions (old) and transactions_new, or use database triggers to handle the cascaded write. BE SURE THESE TRIGGERS ARE CAA AWARE IF USING TUNGSTEN CAA TOPOLOGY.
2. Wait for the code to deploy.
3. Run a backfill script (like the Perl one above) to copy the "Hot" data (last 90 days) from Old → New.
   You must handle duplicate key errors since the app is already writing new data.
4. Once the new table has the last 90 days of data, deploy code to read/write ONLY to transactions_new.
5. Drop the old table.

Conclusion: Which Approach Should You Take?

Managing huge logging tables is about more than saving disk space. It's also about making sure your backups and restores actually work when you need them. There isn't a single "perfect" way to do this, so choose the one that fits your setup:

• Use pt-archiver if you want a solid, pre-built tool and you’re running a standard MySQL setup. It’s great for "set it and forget it" archiving.
• Use a DIY script if you have a complex setup or need total control over how much the process "sleeps" to keep replication lag at zero.
• Use Partitioning if you’re ready to change your table structure to make future cleanups as simple as dropping a partition.