Mysql – Adding column into MySQL table size 150GB

MySQLmysql-5.6online-operations

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…

Best Answer

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.

Related Question