Safely Alter Large MySQL Tables with Online DDL

Altering large production MySQL tables can cause downtime, lock databases, and degrade application performance if done incorrectly. This article explains how to safely execute schema changes on massive MySQL tables using native Online DDL features, alongside robust external tools like pt-online-schema-change and gh-ost. You will learn how to evaluate algorithm choices, manage database resources, and implement best practices to alter production databases without interrupting user traffic.

Understanding Native MySQL Online DDL

MySQL (specifically the InnoDB storage engine) supports native Online DDL, which allows you to run ALTER TABLE statements while concurrent DML (insert, update, delete) operations continue. To control how the alter is executed, you should explicitly define the ALGORITHM and LOCK clauses.

The ALGORITHM Clause

• INSTANT: Available in MySQL 8.0+, this algorithm modifies only the table’s metadata. It executes almost instantly regardless of table size and does not block concurrent writes.
• INPLACE: The operation is performed within the InnoDB engine. It avoids copying the entire table to a temporary file when possible, though it may rebuild the table internally. It allows concurrent reads and writes during most of the execution.
• COPY: The traditional method where MySQL creates a copy of the table, applies the changes, copies all rows, and replaces the old table. This blocks write operations and should be avoided on large production tables.

The LOCK Clause

• NONE: Permits concurrent reads and writes. If the requested operation cannot be completed without blocking writes, the statement fails immediately.
• SHARED: Permits concurrent reads but blocks writes.
• EXCLUSIVE: Blocks both reads and writes.

Safe Native Online DDL Syntax

To ensure your native alter statement does not accidentally block production traffic, explicitly state the safest options:

ALTER TABLE orders ADD COLUMN discount_code VARCHAR(20) DEFAULT NULL,
ALGORITHM=INSTANT, LOCK=NONE;

If INSTANT is not supported for your specific alteration, fallback to INPLACE:

ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at),
ALGORITHM=INPLACE, LOCK=NONE;

Limitations of Native Online DDL

While native Online DDL is highly effective, it has limitations on massive tables (e.g., hundreds of gigabytes or terabytes):

• Replication Lag: The master performs the DDL online, but replication to replicas is often serialized. Replicas may block while applying the DDL, causing massive replication lag.
• Disk Space: Operations that rebuild tables (ALGORITHM=INPLACE) require temporary disk space equivalent to the size of the table.
• Buffer Pool Pollution: Rebuilding a massive table can flush frequently accessed data out of the InnoDB buffer pool, degrading read performance.
• Undo Log and Temp Space: Concurrent DML writes during an INPLACE operation are recorded in an online DDL log. If this log exceeds innodb_online_alter_log_max_size, the alteration fails and rolls back, which can take hours.

Utilizing Third-Party Tools for Large Scale Changes

When native Online DDL poses too much risk to replication or performance, industry-standard third-party tools should be used.

pt-online-schema-change (Percona)

This tool creates a ghost copy of the target table, applies the schema changes to the empty ghost table, and creates triggers on the original table to copy concurrent writes. It then copies data from the original table to the ghost table in small, manageable chunks. Once complete, it atomically swaps the tables.

• Pros: Highly reliable, widely used, automatically throttles if replica lag or system load increases.
• Cons: The use of triggers can add write overhead to highly active tables.

gh-ost (GitHub Online Schema Migrator)

gh-ost is a triggerless online schema migration tool. Instead of triggers, it parses the MySQL binary log (binlog) in real-time to capture concurrent writes and apply them to the ghost table.

• Pros: Triggerless design avoids locking issues and performance overhead on the master. You can pause and resume the migration at any time.
• Cons: Requires row-based replication (RBR) enabled and configuration of binlog streaming permissions.

Best Practices for Production Execution

To guarantee a safe schema migration on a large production MySQL database, adhere to the following checklist:

1. Validate on Staging: Run the exact DDL statement or tool execution on a staging database populated with production-scale data to measure execution time, disk space usage, and IOPS.
2. Verify Disk Space: Ensure you have at least 1.5 to 2 times the table’s current size in free disk space before running an inplace rebuild or using third-party migration tools.
3. Monitor Replication Lag: If using replicas, monitor lag closely. Configure tools like pt-online-schema-change or gh-ost to automatically pause if replica lag exceeds a safe threshold (e.g., 10 seconds).
4. Execute During Off-Peak Hours: Even with online tools, heavy schema changes consume disk I/O and CPU. Run these operations during periods of low application traffic.
5. Configure Timeouts: Set safe session timeout variables to prevent a hung metadata lock from stalling the application if the DDL operation gets blocked at the start or end of its run.