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

Mikael HOUNDEGNON
Author: Mikael HOUNDEGNON

My name is Mikael HOUNDEGNON. I am an experienced MySQL DBA/Developer based in the greater Chicago area. You can find out more about me here. I blog here mostly about things I don’t want to forget ? most likely, MySQL Tips. My specialties : MySQL Replication (Master Slave, MultiMaster, Fail over, etc) MySQL Backups MySQL Query Optimization MySQL Performance Tuning MySQL Stored Procedures Storage Engine Tuning Do you have an interesting project idea? Or you just want to chat? Get in touch!

Leave a Reply

Your email address will not be published. Required fields are marked *