pt-online-schema-change: AI-Optimized Technical Reference

Tool Overview

Function: MySQL schema changes without table locks
Source: Percona Toolkit
Current Version: 3.7.0 (December 2024)
MySQL Support: 5.7-8.4, MariaDB 10.x

Critical Implementation Requirements

Prerequisites (FAILURE CONDITIONS)

• Primary Key Required: Tool fails without primary key - DELETE trigger dependency
• Disk Space: Requires 3x table size free space (official docs claim 2x - insufficient)
• MySQL Version: Minimum 5.0.2+, full MySQL 8.4 support requires Toolkit 3.7.0+

Resource Requirements by Table Size

Table Size	Time Estimate	Real-World Range
1GB	30 minutes	30 minutes - 2 hours
10GB	2 hours	2-8 hours
100GB	8 hours	8-24 hours
500GB+	Days	Multiple days

Critical Warning: Last 10% often takes as long as first 90% due to lock contention

Operational Process

How It Works

1. Shadow Table Creation: Empty copy with schema changes applied
2. Chunked Data Copy: 1,000 rows per batch (configurable), pauses between batches
3. Trigger Synchronization: 3 triggers capture writes during copy process
4. Atomic Swap: RENAME TABLE operation (milliseconds, not hours)

vs Standard ALTER TABLE

• Standard ALTER: Locks entire table for duration (hours for large tables)
• pt-osc: No table locks, controlled resource usage, production-safe

Critical Failure Scenarios

Disk Space Exhaustion

• Symptom: Tool crashes at ~95% completion when logs fill up
• Prevention: Monitor with df -h, ensure 3x table size free
• Impact: Production downtime, emergency disk space procurement

Lock Wait Timeouts

• Symptom: DBD::mysql::db do failed: Lock wait timeout exceeded
• Cause: Long-running queries blocking tool
• Solution: Kill blocking queries via SHOW PROCESSLIST
• Prevention: Run during low-traffic periods (2-6 AM)

Progress Reporting Deception

• Reality: Progress bar lies consistently
• 99% Status: Can indicate 2-6 hours remaining
• Cause: Lock contention in final chunks
• Impact: Inaccurate completion estimates

Configuration That Works in Production

Basic Command Structure

pt-online-schema-change \
  --alter "ADD COLUMN email VARCHAR(255)" \
  --execute \
  --max-lag=5s \
  --max-load=Threads_running=50 \
  --critical-load=Threads_running=100 \
  --chunk-size=1000 \
  --sleep=0.1 \
  D=yourdb,t=yourtable

Critical Parameters

• --max-lag=5s: Prevents replica lag disasters
• --max-load: Prevents CPU overload (Threads_running=50 recommended)
• --critical-load: Emergency brake (Threads_running=100)
• --chunk-size=1000: Balance between speed and resource usage
• --sleep=0.1: Pause between chunks (essential for production)

AWS RDS Specific

• Required: --recursion-method=none
• Reason: RDS networking breaks replica discovery
• Impact: Tool hangs indefinitely without this flag

Foreign Key Handling (Major Pain Point)

Available Methods (All Problematic)

Method	Behavior	Risk Level	Use Case
auto	Tool decides	High	Never use
drop_swap	Disables FK checks	Medium	Most reliable
rebuild_constraints	Rebuilds FKs	High	Often fails

Recommendation: Use drop_swap for production, test thoroughly

Comparison with Alternatives

pt-osc vs gh-ost vs Standard ALTER

Aspect	pt-osc	gh-ost	Standard ALTER
Table Locking	None	None	Complete lock
Trigger Dependency	Yes (3 triggers)	No (binlog)	No
Foreign Key Support	Problematic	None	Full
Production Impact	Medium	Low	High
Failure Recovery	Partial	Good	None
Learning Curve	Medium	Steep	None

Decision Criteria:

• High write traffic: gh-ost preferred
• Foreign keys required: pt-osc only option
• Simple schemas: pt-osc sufficient
• Development/testing: Standard ALTER acceptable

Production Deployment Strategy

Pre-Execution Checklist

1. Verify primary key exists: SHOW INDEX FROM table WHERE Key_name = 'PRIMARY'
2. Check disk space: df -h (need 3x table size)
3. Test with --dry-run (limited reliability)
4. Schedule during low-traffic window
5. Alert team about potential replication lag

Monitoring During Execution

• Process List: SHOW PROCESSLIST for blocking queries
• Replication Lag: Monitor all replicas
• Disk Usage: iostat or df monitoring
• CPU Load: Watch Threads_running metric

Rollback Procedure

-- Emergency rollback (requires downtime)
RENAME TABLE users TO users_broken, users_old TO users;

Warning: No clean rollback method exists

Common Production Failures

Replica Discovery Hangs

• Environment: AWS RDS, complex networking
• Solution: --recursion-method=none
• Impact: Tool hangs indefinitely

Trigger Performance Degradation

• Cause: Heavy write traffic during operation
• Symptom: 30+ second replication lag spikes
• Mitigation: Run during minimal write periods

Out of Space at 95%

• Cause: Insufficient disk monitoring
• Impact: Emergency weekend troubleshooting
• Prevention: 3x space requirement, continuous monitoring

Testing and Validation

Post-Completion Verification

-- Row count validation
SELECT COUNT(*) FROM old_table_name;
SELECT COUNT(*) FROM new_table_name;

-- Data integrity check
pt-table-checksum --databases=yourdb --tables=yourtable

Dry-Run Limitations

Dry-run does NOT test:

• Actual disk space requirements
• Production lock contention
• Foreign key constraint issues
• Real-world performance impact

Resource and Time Investment

Human Resources Required

• Database Administrator: Schema change planning and execution
• Application Team: Coordination for low-traffic windows
• Monitoring Team: Extended oversight during operation
• On-call Support: 24-hour availability for failures

Infrastructure Costs

• Disk Space: 3x table size temporarily
• CPU Overhead: Trigger execution during high-write periods
• Network: Increased replica traffic
• Time: 2-3x initial estimates for large tables

Critical Warnings

What Official Documentation Omits

• Disk space requirements underestimated by 50%
• Progress reporting completely unreliable
• Foreign key handling fundamentally broken
• Dry-run testing insufficient for production validation

Breaking Points

• Table Size: 500GB+ requires multi-day operations
• Write Traffic: Heavy writes cause exponential slowdown
• Foreign Keys: Complex FK relationships often fail
• Disk Space: 95% utilization triggers failures

Hidden Costs

• Extended Monitoring: Manual oversight for hours/days
• Failed Attempts: Debugging time for failed operations
• Emergency Response: Weekend/night troubleshooting
• Risk Management: Backup restoration procedures

Decision Framework

Use pt-online-schema-change When:

• Table size > 1GB
• Production uptime requirements > 99%
• Foreign keys present (no alternatives)
• Standard ALTER would cause unacceptable downtime

Consider Alternatives When:

• Tables < 1GB (standard ALTER acceptable)
• No foreign keys (gh-ost viable)
• Vitess already deployed
• Complex FK relationships (manual process may be safer)

Avoid When:

• Insufficient disk space (< 3x table size)
• No primary key (add PK first)
• Critical production window (insufficient testing)
• Multiple concurrent schema changes needed