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.

Laravel Ingest by Robin Kopp is a configuration-driven ETL (Extract, Transform, Load) package that replaces one-off import scripts with declarative importer classes. It handles files from a few hundred to tens of millions of rows by processing them through PHP Generators and Laravel Queues, keeping memory usage consistent regardless of file size.

Main Features

• Declarative importer classes using a fluent IngestConfig builder
• Automatic resolution of BelongsTo and BelongsToMany relationships
• Duplicate handling strategies: SKIP, CREATE, UPDATE, and UPDATE_IF_NEWER
• Dry-run mode to validate imports before writing to the database
• Failed row tracking with downloadable CSV exports
• Column aliasing to map varying header names to a single field
• Dynamic model resolution based on row data
• Import sources: file upload, filesystem disks (including S3), URL, FTP, and SFTP
• Auto-generated Artisan commands and REST API endpoints per importer

Defining an Importer

After installing the package and running migrations, you create an importer class that implements IngestDefinition and returns an IngestConfig. By convention, these live in the App\Ingest namespace:

namespace App\Ingest;
 
use App\Models\Product;
use LaravelIngest\Contracts\IngestDefinition;
use LaravelIngest\DTOs\IngestConfig;
use LaravelIngest\Enums\DuplicateStrategy;
use LaravelIngest\Enums\SourceType;
 
class ProductImporter implements IngestDefinition
{
 public function getConfig(): IngestConfig
 {
 return IngestConfig::for(Product::class)
 ->fromSource(SourceType::UPLOAD)
 ->keyedBy('sku')
 ->onDuplicate(DuplicateStrategy::UPDATE)
 ->map('Product Name', 'name')
 ->relate('Category', 'category', Category::class, 'slug')
 ->validate([
 'sku' => 'required|string',
 'Product Name' => 'required|string|min:3',
 ]);
 }
}

Register the importer in your AppServiceProvider using the package’s tag:

use LaravelIngest\IngestServiceProvider;
 
$this->app->tag([ProductImporter::class], IngestServiceProvider::INGEST_DEFINITION_TAG);

Running Imports

Once registered, the package exposes both an Artisan command and an HTTP endpoint for each importer.

Via CLI:

php artisan ingest:run product-importer --file=products.csv

Via API (multipart form upload):

POST /api/v1/ingest/upload/product-importer

For dry runs, append the --dry-run flag to the Artisan command to validate the file and surface any errors without touching the database.

Monitoring

The package includes several Artisan commands for checking on running or completed imports:

php artisan ingest:list # List registered importers
php artisan ingest:status {id} # Show progress and row statistics
php artisan ingest:cancel {id} # Stop an in-progress import
php artisan ingest:retry {id} # Reprocess only the failed rows

Equivalent REST endpoints are also available:

• GET /api/v1/ingest — recent runs
• GET /api/v1/ingest/{id} — status and statistics
• GET /api/v1/ingest/{id}/errors/summary — aggregated error breakdown
• GET /api/v1/ingest/{id}/failed-rows/download — CSV of rows that failed

Events

The package dispatches events throughout the import lifecycle — IngestRunStarted, ChunkProcessed, RowProcessed, IngestRunCompleted, and IngestRunFailed — which you can listen to for notifications or custom side effects.

You can find Laravel Ingest on GitHub and read the full documentation at the Laravel Ingest docs.

Last year around this time, Disney’s live-action Lilo & Stitch was about to arrive, and fans wondered if the studio would be able to bounce back from its live-action Snow White stumble and resume its streak of hit remakes. And… yep, no problem there. So the live-action Moana will be diving into much safer waters when it arrives July 10, even if this latest trailer suggests it’s basically a shot-for-shot remake of the original film. That includes Dwayne Johnson as Maui, complete with animated tattoos.

There’s also plenty of built-in goodwill for Moana as a franchise, including its animated sequel, Moana 2, which was a smash hit in 2024 after being retooled from Disney+ series to theatrical film.

This new live-action take on Moana stars Catherine Lagaʻaia as Moana alongside Johnson, who also voiced Maui in the animated films.

Disney also shared a new featurette, “Artistry of Moana,” offering a behind-the-scenes peek at the film with director Thomas Kail as well as Laga’aia, Johnson, and the film’s costume designer and choreographer.

The rest of the cast includes John Tui as Moana’s father, Chief Tui; Frankie Adams as Moana’s mother, Sina; and Rena Owen as Moana’s Gramma Tala. Auli’i Cravalho, the voice of Moana in the animated films, is among Moana‘s executive producers. The film features original songs by Lin-Manuel Miranda, Opetaia Foaʻi, and Mark Mancina, and an original score composed by Mancina.

There’s also a new poster today:

Moana hits theaters July 10.

Want more io9 news? Check out when to expect the latest Marvel, Star Wars, and Star Trek releases, what’s next for the DC Universe on film and TV, and everything you need to know about the future of Doctor Who.