MySQL 5.7 DDL vs GH-OST: A Comprehensive Comparison of Online Schema Change Tools

This article provides an in-depth comparison between MySQL 5.7 native DDL operations and GH-OST (GitHub's Online Schema Change tool), analyzing their differences in execution efficiency, space usage, lock blocking, binlog generation, and replication lag.

Background: DDL operations in MySQL can block DML write operations, cause master-slave replication delays, and lack controllable execution speed. GH-OST offers a reliable online schema change solution with zero downtime, low latency, automation, and reversibility.

MySQL 5.7 DDL Methods:

1. Copy: Server layer creates a temporary table, acquires MDL lock (blocks writes, allows reads), copies data row by row, then renames the temporary table to replace the source table.

2. Inplace - Rebuild Table: Creates new table structure in InnoDB layer, rebuilds primary and secondary indexes based on source data and incremental logs. Includes Prepare, Execute, and Commit phases with MDL lock management.

3. Inplace - Build Index: Similar to rebuild but only creates new indexes without rebuilding the entire table.

4. Only Modify Metadata: Modifies only .frm files and data dictionary without copying table data.

GH-OST Workflow: Creates a shadow table based on DDL statement, copies data from source table using SQL INSERT IGNORE, applies incremental binlog through GH-ost as a proxy, then renames the shadow table to complete the change.

Comparison Results:

• Execution Time: only modify metadata < build-index < rebuild < copy < GH-OST

• Space Usage: only modify metadata < build-index < rebuild = copy < GH-OST (GH-OST requires ~2x space plus significant binlog)

• Lock Blocking: only modify metadata = GH-OST < build-index = rebuild < copy (blocks writes during entire DDL)

• Binlog Generation: MySQL DDL < GH-OST (GH-OST generates extensive row-format binlogs during data copy and incremental apply)

• Replication Lag: GH-OST < MySQL DDL (GH-OST's shadow table changes are replicated in real-time, while MySQL DDL causes slave to wait until DDL replay completes)

Recommendations: Use GH-OST when unable to assess DDL blocking risks, when online schema changes with minimal lock blocking and replication lag are needed. Use native MySQL DDL for metadata-only changes or when lock blocking and replication lag are acceptable.