gh-ost: MySQL Schema Migration Tool - AI-Optimized Reference

Overview

gh-ost is GitHub's MySQL schema migration tool that uses binary log reading instead of triggers to avoid production failures. Built after pt-online-schema-change caused repeated production outages.

Critical Requirements

• MySQL Version: 5.7+ only
• Replication: Row-Based Replication (RBR) required
• Foreign Keys: Must drop before migration, recreate after
• Primary Key: Single-column PK or unique index required (no composite keys)

Key Advantages Over pt-online-schema-change

Trigger Problems Solved

• No Metadata Lock Contention: pt-osc triggers create 30-second operations that become 10-minute deadlocks
• No Transaction Overhead: pt-osc adds 40-80% performance hit on tables with 10k writes/second
• Actually Pausable: echo throttle | socat - /tmp/gh-ost.sock stops all writes instantly
• Safe Failure: Clean artifacts with no data loss vs pt-osc corruption requiring backup restore

Performance Impact

• gh-ost: 15-20% load on master during migration
• pt-osc: 40-60% load on busy tables

Critical Failure Modes

Binary Log Position Failures

Error: "Error 1236: Could not find first log file name in binary log index file"
Cause: Binary logs rotated during migration
Prevention: Set expire_logs_days ≥ 7 for large migrations
Recovery: None - restart migration from beginning

MySQL 8.0.28 Connection Killing

Error: "Connection was killed"
Cause: Long-running SELECT queries killed during operations
Solution: Set --mysql-timeout=300

Primary Key Requirements

Error: "Cannot determine table's PRIMARY KEY"
Cause: No PK or composite keys only
Solution: Add surrogate PK column before migration

Performance Benchmarks (Percona 3GB sysbench table)

Load Level	pt-osc Performance	gh-ost Performance	Winner
Idle	~2x faster	Baseline	pt-osc
Light	~2x faster	Baseline	pt-osc
Heavy (40% capacity)	12% impact, completed	Could not complete	pt-osc

Reality: gh-ost's single-threaded binlog processing hits walls on write-heavy workloads

Table Size Limits

• < 100GB: 2-4 hours, reliable
• 100GB - 1TB: 8-24 hours, plan carefully
• > 1TB: Multi-day migrations, high failure risk
• Largest Success: 5TB table in 3 days

Cloud Provider Gotchas

AWS RDS

• Requires log_bin_trust_function_creators=1
• Automated backups interfere with binlog reading
• Some configurations restrict binlog access

Google Cloud SQL

• Requires point-in-time recovery enabled
• Aggressive binlog retention (3 days vs expected 7)

Azure MySQL

• Requires specific binlog access permissions
• Documentation insufficient for setup

Manual Cleanup Required

When migrations fail, manual cleanup needed:

DROP TABLE IF EXISTS your_table_gho;  -- Ghost table
DROP TABLE IF EXISTS your_table_ghc;  -- Changelog table

Interactive Commands

• Pause: echo throttle | socat - /tmp/gh-ost.sock
• Resume: echo no-throttle | socat - /tmp/gh-ost.sock
• Status: Various status commands available via socket

When to Use gh-ost vs pt-osc

Use gh-ost When:

• Safety is priority over speed
• Need true pause/resume capability
• Working with tables that have heavy write load
• Can drop foreign keys
• Have MySQL 5.7+ with RBR

Use pt-osc When:

• Foreign keys cannot be dropped
• Maximum speed required
• Working with legacy MySQL versions
• Simple schemas without complex constraints

Common Configuration Flags

• --max-lag-millis=1500: Pause when replication lag exceeds threshold
• --chunk-size=1000: Rows copied per operation (increase for better performance)
• --exact-rowcount: Better progress tracking for large tables
• --test-on-replica: Safe testing before production run
• --serve-socket-file: Enable interactive commands

Migration Planning Checklist

Pre-Migration

1. Verify RBR enabled
2. Check binary log retention settings
3. Drop foreign keys
4. Test on replica first
5. Plan maintenance window (2-3x estimated time)

During Migration

1. Monitor replication lag
2. Watch for binlog rotation
3. Keep interactive commands ready
4. Monitor application performance

Post-Migration

1. Recreate foreign keys
2. Clean up ghost tables if failed
3. Verify data integrity
4. Update application configurations

Resource Requirements

• Time: 2-3x longer than pt-osc but safer
• Expertise: Requires understanding of MySQL replication
• Infrastructure: Replica access for binlog reading

Latest Version

v1.1.7 (December 20, 2024) with performance improvements and bug fixes

Adoption Indicators

• 12.9k GitHub stars (indicates production battle-testing)
• Used by GitHub, Shopify, and other high-scale companies
• Active development and bug fixes