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?

  • 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. – Andrew Brennan Sep 27 '16 at 8:38
  • well alter table on MyISAM would held a table level lock, would recommend using table switching technique instead as suggested by @Andew – Nawaz Sohail Sep 27 '16 at 9:15
  • 1- During the altering process, do you write to this table, or only read? 2- Provide show create table table_name result please. – Jehad Keriaki Sep 27 '16 at 15:48
  • 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 – Wuigi Sep 28 '16 at 11:52

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

  • Never heard of ihn. Also pt-osc will refuse to alter if there are triggers in a table. Gh-ost is triggerless – akuzminsky Sep 28 '16 at 2:23
  • LHM - it's ruby gem, uses triggers. – Alexey Sep 28 '16 at 2:33
  • Seems like a pretty handy tool, will take a look at it, thanks! – Wuigi Sep 28 '16 at 11:57

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.

  • Thanks, useful link - unfortunately I can't switch engine for now, because of disk space among other things – Wuigi Sep 28 '16 at 12:13
  • 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? – Rick James Sep 28 '16 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.