[Breaking News] Online DDL behavior has been improved in Amazon Aurora MySQL Version 3! (Aurora MySQL LTS 3.04.2 released)
This is Yuta Onkai (@fat47) from the Service Reliability Group (SRG) of the Media Headquarters.
#SRG(Service Reliability Group) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
In this post, we will discuss a problem we previously covered in our blog: "When you execute online DDL in the LTS version of Aurora MySQL 3.04, the corresponding table becomes invisible to the reader endpoint during the execution."
A version that fixes this behavior has been released, so we've re-examined it and are presenting the results.
When online DDL is being executed, the corresponding 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 in 3.04.2Creating a verification tableInsert approximately 1 million records.Run OPTIMIZE on the user table of the cluster endpointWhile OPTIMIZE is running, execute SELECT on the reader endpoint in another terminalCheck if this occurs during ALTER TABLE executionWhile ALTER is running, execute SELECT on the reader endpoint in another terminalConclusion
When online DDL is being executed, the corresponding 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 further 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:
INPLACEINPLACEAs mentioned aboveImproved online DDL behaviorIt seems that it has been done!!!
We will actually check in a verification environment whether the behavior of online DDL has changed.
Check if the online DDL behavior has been resolved
We will verify this in the same way we did in our previous blog post.
Create an Aurora cluster in 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 during ALTER TABLE execution
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 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 are interested, please contact us here.