Amazon Aurora MySQL 3にアップグレードしました

2024年5月8日 08:49

こんにちは。
エンジニアの矢田です。
先日、クラシコムで利用しているAWSのデータベースサービスであるAmazon Aurora MySQLのバージョンを3にアップグレードしました。
その流れと詰まりポイントを残しておきたいと思います。

動機

今までクラシコムではAurora MySQL2 (MySQL5.7) を利用していました。テックブログを遡ってみるとちょうど2年ほど前にバージョンアップの振り返りの記事を書いていました。

2年前にバージョンアップしたMySQL5.7互換のAurora MySQL 2ですが、2024年10月にAWSのサポートが終了することが発表されています。
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/Aurora.MySQL57.EOL.html
サポート終了後も延長サポート費用を払うことでAurora MySQL 2を引き続き利用することは可能ですが、セキュリティ面でもコスト面でもあまり利用したくはありません。
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/extended-support.html

そのためクラシコムでは2023年11月ごろから段階的にAurora MySQL 3へのアップグレード対応を開始していました。

Aurora MySQL 2の段階での変更追加

Aurora MySQL 3になる上で大きな変更がいくつかあり、そのうちの一つがデフォルトの文字セットと照合順序の変更です。
MySQL5.7までは標準の文字セットと照合順序はutf8とutf8_collation_uiでしたが、MySQL8.0ではutf8mb4とutfmb4_collation_uiに変更になります。これはMySQL互換のAuroraでも同様です。
引き続きutf8を設定することも可能ですが、utf8mb4への変更を行うことになりました。
この文字セットはAurora MySQL 2の状態で設定可能なもののため、他にもAurora MySQL 3への変更があることを考えると分割してリリースした方が安全だろうという考えからAurora MySQL 2の段階で文字セット・照合順序の変更をリリースすることにしました。

文字セット・照合順序の変更

まずシステムの影響範囲の確認を行いました。
utf8mb4系の照合順序を調査・比較・検討を行い、現システムに影響のない utf8mb4_unicode_ci を選択しました。
文字セットと照合順序はALTER文のSQLを発行し、全てのテーブルとデータベースで変更を行いました。またアプリケーションで指定している箇所の変更も行いました。

予約語の確認

Aurora MySQL 3(MySQL8.0)では予約語が大幅に追加されています。
https://dev.mysql.com/doc/refman/8.0/ja/keywords.html#keywords-new-in-current-series
フレームワークを使ってクエリを発行している箇所はエスケープされているので問題にはなりませんが、一部自前でクエリを組み立てている箇所ではテーブル名やカラム名がエスケープされていない箇所がありました。該当の箇所は直接SQLを組み立てないようにフレームワークを利用するように変更しました。

Blue/Green Deploymentの導入

今回のAurora MySQL 3への段階的な切り替えの際、初めてRDSのBlue/Green Deploymentを利用してAuroraクラスターの切り替えを行いました。

今までデータベースの再起動を伴う変更を加えた場合、ダウンタイムが許容できなかったため深夜帯にメンテナンスという形で作業を行っていました。この場合、メンテナンス画面への切り替えや実作業などを含めておおよそ1〜2時間の作業になり、その間サイトは利用できない状況でした。
また、本番作業時はオペレーションミスによる事故を防ぐ目的で作業の確認を行うためエンジニアもペアで作業が必要で生活リズムへの影響があったり、そもそも起きれなくてメンテナンスが行えなかったりするリスクがありました。

Blue/Green Deploymentを行った場合、本番環境への影響は切り替え時に起きるダウンタイムのみで完結します。弊社のシステムの場合だとおおよそ2分ほどのダウンタイムでした。2分程度であればユーザー影響の少ない早朝の時間帯で行って良いというビジネス側のリスク受容があり、今までの深夜帯のメンテナンスよりも作業時間も短く負担の少ない時間に行うことができました。

全体として今回のアップグレードでは3回再起動をすることがありましたが、うち2回はBlue/Green Deploymentを行うことでメンテナンスの負担を減らすことができました。どうしても1回はデータの整合性の問題でBlue/Green Deploymentを利用する選択ができず深夜にメンテナンスを行いましたが、数回に分けてリリースをしたことでリスクを減らすことができ、深夜メンテナンスが一度で済んだため今後もできるだけBlue/Green Deploymentは利用していきたいと思っています。

3アベイラビリティーゾーンへの変更

Blue/Green Deploymentを利用するためにはいくつか必要な設定があり、そのうちの一つはRDSが設置されるサブネットを3アベイラビリティーゾーン（AZ）にする必要があります。
クラシコムでは2AZのみの設定だったため、3AZへの変更を行いました。

binlog_formatの変更

また、3AZへの変更だけでなくバイナリログが出力されている状態でないとBlue/Green Deploymentの利用ができません。そのため、binlog_format をMIXEDに設定する必要がありました。
こちらは変更のための再起動時十数秒のレイテンシーの悪化がありましたがダウンタイムは起こらなかったため、メンテナンスなしで反映しました。

Aurora MySQL 3への切り替え

Aurora MySQL 2の状態で文字セット・照合順序の変更、また追加される予約文字の対応を取り込むことができました。
そのため、Aurora MySQL 3への切り替えの際はその他のAurora MySQL 2で利用できない・確認できない機能に集中して変更確認作業を行うことになりました。

一時テーブルの影響

Aurora MySQL 2からAurora MySQL 3では一時テーブルの挙動が変更になっています。
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/ams3-temptable-behavior.html

他社の事例でMySQL8.0にした際にAuroraのリードレプリカで一時テーブルの変更によって不具合が出る事例が挙げられています。
https://qiita.com/suzukito/items/c7b34be9cff2789e8d84
https://speakerdeck.com/mixi_engineers/reflecting-on-temptable-overflow-in-aurora-mysql-version-3

一時テーブルは集計の並べ替えを行う際や共通テーブルを利用する際など一部のSQLを実行する際に暗黙的に利用されており、決められたメモリーを使い果たすとディスク領域に書き込むようになります。そのディスク領域はローカルストレージを使い果たすと共有ボリュームに書き込むようになりますが、Auroraのリーダーインスタンスでは共有ボリュームに書き込みができないためエラーが発生してしまいます。

クラシコムでは一部最適化されていないSQLクエリを利用している箇所や大きなデータを集計することがあるため、本番相当のデータを確認環境のデータベースに投入し処理件数が多い箇所の動作確認を行いました。

一時テーブルに影響のありそうな箇所の動作確認を行うと挙動に問題があることがわかりました。

WHERE IN句の指定している値が長すぎて処理が遅くなる
- WHERE IN句に渡すIDが range_optimizer_max_mem_sizeを超えるため、フルスキャンが発生しているためクエリの実行時間が長くなってしまっていました
- 根本から解決するためにはクエリを修正する必要があるが、一時的にrange_optimizer_max_mem_size を引き上げることで実行時間を短くすることにしました
実行時間が大幅に遅くなるクエリがある
- Optimizerの挙動が変更になったことでもともと実行時間が遅かったクエリがより遅くなるようになってしまっていました
- indexの作成とクエリの改善によって解消するようにしました

一時テーブルに関してはAuroraインスタンス自体のメモリやディスクに余裕があることから一時テーブルに利用できるメモリとディスクの容量を引き上げることにしました。

  parameter {
    name  = "temptable_max_mmap"
    value = "21474836480" // 20GiB
  }

  parameter {
    name  = "temptable_max_ram"
    value = "3221225472" // 3GiB
  }

また、一時テーブルのメモリーが溢れそうになったことを検知できるように監視をつけることにしました。一時テーブルのメトリクスはSQLで取得することができます。
memory/temptable_physical_ram と memory/temptable_physical_disk が取得できますが、temptable_physical_ramは実際に一時テーブルの作成に利用されたメモリーの量でtemptable_physical_diskは一時テーブルのためにディスクから割り当てられた領域の量です。基本的に大きな一時テーブルが作られない限りメモリー内で一時テーブルが作成されますが、大きな一時テーブルが作成されメモリーから溢れた場合にディスクにも作成されるようになり physical_diskの値が0より大きくなります。

SELECT event_name, CURRENT_NUMBER_OF_BYTES_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name like 'memory/temptable%';

クラシコムでは監視にMackerel、 Auroraの詳細なデータをMackerelに送るためにmaprobeというOSSを利用しています。maprobeでは定期的にコマンドを実行してメトリクスとしてMackerelに送信することができるので、上記のクエリで一時テーブルのデータを取得してMackerelが受け取れるフォーマットに変換して送ることにしました。

#!/bin/bash

set -eu;

db_host="$1";

mysql -h "${db_host}" -P "${DB_PORT:-3306}" \
  -u "${MACKEREL_MYSQL_USERNAME}" -p"${MYSQL_PASSWORD}" --disable-column-names \
  -e "SELECT REPLACE(event_name, '/', '.'), CURRENT_NUMBER_OF_BYTES_USED, UNIX_TIMESTAMP() FROM performance_schema.memory_summary_global_by_event_name WHERE event_name like 'memory/temptable%';" \
  > /tmp/temptable_metric.tsv;

cat /tmp/temptable_metric.tsv;

Mackerelではタブ区切りのデータを受け取ることができるので上記のようなスクリプトを作成しmaprobeに叩かせています。

日付の扱いの変更

Aurora MySQL 3にして動作確認を行っている際、日付まわりでつまづいた箇所がありました。
通常の日付は問題ないのですが、動的に月の頭から最終日までという日付を1日~31日で作っているところがあり、その箇所で動作がうまくいかなくなっていました。
たとえば、2月中に作成されたレコードを取りたい場合に以下のようなSQLを発行していた場合です。

SELECT * 
FROM `test_table`
WHERE
  `created_at` > '2024-02-01 00:00:00'
  AND `created_at` < '2024-02-31 23:59:59';

ALLOW_INVALID_DATES が無効になっている場合、サーバーでは月と日の値が有効である必要があり、それぞれ 1 から 12 および 1 から 31 の範囲内にあるだけではありません。厳密モードが無効になっていると、'2004-04-31' のような無効な日付は '0000-00-00' に変換され、警告メッセージが表示されます。厳密モードが有効なときは、無効な日付によってエラーが発生します。このような日付を許可するには、ALLOW_INVALID_DATES を有効にします。

https://dev.mysql.com/doc/refman/8.0/ja/sql-mode.html#sql-mode-full

ということで実際に発行されたクエリは

SELECT *
FROM `test_table`
WHERE
  `created_at` > '2024-02-01 00:00:00'
  AND `created_at` < '0000-00-00 00:00:00';

となってしまって正しくアプリケーションが動作しない状況になっていました。
これの対応としましては、アプリケーションコードの初期設定で

sql_mode=NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES

を指定しました。Auroraのパラメーターグループでの指定も可能ですが、Laravelの場合全てのsql_modeを上書きして実行されてしまっていたのでアプリケーションコードでの指定が必要でした。(NO_ENGINE_SUBSTITUTION はLaravelのsql_modeのデフォルト値のため設定するようにしています)
ref. https://github.com/laravel/framework/blob/85b28780401dfd37ca1230a30d39494ce642a0ea/src/Illuminate/Database/Connectors/MySqlConnector.php#L164-L175

実装の修正をした方が良い内容ですが、今回はアップグレードを優先しアプリケーションの修正とALLOW_INVALID_DATESの無効化は別途対応としました。

切り替え後の影響

クエリキャッシュの話

Aurora MySQL 3(MySQL8.0)からはクエリキャッシュの機能が廃止されました。その変更自体は知っていたのですが、特に対応できそうになかったためそのままAurora MySQL 3への切り替えを行いました。
その結果、クエリキャッシュが効いていたであろう一部処理の大きなクエリーがスロークエリーとして出てくるようになってしまいました。また、p99やp95のレイテンシーが悪化していることがわかりました。おそらく元々遅かったクエリーがキャッシュが効かなくなったことによってより遅くなったのではと考えられます。

大きいクエリの話

Aurora MySQL 3切り替え前から問題になっていた長すぎるWhere in句が実行された場合にAuroraのCPU使用率が100%近くまで上がってしまう問題が発生しました。
一時テーブルの確認の箇所にも書きましたが、range_optimizer_max_mem_size を引き上げることで検証環境では問題なく見えていましたが本番でAurora MySQL3に切り替えたところフルスキャンが発生してしまう状況になりました。
これに関してはIN句を使わないようクエリを修正する他なく、システムを担当しているチームにクエリの改修をしてもらって解消することができました。

また、Aurora MySQL 3を入れた少し後のタイミングでログの収集のみに利用していたSentryのPerformance Monitoring(APM)を導入しました。これを導入したことで今まで問題になっていなかったスロークエリーがSlackに通知されるようになりより異変に気づきやすくなりました。

まとめ

Aurora MySQL 3にして1ヶ月ほど経過します。いくつか問題は上記のようにありましたが大きな事故はなく過ごせております。クラシコムでは毎週SRE定例でメトリックの確認を行っており、リリース直後はAuroraのメトリックがとんでもないことになるんじゃないかとヒヤヒヤしておりましたが今の所はなんともないようです。
元々遅かったクエリがより遅くなるといった困り事もありましたが、これをきっかけに見直し＆改善をすることができました。引き続きメトリックやSentryの通知に気をつけつつ運用していければと思っております。

クラシコムではインフラの運用およびアプリケーション開発を一緒に進めていける仲間を募集しています。もしAurora MySQL 3の話やバージョンアップの話を少し聞いてみたい話してみたいなと思われた方はぜひ気軽にお話しできればと思います。