Deep Dive into pt-osc: Working Principles, Common Pitfalls, and Practical Recommendations

Background

The author, a senior DBA, has used pt-osc for about eight years and now revisits its internal workflow to better understand the tool’s mechanisms and address several unanswered questions.

Problem List

Why are triggers designed with specific INSERT , UPDATE , and DELETE statements?

Why does the workload not drop when a migration job is paused?

What causes deadlocks during pt-osc operations?

Is the final rename‑and‑drop step atomic, and could it lead to inconsistencies?

Workflow Overview

pt-osc creates a new table identical to the original, applies the ALTER on the new table, creates triggers to sync DML from the original to the new table, copies the full data, updates statistics, renames tables, and finally drops the old table and triggers.

Trigger Design Analysis

The INSERT trigger uses REPLACE instead of INSERT to avoid duplicate‑key conflicts that would cause data inconsistency when the source uses REPLACE . The UPDATE trigger first DELETE then REPLACE to correctly handle primary‑key or unique‑key changes; using a plain UPDATE could lose rows.

Testing Scenarios

Various test tables ( t1 and _t1_new ) are created, populated, and manipulated with different DML statements (INSERT, UPDATE, DELETE, INSERT ON DUPLICATE KEY). The results demonstrate how certain trigger implementations preserve or break data consistency, especially when the incremental copy is mid‑range.

Full‑Copy Phase

Data is copied using a low‑priority INSERT IGNORE INTO … SELECT … LOCK IN SHARE MODE statement, chunked by primary‑key size. The article notes that LOW_PRIORITY only affects table‑level locking engines and has no effect on InnoDB.

Deadlock Analysis

When an UPDATE transaction holds an exclusive lock on the original table while the copy thread holds an auto‑increment lock on the new table, the UPDATE trigger’s REPLACE can wait for the same lock, forming a deadlock. Reducing chunk‑size lowers the probability but cannot eliminate it.

Rename and Trigger Migration

The RENAME TABLE operation moves the original table to a backup name and swaps the new table into place. MySQL automatically moves the associated triggers to the renamed table, preventing DML failures after the swap.

Cleanup

After the rename, the old table and its triggers are dropped, and ANALYZE TABLE is run to refresh statistics, which helps the optimizer choose correct execution plans.

Summary of Drawbacks

Potential deadlocks due to trigger‑generated traffic.

Trigger‑based DML doubles the load on the source table.

Pause functionality only stops the full‑copy phase, not trigger‑driven incremental sync.

Multiple metadata‑lock acquisitions (trigger creation/deletion, stats update, rename) can block reads/writes.

Practical Recommendations & Cases

Use pt-osc when the migration host is under high load and tools like gh‑ost would throttle.

Prefer pt-osc for scheduled migrations that may be paused for long periods, as gh‑ost would keep accumulating binlog events.

Avoid pt-osc for adding unique indexes on tables with duplicate data; use gh‑ost with a hook instead.

References

pt‑osc documentation

gh‑ost repository

MySQL INSERT, ANALYZE TABLE, and RENAME TABLE manuals