[Update Added] Pitfalls of Online DDL in Amazon Aurora MySQL Version 3 and How to Avoid Them
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 explains the behavior in Amazon Aurora MySQL Version 3 where using Online DDL (INPLACE) for adding columns causes the table to become invisible from the reader endpoint while the DDL is being executed, and how to avoid this issue.
Summary and conclusionRegarding the eventWhile DDL is being executed on a table, the corresponding table becomes invisible to the leader instance.What we know at this pointReproduction verificationReproducible on Aurora MySQL Version 3.04.1 (MySQL 8.0.28 compatible).Workaround 1: Upgrade to Aurora MySQL 3.05 or later.Workaround 2: Specify ALGORITHM = COPY when executing ALTER.Workaround 3: Use pt-online-schema-changeIn conclusionReference URL
Summary and conclusion
- Aurora MySQL Version 33.04.1Under the following environmentWhen you execute DDL such as ALTER TABLE、The relevant table may become invisible to the leader instance until execution is complete.
• There are three possible workarounds.
A. Upgrade to Aurora MySQL Version 3.04.2 or later.
- Or upgrade to Version 3.05 or higher
B. When executing DDLSpecify
C. Use Percona's pt-online-schema-change.
[Update] Aurora MySQL Version 3.04.2 was released on March 15, 2024.
The behavior of the above online DDL isThis was fixed in the 3.04.2 release.。
Regarding the event
While DDL is being executed on a table, the corresponding table becomes invisible to the leader instance.
ALTER TABLEOnce the ALTER operation on the writer side is complete, the table will become visible again from the reader endpoint.
I also tried the same query with Aurora MySQL Version 2.11.4, and there were no problems with SELECT queries to the reader endpoint even while DDL was being executed.
Similar incidents appear to have been observed, as they have also been covered on GMO's blog.
This blog post suggests the issue occurred in an Aurora MySQL 3.02.2 environment.
What we know at this point
- Aurora MySQL Version 3 series, 3.04.1 series and earlier.This occurs in several versions.
- This issue cannot be confirmed in version 2.11.4.
- This issue has been resolved in version 3.05.
- This occurs during operations that perform INPLACE, such as OPTIMIZE TABLE and ALTER TABLE.
- For details on the DDL executed by INPLACE, please refer to the official documentation below.
- https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
Reproduction verification
Reproducible on Aurora MySQL Version 3.04.1 (MySQL 8.0.28 compatible).
Create a suitable table
Insert approximately 100,000 to 1,000,000 rows into the table.
OPTIMIZE TALBEOpen another terminal while the above is running.Leader EndpointWhen I execute a SLECT query towards this table, I get an error saying the table does not exist.
If you stop the execution of an ongoing OPTIMIZE TABLE or wait for it to complete before executing it on the leader endpoint,
The SELECT query succeeds again.
This can also occur with OnlineDDL in ALTER TABLE.
In this queryThis is specified.
As before, while an ALTER statement is being executed, executing a SELECT statement on the leader endpoint will make the table invisible.
Workaround 1: Upgrade to Aurora MySQL 3.05 or later.
First, as a workaround, the latest version of Aurora MySQL is3.05 or higherThere is a way to upgrade.
The release notes for Aurora MySQL 3.05 (compatible with MySQL 8.0.32) state that this issue has been fixed.
INPLACEWe have fixed an issue where the reader instance could not open a table, resulting in ERROR 1146. This issue occurred when the writer instance was using the INPLACE algorithm and executing certain types of online data definition language (DDL).
The environment in which the problem mentioned earlier actually occurredv3.05.1After upgrading,
OPTIMIZE TABLEHowever, caution is advised when using the v3.05 series.
The standard support end date for v3.05 isAbout 1 year laterofJanuary 2025Therefore, you will need to upgrade to the next version (which is not yet available) within one year.
The standard support end date for v3.04, which is provided as an LTS version of Aurora MySQL, isOctober 2026Therefore, I would have preferred to choose this option if possible, but if you cannot accept the current behavior, then v3.05 will be an option.
Workaround 2: When executing ALTERSpecify
Another workaround is to execute ALTER, in the case of ALTER We confirmed that specifying this option during execution prevents the INPLACE method from being used, thus avoiding the issue.
There are three types of ALTER.
- INSTANT
- INPLACE
- COPY
Each method has the characteristics shown in the table below.
| method | Example of possible operations | rock |
|---|---|---|
| INSTANT | Add column | Only an exclusive metadata lock is used. Since only metadata updates are performed, execution is immediate. |
| INPLACE | Create a secondary index, rename an index | Instantaneous exclusive metadata lock at start and finish. |
| COPY | Change the data type of a column | Exclusive metadata lock from start to finish |
If no ALGORITHM is specified in the ALTER statement, the system automatically selects the execution method in the order of INSTANT → INPLACE → COPY.
Please refer to the official documentation to see which methods are available for operations using the ALTER statement.
The OPTIMIZE TABLE command is executed as INPLACE, except for temporary conditions.
In this verification example, we are testing by adding a column, but in reality, when adding a column, this can be avoided by running the test with INSTANT, except under certain conditions.
However, if you want to execute a type of DDL that cannot be executed with INSTANTYou need to specify this.
As shown in the table above,A fatal flaw of this approach is that a lock is maintained from the start to the completion of the ALTER command.
In other words, a write lock will be placed on the relevant table during that time, so unless you put the system into maintenance mode or something similar to stop write queries to MySQL, it will affect users.
If you absolutely cannot upgrade Aurora MySQL from your current environment, then for now, try thisI think we'll end up using that method.
In addition,However, depending on the load on the environment, problems may arise due to the acquisition of an exclusive metadata lock when deleting an old table.
Workaround 3: Use pt-online-schema-change
「pt-online-schema-changeThis method involves using "(hereinafter referred to as pt-osc)".
I provided a detailed explanation of this tool several years ago.I wrote about it on my tech blog.Since it is available, please refer to that information.
The behavior when executing ALTER on table A using pt-osc is as follows:
- Create a working table "_TableA_new" with the same structure as "TableA".
- Execute ALTER TABLE on "_tableA_new"
- Create a trigger so that insertions/deletions/updates in "Table A" are reflected in "_Table A_new".
- Copy all records from "Table A" to "_Table A_new".
- Rename the table to replace it.
- Change "Table A" to "_Table A_old"
- Change "_TableA_new" to "TableA"
- Deleting the triggers you created and the table "_tableA_old"
The following is the execution log that is output when pt-osc is actually run.
You can see that the process is being handled exactly as described.
The reason why this issue can be avoided by using pt-osc is because of the mechanism of pt-osc.
1. Create a working table "_TableA_new" with the same structure as "TableA". 2. Execute ALTER TABLE on "_TableA_new".
This is the processing flow,Because the ALTER operation is performed on a different table called "_tableA_new" instead of "tableA".is.
Therefore, even if INPLACE processing is running in the background, it will not affect the existing table A.
Also, executing ALTER only results in a table definition with no data.This will be executed on "_tableA_new".It completes instantly.
Since pt-osc is a third-party tool, AWS probably won't recommend it as a workaround, but it might be good to keep in mind as one option if you're unable to upgrade your Aurora MySQL version.
However, even when using pt-osc, metadata locks on the original table occur when triggers are created or when tables are swapped.
Depending on the environmental load, this issue may occur.
In conclusion
Until recently, I had only used an Aurora MySQL Version 2 environment, so I was very surprised by the behavior of OnlineDDL in Version 3.
I think some people will be upgrading from Version 2, so this behavior requires careful attention.
I hope that an LTS version of Aurora MySQL will be provided that improves this behavior!
AWS, please take care of this for us!!!
SRG is looking for new team members.
If you are interested, please contact us here.