Adding column into MySQL table size 150GB

Question

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

• camba1: 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).
• Rick James: Caution: camba1's suggestion assumes you are not writing to the table during the process.
• John: 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.

Answer 1

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.

• John: 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.