Alter large MySQL table without blocking reads or writes with pt-online-schema-change

 

pt-online-schema-change is a binary part of Percona-toolkit packages by Percona.

It’s one of the most stable and efficient, tools for performing large MySQL table structure change in live production database, minimizing DB downtime without blocking reads and writes while performance your alter in back-end.

 

pt-online-schema-change works by :

• creating an empty copy table test_table_new of the table test_table to alter,
• alter empty table test_table_new as desired,
• add triggers on insert, update & delete to sync table test_table_new  with  test_table during the structure change, so any writes against the original table test_table will be replicated into the new table test_table_new for data consistency
• and then copying rows from the original table test_table into the new table  test_table_new,
• swap test_table with test_table_new by performing an atomic rename

Example usage

On a table with 38 Millions of rows.

 

After 1H 20 minutes the structure change is done.

Notice: it may take a while until the tool starts showing progress, as querying information_schema takes a long time. Use show processlist to see what it’s doing.

More details about each argument here