4

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?

1
  • 2
    pt-online-schema-change will be revamped Real Soon Now, FWIW.
    – user4939
    Jan 31, 2012 at 19:51

1 Answer 1

5

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...

  1. 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)
  2. Point your application at db2, thus making it the active server
  3. Perform schema change on db1 with binary logging disabled
  4. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.