0

I am trying to add column into MySQL table Table size 150GB Table storage engine= innodb MySQL version=5.6.34 Table have already created primary key(cluster index).

More than 2 hours completed and tmp directory taken 207 GB space for this operation and still not completed I tried algorithm =inplace and lock=none then I got syntax error and I was trying to type correct syntax but no luck so I decided to do only write add column query .

My question is why MySQL uses more than 150GB tmp size and still consuming size and not completed yet. I have no idea when it will complete . I checked .sql file size which is created while add column query running The size 1.1TB how?

Table have 4 indexes and aut_increment already on table Please help me...

  • Since you have the space, I would try creating a new table (with the new column), inserting all the data from the old table, dropping the old table, renaming the new table to match the old table's name and the recreating the indexes (this will take time). – camba1 Jul 5 at 16:20
  • Caution: camba1's suggestion assumes you are not writing to the table during the process. – Rick James Jul 5 at 17:29
0

For 5.6: pt-online-schema-change is probably your best bet. It creates a new table with the change, then gradually copies rows over. Meanwhile, it uses a TRIGGER to keep the new copy updated.

For 8.0: ADD COLUMN now has ALGORITHM=INSTANT.

Would you like to show us SHOW CREATE TABLE; there may be some improvements that would decrease the problem.

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.