MySQL - alter huge table without slowing down server

Question

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?

• Andrew Brennan: Create a copy of the table and modify the copy. Then drop the original table and rename the copy to the same name as the table.
• Nawaz Sohail: well alter table on MyISAM would held a table level lock, would recommend using table switching technique instead as suggested by @Andew
• Jehad Keriaki: 1- During the altering process, do you write to this table, or only read? 2- Provide show create table table_name result please.
• Wuigi: I was actually using the switching technique so that the table is not used at all (neither read nor written to) during the process, the problem is that it slows down the whole server, even queries to unrelated tables

Answer 1

Use pt-online-schema-change. It will monitor server load and throttle itself if necessary.

• Alexey: Or github.com/github/gh-ost, github.com/soundcloud/lhm
• akuzminsky: Never heard of ihn. Also pt-osc will refuse to alter if there are triggers in a table. Gh-ost is triggerless
• Alexey: LHM - it's ruby gem, uses triggers.
• Wuigi: Seems like a pretty handy tool, will take a look at it, thanks!

Answer 2

Upgrade to InnoDB. Tips and caveats

Upgrade to at least 5.6

After those, you can do most ALTERs with almost no downtime. See ALGORITHM=INPLACE.

• Wuigi: Thanks, useful link - unfortunately I can't switch engine for now, because of disk space among other things
• Rick James: A common space waster is using BIGINT (8 bytes) when TINYTINT (1 byte) would suffice. And there are others. Let's see SHOW CREATE TABLE`. How much RAM do you have?