In MySQL 8.0.12, we introduced a new algorithm for DDLs that won’t block the table when changing its definition. The first instant operation was adding a column at the end of a table, this was a contribution from Tencent Games.
Then in MySQL 8.0.29 we added the possibility to add (or remove) a column anywhere in the table.
For more information, please check these articles from Mayank Prasad : [1], [2]
In this article, I want to focus on some dangers that could happen when using blindly this feature.
Default Algorithm
Since MySQL 8.0.12, for any supported DDL, the default algorithm is INSTANT. This means that the ALTER statement will only modify the table’s metadata in the data dictionary. No exclusive metadata locks are taken on the table during the preparation and execution phases of the operation, and table data is unaffected, making the operations instantaneous.
The other two algorithms are COPY and INPLACE, see the manual for the online DDL operations.
However, there is a limitation for INSTANT DDLs even when the operation is supported: a table supports 64 instant changes. After reaching that counter, the table needs to be “rebuilt”.
If the algorithm is not specified during the ALTER statement (DDL operation), the appropriate algorithm will be chosen silently. Of course, this can lead to a nightmare situation in production if not expected.
Always specify the ALGORITHM
So the first recommendation is always to specify the algorithm even if it’s the default one when performing DDLs. When the algorithm is specified, if MySQL is not able to use it, it will throw an error instead of executing the operation using another algorithm:
SQL > ALTER TABLE t1 DROP col1, ALGORITHM=INSTANT;
ERROR: 4092 (HY000): Maximum row versions reached for table test/t1.
No more columns can be added or dropped instantly. Please use COPY/INPLACE.Monitor the instant changes
The second recommendation is also to monitor the number of instant changes performed on the tables.
MySQL keeps the row versions in Information_Schema:
SQL > SELECT NAME, TOTAL_ROW_VERSIONS
FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1';
+---------+--------------------+
| NAME | TOTAL_ROW_VERSIONS |
+---------+--------------------+
| test/t1 | 63 |
+---------+--------------------+In the example above, the DBA will be able to perform one extra INSTANT DDL operation but after that one, MySQL won’t be able to perform another one.
As DBA, it’s a good practice to monitor all the tables and decide when a table needs to be rebuilt (to reset that counter).
This is an example of a recommended query to add to your monitoring tool:
SQL > SELECT NAME, TOTAL_ROW_VERSIONS, 64-TOTAL_ROW_VERSIONS AS
"REMAINING_INSTANT_DDLs",
ROUND(TOTAL_ROW_VERSIONS/64 * 100,2) AS "DDLs %"
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE TOTAL_ROW_VERSIONS > 0 ORDER BY 2 DESC;
+--------------------------+--------------------+------------------------+--------+
| NAME | TOTAL_ROW_VERSIONS | REMAINING_INSTANT_DDLs | DDLs % |
+--------------------------+--------------------+------------------------+--------+
| test/t1 | 63 | 1 | 98.44 |
| test/t | 4 | 60 | 6.25 |
| test2/t1 | 3 | 61 | 4.69 |
| sbtest/sbtest1 | 2 | 62 | 3.13 |
| test/deprecation_warning | 1 | 63 | 1.56 |
+--------------------------+--------------------+------------------------+--------+To reset the counter and rebuild the table, you can use OPTIMIZE TABLE <table> or ALTER TABLE <table> ENGINE=InnoDB
Conclusion
In conclusion, MySQL 8.0’s introduction of the INSTANT algorithm for DDL operations has revolutionized schema changes by avoiding blocking changes. However, with the limitation of 64 instant changes, before a table rebuild is required, it’s crucial to specify the algorithm explicitly during ALTER statements to avoid unexpected behaviors. Monitoring the number of instant changes through Information_Schema is also recommended to avoid surprises by reaching the instant change limit unaware and plan carefully the table rebuilds
Enjoy MySQL !
> This means that the ALTER statement will only modify the table’s metadata in the data dictionary. No exclusive metadata locks are taken on the table during the preparation and execution phases of the operation, and table data is unaffected, making the operations instantaneous.
“making the operations instantaneous” is not strictly exact. And it looks locking is involved. In below, you can see that the ALTER blocked on the open transaction on the table, and that the 2nd INSERT blocked on the ALTER.
$ ./use <<< "create database test_jfg; create table test_jfg.t(i int)"
$ ./use -N test_jfg <<< "select 'begin', version(), now(); begin; insert into t(i) value(1); do sleep(10); commit"& ./use -N test_jfg <<< "alter table t add column v int default 0; select 'end alter', now()"& sleep 2; ./use -N test_jfg <<< "insert into t(i) value(2); select 'end 2nd insert', now()"& wait
[1] 1297
[2] 1298
begin 8.0.36 2024-03-22 20:31:48
[3] 1302
[1] Done ./use -N test_jfg <<< "select 'begin', version(), now(); begin; insert into t(i) value(1); do sleep(10); commit"
end alter 2024-03-22 20:31:59
[2]- Done ./use -N test_jfg <<< "alter table t add column v int default 0; select 'end alter', now()"
end 2nd insert 2024-03-22 20:31:59
[3]+ Done ./use -N test_jfg <<< "insert into t(i) value(2); select 'end 2nd insert', now()"
A metadata change always requires a minimal lock, which in a concurrency environment can take some time… but in this case it’s the ALTER that is waiting to get the lock isn’t it ? But the ALTER operation is very quick.
Cheers,