I'm trying to alter a pretty large table (25GB including index) by adding a column & modifying an index, but in addition to taking a very long time (> 1 hour), which is ok, it affects the whole system, making queries on other tables more than 100 times slower, which is a huge issue and forced me to cancel the operation.
The server is pretty solid (196GB RAM with only 70GB used, SSD RAID disks with 300GB free space). Storage engine is MyISAM.
Is there any way to give the ALTER TABLE very low priority so that it doesn't dramatically affect the rest of the operations (I don't care if it takes twice the time)? Is it even normal that it does?
Best Answer
Use pt-online-schema-change. It will monitor server load and throttle itself if necessary.