DatadogのDatabase Monitoringを使って非効率なSQLを見つける話

はじめに

こちらの記事は、アソビュー！ Advent Calendar 2024の17日目（裏面）です。
アソビューでSREを担当している鈴木です。

SREの大切なミッションの一つに可用性を保証するというミッションがあります。その可用性をすこしでも向上させるためにDatadogのDatabase Monitoringという機能を使って、改善した話を書きたいと思います。

SREという役割の大事なミッションの一つとして、障害の種を検出してそれを除去していくという活動を日々行うというものがあります。これは、一般的な世界でわかりやすい例で例えるならば、健康診断などで病気を早期発見して、より長く健康状態を保つという行為に似ております。健康診断では

数多くの計測手法を確立
定期的に計測
異常を検知
精密検査を実施して、医者が状況をより詳しく見ていく

という流れで病気の発見を行っていくと思います。SREでも、本質的には上と同じ流れをすることにより、システムの安定稼働を脅かす素因要素を発見し、それを解消していくということを日々実行することが大事になります。

今回のブログでは、その一側面である、悪いSQLを検出していく具体的な方法を紹介していきます。

はじめに
背景
Database Monitoringのセットアップ
DatadogのDatabase Monitorinigの利用した機能の紹介
どのように利用して課題解決したか
実施前と実施後の変化について
現状の課題
さいごに

背景

アソビュー！では、施設やイベントがテレビで紹介されたり、人気チケットの発売があったりすると一時的に高負荷になることがあります。当社では、データベースはAurora Serverlessを利用しているものの、急激に負荷が増加する場合には、スケールの調整が間に合わずにデータベースに負荷がかかってしまい、一時的に繋がりにくい状態になるというケースがありました。

課題としては、半年に一度くらい現実的に発生しうる程度の負荷の急な増加に対しても、Aurora Serverlessの対応可能な範囲に収めて、サービスの安定稼働を実現したいという課題が有りました。

Database Monitoringのセットアップ

Aurora MySQLの場合のセットアップ手順の概略は、こちらのページの手順に従います。

大雑把に言うと、下記の3ステップでセットアップが完了します。

パラメータストアが前提条件を満たしていることを確認
各Databaseに、ユーザ、権限、スキーマ、トリガ、プロシージャなどを作成する
kubernetesの場合にはサービスアノテーションを付けることにより監視対象のインスタンスを定義する

Database Monitoringの詳細なセットアップの手順は、公式の手順を参照してください。

DatadogのDatabase Monitorinigの利用した機能の紹介

Database Monitoringをセットアップすると、こちらのFindy Toolsの記事にも書きましたが、SQLの実行計画などができたりします。それ以外にも下記の指標は非常に役に立ちます。今回は、Query Metricsのページにでてくる指標について着目していきたいと思います。

Query Metricsのページは下記のような構成になっています。

負荷懸念のあるクエリを抽出するために、当社では、下記の指標に着目することにしました。

TOTAL DURATION：こちらは、単純に総実行時間です
ROW SCANNED per SENT：1度のSQL実行時にスキャンされた行の数です。
NO INDEX USED：SQL実行時にインデックスが利用されない総数になります。
NO GOOD INDEX：最適ではないインデックスが利用された総数になります。
SELECT FULL JOIN：FULL スキャンされた総数になります。

どのように利用して課題解決したか

上記の指標を使って、負荷懸念のあるSQLというのを定義してみました。そして、下記の条件に一致するクエリは、高負荷時にDBにダメージを与える可能性が高いということで、検出されたらすぐに修正をしていくという方針を定めました。

１つのSQLの1日の総実行時間が24hを超えない(TOTAL DURATIONを1日の範囲で確認する)

1日の総実行時間が24hを超えているということは、常にそのクエリはDB上の１CPUを利用しているということを意味しています。このようなクエリは、負荷が高まった時には、より多くのCPUを利用することになり、高負荷時にDBのCPUを使い切ってしまうリスクがあります。

１つのSQLのピーク5分の間の総実行時間が5分を超えない(TOTAL DURATIONを5分の範囲で確認する)

負荷のスパイクというのは、大きな波もあれば、中波や、小波もあります。こちらは、中波が来たときとかに、この基準を満たすSQLが抽出されることがあります。こちらも上記と同様、中並みや小波でこの程度のラインを超えてくる場合には、大きな波に耐えられないリスクがあります。

ROW SCANNED per SENTが１０M超えかつ、１日の実行回数が１００回を超えているもの

NO INDEX USEDが４M超え

NO GOOD INDEXが１以上かつ総実行時間が３０分を超える

SELECT FULL JOINが４M超えのもの

後半の４つは、いずれも適切なIndexが存在してないか、行のスキャンが多くなっているクエリを抽出しています。これをDBの可用性に影響のない範囲で実行を許容したものとして、こちらの数値を適用しています。

これらの基準は、実際に過去の高負荷時にDBに負荷をかけ不安定にさせたSQLの実測値をベースに、その過去にはどういう状態だったかというのを見ながら定めたものになります。

例えば、最初の項目については、下の図のようにSQLの実行時間の多い順にソートができますので、これをチェックして、問題を起こしそうなクエリがないかどうかをチェックしておきます。

実施前と実施後の変化について

実施前には、高負荷になったときにDBのレイテンシーの悪化や一時的なエラーの増加などの事象が発生してましたが、こちらの基準を満たすクエリをない状態で運用してからは、高負荷時においても、DBのレイテンシーの悪化や一時的なエラーの増加なしに安定して稼働するようになりました。

現状の課題

現状の課題としては、このメトリックスがAPIなどで抜けず、自動化ができてないことです。現在は、手動で上記の項目を週に一度の割合で目視チェックして、データベースに負荷懸念がないかどうかというのを確認してますが、負荷が定常的にかかってしまうので、なんとか自動化していきたいです。

さいごに

アソビューでは、「生きるに、遊びを。」を実現するための良いプロダクトを世の中に届けられるよう共に挑戦していく様々なエンジニアを募集しています。弊社のSREに興味のある方もぜひ一度お話しできればと思います。

カジュアル面談のご希望も随時お受けしておりますので、お気軽にエントリーください！お待ちしております。