Vitess Online DDL: Declarative Schema Migration with gh‑ost

Key Takeaway

Vitess’s ApplySchema command lets you submit an ALTER TABLE once and have Vitess execute it safely across every shard, using a strategy like gh‑ost. The migration runs in a throttled, cancellable, and idempotent way, but it is not atomic across shards and not all DDL statements are supported online.

How Vitess Online DDL Works

When you invoke vtctldclient ApplySchema with a strategy flag, Vitess creates a shadow table on each tablet, copies rows in configurable chunks, streams binlog changes to the shadow, and finally swaps the shadow in for the original. The process is:

• Shadow creation: CREATE TABLE new_customer LIKE customer on every tablet.
• Data copy: Rows are copied in batches, respecting --max-load or similar throttling flags.
• Delta replay: Binlog events are applied to the shadow to keep it in sync with the live table.
• Cut‑over: Once copy completes, the shadow replaces the original via a RENAME TABLE pair.

Vitess tracks each migration in the schema_migrations table, assigning a UUID that ensures idempotency. The migration lifecycle states—queued, ready, running, complete, failed—are exposed via vtctldclient OnlineDDL.

Running an Online DDL Migration

Below is a minimal, safe example for adding an email column to the customer table in the commerce keyspace. Replace <YOUR_KEYSPACE> with your keyspace name and run on the vtctld host.

# Submit the migration with gh‑ost strategy and a load threshold
vtctldclient ApplySchema \
  --ddl-strategy="gh-ost --max-load=Threads_running=100" \
  --sql="ALTER TABLE customer ADD COLUMN email VARCHAR(255)" \
  commerce

Key points:

• --ddl-strategy accepts a strategy name and optional sub‑flags. Here we set a maximum of 100 running threads to throttle the copy.
• The command is run as a user with root MySQL privileges on vtctld.
• Vitess returns a UUID; keep it if you need to query status or cancel.

Checking Progress

# Replace <UUID> with the one returned above
vtctldclient OnlineDDL commerce show <UUID>

The output will contain a state field. A typical progression looks like:

state: queued
state: ready
state: running
state: complete

Canceling or Retrying

If you need to stop a migration, use:

vtctldclient OnlineDDL commerce cancel <UUID>

To retry (e.g., after fixing a flag), submit the same command again; Vitess will deduplicate the UUID and resume from the last state.

Monitoring and Handling Errors

Vitess automatically backs off when replica lag or CPU load exceeds the thresholds you set. You can verify this by inspecting the tablet_heartbeat metrics in Prometheus or the replica_lag column in tablet_stats. A sudden spike in Threads_running will trigger the --max-load throttle, slowing the copy.

Common error messages:

• DDL not supported for this statement type – the statement cannot be performed online (e.g., DROP COLUMN without REVERT support).
• Shard migration failed – often due to insufficient disk for the shadow table; check tablet_datadir size.

Limitations and Common Mistakes

• Non‑atomic across shards: The migration completes one shard at a time. If your application assumes the new column exists everywhere, it may encounter Unknown column errors mid‑migration. Wrap your code in a feature flag or use a versioned schema approach.
• Unsupported DDL: Statements like DROP COLUMN or ALTER TABLE ... MODIFY COLUMN may not be online‑ready. Use the mysql strategy to run them directly, but note that this blocks the tablet and offers no throttling.
• Disk capacity: The shadow table duplicates the entire table. Ensure each tablet has enough free space, especially for large tables.
• Version mismatch: Strategy flags and supported statements differ between Vitess releases. Run vtctldclient ApplySchema --help and check vtctld --version to verify compatibility.
• Revertability: Vitess can revert additive changes (e.g., ADD COLUMN) but not destructive ones. Plan migrations carefully.

Best Practices

1. Run a small test migration in a staging keyspace before production.
2. Verify schema_migrations entries after each migration to ensure idempotency.
3. Set realistic --max-load values; monitor Threads_running and Replica_lag during the copy.
4. Use gh‑ost as the default strategy; it supports pause, throttle, and test‑cut‑over.
5. Document migration steps in your deployment pipeline and include rollback procedures for destructive changes.

Conclusion

Vitess’s Online DDL provides a powerful, declarative way to perform schema changes on sharded MySQL clusters with minimal disruption. By understanding the migration lifecycle, leveraging the gh‑ost strategy, and being aware of its limits, you can safely evolve your database schema in production.