DML operations during adding a column using 'algorithm=inplace'

Question

MySQL version: MySQL5.7

Scenario: Need to add a column in a big table of size more than 1TB using 'algorithm=inplace' . This is a busy table with lot of DML operations running every second.

We know that 'algorithm=inplace' operation permits concurrent DML, but I read that there will be a lock in the final commit phase while these concurrent DML operations are applied.

Is this true? If yes, is this a lock which lasts only a few seconds or is it depends on any other factors? Can we predict the time, when this lock will happen? (because if this lock lasts for few minutes, there will be a downtime and we need to plan accordingly)

Also please share if there are any other better approaches for adding a column in a big table. This will be very helpful for us.

Answer 1

According to the docs you should be fine with:

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;

This only works if the column added is at the end, and is not an autoincrement.

Note that if you start to update the table to add data in the final column, you'll start to get fragmented tables. The solution to that is to optimize the table. But that WILL block with a 2 TB table. The usual solution to that is to partition the table, so you can optimize one partition at a time. But that will also be a large operation the first time. So while those are recommended, they also will take a lot of planning.