[Breaking News] Online DDL behavior has been improved in Amazon Aurora MySQL Version 3! (Aurora MySQL LTS 3.04.2 Released)
This is Onkai Yuta (@fat47) from the Service Reliability Group (SRG) of the Media Headquarters.
#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
In this article, we will discuss a previous blog post that mentioned, "When performing online DDL on Aurora MySQL LTS version 3.04, the corresponding table becomes invisible to the reader endpoint during the execution."
A version that resolves this issue has been released, so we have re-examined it and are presenting the results.
During online DDL execution, the table becomes invisible from the reader endpoint.Latest minor version 3.04.2 of LTS releasedCheck if the online DDL behavior has been resolvedCreate an Aurora cluster with 3.04.2Creating a verification tableInsert approximately 1 million recordsRun OPTIMIZE on the user table of the cluster endpointWhile OPTIMIZE is running, execute SELECT on the reader endpoint in another terminalCheck if this occurs while ALTER TABLE is runningWhile ALTER is running, execute SELECT on the reader endpoint in another terminalConclusion
During online DDL execution, the table becomes invisible from the reader endpoint.
This is the behavior up to Aurora MySQL LTS 3.04.1, which I wrote about in a previous blog post.
Please refer to the blog above for more details.
To put it simply,
When online DDL processing is performed by executing OPTIMIZE TABLE or some ALTER TABLE commands,
The table will not be visible to the reader endpoint (reader instance) until execution is complete.。
That was a troubling behavior.
This behavior was resolved with the release of Aurora MySQL 3.05, but the standard support period for 3.05 isUntil January 2025This was the problem.
The standard support period for LTS 3.04 isUntil October 2026So I would like to choose this option if possible.
Latest minor version 3.04.2 of LTS released
I noticed that the latest version 3.04.2 of this LTS, which has a long support period, was released on Friday, March 15th.
Release notes today3/18(Monday)It was featured in.
There are a number of changes, but the most notable ones are:
INPLACEINPLACEThe aforementionedImproved online DDL behaviorIt seems that it has been done!!!
We will actually check whether the behavior around online DDL has changed in the verification environment.
Check if the online DDL behavior has been resolved
We will verify this using the same method we used in our previous blog post.
Create an Aurora cluster with 3.04.2
Create an Aurora cluster with one writer and one reader.
Creating a verification table
Insert approximately 1 million records
Run OPTIMIZE on the user table of the cluster endpoint
While OPTIMIZE is running, execute SELECT on the reader endpoint in another terminal
The results will be displayed without any problems!
ERROR 1146 (42S02) at line 1: Table 'test.user' doesn't exist Check if this occurs while ALTER TABLE is running
While ALTER is running, execute SELECT on the reader endpoint in another terminal
ALGORITHM=INPLACEConclusion
The long-awaited fix has been released in the LTS version of Aurora MySQL!
Now I can proceed with the upgrade to Aurora MySQL version 3 without any worries!
Thank you so much AWS!!!
SRG is looking for people to work with us.
If you're interested, please contact us here.