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_newof the table
- alter empty table
- add triggers on insert, update & delete to sync table
test_tableduring the structure change, so any writes against the original table
test_tablewill be replicated into the new table
test_table_newfor data consistency
- and then copying rows from the original table
test_tableinto the new table
test_table_newby performing an atomic rename
On a table with 38 Millions of rows.
sudo pt-online-schema-change --ask-pass --user mhoundegnon --execute --alter " ADD INDEX CUSTOMER_ID
(CUSTOMER_ID ASC); " D='segmentation', t='responsys_sent'
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