Altering a column length on a large Innodb table with minimal downtime

Question

I need to change the the length of a column in a MySQL innodb table with 164M rows. Here's the script I want to run: ALTER TABLE SESSION_DECISION CHANGE COLUMN NAME NAME VARCHAR(255);

When I tried running the alter query, the database churned for an hour and didn't complete the task. Users accessing the system (who needed to write to the table) were locked out. This is a live system so I ended up ctrl-c'ing the request to let users access the site.

(this was in the middle of the night-- usage was minimal but I was getting nervous).

I'm on MySQL 5.0.77.

Any suggestions as to how I can change the column with a minimum of downtime?

Answer 1

Your best option is to setup a slave database using mysql replication. Assuming your schema change is additive, then you can issue the update on that replica, which will be blocked for the duration of the update. Once that is done, and check your slave has caught up with all the updates then declare a small downtime to promote your slave database to the master.

You can find instructions on setting up mysql replication here.

Answer 2

If your data is strictly inserted (and not updated), you could probably do something like:

• create a new table with the desired definition.
• copy old data to the new table (being sure to specify nolock)
• shut down access to the old table
• do an incremental update from old to new, just to catch anything that was inserted while you did the copy.
• rename the old table out of the way and the new table into place
• restore access

Answer 3

Of course, it takes long. You should understand, that changing the column datatype, you change the underlying data structure, so that the data has to be moved around physically, and it can take quite a long time. I don't know about the capacity of your hardware, but either way, it is going to take well over an hour (think, overnight).

As for the way to reduce downtime, - you can not do it directly. I suppose you don't have a backup instance of the database, do you?

Answer 4

I hear a lot of people cringe when you mention running a database in a VM. Truth is, it depends on the VM. If you are not virtualizing hardware, you do not suffer the performance penalty that they fear. Using paravirtualization via Xen (I've heard good things about OpenVZ, but haven't used it.) you can do exactly what Dave Cheney said (which is the right approach) and only have to use one piece of hardware.

The tools/concepts in play would be:

1. Xen Virtualization (for paravirtualization)
2. LVM (for copying your data via snapshot)
3. virtual interfaces (eth0:1, eth0:2, etc.), ifup & ifdown to implement...
4. STONITH

This, of course is not a complete guide, but a recommendation for a sound combination of technologies that you can research and implement in a manner that fits your need. My approach is a little more about being prepared for the future, where Dave Cheney's is dealing with what you (probably) have today.