3

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...

Improve this question
3
  • 1
    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
    Commented Jul 5, 2019 at 16:20
  • Caution: camba1's suggestion assumes you are not writing to the table during the process.
    – Rick James
    Commented Jul 5, 2019 at 17:29
  • Mysql is extremely limited in the ability to deal with large tables that are being written to, in so many ways. 1) use multiple threads (xargs, parallel, etc) and INSERT the old table into a new table 2) same approach but now REPLACE changed/new rows 3) now write lock the source table 4) same appraoch, REPLACE changed/new rows 5) rename table source to old, new to source 6) unlock This can speed up the process a couple hundred times, the downtime is very low.
    – John
    Commented Jan 23, 2020 at 21:04

1 Answer 1

Reset to default
3

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.

Improve this answer
1
  • 1
    Should be added "INSTANT" doesn't work on compressed tables at all, most large tables benefit a lot from compression so he'll be limited to "INPLACE" in that case.
    – John
    Commented Jan 23, 2020 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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