MySQL Performance: MySQL => Online Index Creation and Schema Changes

Online Index Creation and Schema Changes [message #2131]

Wed, 24 October 2007 11:09

Jjhuff
Messages: 5
Registered: October 2007

Junior Member

We have some schema and index tweaks and some schema changes (mostly column adds/mods) that we'd like to do on a couple largish tables. I'm estimating that some of these will take an hour to complete.
Unfortunately, the tables in question get a fair amount of writes, so it would effectively take our site down for the entire operation.
My thought was to create a new table (CREATE TABLE LIKE..), ALTER it, and then insert all the rows from the old (and online) table. After some iteration, the tables should 'converge', at which point we use RENAME to swap the two and then run a final pass to sync the two tables.

The online table will need to be locked during the swap and final pass to make sure that no updates/deletes sneak through. Unfortunately, RENAME doesn't like to be run on locked tables.

Any ideas here? Is there another way? Heck, I'd be fine with acquiring a global lock.

Thanks!

Report message to a moderator