pt-online-schema-change - The Tool That Prevents 3AM Disaster Calls

Standard MySQL ALTER TABLE on a 50GB table? Hope you like explaining to your boss why the site was down for 6 hours. Here's what MySQL actually does when you run ALTER TABLE users ADD COLUMN email VARCHAR(255):

1. MySQL creates a new table with your schema change
2. Copies every single row from the old table to the new one
3. Locks the entire table while this happens
4. Swaps the tables when done

On my 200GB user table, this took 8 hours and locked out every single user. Learned that lesson the hard way. The MySQL docs explain the process but conveniently skip the 'your users will hate you for half a day' part.

How pt-online-schema-change Actually Works

pt-osc does the same thing but doesn't fuck your table with locks:

Step 1: Create the Shadow Table
Makes an empty copy of your table with the schema changes applied. Your original table keeps working normally.

Step 2: Copy Data in Chunks
Copies 1,000 rows at a time by default. Pauses between batches so your app doesn't die. Takes forever but won't murder your database. The chunking algorithm uses the primary key to efficiently iterate through data.

Step 3: Keep Everything in Sync
Creates triggers on your original table to capture any writes happening during the copy. These get replayed on the shadow table so nothing gets lost. This is the critical difference from gh-ost, which uses binary log parsing instead.

Step 4: The Atomic Swap
When copying is done, it does a RENAME TABLE operation that swaps the old and new tables. This part takes milliseconds, not hours. The MySQL RENAME TABLE operation is atomic and metadata-only.

The Real Gotchas Nobody Tells You

Disk Space: You need 3x your table size free. The official docs say 2x but that's horseshit. Last time I ran out of space at 95% on a 180GB table when the logs filled up. Spent Saturday explaining to the CTO why we needed emergency disk space at 2AM. Run df -h first or suffer.

Primary Key Required: If your table doesn't have a primary key, you're fucked. The tool needs it for the DELETE trigger. Found this out on a legacy table that took 2 days to add a PK to. This MySQL bug report explains why triggers require PKs.

Foreign Keys Are Hell: The `--alter-foreign-keys-method` options are all terrible in different ways. drop_swap disables FK checks (scary), rebuild_constraints can fail spectacularly, and auto just picks one of these disasters. Percona's FK handling guide explains the nightmare.

Triggers Can Cause Replication Lag: Heavy write traffic means lots of trigger execution. I've seen 30-second replication lag spikes that made monitoring scream. MySQL trigger performance degrades with table size.

The Progress Bar Lies: When it says 99%, that could still be 2 hours. The last chunk often takes forever because of lock contention. This forum thread covers the common hang scenarios.

This tool works, but test it on a replica first or you'll be debugging production at 3AM like I was last Tuesday. Percona's testing guide covers replica testing if you want to do this right.

Anyway, here's when to actually use this thing vs the other options.

Installation

pt-osc comes with Percona Toolkit. Latest is 3.7.0 (Dec 23, 2024) with MySQL 8.4 support. Install from Percona's repos:

## Ubuntu/Debian
apt install percona-toolkit

## CentOS/RHEL
yum install percona-toolkit

## macOS
brew install percona-toolkit

Or download packages or use Docker if you're into that. Works with MySQL 5.7-8.4 and MariaDB 10.x.

Before You Run Anything

Check Your Table Has a Primary Key:

SHOW INDEX FROM your_table WHERE Key_name = 'PRIMARY';

No primary key = no pt-osc. You'll need to add one first. MySQL's documentation explains adding primary keys, but be aware this itself requires a schema change.

Check Disk Space:

df -h

You need 3x your table size free. The official docs lie about "2x" - trust me. I've run out of space too many times. Also check InnoDB file-per-table if you're using shared tablespaces.

Test Your ALTER Statement:

pt-online-schema-change --alter \"ADD COLUMN email VARCHAR(255)\" --dry-run D=test,t=users

If dry-run fails, fix it before wasting 6 hours on a broken command like I did once. The dry-run documentation explains what gets tested (spoiler: not the stuff that actually breaks in production).

Basic Command That Actually Works

pt-online-schema-change \
  --alter \"ADD COLUMN email VARCHAR(255)\" \
  --execute \
  --max-lag=5s \
  --max-load=Threads_running=50 \
  --critical-load=Threads_running=100 \
  --chunk-size=1000 \
  --sleep=0.1 \
  D=yourdb,t=yourtable

Real-World Time Estimates

The progress percentage is complete bullshit. Here's reality:

• 1GB table: 30 minutes to 2 hours (depending on write traffic)
• 10GB table: 2-8 hours (plan for the high end)
• 100GB table: 8-24 hours (seriously, run this overnight)
• 500GB+ table: Days. Schedule vacation time.

The last 10% often takes as long as the first 90% due to lock contention.

When It Will Fail

Lock Wait Timeouts:

DBD::mysql::db do failed: Lock wait timeout exceeded

This means long-running queries are blocking the tool. Use `SHOW PROCESSLIST` to find blocking queries and kill them, or run during low traffic. Adjust `innodb_lock_wait_timeout` if needed.

Out of Disk Space:
The tool doesn't check disk space because that would make sense. I've watched it fill drives at 95% and crash. MySQL 8.0.24 had a bug where it wouldn't fail gracefully on ENOSPC - just died. Watch disk usage with `df` or `iostat`. Percona's PMM covers monitoring if you want to do this right.

Replica Discovery Hangs:
If your replicas aren't reachable, pt-osc hangs forever trying to connect. Use `--recursion-method=none` to skip replica discovery. Essential for AWS RDS environments.

Foreign Key Hell:
Foreign keys make everything harder. The `--alter-foreign-keys-method` options are:

• auto: Lets the tool decide (usually wrong)
• rebuild_constraints: Rebuilds FKs (can fail with complex schemas)
• drop_swap: Disables FK checks temporarily (scary but works)

This Percona blog post explains why FK handling is fundamentally broken.

Production Survival Guide

Before You Start:

1. Run during low traffic hours (2-6 AM)
2. Alert your team - this will cause replication lag
3. Have the rollback plan ready (hint: there isn't really one)
4. Monitor disk space, CPU, and replication lag

While It's Running:

• Watch SHOW PROCESSLIST for blocking queries
• Monitor replication lag on all slaves
• Don't panic when progress stalls at 99%

If It Breaks:
The original table gets renamed to _old. You can swap it back, but that requires downtime:

RENAME TABLE users TO users_broken, users_old TO users;

The Ugly Truth

This tool works, but it's not magic. I've run it on 500GB tables and it always takes longer than you think. Budget 2x your estimate, then double that when your manager asks.

Test on a replica first. Dry-run lies - it'll pass dry-run and fail in production because MySQL hates you.

Here's what actually breaks at 2AM.