For the last few days I've been using MySQLs ALTER and REPAIR table functionality and its caused tons of countless problems and a great deal of lost sleep.
The first problem I noticed was that for large tables ALTER TABLE was taking hours! Lets say you have a 30G table. Good luck altering it as the default MySQL configuration will probably take 100 or more hours.
In MySQLs defense there are a number of variables you can use to increase the performance of an ALTER but the problem is that the two major ones (myisam_max_extra_sort_file_size, and myisam_max_sort_file_size) can't be set at runtime (during an ALTER). If these are set too low MySQL will revert to a /"Repair with keycache"/ strategy and this is 1000x slower than "Repair with sort". Once MySQL selects a ALTER strategy it can't use a faster one and you just have to ride it out.
http://www.peerfear.org/rss/permalink/2004/10/16/MySQLAndALTERTABLEGuiltyAsCharged
... and ...
It turns out that the performance of ALTER TABLE is somewhat of a black art and kept secret by the MySQL high priests <http://www.lordblink.com/> .
First off it seems there are two problems:
# In a replicated environment the ALTER TABLE is actually run on the master first and *then* the slave. This is not what you want because this will take twice as long. There needs to be a way to say /"don't run this on the slave"/ which would allow you to then ssh into your slave and run the ALTER TABLE directly. Of course if I could get the ALTER TABLE to run fast enough then this wouldn't be a problem.
http://www.peerfear.org/rss/permalink/2004/10/15/MaxTableSizeInMySQL
Interested in feedback...
--
Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat.
Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod!
Kevin A. Burton, Location - San Francisco, CA
AIM/YIM - sfburtonator, Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
