SQLクエリの最適化または危険な犯罪者の検索

Appboosterのケーススタディ



私は、Ruby on RailsとPostgresをバックエンドのメインウェポンとして使用しているほとんどすべてのプロジェクトが、開発の速度、コードの可読性/保守性、および本番環境でのプロジェクトの速度の間で絶えず闘っていると信じています。入り口で可読性と作業速度が低下した場合の、これら3頭のクジラのバランスの経験についてお話しします。結局、才能のあるエンジニアたちが私の前に何をしようとしていたかがわかりませんでした。







全体の話はいくつかの部分に分かれます。これは、SQLクエリを最適化するためのPMDSCとは何かについて話し、postgresでクエリパフォーマンスを測定するための便利なツールを共有し、まだ関連のある1つの便利な古いチートシートを思い出させる最初のものです。



さて、しばらくして、「後を見て」、私はこの事件の入り口で、私が成功することをまったく期待していなかったことを理解しています。したがって、この投稿は、経験豊富な開発者ではなく、大胆なSQLでレールを見てきたスーパーシニアよりも、大胆な開発者には役立ちます。

入力データ

Appboosterでは、モバイルアプリを宣伝しています。仮説を簡単に提示してテストするために、いくつかのアプリケーションを開発しています。それらのほとんどのバックエンドはRails APIとPostgresqlです。



この出版物のヒーローは2013年の終わり以来開発中であり、その後、rails 4.1.0.beta1がリリースされました。それ以来、プロジェクトは、Amazon RDSの個別のデータベースインスタンス（4つのvCPUと16 GBのRAMを備えたdb.t3.xlarge）を備えたAmazon EC2の複数のサーバーで実行される完全にロードされたウェブアプリケーションに成長しました。ピーク負荷は25k RPM、平均日負荷は8-10k RPMに達します。



この話は、データベースインスタンス、またはむしろそのクレジット残高から始まりました。







Amazon RDSでPostgresタイプの「t」インスタンスがどのように機能するか：データベースが特定の値を下回る平均CPU消費で実行されている場合、アカウントにクレジットを蓄積します。これにより、インスタンスは高負荷時にCPU消費に費やすことができます-これにより、過払いを節約できますサーバー容量と高負荷に対応するため。 AWSを使用して何をどのくらい支払うかについての詳細は、CTOの記事を参照してください。



ある時点でのローンの残高がなくなりました。ローンの残高にお金を補充できるため、しばらくの間、これはそれほど重要ではありませんでした。月に約20ドルかかります。これは、コンピューティングパワーのレンタルの総コストとしてはあまり目立ちません。製品開発では、ビジネス要件から定式化されたタスクに主に注意を払うのが通例です。データベースサーバーの増加したCPU消費は技術的な負債に適合し、クレジット残高を購入するための小さなコストによって相殺されます。



ある晴れた日、私は毎日の要約に、プロジェクトのさまざまな部分に定期的に現れる「火」を消すのに非常に疲れていると書いていました。これが続く場合、燃え尽きた開発者は時間をビジネスタスクに費やします。同じ日に、私はメインプロジェクトマネージャーに行き、配置を説明し、定期的な火災と修理の原因を調査する時間を求めました。合格した後、私はさまざまな監視システムからデータを収集し始めました。



Newrelicを使用して、1日あたりの合計応答時間を追跡します。画像は次のようになっています







。Postgresが取る応答時間の一部がグラフで黄色で強調表示されています。ご覧のように、応答時間が1000ミリ秒に達する場合があり、ほとんどの場合、応答を検討したのはデータベースでした。したがって、SQLクエリで何が起こっているかを確認する必要があります。

PMDSCは、退屈な SQL最適化ジョブのためのシンプルで簡単な方法です。

それをプレイ！

測定してください！

それを描く！

それを想定してください！

確認してください！

それをプレイ！

おそらく全体の練習の中で最も重要な部分。誰かが「SQLクエリを最適化する」というフレーズを言うと、大多数の人々はむしろあくびと退屈の発作を引き起こします。あなたが「探偵の調査と危険な悪役を探す」と言うとき-それはあなたをより魅力的にし、あなた自身を正しい気分にさせます。したがって、ゲームに参加することが重要です。私は探偵をするのを楽しんだ。データベースの問題は危険な犯罪者か珍しい病気のどちらかだと思いました。そして彼は、シャーロックホームズ、コロンボ中尉、またはドクターハウスとしての役割を想像しました。あなたの好みに合ったヒーローを選んで行ってください！

測定してください！

リクエストの統計を分析するために、PgHeroをインストールしました。これは、pg_stat_statements Postgres拡張機能からデータを読み取る非常に便利な方法です。 /クエリに移動し、過去24時間のすべてのクエリの統計を確認します。デフォルトで[合計時間]列-データベースがクエリを処理する合計時間の割合-容疑者を見つけるための貴重な情報源に従ってクエリを並べ替えます。平均時間-要求が実行された平均回数。呼び出し-選択した期間中に行われたリクエストの数。 PgHeroは、リクエストが1日に100回以上実行され、平均で20ミリ秒以上かかった場合、リクエストが遅いと見なします。重複するインデックスのリストの直後にある、最初のページの遅いクエリのリスト。







リストの最初の1つを取り、クエリの詳細を確認します。すぐに、分析の説明を見ることができます。計画時間が実行時間よりもはるかに短い場合は、このリクエストに何か問題があり、この容疑者に注意を向けています。



PgHeroには独自の視覚化方法がありますが、explain.depesz.comをさらに使用して、explainからデータをコピーしてそこに分析しました。







疑わしいクエリの1つは、インデックススキャンを使用しています。視覚化は、このインデックスが効果的ではなく、弱点であることを示しています-赤で強調表示されています。いいね！私たちは容疑者の足跡を調べ、重要な証拠を見つけました！正義は避けられない！

それを描く！

クエリの問題のある部分で使用される大量のデータを描画してみましょう。インデックスがカバーするデータと比較すると便利です。



少しコンテキスト。アプリケーションでオーディエンスを維持する方法の1つをテストしました-現地通貨を獲得できる宝くじのようなものです。賭けをし、0から100までの数字を推測し、乱数ジェネレータが受け取った数字に最も近い場合は、ポット全体を取ります。私たちはそれを「アリーナ」と呼び、集会を「バトル」と呼びました。







調査時のデータベースには、約50万件の戦闘記録が含まれています。リクエストの問題のある部分では、レートがユーザーのバランスを超えておらず、バトルのステータスがプレイヤーを待っているバトルを探しています。セットの交差部分（オレンジ色で強調表示）が非常に少数のレコードであることがわかります。



リクエストの疑わしい部分で使用されるインデックスは、created_atフィールドで作成されたすべての戦闘をカバーしています。リクエストは、それが40を選択する505330レコードを通過し、505290は排除します。それは非常に無駄に見えます。

それを想定してください！

仮説を立てます。データベースが50万件のレコードから40件を見つけるのに役立つものは何ですか？レートフィールドをカバーするインデックスを作成してみましょう。「プレーヤーを待っています」ステータスの戦闘のみ、部分インデックスを作成します。

add_index :arena_battles, :bet,
          where: "status = 'waiting_for_players'",
          name: "index_arena_battles_on_bet_partial_status"

部分インデックス -条件に一致するレコードにのみ存在します。ステータスフィールドは「プレーヤーを待機中」に等しく、レートフィールドにインデックスを付けます-クエリ条件に正確にあるもの。この特定のインデックスを使用することは非常に有益です。40キロバイトしかかからず、すでにプレイされた戦闘をカバーしていないため、サンプルを取得する必要はありません。比較のために、容疑者によって使用されたindex_arena_battles_on_created_atインデックスは約40 MBを要し、戦闘のテーブルは約70 MBです。このインデックスは、他のクエリで使用されていない場合は安全に削除できます。

確認してください！

新しいインデックスを使用した移行を本番環境に展開し、戦闘によるエンドポイントの応答がどのように変化したかを観察します。







グラフは、移行を展開した時間を示しています。12月6日の夕方に、応答時間が約500ミリ秒から約50ミリ秒に約10倍に減少しました。法廷で容疑者は囚人の地位を受け、現在は刑務所にいる。いいね！

脱獄

数日後、私たちは私たちが早く幸せであることに気付きました。囚人は共犯者を見つけ、脱出計画を開発し実施したようです。







12月11日の朝、postgresクエリスケジューラは、新しく解析されたインデックスを使用することはもはや有益ではないと判断し、古いインデックスを再び使用し始めました。



Suppose itステージに戻りました！ハウス博士の精神に基づいて鑑別診断をまとめる：

• postgres設定を最適化する必要があるかもしれません。
• 多分、postgresをマイナーな用語で新しいバージョンにアップグレードします（9.6.11-> 9.6.15）。
• そしておそらく、もう一度、どのSQLクエリがRailsを形成しているかを注意深く調べますか？

3つの仮説すべてをテストしました。後者は私たちを共犯者の道へと導きました。

SELECT "arena_battles".* 
FROM "arena_battles" 
WHERE "arena_battles"."status" = 'waiting_for_players' 
   AND (arena_battles.bet <= 98.13) 
   AND (NOT EXISTS (
            SELECT 1 FROM arena_participations
            WHERE arena_battle_id = arena_battles.id
              AND (arena_profile_id = 46809)
          )) 
ORDER BY "arena_battles"."created_at" ASC 
LIMIT 10 OFFSET 0

このSQLを一緒に見ていきましょう。ステータスが「プレーヤーを待っています」に等しく、レートが特定の数値以下であるすべての戦場をバトルテーブルから選択します。これまでのところ、すべてが明確です。状態の次の用語は不気味に見えます。

NOT EXISTS (
            SELECT 1 FROM arena_participations
            WHERE arena_battle_id = arena_battles.id
              AND (arena_profile_id = 46809)
          )

存在しないサブクエリ結果を探しています。バトルIDが一致し、参加者のプロファイルがプレイヤーに属しているバトル参加テーブルから最初のフィールドを取得します。サブクエリで記述されたセットを描画してみます。







理解するのは難しいですが、結局、このサブクエリを使用して、プレイヤーがすでに参加しているバトルを除外しようとしました。クエリの一般的な説明を見て、計画時間：0.180ミリ秒、実行時間：12.119ミリ秒を参照します。共犯者を見つけた！



2008年からインターネットで利用されている、私のお気に入りのチートシートの時間です。ここにあります：







はい！クエリが別のテーブルのデータに基づいて特定の数のレコードを除外する必要がある何かに遭遇するとすぐに、ひげとカールのあるこのミームがメモリにポップアップ表示されます。



実際、これは私たちが必要とするものです：







この写真を自分用に保存するか、印刷してオフィスのいくつかの場所に掛けます。



サブクエリをLEFT JOIN WHERE B.key IS NULLに書き換えると、次のようになります。

SELECT "arena_battles".* 
FROM "arena_battles" 
LEFT JOIN arena_participations 
   ON arena_participations.arena_battle_id = arena_battles.id 
   AND (arena_participations.arena_profile_id = 46809)
WHERE "arena_battles"."status" = 'waiting_for_players' 
   AND (arena_battles.bet <= 98.13) 
   AND (arena_participations.id IS NULL) 
ORDER BY "arena_battles"."created_at" ASC
LIMIT 10 OFFSET 0

修正されたクエリは、2つのテーブルで同時に実行されます。「左」の戦闘へのユーザーの参加の記録を含むテーブルを追加し、参加識別子が存在しないという条件を追加しました。受け取ったクエリを分析して説明を見てみましょう：計画時間：0.185ミリ秒、実行時間：0.337ミリ秒。いいね！これで、クエリプランナーは部分インデックスを使用することをためらうことなく、最速のオプションを使用します。逃亡した囚人とその共犯者は、厳格な政権機関で終身刑を宣告された。彼らが脱出するのはもっと難しいでしょう。



概要は簡単です。

• Newrelicまたは他の同様のサービスを使用してリードを見つけます。問題はデータベースクエリにあることに気づきました。
• PMDSCプラクティスを使用してください。これは機能し、いずれにしても非常に魅力的です。
• PgHeroを使用して、容疑者を見つけ、SQLクエリ統計の手がかりを調査します。
• Explain.depesz.comを使用してください-ここでExplain分析クエリを読むのは簡単です。
• リクエストが正確に何をしているかわからない場合は、大量のデータを描画してみてください。
• 別のテーブルにないものを探しているサブクエリを見つけたとき、頭の周りにカールがあるタフな男を考えてください。
• 探偵を演じてください、あなたはバッジを得るかもしれません。