The Problem: ALTER TABLE Is a Nightmare on Large Tables
You have an orders table with 50 million rows running in production. Your Product Manager asks you to add a payment_verified_at column. You type ALTER TABLE orders ADD COLUMN payment_verified_at TIMESTAMP NULL;, press Enter — and the website starts timing out 30 seconds later.
The problem is that MySQL has to lock the entire table for the duration of the rebuild. There’s no way around it. With 50 million rows on average hardware, that’s 30–60 minutes of downtime. Every read and write query has to wait, the connection pool drains, and users see a blank screen.
I once ran into a database corruption incident at 3 AM and had to restore from backup — it took nearly 2 hours to sort out. Ever since, I’ve never touched a production schema without a clear plan. This post will show you how to do it right from the start.
Comparing 3 MySQL Schema Change Approaches
Approach 1: Direct ALTER TABLE
-- Only use for small tables (under 500K rows)
ALTER TABLE users ADD COLUMN phone_verified_at TIMESTAMP NULL;
Simple, no additional tools needed. But MySQL locks the entire table throughout the process — a 10M row table can easily cost you 10 minutes of downtime. You can’t abort mid-operation if you spot a problem.
Approach 2: pt-online-schema-change (Percona Toolkit)
pt-osc has been the MySQL community’s go-to for years. It works by creating a shadow table _users_new, placing 3 triggers (INSERT/UPDATE/DELETE) on the original table to sync new writes, copying old data over in chunks, then renaming when done.
- Pros: No table lock, battle-tested over many years, large community.
- Cons: Triggers add overhead to every write operation. Incompatible with Galera or NDB Cluster. Difficult to pause/resume. Can’t adjust speed while running.
Approach 3: gh-ost (GitHub Online Schema Transmogrifier)
GitHub built gh-ost because pt-osc wasn’t powerful enough at their database scale — billions of rows, extremely high write throughput, and no tolerance for the extra overhead that triggers bring. The solution: instead of triggers, gh-ost connects as a MySQL replica and reads the binlog directly to track changes while copying data.
- Pros: No triggers, no additional write overhead. Pause/resume at any time. Adjust speed in realtime. Dry-run mode to test beforehand. Precise control over when to rename the table.
- Cons: Requires ROW binlog format. Needs REPLICATION SLAVE privilege. Setup is slightly more involved than pt-osc.
Pros and Cons — Which Tool Should You Pick?
| Criteria | Direct ALTER | pt-osc | gh-ost |
|---|---|---|---|
| Small table (<500K rows) | ✅ Best | Overkill | Overkill |
| Large table (production) | ❌ Don’t use | ✅ OK | ✅ Best |
| Table has existing triggers | OK | ❌ Conflict | ✅ OK |
| Galera / PXC Cluster | Varies | Limited | ❌ Not supported |
| Pause / Resume | ❌ | ❌ Difficult | ✅ Easy |
| Realtime throttle | ❌ | Limited | ✅ Full |
| Rename timing control | ❌ | ❌ | ✅ Postpone flag |
So which should you pick? Direct ALTER for small tables or dev environments. gh-ost for production with large tables. pt-osc when you’re on Galera or in environments that don’t support binlog streaming.
Step-by-Step gh-ost Deployment Guide
Step 1: Install gh-ost
gh-ost is a single binary with no runtime dependencies:
# Download binary from GitHub Releases (check for the latest version)
VERSION="1.1.6"
wget https://github.com/github/gh-ost/releases/download/v${VERSION}/gh-ost-binary-linux-amd64-${VERSION}.tar.gz
tar -xzf gh-ost-binary-linux-amd64-${VERSION}.tar.gz
sudo mv gh-ost /usr/local/bin/
chmod +x /usr/local/bin/gh-ost
# Verify
gh-ost --version
Step 2: Verify MySQL Binlog Configuration
gh-ost requires the binlog to be enabled in ROW format:
-- Check current configuration
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
-- If needed, add to /etc/mysql/my.cnf or /etc/my.cnf
-- [mysqld]
-- log_bin = /var/log/mysql/mysql-bin.log
-- binlog_format = ROW
-- server_id = 1
-- Then restart MySQL
Step 3: Dry Run Before the Real Thing
Always dry run first. This command checks the connection, permissions, and binlog configuration — but makes no changes whatsoever:
gh-ost \
--host="127.0.0.1" \
--port=3306 \
--user="root" \
--password="your_password" \
--database="myapp" \
--table="orders" \
--alter="ADD COLUMN payment_verified_at TIMESTAMP NULL" \
--dry-run \
--verbose
Step 4: Run the Actual Migration
gh-ost \
--host="127.0.0.1" \
--port=3306 \
--user="root" \
--password="your_password" \
--database="myapp" \
--table="orders" \
--alter="ADD COLUMN payment_verified_at TIMESTAMP NULL" \
--chunk-size=1000 \
--max-load="Threads_running=25" \
--critical-load="Threads_running=50" \
--switch-to-rbr \
--exact-rowcount \
--panic-flag-file=/tmp/ghost.panic \
--postpone-cut-over-flag-file=/tmp/ghost.postpone \
--execute
Make sure you understand these flags before running:
--chunk-size=1000: Copies 1000 rows per batch. Increase to 2000–5000 when the server is idle, drop to 500 under high load.--max-load="Threads_running=25": gh-ost auto-pauses if MySQL is overloaded, and auto-resumes when load drops.--critical-load="Threads_running=50": Emergency stop if things get worse.--panic-flag-file=/tmp/ghost.panic: Create this file to stop the migration immediately.--postpone-cut-over-flag-file=/tmp/ghost.postpone: Create this file to delay the final table rename step.
Step 5: Monitor and Control in Realtime
gh-ost creates a Unix socket (path format: /tmp/gh-ost.{database}.{table}.sock), letting you interact with it directly while the migration is running:
# Check progress
echo "status" | nc -U /tmp/gh-ost.myapp.orders.sock
# Increase chunk-size when server load is low (no restart needed)
echo "chunk-size=3000" | nc -U /tmp/gh-ost.myapp.orders.sock
# Manually throttle (temporarily slow down)
echo "throttle" | nc -U /tmp/gh-ost.myapp.orders.sock
# Remove throttle
echo "no-throttle" | nc -U /tmp/gh-ost.myapp.orders.sock
Step 6: Control the Cut-Over Timing
This is my favorite gh-ost feature. When the migration finishes copying data, instead of renaming the table right away, you have precise control over when the cut-over happens:
# Create this file upfront so gh-ost waits after the copy finishes
touch /tmp/ghost.postpone
# gh-ost will print: "Postponing cut-over; waiting for /tmp/ghost.postpone to be deleted"
# When you're ready (e.g., waiting for the lowest traffic window)
rm /tmp/ghost.postpone
# gh-ost will perform the rename immediately
This feature is especially useful when you need to deploy new code at the same time as a schema change — run the migration first, wait for the code deploy to finish, then cut over.
Step 7: Handling Emergency Stops
# Stop immediately (safe — original table is unaffected)
touch /tmp/ghost.panic
# gh-ost leaves 2 temporary tables behind; delete them after verification
# _orders_gho — ghost table being built
# _orders_ghc — changelog table
gh-ost never deletes the original table until the rename completes successfully — this is by design. No matter why the migration is interrupted, your orders table remains intact, with not a single row lost.
Common ALTER Operations with gh-ost
# Add a column
--alter="ADD COLUMN is_verified TINYINT(1) NOT NULL DEFAULT 0"
# Add an index
--alter="ADD INDEX idx_status_created (status, created_at)"
# Multiple changes at once — saves one full table copy
--alter="ADD COLUMN notes TEXT NULL, ADD INDEX idx_user_status (user_id, status)"
# Change data type (watch out for data truncation)
--alter="MODIFY COLUMN status VARCHAR(50) NOT NULL DEFAULT 'active'"
# Add a column with a default value
--alter="ADD COLUMN tier ENUM('free','pro','enterprise') NOT NULL DEFAULT 'free'"
Real-World Workflow — Pre-Migration Checklist
- Confirm a backup has run and can be restored from that same day
- Run
--dry-runon staging with data similar to production - Estimate time: 1M rows ≈ 5–10 minutes depending on server and chunk-size
- Choose the lowest-traffic window — use the postpone flag if you need flexibility
- Set
--panic-flag-fileand--postpone-cut-over-flag-filefrom the start - Monitor server load throughout the migration
- After cut-over, verify the new schema and drop the 2 temporary ghost tables (
_orders_gho,_orders_ghc)
