DB-유

Using pt-online-schema-change for Zero-Downtime Alters

· mysql, percona, schema

I don't run a plain ALTER TABLE on a big table on a busy master anymore. 5.6 and later can do many alters in place, but some still rebuild the table, some block DML, and any long alter turns into hours of replication lag because the slave applies it single-threaded. pt-online-schema-change from Percona Toolkit takes a different route: it creates an empty copy of the table with the new definition, copies rows in chunks, keeps the copy in sync with triggers, and swaps the two with an atomic RENAME TABLE.

You need Percona Toolkit 3.x (yum install percona-toolkit from the Percona repo, or apt-get install percona-toolkit), a PRIMARY KEY or at least one UNIQUE index on the table for chunking, and free disk for a second copy plus its indexes. Before Toolkit 3.0.4 the table can't already have triggers; newer versions can keep them with --preserve-triggers.

Start with a dry run

--dry-run creates and drops the new table without copying anything, and prints the plan. I never skip this:

pt-online-schema-change --dry-run \
  --alter "ADD COLUMN sku_alt VARCHAR(64) NULL, ADD INDEX idx_sku_alt (sku_alt)" \
  D=shop,t=products,h=localhost,u=root,p=R00t-P@ss

Note that --alter takes the part after ALTER TABLE tablename, not the whole statement. Check the output for Not creating triggers because this is a dry run and for the chunk size estimate.

The real run, with safety limits

pt-online-schema-change --execute \
  --alter "ADD COLUMN sku_alt VARCHAR(64) NULL, ADD INDEX idx_sku_alt (sku_alt)" \
  --chunk-time 0.5 \
  --max-load "Threads_running=50" \
  --critical-load "Threads_running=200" \
  --check-slave-lag h=10.0.1.21 --max-lag 5 \
  --alter-foreign-keys-method auto \
  --progress time,30 \
  D=shop,t=products,h=localhost,u=root,p=R00t-P@ss

What each does:

  • --chunk-time 0.5 adjusts the chunk size dynamically so each copy statement takes about half a second.
  • --max-load pauses copying while Threads_running exceeds 50; --critical-load aborts the run if it exceeds 200.
  • --check-slave-lag with --max-lag pauses when the named slave falls more than 5 seconds behind. The tool otherwise discovers slaves via SHOW SLAVE HOSTS or --recursion-method, which does not always work through NAT.
  • --alter-foreign-keys-method auto handles child tables that reference products; rebuild_constraints is safer, drop_swap is faster but has a brief window where the table does not exist.
  • --progress time,30 prints progress every 30 seconds.

The tool creates _products_new, adds three triggers (pt_osc_shop_products_ins, _upd, _del) on products, copies the rows, swaps with RENAME TABLE products TO _products_old, _products_new TO products, and drops the old table. Add --no-drop-old-table if you want to keep _products_old for a quick rollback.

Watching it work

From another session:

SHOW PROCESSLIST;
SHOW TRIGGERS FROM shop LIKE 'products';
SELECT table_name, table_rows, data_length FROM information_schema.tables WHERE table_schema='shop' AND table_name LIKE '%products%';

You will see INSERT LOW_PRIORITY IGNORE INTO shop._products_new (...) SELECT ... FROM shop.products FORCE INDEX(PRIMARY) WHERE ((id >= ?)) AND ((id <= ?)) LOCK IN SHARE MODE chunks going by. If you need to stop, kill the tool with Ctrl-C; it cleans up the triggers and the new table on exit. If it is killed hard (kill -9), clean up by hand: drop the three triggers and _products_new.

After the swap

Check:

SHOW CREATE TABLE shop.products\G
SELECT COUNT(*) FROM shop.products;
SHOW TRIGGERS FROM shop LIKE 'products';   -- must be empty

Compare the row count with the value before the run, allowing for rows the application inserted in the meantime. On the slave, SHOW SLAVE STATUS\G should show lag returning to zero; the alter itself replicated as thousands of small INSERT chunks, not one long statement.

Things that stopped it on me

  • The table shop.products has triggers but --preserve-triggers was not specified.: upgrade Percona Toolkit to 3.0.4 or later and add --preserve-triggers, or drop the triggers for the duration.
  • Cannot chunk the original table shop.products: There is no good index and the table is oversized. Use --force: the table lacks a primary or unique key. Add one first; running with --force on a large table means a single-chunk copy that locks everything.
  • Error creating new table: DBD::mysql::db do failed: Duplicate entry '4711' for key 'PRIMARY' during the copy: a UNIQUE index you are adding is violated by existing data. The INSERT ... IGNORE would silently drop rows, which is why the tool checks and stops. Clean the duplicates or use --no-check-unique-key-change only if you understand the loss.
  • Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Access denied; you need (at least one of) the SUPER privilege(s): the account needs SUPER (or SYSTEM_VARIABLES_ADMIN on 8.0) to set session variables, plus PROCESS, REPLICATION CLIENT, and full DML/DDL on the database.
  • The swap fails with Lock wait timeout exceeded on RENAME TABLE: a long-running transaction holds a metadata lock. Find it in sys.schema_table_lock_waits and kill it, or set --tries swap_tables:20:2 to retry more.

My rule: use it for anything that would take more than a few minutes as a native ALTER on a master with slaves I care about. Skip it for tiny tables, tables without a unique key, and alters that 5.6+ already runs as ALGORITHM=INPLACE, LOCK=NONE when lag isn't a worry.