5

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?

Anoosh
  • 105
  • 3
  • 10
  • Any DDL statement needs to wait for all queries against the table to finish. This is called [metadata locking](https://dev.mysql.com/doc/refman/8.0/en/metadata-locking.html). Any query, even a read-only SELECT, will block an ALTER TABLE. And vice versa - the ALTER TABLE, while waiting for the metadata lock, blocks any further queries. – Bill Karwin Aug 18 '20 at 22:10
  • @BillKarwin if you look at the doc attached. Alter table Algorithm=INSTANT does not take any metadata lock. – Anoosh Aug 19 '20 at 06:18
  • Interesting, thanks for the correction. I did not read that before. Well, then it's a mystery to me. You could try investigating the stages of the ALTER TABLE statement in the performance schema, to see where it's spending its time. – Bill Karwin Aug 19 '20 at 06:28
  • 1
    I'm on 8.0.28 also with a DB table of ~40M rows and high I/O and also ran into this issue adding a column. I did not wait for the operation to complete, but it had been about 120 seconds when I gave up and bailed. Is there any more documentation on the limitations of Algorithm=INSTANT and scenarios where it may not operate as expected? – William W Jun 06 '23 at 01:20

0 Answers0