ALTER TABLE ADD COLUMN takes a long time

Question

I was just trying to add a column named "location" to the table (main_table) in the database. The team I ran was

ALTER TABLE main_table ADD COLUMN location varchar (256); 

The main table contains> 2,000,000 rows. It has been working for more than 2 hours and is still not completed.

I tried using mytop to monitor the activity of this database, to make sure that the request is not blocked by another request process, but it does not seem to be so. Does it have to take so long? In fact, I just rebooted the machine before running this command. Now this command is still running. I'm not sure what to do.

Answer 1

Your ALTER TABLE statement assumes that mysql will have to rewrite each row of the table, including a new column. Since you have more than 2 million lines, I would definitely expect that it will take a considerable amount of time, during which your server will most likely be mainly connected with IO. Normally, it would be more convenient for you to do the following:

 CREATE TABLE main_table_new LIKE main_table; ALTER TABLE main_table_new ADD COLUMN location varchar(256); INSERT INTO main_table_new (fields_in_main_table) SELECT * FROM main_table; RENAME TABLE main_table TO main_table_old, main_table_new TO main_table; DROP TABLE main_table_old; 

Thus, you add a column to an empty table and basically write data to this new table, which, as you are sure, no one will look for without locking as many resources as possible.

Answer 2

I think the appropriate answer for this is to use a function like pt-online-schema-change or gh-ost .

We have migrated more than 4 billion lines with this, although it can take up to 10 days, with a minute of downtime.

Percona works the same as above

• Create temporary table
• Creates triggers in the first table (for inserts, updates, deletes) so that they are replicated to the temp table
• In small batches transfer data
• When done, rename the table to a new table and release another table