Using gh-ost for Online Schema Changes in MySQL: Introduction, Usage, Parameters, and Comparison with pt-osc

Author Introduction

Yang Qilong (nickname "Bei Zai Nan Fang"), a DBA with 7 years of experience, currently works at Hangzhou Youzan Technology as a DBA, focusing on database architecture design and operation platform development, and is proficient in performance tuning and fault diagnosis.

1. Introduction

For MySQL DBAs, altering large tables (greater than 10 GB) is painful because many DDL statements lock the table and affect business continuity. The common solution is Percona's pt‑osc, but this article focuses on the open‑source Go‑based tool gh‑ost, whose working principle will be covered in a separate article.

2. Usage

2.1 gh‑ost Overview

gh‑ost acts as a disguised replica: it reads the binlog from the master or replica, filters it, and re‑applies the changes to a ghost table on the master, effectively performing incremental operations on the ghost table.

Workflow:

gh‑ost connects to the master and creates a ghost table based on the ALTER statement.

It connects as a replica (or directly to the master depending on parameters), copies existing data to the ghost table while pulling binlog events from the replica and applying them back to the master.

After all data is synchronized, a cut‑over swaps the ghost table with the original table. The cut‑over locks the source table, waits for binlog replay to finish, then replaces the ghost table.

gh‑ost performs many pre‑checks such as binlog_format, primary/unique keys, and foreign keys.

2.2 gh‑ost Operation Modes

a. Connect to replica, migrate on master

gh‑ost checks replica status, finds the master, and performs migration on the master. The replica must have binlog enabled ( log_bin , log_slave_updates ) and binlog_format=ROW .

b. Connect directly to master

Using --allow-on-master , gh‑ost runs on the master as a slave, requiring the master’s binlog to be in ROW mode.

c. Migrate/Test on replica

All operations run on the replica, leaving the master untouched. This mode allows throttling and safe testing before the final cut‑over.

--migrate-on-replica runs migration on the replica; --test-on-replica performs a test migration that stops before the final switch.

3. Practical Example

Repository: https://github.com/github/gh-ost

3.1 Important Parameters

Key parameters include:

gh-ost --help

-allow-master-master : allow running in dual‑master replication.

-allow-nullable-unique-key : permit NULL in unique keys (use with caution).

-allow-on-master : run directly on the master.

-alter : the DDL statement to apply.

-assume-master-host : specify a master host (useful in master‑master setups).

-chunk-size : rows processed per iteration (default 1000, range 100‑100000).

-critical-load / -critical-load-interval-millis : define load thresholds that trigger abort.

-cut-over : choose cut‑over type (atomic or two‑step).

-max-lag-millis : max replication lag before throttling.

-ok-to-drop-table : delete the old table after migration.

-timestamp-old-table : rename old table with a timestamp suffix.

… (many other flags for retries, batch size, heartbeat, etc.)

3.2 Executing DDL

Example command to rebuild table b with InnoDB engine:

/opt/gh-ost/bin/gh-ost \
  --max-load=Threads_running=20 \
  --critical-load=Threads_running=50 \
  --critical-load-interval-millis=5000 \
  --chunk-size=1000 \
  --user="root" \
  --password="" \
  --host='127.0.0.1' \
  --port=3316 \
  --database="test" \
  --table="b" \
  --verbose \
  --alter="engine=innodb" \
  --assume-rbr \
  --cut-over=default \
  --cut-over-lock-timeout-seconds=1 \
  --dml-batch-size=10 \
  --allow-on-master \
  --concurrent-rowcount \
  --default-retries=10 \
  --heartbeat-interval-millis=2000 \
  --panic-flag-file=/tmp/ghost.panic.flag \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
  --timestamp-old-table \
  --execute 2>&1 | tee /tmp/rebuild_t1.log

During execution gh‑ost creates two intermediate tables:

_b_ghc : records the migration process.

_b_gho : the ghost table that will replace the original.

3.3 gh‑ost Features

gh‑ost is lightweight, pause‑able, dynamically controllable, auditable, and testable. Control is achieved via special flag files or a Unix socket.

Pause/Resume

echo throttle | socat - /tmp/gh-ost.test.b.sock   # enable throttling

echo no-throttle | socat - /tmp/gh-ost.test.b.sock   # disable throttling

Changing runtime parameters (e.g., chunk‑size, max‑lag, max‑load) is done similarly:

echo chunk-size=1024 | socat - /tmp/gh-ost.test.b.sock

Terminate

Creating the panic flag file stops the process immediately:

touch /tmp/ghost.panic.flag

Log output:

2019-08-31 22:50:52.701 FATAL Found panic-file /tmp/ghost.panic.flag. Aborting without cleanup

Note that stopping gh‑ost leaves the intermediate tables ( xxx_ghc , xxx_gho ) and socket files, which must be cleaned before a new run.

4. Comparison with pt‑osc

Both tools have strengths: pt‑osc may perform better under high write concurrency, while gh‑ost offers greater flexibility and dynamic parameter adjustment.

Further reading:

Comparison Blog 1

Comparison Blog 2

5. Summary

gh‑ost is an excellent open‑source solution for online schema changes on large MySQL tables, providing minimal impact on production workloads and extensive control features. It is highly recommended for DBAs seeking a reliable DDL migration tool.

References:

Reference Article 1

Reference Article 2