【追記有り】Amazon Aurora MySQL Version3でのOnline DDLの落とし穴とその回避方法
メディア統括本部 サービスリライアビリティグループ（SRG）の鬼海 雄太（@fat47）です。
#SRG（Service Reliability Group）は、主に弊社メディアサービスのインフラ周りを横断的にサポートしており、既存サービスの改善や新規立ち上げ、OSS貢献などを行っているグループです。
本記事は、Amazon Aurora MySQL Version3でカラム追加などでOnline DDL(INPLACE)を利用すると、DDL実行中はリーダーエンドポイントからテーブルが見えなくなるという挙動についての解説と、その回避方法について説明しています。
先にまとめと結論事象についてテーブルへDDL実行中、リーダーインスタンスからは該当のテーブルが見えなくなる現状わかっていること再現検証Aurora MySQL Version 3.04.1(MySQL8.0.28互換)で再現確認回避方法1: Aurora MySQL 3.05以上にアップグレードする回避方法2: ALTER実行時にALGORITHM = COPYを指定する回避方法3: pt-online-schema-changeを利用する終わりに参考URL
先にまとめと結論
・Aurora MySQL Version3の3.04.1以下の環境でALTER TABLEなどのDDLの実行をすると、実行が完了するまでリーダーインスタンスからは該当テーブルが見えなくなる場合がある
・回避策は3パターン
A. Aurora MySQL Version 3.04.2以上にアップグレードする
- もしくはVersion3.05以上にアップグレード
B. DDL実行の際にを指定する
C. Perconaのpt-online-schema-changeを利用する
【追記】2024年3月15日にAurora MySQL Version 3.04.2がリリース
上記のオンラインDDLの挙動は3.04.2のリリースによって修正されました。
事象について
テーブルへDDL実行中、リーダーインスタンスからは該当のテーブルが見えなくなる
Aurora MySQL Version3の3.04.1のライターエンドポイントに対して、やを実行中、リーダーエンドポイントにSELECTクエリを投げると下記のようにテーブルが存在しないというエラーになります。
ライター側のALTERなどの実行が完了すると、再びリーダーエンドポイントからもテーブルが見られるようになります。
Aurora MySQL Version2系の2.11.4でも同じクエリで試してみましたが、DDL実行中もリーダーエンドポイントへのSELECTは問題ありませんでした。
同様の事象はGMO様のブログでも取り上げられているように観測されているようです。
こちらのブログ記事ではAurora MySQL 3.02.2の環境で発生したようです。
現状わかっていること
- Aurora MySQL Version3系 3.04.1系以下のいくつかのバージョンで発生
- Version2.11.4では発生を確認できない
- Version3.05系では解消されている
- OPTIMIZE TABLEやALTER TABLEなどのINPLACEがおこなわれる操作で発生
- INPLACEで実行されるDDLは下記の公式ドキュメントを参照
- https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
再現検証
Aurora MySQL Version 3.04.1(MySQL8.0.28互換)で再現確認
適当なテーブルを作成
テーブルに適当に十数万〜100万行ぐらいINSERT
クラスターエンドポイントでの実行
上記実行中に別ターミナルを立ち上げてリーダーエンドポイントに向けてSLECTクエリ実行すると、テーブルが存在しないエラーが出ます。
実行中のOPTIMIZE TABLEの実行中止 もしくは完了を待ってからリーダーエンドポイントで実行すると、
再びSELECTクエリは成功します。
ALTER TABLEのOnlineDDLによっても発生します。
このクエリではを指定しています。
先程と同様にALTER実行中は、リーダーエンドポイントへのSELECTを実行するとテーブルが見えなくなります。
回避方法1: Aurora MySQL 3.05以上にアップグレードする
まず回避方法としてAurora MySQLの最新バージョンである3.05以上にアップグレードする方法があります。
Aurora MySQL 3.05(MySQL8.0.32互換)のリリースノートを見るとこの問題が修正されている記述があります。
• Fixed an issue where the reader instance is unable to open a table, with ERROR 1146. This issue occurs when executing certain types of online Data Definition Language (DDL) while the algorithm is being used on the writer instance.
リーダ・インスタンスがテーブルをオープンできず、ERROR 1146が発生する問題を修正しました。この問題は、ライター・インスタンスでINPLACEアルゴリズムが使用されているときに、特定のタイプのオンライン・データ定義言語（DDL）を実行すると発生します。
実際に先程の問題があった環境をv3.05.1にアップグレードしてから、
先程と同じテーブルに対して、とALTER TABLE … を実行してみたところ、問題の挙動は発生しませんでした。
ただしv3.05系を使うのには注意が必要です。
v3.05の標準サポート期限は約1年後の2025年1月となっているので、1年以内にさらに次のバージョン（現時点で未提供）へアップグレードをする必要があります。
Aurora MySQLのLTSとして提供されているv3.04の標準サポート期限は2026年10月なので、できればこちらを選択したかったのですが、今回の挙動を許容できない場合はv3.05が選択肢に入ってくるかと思います。
回避方法2: ALTER実行時にを指定する
別の回避方法としてALTER実行の場合、ALTERに を指定して実行することで、INPLACE方式が使われないため事象を回避できることを確認しました。
ALTERには3つの方式が存在しています。
- INSTANT
- INPLACE
- COPY
それぞの方式は下記の表のような特徴があります。
| 方式 | 可能な操作一例 | ロック |
|---|---|---|
| INSTANT | カラム追加 | 排他metadataロックのみ。metadata更新のみなので実行は即時完了 |
| INPLACE | セカンダリインデックス作成、インデックス名前変更 | 開始時と完了時のみ瞬間的な排他metadataロック |
| COPY | カラムのデータ型変更 | 開始から完了まで排他metadataロック |
ALTER文はALGORITHMの指定がない場合、INSTANT→INPLACE→COPYの順に実行可能な方式が自動で選択されます。
ALTER文による操作によってどの方式が利用可能かはこちらの公式ドキュメントをご参照ください。
OPTIMIZE TABLEは一時部条件を除いてINPLACEで実行されます。
今回の検証例ではカラム追加で検証していますが、実際はカラム追加の場合は一部条件を除いてINSTANTで実行することで回避できます。
しかし、INSTANTで実行できないタイプのDDLを実行したい場合はを指定する必要があります。
前述の表のとおり、の致命的な欠点として、ALTER実行開始から完了までロックが掛かることになります。
つまり、その間は該当テーブルへ書き込みロックがかかることになるので、システムをメンテナンスモードに入れるなどして、MySQLへの書き込みクエリを止めないとユーザーに影響が出ることになってしまいます。
どうしても今の環境からAurora MySQLのアップグレードができない場合は、ひとまずこのを使う方法をとることになるかと思います。
なお、でも環境の負荷状況によっては旧テーブルの削除時の排他metadataロック取得によって、問題が生じる可能性はあります。
回避方法3: pt-online-schema-changeを利用する
「pt-online-schema-change(以下pt-oscと記載)」を利用するという方法です。
pt-oscでALTERをテーブルAに対して実行する時の挙動は下記のような流れになります。
- 「テーブルA」と同じ構造の作業用テーブル「_テーブルA_new」を作成
- 「_テーブルA_new」にALTER TABLEを実行
- トリガーを作成し「テーブルA」の挿入/削除/更新が「_テーブルA_new」に反映される状態
- 「テーブルA」から「_テーブルA_new」にレコード全件のコピー
- テーブルのリネームでテーブルを入れ替える
- 「テーブルA」を「_テーブルA_old」に
- 「_テーブルA_new」を「テーブルA」に
- 作成したトリガーや「_テーブルA_old」の削除
下記が実際にpt-oscを実行した時に出力される実行ログです。
説明した流れのとおりに処理されていることがわかります。
pt-oscを利用するとなぜ今回の事象が回避できるのかというと、pt-oscの仕組みによって
1.「テーブルA」と同じ構造の作業用テーブル「_テーブルA_new」を作成 2. 「_テーブルA_new」にALTER TABLEを実行
という処理の流れになるので、ALTERの実行が「テーブルA」ではなく、「_テーブルA_new」という別のテーブルに対して実行される為です。
そのため裏側でINPLACE方式の処理が走っていたとしても、既存のテーブルAには影響はありません。
また、ALTERの実行もデータの無いテーブル定義だけの「_テーブルA_new」に対して実行するので、瞬間的に完了します。
pt-oscはサードパーティのツールになるので、回避の方法としてAWSさんから案内されることはないかと思いますが、Aurora MySQLのバージョンアップグレードができない場合の一つの手段として覚えておくといいかもしれません。
ただし、こちらのpt-oscを利用する場合も、トリガー作成のタイミングやテーブルの入れ替え時に元テーブルに対するmetadataロックが発生します。
環境の負荷状況によっては今回の事象が発生する可能性もあります。
終わりに
最近までAurora MySQL Version2の環境しか利用していなかったので、Version3のOnlineDDLの挙動には大変驚きました。
これからVersion2系からアップグレードする方もいらっしゃるかと思うで、この挙動は十分に注意する必要があるかと思います。
Aurora MySQLのLTSバージョンでこの挙動が改善されているものが提供されることを望みます！
AWSさんよろしくおねがいします！！！
SRG では一緒に働く仲間を募集しています。
ご興味ありましたらぜひこちらからご連絡ください。
