Mastering MySQL Instant DDLs: Enhancing Schema Changes with INSTANT Algorithm, Monitoring, and Rebuild Strategies

In MySQL 8.0.12, a new DDL algorithm called INSTANT was introduced, enabling non-blocking schema changes. Initially, INSTANT DDLs allowed adding a column at the end of a table, contributed by Tencent Games. By MySQL 8.0.29, adding or removing columns anywhere in a table became possible.

The INSTANT algorithm modifies only metadata without impacting table data, avoiding exclusive locks and making changes immediate. However, each table supports up to 64 instant changes before requiring a full rebuild. This limitation necessitates proactive monitoring and strategic rebuilding using queries to track instant changes.

Default Algorithm

The default algorithm for supported DDL operations is now INSTANT, but not specifying it explicitly during ALTER operations can result in an unexpected fallback to other algorithms like COPY or INPLACE, leading to unexpected behavior.

Recommendations for DBA

1.Always Specify the Algorithm: Explicitly define the algorithm (like INSTANT) to avoid unexpected fallback and errors during schema changes. This practice ensures better control over operations and minimizes the risk of performance issues.

2.Monitor Instant Changes: Keep track of the TOTAL_ROW_VERSIONS column in the INFORMATION_SCHEMA.INNODB_TABLES to see how close you are to the 64-instant-change limit. Use a structured query to identify tables nearing this limit to prevent reaching it without notice.

Example Monitoring Query

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;

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;

Table Rebuild Strategy

If the count is near 64, a rebuild is required using:

•OPTIMIZE TABLE <table>;

•ALTER TABLE <table> ENGINE=InnoDB;

Conclusion

INSTANT DDLs greatly enhance the efficiency of schema changes in MySQL. However, its 64-change limit means DBAs must actively specify algorithms and monitor instant change counters to plan rebuilds proactively. Proper planning ensures seamless performance and avoids unexpected interruptions.