Zenn
e-dash Tech BlogPublicationへの投稿
🐠

自分の書いたコードがデプロイされたらWebサイトが重くなった！どうする！？（バックエンドエンジニア編）

jellyfish1129
2024/12/25に公開
MySQL
tech

はじめに

こちらはe-dash advent calendar 2024の25日目の記事です。

Merry X'mas！
はじめまして、e-dashのバックエンドエンジニア、jellyfish1129です。

自社のWebアプリケーションが重くなりました！

素敵なクリスマスプレゼントですね。開発環境での問題のためステークホルダーへの連絡は不要としましょう。

「アプリケーションが重くなった」場合、前提や状況に応じて動き方が全く異なります。ここでは、おおよそ以下のような仮定をします。

  • あるコミットがマージされたら重くなった
  • データの集計結果を表示するページが重い
  • 大規模トラフィックが飛び交う訳ではない
  • データ量はまあ多い
  • ネットワーク、インフラ(AWS等)、フロントエンドの話は考えない
  • コミット内容から、「バックエンド・DB間のやり取りにボトルネックがある」と仮説を立ててスタートする

それでは、実際に私が業務で遭遇した1シーンを題材に、アプローチの例を記述していきます。

初動:計測して、原因を特定する

さて、パフォーマンス劣化が起きた際の原則、「推測するな、計測せよ」に則り、まずはDatadogで重くなっている箇所を探しました。この過程はお見せできないのですが、重いEndpointを特定し、追跡を行ってゆきます。追跡調査の結果、以下のクエリが重い原因になっていることが分かりました。

SELECT
    l.*,
    JSON_ARRAYAGG(
        CASE
            WHEN s.id IS NOT NULL THEN JSON_OBJECT(
                'id', s.id,
                'name', s.name,
                'class_name', s.class_name
            )
            ELSE NULL
        END
    ) AS enrolled_students
FROM lectures l
LEFT JOIN lecture_student_mapping lsm ON l.id = lsm.lecture_id
LEFT JOIN students s ON lsm.student_id = s.id AND s.deleted_at IS NULL
WHERE l.deleted_at IS NULL AND [他の絞り込み条件]
GROUP BY l.id;

講義(lectures)というデータを一覧で取得するのですが、講義を受講する生徒(students)の情報を内包した状態で取得する処理を行っています。（架空のテーブルに置き換えています）

各テーブル間の関係性は以下です。各テーブルに、~10000程度のデータが入っています。

イメージがしやすいように、結果をJSON Response-likeにしたものを示します。

[
    {
        "id": 1,
        "title": "Mathematics 101",
        "desciprion": "Basics of Mathematics",
        "year": 2024,
        "type": 1,
        "deleted_at": null,
        "enrolled_students": [
            {
                "id": 101,
                "name": "Alice",
                "class_name": "Algebra Basics"
            },
            {
                "id": 102,
                "name": "Bob",
                "class_name": "Geometry Foundations"
            }
        ]
    }
]

では、重いクエリを計測してみましょう。EXPLAIN ANALYZEを使用することで、コスト・実行時間を分析することができます。ここで注意して頂きたいのは、コストとはDB側でクエリ実行前に、複数の実行パスに対して必要なリソースを計算した「予測値」に過ぎないことです。大切なのは実行時間です。

さて、結果が以下のようになったとしましょう。（localでそれっぽい感じに再現しています）

結果を眺めてみると、2種類の処理が重い原因として考えられます。

  1. Nested Loop Left Join
  2. Aggregate

尚、最初に疑うのはインデックスの有無です。適切にインデックスを貼っていない場合、Nested Loop Joinはかなり重い処理になってしまいます。
ここではインデックスは適切に貼られていたと仮定して先に進めていきます。

Nested Loop Left Join

Left Join(Outer Join)は危険な代物です。特に、複数テーブルを跨る場合、SELECTの結果が急激に増えてしまう可能性があります。
まずは本当にLeft Joinで良いのかを検討しましょう。LEFT JOINを使用すると、以下のようにenrolled_studentsが空のものも結果に入ってきます。

[
    {
        "id": 1,
        "title": "Mathematics 101",
        "desciprion": "Basics of Mathematics",
        "year": 2024,
        "type": 1,
        "deleted_at": null,
        "enrolled_students": [
            {
                "id": 101,
                "name": "Alice",
                "class_name": "Algebra Basics"
            },
            {
                "id": 102,
                "name": "Bob",
                "class_name": "Geometry Foundations"
            }
        ]
    },
    {
        "id": 2,
        "title": "History of Art",
        "desciprion": "Introduction to Art History",
        "year": 2024,
        "type": 2,
        "deleted_at": null,
        "enrolled_students": [] ←ここが空のデータは、実は不要だった
    }
]

ところが、アプリケーションロジックをよく調べていくと、enrolled_studentsが空のデータは使用されないため、不要であることが分かりました。
したがって、このLEFT JOINは通常のINNER JOINに変更しても良さそうです。

SELECT
    l.*,
    JSON_ARRAYAGG(
        CASE
            WHEN s.id IS NOT NULL THEN JSON_OBJECT(
                'id', s.id,
                'name', s.name,
                'class_name', s.class_name
            )
            ELSE NULL
        END
    ) AS enrolled_students
FROM lectures l
JOIN lecture_student_mapping lsm ON l.id = lsm.lecture_id
JOIN students s ON lsm.student_id = s.id AND s.deleted_at IS NULL
WHERE l.deleted_at IS NULL AND [他の絞り込み条件]
GROUP BY l.id;

では、EXPLAIN ANALYZEをしてみましょう。

コストは激減し、実行時間も少し減りましたが、大幅に削減された訳ではありません。

一旦、次に進んでみましょう。

Aggregate

JSON_ARRAYAGG関数とは、集約関数です。集約関数それ自体にはインデックスは効きません。しかし、WHEREやGROUP BYに使用されるカラムにインデックスが適切に貼られている場合、性能改善が期待できます。今回はインデックスは大丈夫だと仮定しましょう。

さて、当時の私はここで行き詰まったので、手掛かりがないかAWSコンソールからRDSのモニタリングを見に行きました。

「あれ、Disk I/Oが多くない・・・？」
集約関数にてGROUP BYが実行される際、データベース内部ではソート処理が行われています。どうやら、この時に一時メモリに乗り切らなくなっていたようです。MySQLにはsort_buffer_sizeというパラメータがあるので、これを増やせば上手くいくかもしれません。

しかし、一部の処理が重いからとグローバルパラメータを変更するのは危険です。SREチームのメンバーと話し合いの後、sort_buffer_sizeを増やすことになりましたが、パラメータの変更には慎重であることが望ましいでしょう。

結局、DBのメモリが原因だった

sort_buffer_sizeを増やすことで事態は収まりました。レスポンスタイムについても、QAチームと話し合いの上で許容範囲内という結論を出しています。

今回、メモリスワップが重い原因だったということですが、これまでのアプローチを振り返ってみます。

  1. まず、Datadogを使用してボトルネックを調べた（今回は特定のクエリが重かった）
  2. EXPLAIN ANALYZEを利用してクエリの実行時間を調べ、重い処理をピックアップした
  3. 2でピックアップした箇所を、1つ1つ面倒を見ていく。

また、個人的に大事だったなと思う点をピックアップします。

  • アプリケーションロジック的に本当にこの処理は必要なのかを考える
  • 詰まったら、別の角度から手掛かりを探しにいく
  • 変更による影響箇所が大きい場合、事前にチームメンバーと相談する

恒久対応と取捨選択

応急処置は完了しましたが、根本的にはどのような対処が考えられるでしょう？

  • そもそもデータ量が多いテーブル同士を結合しているのが問題。また、データ量が今後も増加していくといずれ再発する。
    → 最初の段階でより絞り込めるように、アプリケーションロジックを変更できないか？
  • 講義と受講生徒というテーブルには頻繁な変更は入らない。結果をキャッシュしておきたい。
    → Redisなどの導入はできないだろうか？

その一方で、これらの改修・実装に工数も必要です。スタートアップ企業としてスピード感を持った開発をしてゆくには「70,80点で妥協」というスタンスも、時には必要になるでしょう。

それでは、来年も良いエンジニア生活が送れることと、良いプロダクトを沢山生み出せることを祈って。

良いお年を！

e-dash Tech Blog
e-dash Tech BlogPublication

Discussion