gh-ost - GitHub's MySQL Migration Tool That Doesn't Use Triggers

Why gh-ost Exists: Because pt-online-schema-change Will Ruin Your Weekend

GitHub built gh-ost after getting burned too many times by pt-online-schema-change taking down production during routine migrations. Released in 2016, it's their solution to the "why is our database locked up again?" problem.

The Real Problem with Triggers

Every other MySQL migration tool uses triggers. Triggers are fucking evil because they run in the same transaction as your application queries. First, they create metadata lock contention that can freeze your entire database - I've seen 30-second ALTER TABLE operations turn into 10-minute deadlocks because triggers couldn't be installed. Then every INSERT, UPDATE, and DELETE on your table has to execute trigger code, adding massive overhead to every transaction (I've seen 40-80% performance hits) on a table doing 10k writes/second. And once pt-osc starts? You can't actually stop it without data loss because the "throttle" option only pauses the copy phase while triggers keep firing and eating CPU. Your only choice is to let it finish or restore from backup.

Alright, Here's How gh-ost Fixes This Clusterfuck

OK, rant over. gh-ost reads from the MySQL binary log instead of using triggers, which fixes all this shit:

• Actually Pausable: Run echo throttle | socat - /tmp/gh-ost.sock and it immediately stops all writes. Your database load drops to normal instantly. See interactive commands documentation.

• No Lock Contention: No triggers to install/remove means no metadata locks. The migration runs completely outside your application transactions. Read about MySQL's locking mechanisms.

• Real Throttling: You can pause, resume, change chunk sizes, and even abort mid-migration without leaving your database in a broken state. Check the throttling documentation for all options.

The Catch

gh-ost isn't magic. It requires Row-Based Replication and MySQL 5.7+. Foreign keys are a no-go - you have to drop them before migration and recreate after. But honestly, if you're running MySQL in production at any scale, you should already have RBR enabled and foreign keys removed for performance reasons.

Who Actually Uses This

Version 1.1.7 was released December 20, 2024 with performance improvements and several bug fixes. GitHub uses it for their own production (obviously), along with companies like Shopify and others who got tired of pt-osc ruining their weekends. It has 12.9k stars on GitHub, which in database tool terms means "actually battle-tested." You can find the complete requirements and limitations in the official documentation.

How It Really Works

gh-ost creates a "ghost" table with your new schema, then copies data in chunks while tailing the binary log to catch live changes. The interactive commands let you actually control it while it's running, unlike pt-osc which is basically "start it and pray."

Here's how it's supposed to work (spoiler: reality is messier): It copies your shit in chunks, watches the binary log for new changes, then swaps tables when done. That's it. Well, technically it creates a your_table_gho ghost table first, copies 1000 rows at a time by default, tails the binlog from a replica to catch live changes, applies those to the ghost table, and finally does a quick table swap. But you get the idea.

The Stuff That Actually Works

You Can Actually Pause It: echo throttle | socat - /tmp/gh-ost.sock stops all writes immediately. This saved my ass when load spiked during a migration and I needed to back off instantly. Full command reference here.

Replica Testing: The --test-on-replica flag lets you run the entire migration on a replica first. It stops replication at the end so you can verify the data looks right before risking production.

Lag Monitoring: It watches replication lag and throttles automatically if replicas fall behind. You can set --max-lag-millis=1500 and it'll pause when lag hits that threshold.

Where It All Goes Wrong in Production

Binary Log Position Can Get Fucked: On our AWS RDS setup running MySQL 8.0.28, if the binary log gets corrupted or the position becomes invalid, your migration dies with a cryptic error about binlog coordinates. Had this migration die on us when the logs got purged mid-run because some genius set expire_logs_days=1. Took forever to figure out what happened, ended up having to restart the whole thing. Not fun when you're trying to hit a maintenance window.

Foreign Keys = Manual Work: No foreign key support means you drop them all before migration, then recreate after. For a table with 15 FKs pointing to it, this becomes a whole project involving application code changes and careful ordering.

Cleanup Is Manual: When a migration fails (and they do), you're left with ghost tables and changelog tables to clean up manually. The --serve-socket-file disappears, so you can't even ask it what went wrong.

MySQL Version Gotchas:

• Works on MySQL 5.7+ only
• Requires Row-Based Replication
• Some cloud providers (looking at you, RDS) have weird restrictions on binary log access
• Check MySQL 8.0 compatibility notes for latest versions

Real Performance Numbers

From Percona's benchmarks on a 3GB sysbench table:

• Idle load: pt-osc finished nearly twice as fast as gh-ost
• Light load: pt-osc still ~2x faster than gh-ost
• Heavy load (40% capacity): gh-ost couldn't complete, pt-osc finished with 12% performance impact

The reality? gh-ost's single-threaded binlog processing hits a wall on write-heavy workloads. It's great for safety and control, but pt-osc can handle higher write volumes.

When It Breaks

"Error 1236: Could not find first log file name in binary log index file": Happens when your binary log files got rotated during migration. If you're fucked, you're fucked - there's no recovery from this. We had expire_logs_days=1 set too aggressively. Discovered this during peak traffic, naturally.

"Cannot determine table's PRIMARY KEY": Happens on tables with no PK or only composite keys. You need a single-column primary key or unique index. Yes, even in 2025 there are tables without proper PKs. We had to add a surrogate PK column to a 200GB audit log table - that was a fun conversation with the architect.

"Connection was killed": MySQL 8.0.28 has a nasty bug where long-running SELECT queries get killed during gh-ost operations. Set --mysql-timeout=300 or you'll randomly fail after hours of work. Nothing like watching 8 hours of progress vanish.

Replication lag spikes: The binary log tailing can fall behind on write-heavy workloads. Monitor SHOW SLAVE STATUS and be ready to increase --chunk-size if needed. AWS RDS has its own special way of calculating lag that doesn't always make sense.