Apr 4, 2015

Modifying Your MySQL Structure w/o Downtime

Did you try to add a new index to your MySQL huge table and suffered from a downtime?
If the answer is positive, you should introduce yourself PT-ONLINE-SCHEMA-CHANGE.

How the Magic is Done?
Actually Percona imitates MySQL behavior with a little tweak.
When modifying a table structure MySQL copies the original table structure, modifies it, copies the data and finally renames the table.
The only problem w/ this behavior that it locks the original table...

Percona is doing the same, but instead of locking the original table, it reviews the latest changes and implements them on the new table. That way the original table still serves the users, and changes replacement is done in a single atomic process.

Percona Toolkit Installation
Download the Percona toolkit and install it (the following is relevant for Ubuntu):
> wget http://www.percona.com/downloads/percona-toolkit/2.2.13/deb/percona-toolkit_2.2.13_all.deb
> sudo dpkg -i percona-toolkit_2.2.13_all.deb

Making a Change
Just call the tool with permissions, database name (D flag), table name (t flag), command to execute (--alter flag) and finally use the execute flag to implement the changes.
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor -uuser -p"password" --execute

Things to Notice
  1. You must have a primary key on the table
  2. If you want only to verify the process before replacing the tables themselves, use --dry-run instead of execute (or just drop this parameter).

Bottom Line
Modifying your database will cause performance degradation, but it should not result in a downtime.

Keep Performing,
Moshe Kaplan


Intense Debate Comments

Ratings and Recommendations