[Breaking News] The behavior of online DDL in Amazon Aurora MySQL Version 3 has been improved! (Aurora MySQL LTS 3.04.2 Release)
This is Yuta Kikai (@fat47) from the Service Reliability Group (SRG) of the Media Management Division.
#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).
This article addresses a previous blog post I wrote about: "When executing online DDL in Aurora MySQL LTS version 3.04, the relevant table becomes invisible from the reader endpoint during execution."
A version that resolves this issue has been released, so I'm presenting the results of my re-examination.
During online DDL execution, the relevant table becomes invisible from the leader endpoint.LTS latest minor version 3.04.2 releaseCheck if the online DDL behavior has been resolved.Create an Aurora cluster in 3.04.2Creating a verification tableInsert approximately 1 million records randomly.Execute OPTIMIZE on the user table of the cluster endpoint.While OPTIMIZE is running, execute SELECT on the leader endpoint in a separate terminal.Check if this issue occurs even while ALTER TABLE is in progress.While ALTER is running, execute SELECT on the leader endpoint in another terminal.In conclusion
During online DDL execution, the relevant table becomes invisible from the leader endpoint.
This behavior is consistent with Aurora MySQL LTS version 3.04.1, which I wrote about in a previous blog post.
Please refer to the blog linked above for more details.
To put it simply,
When OPTIMIZE TABLE or some ALTER TABLE operations are performed, online DDL processing is executed.
The table will be hidden from the leader endpoint (leader instance) until execution is complete.。
That was a troublesome behavior.
This behavior was resolved in the release of Aurora MySQL 3.05, but the standard support period for 3.05 isUntil January 2025That was the sticking point.
The standard support period for LTS version 3.04 isUntil October 2026Therefore, I would prefer to choose this option if possible.
LTS latest minor version 3.04.2 release
I noticed that the latest version, 3.04.2, of this long-term support (LTS) version was released on March 15th (Friday).
Release notes are available today3/18(Monday)It appeared on TV.
There are several points that need correction, but the following are particularly noteworthy.
INPLACEINPLACEAs mentioned aboveOnline DDL behavior has been improved.It seems so!!!
We will actually check if the behavior around online DDL has changed in the test environment.
Check if the online DDL behavior has been resolved.
We will verify this using the same method as when we tested it in a previous blog post.
Create an Aurora cluster in 3.04.2
We will create an Aurora cluster using one writer and one reader.
Creating a verification table
Insert approximately 1 million records randomly.
Execute OPTIMIZE on the user table of the cluster endpoint.
While OPTIMIZE is running, execute SELECT on the leader endpoint in a separate terminal.
The results will be displayed without any problems!
ERROR 1146 (42S02) at line 1: Table 'test.user' doesn't exist Check if this issue occurs even while ALTER TABLE is in progress.
While ALTER is running, execute SELECT on the leader endpoint in another terminal.
ALGORITHM=INPLACEIn conclusion
The long-awaited fix has been released in the LTS version of Aurora MySQL!
Now I can proceed with upgrading to Aurora MySQL version 3 with peace of mind!
Thank you so much, AWS!!!!
SRG is looking for new team members.
If you are interested, please contact us here.