
MySQL, Alter Table, and How to Observe Progress - tortilla
http://gabrielcain.com/blog/2009/08/05/mysql-alter-table-and-how-to-observe-progress/
======
Devilboy
Is it normal for a MySQL ALTER TABLE to run for 22 hours on a table with less
than 50 million rows?

~~~
chrisbolt
There are many variables. Memory, disk speed, average row length, whether or
not MySQL has been correctly tuned to handle that kind of load.

~~~
jacquesm
And of course whatever else is running on that machine. If you do this on a
production server then you might end up seeking back and forth across the disk
all the time.

Alter tables are pretty bad, there is a quick way to do them though, here is
how:

\- create new table, right structure, no keys

\- load data in it (now you should have a .MYD)

\- create table with all keys defined, copy the .frm and .MYI files over the
ones of the .MYD above

\- flush tables (don't forget this, alternatively you can shut down mysql at
this point and use the my*chk tool with the -r option to recreate your
indexes)

\- repair table (skip if you shutdown mysql above)

This will work a whole lot faster than an alter table on a table of the same
size, but it is a very gory hack and I would advise you to make a backup
before trying any of this.

~~~
chrisbolt
If you have a highly available setup with master-master replication, you can
simply run SET SQL_LOG_BIN=0; ALTER TABLE whatever; on the inactive master,
then swap masters and do the same on the previously active master.

