Webinar review: Zero-Downtime Schema Changes In MySQL

Yesterday I attended the Zero-Downtime Schema Changes In MySQL webinar by Baron Schwartz, Percona (do you say “attended” for something you listened to from your home office?)

I was keen to learn about possible enhancements and improvements of pt-online-schema-change over oak-online-alter-table. Here are my impressions:

The base logic of pt-online-schema-change is essentially the same as of oak-online-alter-table. You create a ghost/shadow table, create complex triggers, copy in chunks, freeze and swap. Both work on any type of PRIMARY KEY (oak-online-alter-table can work with any UNIQUE KEY, I’m not sure about pt-online-schema-change on this), be it an INTEGER, other type, or a multi column one.

However, pt-online-schema-change also adds the following:

• It supports FOREIGN KEYs (to some extent). This is something I’ve wanted to do with oak-online-alter-table but never got around to it. Foreign keys are very tricky, as Baron noted. With child-side keys, things are reasonably manageable. With parent-side this becomes a nightmare, sometimes unsolvable (when I say “unsolvable”, I mean that under the constraint of having the operation run in a non-blocking, transparent way).
• Chunk size is auto-calculated by the script. This is a cool addition. Instead of letting the user throwing out numbers like 1,000 rows per chunk, in the hope that this is neither too small nor too large, the tool monitors the time it takes a chunk to complete, then adjusts the size of next chunk accordingly. Hopefully this leads to a more optimized run, where locks are only held for very short periods, yet enough rows are being processed at a time.
• The tool looks into replicating slaves to verify they’re up to the job. If the slave lags too far, the tool slows down the work. This is an excellent feature, and again, one that I always wanted to have. Great work!

So the three bullets above are what I understand to be the major advantages of Percona’s tool over oak-online-alter-table.

Q & A

The presentation itself was very good, and Baron answered some questions. There was one question he did not answer during the webinar, nor here, and I though I may pop in and answer it. Although I can’t speak for the coders of pt-online-schema-change, I safely assume that since the logic follows that of oak-online-alter-table, the same answer applies in the case of Percona’s toolkit.

But, first, a background question (asked and answered during the webinar):

Q: What if my table already has AFTER TRIGGERs?

A: Then this can’t work out. The table must not have triggers.

Which led to the next question:

Q: Can’t the tool use BEFORE TRIGGERs instead?

Imagine a MyISAM table being altered to InnoDB (this is a major task for which my tool was built). Suppose we used a BEFORE trigger on an INSERT, but the INSERT failed. That would make the shadow table inconsistent with the original table. Which is the reason why the trigger must be an AFTER trigger.

With InnoDB this should not be an issue, since triggers and actions all play within the same transaction, so all succeed or all fail. I have this nagging feeling at the back of my head which says I’ve already had thoughts on this and have found a problem with InnoDB tables as well. I can’t put my finger on it now, so no comment on this one at this stage.