はじめまして、SRE チームの村山です。
今回、弊社の主要サービスで使用している Aurora MySQL のアップグレードを行いましたので、対応内容や得られた知見についてご紹介したいと思います。
背景
弊社で使用している Aurora は、2022 年 10 月に Aurora MySQL v1 (MySQL 5.6 互換) から Aurora MySQL v2 (MySQL 5.7 互換) へとアップグレードしております。その際の対応については、以下の記事をご覧ください。
その後も v2 を使用していましたが、2024 年 10 月 31 日 をもって v2 の標準サポートが終了になるため、Aurora MySQL v3 (MySQL 8.0 互換) へのアップグレードが必要になりました。
変更点の影響調査
アップグレードに向けて、まずはドキュメントを中心に変更点と影響範囲の調査を進めました。 これは SRE チームではなく、Web サービスの基盤部分を横断的に見るアプリケーション基盤チームを中心に実施して頂きました。
- Aurora MySQL version 3 compatible with MySQL 8.0 - Amazon Aurora
- MySQL :: MySQL 8.0 Reference Manual :: 1.3 What Is New in MySQL 8.0
この中で対応が必要だと判断し、実施した主な内容を以下に記載します。
デフォルトの COLLATION が utf8mb4_0900_ai_ci になる
MySQL 8.0 では、utf8mb4 のデフォルトの COLLATION が utf8mb4_general_ci から utf8mb4_0900_ai_ci に変更になりました。
Each character set has a default collation. For example, the default collations for utf8mb4 and latin1 are utf8mb4_0900_ai_ci and latin1_swedish_ci, respectively.
これまで DB のテーブル定義 (マイグレーションファイル) には明示的に COLLATION を設定していなかったので、今後新規にテーブルを作成する際は COLLATE=utf8mb4_general_ci を設定するように開発ルールを変更しました。
DB アップグレードのタイミングに合わせ、既存のテーブルも COLLATE を設定するように変更しています。
FLOAT, DOUBLE, DECIMAL タイプのカラムに対して UNSIGNED 属性が非推奨になる
MySQL 8.0.17 で非推奨になったため、ALTER TABLE で対象のカラムから UNSIGNED 属性を削除するようにしました。対象のカラムが多いため、稼働中の DB には ALTER TABLE を適用せず、後述するクローンの DB にだけ適用するようにしています。詳しくは「アップグレード方法」の項に記載します。
As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms);
SQL_CALC_FOUND_ROWS, FOUND_ROWS() が非推奨になる
MySQL 8.0.17 で非推奨になったため、使用箇所を修正しました。アップグレード前に修正できる内容でしたので、事前に対応しています。
The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17;
utf8mb3 が非推奨になる
MySQL 8.0 で非推奨になったため、ALTER TABLE で対象のテーブル・カラムを utf8mb4 に変換しました。こちらも後述するクローンの DB にだけ適用するようにしています。
The utf8mb3 character set is deprecated. utf8mb3 remains supported for the lifetimes of the MySQL 8.0.x and following LTS release series, as well as in MySQL 8.0.
予約語利用箇所の対処
MySQL 8.0 より RANK や LEAD の単語が予約語に追加されました。
Reserved words are permitted as identifiers if you quote them as described in Section 11.2, “Schema Object Names”:
既存のテーブルに同名のカラムがあることを下記クエリで確認しましたが、アプリのライブラリにより引用符で囲む処理をするため、対応は不要の想定でした。
select table_schema, table_name, column_name
from information_schema.columns
where table_schema not in ('information_schema', 'mysql', 'sys')
and column_name in (select WORD from information_schema.KEYWORDS where reserved = 1)
and TABLE_NAME NOT IN ('ar_internal_metadata');
しかし、後の段階で一部のアプリではその処理がされず、エラーになることが判明したため、最終的にはカラム名を変更する対応をとりました。
GROUP BY 句での ASC/DESC の廃止
MySQL 8.0 より GROUP BY 句で ASC/DESC をつけることができなくなりました。後述する「テスト」の段階で、各サービスの開発担当者にて使用箇所を修正して頂きました。(昔からある一部の機能で利用されていました)
The deprecated ASC or DESC qualifiers for GROUP BY clauses are removed. Queries that previously relied on GROUP BY sorting may produce results that differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.
上記のほか、アップグレードにより変更されたパラメータグループの設定値の調査や検討なども行いました。
アップグレード方法
アップグレードの方法は、前回のメジャーバージョンアップ時と同じく、binlog レプリケーションを使った Blue-Green Deployment を採用しました。採用の理由は、実績があり手順が確立できていること、リリース当日のメンテナンス作業の時間を短くできること、切り戻しを容易にできることです。
具体的な手順は以下の記事を参考に検討しました。
最初に検討した手順の大まかな流れは以下のとおりです。
※ テストで不備がみつかり修正したため、実際の手順とは異なります。詳しくは「アップグレード手順のテスト」の項に記載します
- 既存の Aurora MySQL v2 からクローンを作成する
- クローンを v3 へインプレースアップグレードする
- v2 から v3 へレプリケーションする
- v3 へ ALTER TABLE を実行する
- v2 から v3 へのレプリケーションを停止する
- アプリケーションの接続先を v3 へ切り替える
- v3 から v2 へレプリケーションする
手順 1 ~ 4 は事前に実施し、5 ~ 7 をリリース当日のメンテナンス作業時に実施します。そうすることで、リリース当日の作業時間を短くすることができます。
「変更点の影響調査」の項に記載した ALTER TABLE は、手順 4 のタイミングで v3 にだけ実行します。この時点でアプリケーションは v2 へ接続しているため、ALTER TABLE を安全に適用できます。
手順 7 は、アップグレード後に v2 へ切り戻せるようにするため実施します。
なお、RDS がサポートする Blue-Green Deployment の方法も候補にありましたが、切り戻しの容易さから前述の方法をとることにしました。
テスト
開発環境にてアップグレード手順のテストと、アップグレード後のアプリケーションのテストを行いました。
アップグレード手順のテスト
「アップグレード方法」の項で述べた手順で進めましたが、手順 4 の ALTER TABLE 実行後、v2 から v3 へのレプリケーションでエラーが発生してしまいました。
Column 17 of table 'テーブル名' cannot be converted from type 'varchar(765(bytes))' to type 'varchar(1020(bytes) utf8mb4)'
utf8mb4 へ変換する ALTER TABLE を v3 側だけに実行していたため、データ型の不一致が原因のようです。これを許容してレプリケーションできるようにするため、v3 側のパラメータグループで replica_type_conversions を設定するようにしました。
今回は source (v2) よりも target (v3) 側の型が大きいため、非不可逆変換を許可する ALL_NON_LOSSY の値を設定します。設定値の詳細は以下をご参照ください。
設定後、v2 から v3 へのレプリケーションがエラーにならないことを確認できました。
ただここで、手順 7 の v3 から v2 へのレプリケーションにおいても同じ問題に遭遇することが予想できました。型の大小関係が逆になるため、不可逆変換を許可する ALL_LOSSY を設定する必要があります。しかし、値が切り捨てられデータの不整合が生じる可能性があるため、他の方法を検討することにしました。
考えた方法は、v2 切り戻し用のクラスターを別に作成し、クラスター 3 台でレプリケーションする構成です。
具体的な手順は以下になります。
- 既存の Aurora MySQL v2 からクローンを 2 つ作成する (v3 用と切り戻し用)
- 片方のクローンを v3 へインプレースアップグレードする
- v3 から切り戻し用の v2 へレプリケーションする
- v2 から v3 へレプリケーションする
- v3 へ ALTER TABLE を実行する
- v2 から v3 へのレプリケーションを停止する
- アプリケーションの接続先を v3 へ切り替える
切り戻し用の v2 には、v3 からのレプリケーションにより ALTER TABLE が反映されているため、型の不一致が起こりません。そのため、ALL_LOSSY を設定することなくレプリケーションすることができました。
また副産物として、v3 から切り戻し用の v2 へのレプリケーションを事前に実施できるため、リリース当日の作業を 1 つ減らすこともできています。
アプリケーションのテスト
上記の手順で開発環境を v3 へアップグレードした後、各サービスの開発担当者にてアプリケーションのテストを実施して頂きました。テストコードや、前回のバージョンアップ時に作成した実行計画を比較する機能を活用してテストをしています。
テストにより修正した内容の 1 つに、パラメータグループの optimizer_switch の変更があります。
実行結果が変わってしまうクエリが見つかったため、実行計画を確認するなどして調査を進めました。試行錯誤の結果、セミジョイン最適化の duplicateweedout の動きが原因ではないかと考え、開発環境で optimizer_switch の duplicateweedout を off にしたところ、問題が解消しました。
バグレポートをみると、duplicateweedout 含めセミジョイン関連のバグがいくつか報告されていました。ただ今回のテストでは、セミジョインに起因する他の問題は起こらなかったので、duplicateweedout だけ off にする方針にしています。
その他にも問題のあった箇所は、各開発者にて修正をして頂きました。
リリース
テストが完了した後、リリースへ向けた対応を進めました。
まずリリースの前週に「アップグレード手順のテスト」の項に記載した手順の 5 までを実施します。v3 へのインプレースアップグレードは約 15 分、ALTER TABLE の実行は約 15 分で完了し、作業全体は 70 分程で完了しました。
そしてリリース当日のメンテナンス中に手順 6, 7 を実施しました。正確にはレプリケーションの同期確認やアプリのデプロイ作業も含むため、メンテナンス時間は 2 時間設けていましたが、ほぼ予定時間内に作業を完了することができました。
ちなみにレプリケーションの同期確認では、各 DB のレコード件数が一致することの確認をしています。テーブルごとに SELECT COUNT をしているのですが、処理を並列化することにより 10 分程で完了しました。 直列で行なうと 1 時間以上かかることがわかっていたため、作業時間の短縮に繋がりました。
リリース後の修正
v3 へのアップグレード後、v2 へ切り戻すほどの致命的なエラーはありませんでしたが、いくつか問題が発生しました。
ひとつは、パフォーマンスが極端に劣化した SQL の問題です。実行時間が著しく伸び、DB の負荷も増大したため急ぎ修正する必要がありました。
その主な原因は、「テスト」の項でも述べたセミジョインによるものでした。
MySQL 8.0 では内部のオプティマイザに変更が多く、サブクエリを使う SQL において v2 ではセミジョインが使われなくても、v3 だとセミジョインが使われるケースが多くあります。
オプティマイザの不具合なのか、セミジョインを使うと速度が大きく劣化するようなケースにもかかわらず、セミジョインを利用してしまうようなこともありました。
具体的な例を示します。以下は同一の SQL について、セミジョインを on(デフォルト) または off にしたときの、EXPLAIN FORMAT=TREE の実行結果になります。(最初の 2 行のみ抜粋)
optimizer_switch: semijoin=on のケース
-> Aggregate: avg((db_1.table_1.column_1 / (case when (((to_days(db_1.table_2.column_1) - to_days(db_1.table_2.column_2)) / 30) < 1) then 1 else round(((to_days(db_1.table_2.column_1) - to_days(db_1.table_2.column_2)) / 30),0) end))) (cost=7839673608.28 rows=6050893371)
-> Nested loop semijoin (cost=7234584271.20 rows=6050893371)
optimizer_switch: semijoin=off のケース
-> Aggregate: avg((db_1.table_1.column_1 / (case when (((to_days(db_1.table_2.column_1) - to_days(db_1.table_2.column_2)) / 30) < 1) then 1 else round(((to_days(db_1.table_2.column_1) - to_days(db_1.table_2.column_2)) / 30),0) end))) (cost=302837.05 rows=618503)
-> Nested loop inner join (cost=240986.72 rows=618503)
セミジョインを利用する場合だと、rows=6050893371 という内部テーブルができてしまっています。実際にこれが原因で、SQL の実行時間が著しく伸びてしまっていました。
このようなケースに対処するためには、SQL を書き換えたりして局所的にセミジョインを回避する必要がありました。オプティマイズヒントを使って無効化する方法も把握していましたが、フレームワーク経由で SQL が実行される都合上、SQL を書き換えることで対応し、解決することができました。
他には、バッチ処理で以下のエラーが発生した問題がありました。
The table '/rdsdbdata/tmp/#sqlxxx_xxx' is full
これは MySQL 8.0 から一時テーブルの動作が変更されており、それに起因するものでした。リソースの制限値を超えてしまったことによるエラーのため、パラメータグループから temptable_max_ram と temptable_max_mmap の値を調整することで解決することができました。
その他にもいくつかの問題が発生しましたが、内容に応じて各サービスの開発者や SRE チームにて修正を行っております。
リリース後のパフォーマンス
v3 へのアップグレード後、主要サービスの全体的なレスポンスタイムは大きな変化がありませんでした。
先に述べたパフォーマンスが劣化する SQL の影響があり、リリース日は悪化していましたが、修正によりその後は元に近い値となっています。
現状、v3 の仕様や追加機能にあわせたチューニングはしていないので、大きく改善することもなく変化は少なかったのかなと考えています。
まとめ
各チームの開発者とチーム横断で協力して対応した結果、無事に Aurora MySQL v3 へのアップグレードを完了することができました。
本記事が Aurora アップグレードへ取り組む方にとって、少しでもお役に立てれば幸いです。
