Understanding MySQL Instant DDL: What DBAs Should Know

MySQL 8.0.12 introduced a new DDL algorithm called INSTANT , which allows schema changes without blocking the table; the first instant operation—adding a column at the end—originated from a contribution by Tencent Games. MySQL 8.0.29 later added the ability to add or drop columns at any position.

The article warns about the risks of using this feature blindly and outlines four key recommendations.

1. Default Algorithm

Since MySQL 8.0.12, the default algorithm for any supported DDL is INSTANT . An ALTER statement with this algorithm only updates the table’s metadata in the data dictionary, avoiding exclusive metadata locks and leaving the actual data untouched, making the operation truly instant.

The other algorithms are COPY and INPLACE , which are part of the Online DDL family. Even when Online DDL is supported, a single table can only perform 64 instant changes; after reaching this limit the table must be rebuilt.

If an ALTER statement does not explicitly specify an algorithm, MySQL silently chooses the appropriate one, which can lead to unexpected production issues.

2. Always Specify the Algorithm

It is recommended to always specify the algorithm (e.g., ALGORITHM=INSTANT ) even when it is the default. If MySQL cannot use the requested algorithm, it will raise an error instead of silently falling back to 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.

3. Monitor Instant Changes

DBAs should monitor the number of instant changes performed on each table. MySQL stores the row‑version count in the information_schema.INNODB_TABLES table.

SQL > SELECT NAME, TOTAL_ROW_VERSIONS
       FROM INFORMATION_SCHEMA.INNODB_TABLES
       WHERE NAME LIKE 'test/t1';
+----------+--------------------+
| NAME     | TOTAL_ROW_VERSIONS |
+----------+--------------------+
| test/t1  |                63 |
+----------+--------------------+

When the counter approaches 64, the next instant DDL will fail. A useful monitoring query is provided below:

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;

To reset the counter and rebuild the table, use either OPTIMIZE TABLE <table> or ALTER TABLE <table> ENGINE=InnoDB .

4. Conclusion

MySQL 8.0’s INSTANT DDL dramatically reduces schema‑change downtime, but the 64‑change limit makes it essential to explicitly specify the algorithm in ALTER statements and to monitor information_schema for row‑version usage. Proper planning and occasional table rebuilds ensure safe and predictable schema evolution.

References

[1] MySQL 8.0.12 release notes: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-12.html

[2] MySQL 8.0.29 release notes: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-29.html

[3] Online DDL documentation: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html