Yes, MySQL locks the table completely whilst it does the ALTER TABLE statement. Most of this time is spent in physically copying the table, which is why it is recommended you put all your necessary changes together in the one ALTER TABLE statement.
There are several approaches to mitigating this problem on a live database if you can't get a decent maintenance window.
First of all, many environments have timeouts of several minutes waiting for a table to become available for their query and don't know (and it's really difficult to check) just why the table is locked. I've used this quirk of a live website to do changes to tables. On a site I used to look after, I figured we had an allowance of about 7 minutes before someone started noticing. :-) It helps to make sure your boss is on your side.
Another way to do it is to do the select-insert-rename trick. This works well if the table either has fairly low
UPDATE frequency, or is purely the target of
INSERTs. The basic steps are to copy the table's schema, make the necessary changes, craft up a statement to do a
INSERT...SELECT from the old to the new, and rename the tables (do the rename in one statement). You also need prepare ahead of time a statement to copy over any "new" records that got added or updated between the
SELECT and the
RENAME. I also did this a few times in a past job.
However, there are come caveats:
- You will almost certainly have problems if the source table is MyISAM, unless perhaps the table is almost never written to. This is because of the way MyISAM tables lock. It works better with InnoDB tables because it can still be read whilst your big
INSERT...SELECT is running.
- You need a foolproof way of figuring out records that have been added or changed between the
SELECT and the
RENAME. For tables that are only used for
INSERT, use the auto_increment column. For tables that get
UPDATEs, you will need a reliable last-modified column.
Other ways to tackle this problem involve modifying slaves and failing the application over. This is more closely tied up with how your databases replicate. I also haven't done this one myself, so I can't describe exact steps.
Finally, there are a dozen server settings you can twiddle and several more that are much harder to change that will affect how long it takes to copy a table. The sort buffer is one, but also how much memory MySQL is allowed to use is another. (Remember that you can set a lot of those per connection, too, rather than setting some of them high globally.) When dealing with a lot of data, MySQL has a 'tipping point' effect where things are fairly linear up to a certain size, and then go to hell suddenly. It often comes up with complex queries working with a lot of data and is related to internal temporary table sizes and how much memory it is allowed to use, but it can come up with table alterations because they involve re-indexing the data. That is one reason why giving a database more memory is almost always a good thing.