note の Aurora MySQL を v2 から v3 へアップグレードしました
note ではメインデータベースとして Aurora MySQL を採用し、日々発生する膨大なトラフィックを処理しています。Aurora MySQL v2 (MySQL 5.7 互換) の標準サポートは2024/10/31 に終了するため、これを機に v3 (MySQL 8.0 互換) へのアップグレードを行いました。
アップグレードは無事に完了しましたが、いくつかの問題にも直面しました。これらを共有することで、これからアップグレードを検討している方へ参考になればと思います。
事前に検討した課題
アップグレード後に致命的な問題が起きたらどうするか
v3 へのアップグレード後に v2 へ切り戻すことは容易ではなく、スナップショットなどからの復元が必要になります。データをロールバックすることになるため、ユーザ影響が極めて大きく避けたい事態です。
そのため、基本的に切り戻しはできないという前提でアップグレード計画を進めました。
SELECT や COUNT クエリ発行時の速度劣化
バッチ処理で発行される重い SELECT クエリ(数十分以上要するもの)が 15 〜 30 % 程度の速度劣化することが検証環境で確認できました。これは特定の構文を含むクエリというわけではなく、重い SELECT クエリが軒並み速度劣化したという検証結果でした。
またバグレポートに挙がっているように、COUNT クエリの速度劣化も再現しました。
パラメータチューニングや、AWS サポートへの問い合わせも試みましたが解決には至らなかったため、これらは可能な限りクエリを最適化するようにしました。
幸い API 経由でリアルタイム発行されるクエリ(〜数百 ms 程度)は検証環境で明確な速度差はありませんでした。リアルタイム性を求められるクエリが劣化するのであればアップグレードを延期するしかないと考えていたので、これに関しては良かったです。
一部クエリのパフォーマンス劣化がわかっている中でアップグレードするのは苦渋の判断と言えますが、今回該当したクエリの速度劣化によるユーザ影響は小さかったため、許容範囲内であることの合意を得て進めることにしました。
アップグレード後に直面した課題
IN 句を含むクエリがフルスキャンになってしまう
IN 句を含むクエリが、インデックスを使わずにフルスキャンとなりスロークエリを頻発するようになりました。以下のようなごく素朴なクエリでも発生していました。
SELECT * FROM items WHERE user_id IN ( ? )
IN 句に大量の件数を指定した場合、範囲アクセスで使うメモリが 「range_optimizer_max_mem_size」の値を超えるとフルスキャンになることがあります。
この挙動は v2 の頃からあったため対処はしていたのですが、アップグレードによって「range_optimizer_max_mem_size 」のメモリを越えるケースが出るようになってしまったようです。
「range_optimizer_max_mem_size」 の値を十分大きくすることで解消しました。設定値の見積もりは、上記リンクのドキュメント内に記載があるので参考にしました。IN 句を含むクエリを発行する際は、件数の閾値を超えたら適切な単位に分割することも大事です。
特定のクエリで ICP(インデックスコンディションプッシュダウン)による最適化が行われなくなった
一部のクエリにおいて、ICP による最適化が行われずに実行速度が極端に遅くなっているものがありました。
どういうことか少々わかりにくいので、具体例を抜粋します。
テーブルに人とそのアドレスに関する情報が含まれており、テーブルに INDEX (zipcode, lastname, firstname) として定義されたインデックスがあるとします。 個人の zipcode 値がわかっているが、姓がわからない場合は、次のように検索できます:
SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';
MySQL はインデックスを使用して、zipcode='95054' を持つ人をスキャンします。 2 番目の部分 (lastname LIKE '%etrunia%') を使用してスキャンする必要がある行数を制限することはできないため、インデックス条件プッシュダウンを使用しない場合、このクエリーでは、zipcode='95054'を持つすべてのユーザーの完全なテーブルの行を取得する必要があります。
インデックス条件プッシュダウンでは、MySQL はテーブルの行全体を読み取る前に lastname LIKE '%etrunia%'部分をチェックします。 これにより、zipcode 条件に一致するが lastname 条件に一致しないインデックスタプルに対応する完全な行の読取りが回避されます。
ICP による最適化がない場合に対象行数が膨大になるクエリがあり、それが大幅に速度劣化をしていました。
最適化が行われているかどうかは実行計画の Extra カラムに 「Using index condition」が表示されているかどうかで確認ができます。v2 と v3 でクエリ、データが同じ状況であったにもかかわらず実行計画に差が出たのはオプティマイザの挙動が変わった影響かと思います。解決として ICP に頼らずに適切なインデックスを追加することで対応を行いました。
Timeout exceeded in regular expression match エラーが出るようになった
パターンマッチを含むクエリで「Timeout exceeded in regular expression match」エラーが発生しました。
このエラーは MySQL 8.0 で導入された 「regexp_time_limit」 の影響で、パターンマッチの評価が 「regexp_time_limit」 に指定した時間を越えるとエラーになります。0 を指定して無効化する(または値を十分大きくする) ことで解消しました。
GROUP BY を含むクエリ結果の順序が変わってしまった
GROUP BY カラムに対する暗黙ソートが廃止されたため、結果の順序が保証されなくなりました。順序保証が必要な処理において対応が必要ですが、クエリ自体はエラーにならないため気づきにくく対応漏れがありました。順序保証が必要な処理には、それを検証する自動テストをしっかり書いておくようにしたいですね。
アップグレード後のメトリクス変化
まずは更新系クエリがメインの Writer インスタンスの AAS(Average Active Sessions) の変化です。AAS は DB に存在するアクティブなセッション数を表し、負荷の継続的な監視で利用されるメトリクスです。赤線がアップグレード日時の境界線です。
負荷が下がりスループットが改善していることが確認できました。
次に Reader インスタンスです。
アップグレード直後は先述の問題があったため大きく悪化しています。問題が落ち着いた後でもピーク時で 1.5 倍近く AAS の値が増加しており、負荷が上昇していることがわかります。CPU 使用率は v2 比で増加し、反対にメモリ使用率は下がっていました。重い SELECT クエリは Reader インスタンスに集中して発行しているため、 Writer とは対象的な変化でした。
次に API 全体の平均レスポンスタイムです。リアルタイムに発行されるクエリでは目立つ劣化はなく、API レスポンスタイムへの影響は特に見られませんでした。
まとめ
オプティマイザによる最適化で乗り切っていた部分や、巨大テーブルを定期的に集計する重いクエリが残っていることが、課題として浮き彫りになりました。
結果を見ると v3 でパフォーマンスが悪化した、というように見えますが、今までパフォーマンスの悪いクエリを MySQL がよしなに最適化していたという見方もできるかと思いました。所感ですが、v3 では素直に良くないクエリは良くないパフォーマンスとして現れるようになったので、これまでより予想しやすい挙動になったように思いました。
note の会員登録者数は 770 万を超え、データ量、トラフィック共にますます膨大になってきています。
累積する巨大テーブルへのアプローチや集計処理の最適化、といったところが大きな課題であるということを改めて実感しました。引き続き、安定したサービスを提供できるように課題へ取り組んでいきたいと思います。
この記事が気に入ったらサポートをしてみませんか？