CODEWITHSUNDEEP
mysqlamazon-rdsinnodbddlalter
Anoosh
Anoosh

Reputation: 155

MySQL 8.0 Alter Table Algorithm=INSTANT not working as expected (takes 40 secs)

MySQL 8.0 - As stated in MySQL Official Doc and MySQL Server Team, Alter table with Algorithm=INSTANT adds the column instantly without taking any lock.

But, It worked out different.

Test Setup - Table with 40M rows. Reads and writes (1000 TPS) on the table. Table schema is very simple.

field,type,null,key,default,extra
id,bigint(20),NO,PRI,,auto_increment
t_a,bigint(20),NO,MUL,,""
t_b,bigint(20),NO,MUL,,""
t_c,int(11),NO,"",1,""

Infra: AWS RDS MySQL 8 - Engine Version - 8.0.17

Test case: Adding a new column to the table.

Statement:

alter table table_name add column_name int, algorithm=instant;

Result:

mysql> alter table test_table add test_column int, algorithm=instant;
Query OK, 0 rows affected (36.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

It has taken ~40 secs. It has Blocked both Reads and Writes during the 40secs

Is this expected? Is there something I'm missing?

Upvotes: 6

Views: 2082

Answers (1)

enharmonic
enharmonic

Reputation: 2108

Looking at the doc, it says the "instant" algorithm takes metadata locks:

ALTER TABLE operations are processed using one of the following algorithms: COPY: ... INPLACE: ... INSTANT: Operations only modify metadata in the data dictionary. An exclusive metadata lock on the table may be taken briefly during the execution phase of the operation.

I suppose "briefly" is a matter of opinion.

Upvotes: 0

Related Questions