Online DDL in Practice

MySQL Online DDL and Schema Changes Guide

Schema changes on production tables require careful planning. This guide covers Online DDL algorithms, strategies for large tables, and zero-downtime approaches.

1. Online DDL Algorithms (MySQL 8.0+)

1.1 Three Algorithm Types

ALGORITHM=INSTANT (MySQL 8.0.12+)
─────────────────────────────────
Characteristics:
├─ No table copy needed
├─ Metadata-only changes
├─ Sub-millisecond execution
├─ No downtime
└─ No disk I/O

Supported operations:
✅ Add columns to end (InnoDB)
✅ Change column default values
✅ Change column visibility
✅ Drop columns (MySQL 8.0.14+)
✅ Rename columns
✅ Change ENUM/SET values

Not supported:
❌ Modify column types (VARCHAR 50 → 100)
❌ Reorder columns
❌ Change NOT NULL to nullable
❌ Add column before existing

Example:
ALTER TABLE users ADD COLUMN last_login DATETIME DEFAULT CURRENT_TIMESTAMP,
ALGORITHM=INSTANT;  -- Instant completion

-- Verify instant execution
SHOW ENGINE INNODB STATUS\G | grep -i "INSTANT"


ALGORITHM=INPLACE (MySQL 5.6+)
──────────────────────────────
Characteristics:
├─ Rebuild table in-place (no copy)
├─ Can take minutes/hours for large tables
├─ Readers/writers allowed during most of operation
├─ Reduced downtime vs COPY
└─ Some locks during prepare/finalize phases

Supported operations:
✅ Add index
✅ Drop index
✅ Change column type (sometimes)
✅ Rename column
✅ Change NOT NULL
✅ Reorder columns

Example:
ALTER TABLE orders ADD INDEX idx_user_id (user_id),
ALGORITHM=INPLACE, LOCK=SHARED;

-- Monitors progress
SHOW PROCESSLIST;  -- Status shows "Altering table"


ALGORITHM=COPY (Original)
─────────────────────────
Characteristics:
├─ Creates full copy of table
├─ Read-only table during operation
├─ High disk I/O
├─ Downtime required
└─ Last resort

Use only when:
❌ Operation not supported by INSTANT/INPLACE
❌ No other option available

Example (avoid in production):
ALTER TABLE users CHANGE COLUMN email email VARCHAR(255),
ALGORITHM=COPY;
-- Table locked for reads and writes


LOCK LEVELS:
───────────
LOCK=NONE: No locks, best for concurrency
LOCK=SHARED: Read allowed, writes blocked
LOCK=EXCLUSIVE: Table completely locked

1.2 Strategy for Large Tables

LARGE TABLE CHANGE STRATEGY (1B+ rows):

Problem:
├─ Direct ALTER can take 8+ hours
├─ Table locked during operation (if not careful)
├─ May cause outage

Solution 1: Online ALTER with timing
─────────────────────────────────────

-- Off-peak hours (2 AM)
SET SESSION innodb_lock_wait_timeout = 600;  (10 minutes)
SET SESSION innodb_ddl_buffer_size = 1000000;  (1GB for DDL buffer)

ALTER TABLE orders
ADD INDEX idx_status (status),
ALGORITHM=INPLACE,
LOCK=SHARED;  -- Readers allowed

-- Monitor progress
SHOW PROCESSLIST;  -- Shows percentage complete

-- Verification
SELECT COUNT(*) FROM orders;  -- Verify data intact

Solution 2: Ghost table approach (pt-online-schema-change)
──────────────────────────────────────────────────────────

1. Create new table with schema changes
CREATE TABLE orders_new LIKE orders;

2. Copy data in chunks (non-blocking)
INSERT INTO orders_new
SELECT * FROM orders WHERE id BETWEEN 1 AND 1000000;

(repeat for each 1M rows)

3. Copy remaining rows, apply binary logs
-- Percona Toolkit handles this

4. Atomic switch
RENAME TABLE orders TO orders_old, orders_new TO orders;

5. Drop old table after verification
DROP TABLE orders_old;

Advantage: Readers see no downtime
Disadvantage: Requires disk space for copy

Solution 3: Canary deployment
──────────────────────────────

For critical changes:

1. Stage 1 (10% replicas): Apply schema change
2. Monitor for 24 hours
3. Stage 2 (50% replicas): Apply to more replicas
4. Monitor for 48 hours
5. Stage 3 (100%): Apply to all
6. Master: Apply during maintenance window

Timeline:
Day 1: Start on 1 replica (10%)
Day 2: Expand to 5 replicas (50%)
Day 4: Expand to 10 replicas (100%)
Day 5: Apply to master (low-traffic hour)

COMMON DDL CHANGES:

1. Add column (use INSTANT if possible)
ALTER TABLE users
ADD COLUMN phone_number VARCHAR(20),
ALGORITHM=INSTANT;

2. Add index (INPLACE)
ALTER TABLE orders
ADD INDEX idx_user_created (user_id, created_at),
ALGORITHM=INPLACE,
LOCK=SHARED;

3. Change column type (INPLACE if compatible)
ALTER TABLE users
CHANGE COLUMN age age INT UNSIGNED,
ALGORITHM=INPLACE;

4. Rename column (INSTANT or INPLACE)
ALTER TABLE users
RENAME COLUMN user_email TO email,
ALGORITHM=INSTANT;

5. Drop column (INSTANT)
ALTER TABLE users
DROP COLUMN deprecated_field,
ALGORITHM=INSTANT;

Testing procedure:
1. Test on staging (same size as production)
2. Estimate time: (rows × milliseconds per row)
3. Verify algorithm used: SHOW ENGINE INNODB STATUS
4. Confirm no locks: SELECT * FROM data_locks
5. Apply during low-traffic window

2. Rollback and Verification

ROLLBACK PROCEDURE:

If ALTER fails or needs reversal:

Option 1: Reverse ALTER (if possible)
ALTER TABLE users
CHANGE COLUMN new_field old_field VARCHAR(100),
ALGORITHM=INPLACE;

Option 2: Restore from backup
-- If change was catastrophic

Step 1: Full backup available? (yes/no)
├─ Yes: Restore from point-in-time
└─ No: Manually revert via reverse ALTER

Step 2: If reverse ALTER needed
-- Keep track of DDL statements
SHOW CREATE TABLE users;  -- Before DDL

-- Prepare reverse DDL
ALTER TABLE users DROP COLUMN phone_number;

Step 3: Apply reverse DDL
-- During maintenance window
-- Minimal downtime

VERIFICATION CHECKLIST:

After DDL change:

[ ] Row count unchanged
[ ] Index exists and is used
[ ] Data integrity (no NULLs where unexpected)
[ ] No performance regressions
[ ] Replication lag normal
[ ] Application still works
[ ] Backup works with new schema

Verification queries:

-- Check table size
SELECT table_name, data_length FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='users';

-- Verify index
EXPLAIN SELECT * FROM users WHERE status='active'\G

-- Check column definition
DESCRIBE users;

-- Verify data
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE new_column IS NULL;

3. Summary

Online DDL allows schema changes with minimal downtime when planned correctly. Always prefer ALGORITHM=INSTANT for supported operations, use ALGORITHM=INPLACE with LOCK=SHARED for index changes, and resort to the ghost table approach or pt-online-schema-change for complex changes on very large tables. Test every DDL change on staging before applying to production.