MySQL 24x7 - InnoDB ALTER TABLE blocks (TABLE LOCK)

Question

Hello,

we are trying to minimize (maintenance) downtimes of our mysql based application.

It seems that InnoDB hotbackup will give us the possibility to do regular backups without stopping the server; Master/Slave replication will give us failover capabilities (loosing a few seconds of data due to replication lag is not great, but not a showstopper also).

So far for backup and unexpected downtimes. Now to expected downtimes -

As far as I understand from reading online documentation and books an ALTER TABLE on an InnoDB table will require a TABLE LOCK thus blocking all reads and writes to this table. Effectively this will mean downtime to the application. Some large tables may take hours to be updated.

Are there any known workarounds to this? The perfect workaroudn would be of course a non-blocking ALTER TABLE. But anything to make ALTER TABLE faster is also interesting.

Thanks in advance!

PS - commercial (non-free) tools would be ok also, free solutions are of course also welcome

Answer 1

The best workaround would be to not alter your tables.

The only time a schema change should be required is if you're adding functionality, or somehow forgot an index.

If you're adding functionality, you'll likely have downtime anyway, to stage your production server.

If you forgot an index, then the database is likely slow anyway, so your users shouldn't mind downtime to fix the performance issue. You should run all you queries through an EXPLAIN to make sure you have the proper indexes declared already.

If you're afraid that you'll be altering tables frequently you might want to re-examine your schema.

Answer 2

Since you have replication setup, it is normally possible to do some trickery with ALTER TABLE on the slave, let the slave catchup after it is done, swap roles, and then ALTER on the former master. This doesn't work for all ALTER TABLE commands, but it can handle the majority of them.

There is also a third-party tool at here, but I'm not sure how commonly it is used, how well it works, etc...