3

I have a 3-node galera cluster with a few hundred databases servicing various clients.

I'm only using galera as an easy way to handle replication and fallover. I'm not actually using multiple masters at once.

I need to run a rather expensive ALTER on some tables in each database. Typically, I would have a script that ran an upgrade script on each database, one by one, and just turn each site off one at a time.

But with this galera cluster, any slow DDL has resulted in a complete lock on all databases, not just the one it's run against. Basically it means whenever I need to run an upgrade, everyone goes offline for the entire time it takes me to run the upgrade for everyone.

I know there is wsrep_OSU_method, but changing this to RSU has problems of it's own and I don't think it helps.

Is there just a way to disable the lock? Or at least make it a database-level lock rather than a server-level lock?

What if I were to disable my other nodes in the cluster while running the query -- would this still result in a global lock? Like I said, I don't actually use multiple masters, so in this case, having the other nodes offline for a few minutes is okay (assuming if the usual automated rejoin process happens when I brought them back).

chroder
  • 634
  • 2
  • 7
  • 16

1 Answers1

1

You're in luck! Percona makes a tool called pt-online-schema-change for just this sort of situation.

pt-online-schema-change emulates the way that MySQL alters tables internally, but it works on a copy of the table you wish to alter. This means that the original table is not locked, and clients may continue to read and change data in it.

pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. By default, it also drops the original table.

While it's made by Percona, it works on any of the major MySQL variants.

Community
  • 1
ceejayoz
  • 32,469
  • 7
  • 81
  • 105
  • 1
    This is what I ended up using. It has a few caveats (clearly listed in the docs), but nothing critical. I've just finished applying changes to some 600 databases and the process was really smooth, no surprises. – chroder Feb 08 '15 at 12:52
  • @ChristopherNadeau Awesome! It has saved my butt a number of times. – ceejayoz Feb 08 '15 at 14:35