Posted inDatabase

MySQL Schema Migrations at Scale: A Deep Dive into gh-ost

Database tutorial - IT technology blog
Database tutorial - IT technology blog

The 3 AM Metadata Lock: A Production Reality

You’re adding a single nullable column to a 200GB orders table. You expect a five-minute wait. Instead, the ALTER TABLE command triggers a metadata lock that freezes every incoming query. Within seconds, your connection pool hits its 5,000-session limit, and users are staring at 504 Gateway Timeouts. Your monitoring dashboard isn’t just red—it’s screaming.

MySQL schema changes on massive datasets are high-stakes operations. While MySQL 8.0 improved Online DDL support, many structural changes still require full table copies or restrictive locks. If you are handling 2,000+ writes per second, even a three-second stall can snowball into a site-wide outage. I’ve navigated these failures across MySQL, PostgreSQL, and MongoDB environments, and MySQL’s legacy handling of DDL remains one of the most common causes of engineering ‘incidents.’

Why Standard Migrations Fail

A native ALTER TABLE often creates a hidden temporary table, copies every row, and then swaps the old for the new. The database must maintain strict consistency during this transition. Even with the ‘Online’ flag, the final ‘cut-over’ phase requires an exclusive lock to rename the tables. On a busy system, that brief lock stalls everything. The resulting backlog of pending transactions can crash your application servers long before the database recovers.

Evaluating Migration Strategies

The community has built several tools to work around these locking behaviors. Each has a specific trade-off profile.

1. Native Online DDL

Introduced in MySQL 5.6, this is the ‘built-in’ way. It’s convenient but opaque. You cannot easily throttle its I/O usage, and if the process fails 90% of the way through, the rollback itself can take hours, keeping your CPU pegged the entire time.

2. pt-online-schema-change (Percona Toolkit)

This was the industry standard for a decade. It creates a shadow table and uses database triggers to sync incoming writes. However, triggers are double-edged swords. They add significant latency to every INSERT or UPDATE on your original table. In high-concurrency environments, this overhead often leads to the very deadlocks you were trying to avoid.

3. gh-ost (GitHub Online Schema Transformer)

GitHub built gh-ost to kill the trigger dependency. Instead of forcing the database to sync data via triggers, gh-ost streams changes directly from the MySQL binary logs (binlog). It acts like a replica, quietly consuming the log stream and applying it to a ghost table. This ‘triggerless’ design ensures your application performance remains flat, even during a 10-hour migration.

Is gh-ost Right for You?

The Wins

  • Zero Latency Impact: No triggers mean no added overhead on your application’s write path.
  • Total Control: You can pause the migration instantly if you see replication lag climbing above 500ms.
  • Dry-Run Verification: You can test the entire migration logic on a replica to ensure the schema is valid before touching production.
  • Clean Failure: If the gh-ost process dies, your database is unaffected. There are no orphan triggers to clean up.

The Constraints

  • Storage Overhead: You need at least 2.1x the space of the original table. A 500GB table requires over 1TB of available disk.
  • Strict Requirements: Your environment must use ROW-based binlog formatting and every table must have a Primary Key.
  • Operational Complexity: It is a separate Go binary. You need to manage permissions and connectivity outside of standard SQL.

Infrastructure Requirements

Safety first. Ensure your MySQL configuration matches these settings:

  1. Binlog Format: log_bin must be active and binlog_format must be set to ROW.
  2. The Replica Strategy: While gh-ost can run on a primary, it’s safer to point it at a replica. It reads data from the follower but executes the final cut-over on the primary.
  3. Privileges: The migration user needs SUPER, REPLICATION CLIENT, and REPLICATION SLAVE rights.
# Verify your configuration
SHOW VARIABLES LIKE 'binlog_format';
# Must return 'ROW'

The Implementation Workflow

Let’s add a user_bio column to a users table containing 50 million rows.

Step 1: Install the Binary

Grab the pre-compiled binary. It’s a self-contained Go executable.

wget https://github.com/github/gh-ost/releases/download/v1.1.6/gh-ost-binary-linux-20231207144602.tar.gz
tar -xf gh-ost-binary-linux-20231207144602.tar.gz
sudo mv gh-ost /usr/local/bin/

Step 2: The Dry Run

Skip this, and you’re asking for trouble. A dry run validates permissions, connectivity, and syntax without moving a single byte.

gh-ost \
  --user="migrator" \
  --password="secret" \
  --host="db-primary.internal" \
  --allow-on-master \
  --database="prod_db" \
  --table="users" \
  --alter="ADD COLUMN user_bio TEXT" \
  --dry-run

Step 3: Execution

Once the dry run passes, swap --dry-run for --execute. Always define a --panic-flag-file. If things go south, simply touch that file, and gh-ost will abort immediately.

gh-ost \
  --user="migrator" \
  --password="secret" \
  --host="db-primary.internal" \
  --allow-on-master \
  --database="prod_db" \
  --table="users" \
  --alter="ADD COLUMN user_bio TEXT" \
  --panic-flag-file="/tmp/ghost.panic" \
  --execute

Step 4: Live Throttling

Monitoring is key. If your other replicas start lagging because of the migration load, you can throttle the process via its Unix socket in real-time.

# Pause the copy process
echo throttle | nc -U /tmp/gh-ost.prod_db.users.sock

# Resume when the replicas catch up
echo no-throttle | nc -U /tmp/gh-ost.prod_db.users.sock

Step 5: The Cut-over

After copying the data, gh-ost prepares the swap. It uses a sophisticated two-session atomic swap to ensure no queries are lost. The transition usually completes in under 100 milliseconds.

Hard-Won Advice

Even with the best tools, production migrations require discipline:

  • Watch Your Disk: If you have 80GB free on a 150GB disk, and your table is 100GB, gh-ost will crash your database by filling the disk.
  • Automatic Throttling: Use --max-lag-millis=1000. This tells gh-ost to stop working automatically if any replica falls more than one second behind.
  • Post-Migration Cleanup: gh-ost renames the old table to something like _users_del. Keep it for 24 hours as a safety net, then drop it to reclaim space.

Schema changes don’t have to be a source of anxiety. Moving from trigger-based tools to a binlog transformer like gh-ost gives you the visibility and control needed to evolve your database without waking up the on-call engineer.

Share:
Tags: