MySQL InnoDB’s Instant Schema Changes: What DBAs Should Know

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 !