mysql change schema on the fly Recently I faced the problem that my Db became very huge and I cannot allow to myself downtime every time when I want to change the schema.

This caused me to start looking for solution that do schema changing online.

1) pt-online-schema-change. Very attractive project, but honestly I was disappointed. Having downloaded the newest version, I had to change the code in order to get the script working. At the beggining script fails with error messages. All of them I found on the bug report of Percona. After patching script did it's job, hopefully well.

2) oak-online-alter-table. Also very interesting project, facebook mentioned it as good product  on Online Schema Change for MySQL. Tomorrow I gonna try it.

If you are more experienced, please share with us your opinion. What's the best and most reliable product?
My first quesiton is, what is "very huge?"  Depending on the answer to that question, there may be other ways to optimize your migrations to reduce the time that they take.

Second question is, how often are you making schema changes on your live application?

If the online schema change tools don't work for you, you could try this from another angle.  Use master/master replication in active/passive mode to minimize your downtime.

The following assumes that you have db1 and db2 in an active/passive master/master configuration...


Perform the schema change on the db2 with binary logging disabled (SET SQL_LOG_BIN=0 in the session before you perform the ALTER TABLE)
Point your application at db2, thus making it the active server
Perform schema change on db1 with binary logging disabled
optionally, switch back to db1


For this to work, your schema migrations have to be backwards compatible, which is likely the case anyhow in most applications.  This isn't zero downtime, but it is close.  Realistically, most applications can tolerate a small amount of downtime periodically for maintenance.  True zero downtime is a very expensive luxury.

To potentially speed up your table alters, look into using Percona Server and the expand_fast_index_creation setting.

Also, another option for online schema changes is Facebook's OSC.