PostgreSQL クエリの一括最適化。 キリル・ボロビコフ (テンソル)

レポートでは、これを可能にするいくつかのアプローチが紹介されています。 XNUMX 日に数百万件の SQL クエリが発生する場合の SQL クエリのパフォーマンスを監視する、監視対象の PostgreSQL サーバーは数百台あります。

このような大量の情報を効率的に処理できる技術ソリューションは何でしょうか?また、これによって一般の開発者の作業がどのように楽になるのでしょうか?

誰が興味を持っていますか? 特定の問題の分析とさまざまな最適化手法 SQL クエリと PostgreSQL での典型的な DBA の問題の解決 - また、 一連の記事を読む このトピックについて


私の名前はキリル・ボロビコフです。代表を務めます。 テンソル会社。 具体的には、社内でデータベースを扱うことを専門としています。

今日は、単一のクエリのパフォーマンスを「個別に抽出」する必要がなく、問題をまとめて解決する場合に、クエリを最適化する方法について説明します。 何百万ものリクエストがあり、いくつかを見つける必要がある場合 解決へのアプローチ この大きな問題。

一般に、XNUMX 万のクライアントにとって Tensor は次のとおりです。 VLSI は私たちのアプリケーションです：企業のソーシャルネットワーク、ビデオコミュニケーションのソリューション、社内外のドキュメントフロー、会計と倉庫の会計システム...つまり、100以上の異なる企業が統合された経営管理のための「メガコンバイン」です。内部プロジェクト。

すべてが正常に動作し、開発できるようにするために、当社は全国に 10 か所の開発センターを設けており、さらに多くの開発センターを設置しています。 1000人の開発者.

当社は 2008 年から PostgreSQL を使用しており、クライアント データ、統計データ、分析データ、外部情報システムからのデータなど、処理するデータを大量に蓄積してきました。 400TB以上。 本番環境だけでも約 250 台のサーバーがあり、合計で約 1000 台のデータベース サーバーが監視されています。

SQL は宣言型言語です。 何かが「どのように」機能するかではなく、「何を」達成したいかを説明します。 DBMS は、JOIN の作成方法、つまりテーブルの接続方法、課す条件、インデックスを通過するものと通過しないものをよく知っています。

一部の DBMS は、「いいえ、この XNUMX つのテーブルをこれこれのキューに接続してください」というヒントを受け入れますが、PostgreSQL ではこれができません。 これは、主要な開発者の意識的な立場です。「開発者に何らかのヒントを使用させるよりも、クエリ オプティマイザーを完成させたいと考えています。」

しかし、PostgreSQL は「外部」による自身の制御を許可していないという事実にもかかわらず、完全に許可しています。 彼の中で何が起こっているか見てみましょうクエリを実行するときと、どこで問題が発生したか。

一般に、開発者 (DBA にとって) は通常、どのような典型的な問題に直面しますか? 「ここで私たちはその要求を満たしました、そして、 私たちにとってはすべてが遅い、すべてがハングアップしています、何かが起こっています...何らかのトラブルが発生しています!」

理由はほとんど常に同じです。

• 非効率的なクエリアルゴリズム
  開発者: 「今、JOIN 経由で SQL で 10 個のテーブルを彼に与えています...」 - そして、彼の条件が奇跡的に事実上「解きほぐされ」、すべてをすぐに取得できることを期待しています。 しかし、奇跡は起こらず、このような変動性のあるシステム (10 つの FROM に XNUMX 個のテーブル) では常に何らかのエラーが発生します。 [記事]
• 無関係な統計
  この点は特に PostgreSQL に非常に関連しており、大規模なデータセットをサーバーに「注ぎ」、リクエストを行うと、タブレットが「セックス」されます。 昨日は 10 件のレコードがあり、今日は 10 万件のレコードがありましたが、PostgreSQL はまだこれを認識していないため、PostgreSQL にそれを伝える必要があります。 [記事]
• リソースに「接続」する
  ディスク、メモリ、プロセッサのパフォーマンスが十分でない弱いサーバーに、大規模で負荷の高いデータベースをインストールしました。 それがすべてです...どこかにパフォーマンスの上限があり、それを超えるとジャンプできなくなります。
• ブロッキング
  これは難しい点ですが、さまざまな変更クエリ (INSERT、UPDATE、DELETE) に最も関連しています。これは別の大きなトピックです。

計画を立てる

...その他すべてについては、 計画が必要です！ サーバー内で何が起こっているかを確認する必要があります。

PostgreSQL のクエリ実行プランは、テキスト表現でのクエリ実行アルゴリズムのツリーです。 まさにプランナーが分析した結果、最も効果的であると判明したアルゴリズムです。

各ツリー ノードは、テーブルまたはインデックスからのデータの取得、ビットマップの構築、XNUMX つのテーブルの結合、結合、交差、または選択の除外などの操作です。 クエリを実行するには、このツリーのノードをたどる必要があります。

クエリ プランを取得するには、次のステートメントを実行するのが最も簡単な方法です。 EXPLAIN。 すべての実際の属性を取得するには、つまりベースに対して実際にクエリを実行するには - EXPLAIN (ANALYZE, BUFFERS) SELECT ....

悪い点: 実行すると、「今ここ」で実行されるため、ローカル デバッグにのみ適しています。 データ変更の強いフロー下にある高負荷のサーバーを例にすると、次のようになります。 ここでは実行が遅くなります夏 リクエスト。" XNUMX 分、XNUMX 時間前、実行中にこのリクエストをログから取得し、サーバーに戻している間に、データセット全体と統計が変更されました。 デバッグのために実行すると、すぐに実行されます。 そして、あなたはなぜ、なぜ、理解できないのですか было ゆっくり。

リクエストがサーバー上で実行された瞬間に何が起こったのかを正確に理解するために、賢い人々は次のように書きました。 auto_explain モジュール。 これは、最も一般的な PostgreSQL ディストリビューションのほぼすべてに含まれており、構成ファイルで簡単にアクティブ化できます。

一部のリクエストが指定された制限を超えて実行されていることが判明した場合は、実行します。 このリクエストの計画の「スナップショット」を作成し、それらをまとめてログに書き込みます.

今ではすべてがうまくいっているようです。ログに行き、そこを見てみます...[テキストフットクロス]。 しかし、実行に 11 ミリ秒かかったという点では、これが優れた計画であるという事実以外には何も言えません。

すべてがうまくいっているように見えますが、実際に何が起こったのかは何も明らかではありません。 一般的な時間以外には、実際には何も見えません。 なぜなら、そのようなプレーンテキストの「子羊」を見ることは、一般に視覚的ではないからです。

しかし、それが明白でなくても、不便であっても、もっと根本的な問題があります。

• ノードが示すのは、 サブツリー全体のリソースの合計 彼の下で。 つまり、その下にネストされた条件がある場合、この特定のインデックス スキャンに費やされた時間を単に調べることはできません。 内部に「子」や条件変数、CTE があるかどうかを動的に確認し、これらすべてを「心の中で」差し引く必要があります。
• XNUMX点目：ノードに表示される時刻は 単一ノードの実行時間。 たとえば、テーブル レコードを複数回ループした結果としてこのノードが実行された場合、プラン内のループ数 (このノードのサイクル) が増加します。 ただし、アトミック実行時間自体は計画上同じままです。 つまり、このノードが合計でどのくらいの時間実行されたかを理解するには、もう一度「頭の中で」何かを掛ける必要があります。

このような状況では、「誰が最も弱いリンクなのか？」を理解してください。 ほぼ不可能である。 そのため、開発者自身も「マニュアル」にこう書いています。 「計画を理解するには、学習し、経験しなければならない技術です...」.

しかし、当社には 1000 人の開発者がおり、この経験を各人に伝えることはできません。 私も、あなたも、彼も知っていますが、向こうの誰かはもう知りません。 おそらく彼は学ぶだろうし、学ばないかもしれないが、彼は今働く必要がある - そして彼はどこでこの経験を得るのだろうか?

計画の視覚化

したがって、これらの問題に対処するには、次のことが必要であることに気づきました。 計画を適切に視覚化する. [記事]

私たちはまず「市場を調べて」みました。インターネットで何が存在するのか見てみましょう。

しかし、多かれ少なかれ開発中の比較的「ライブ」なソリューションはほとんどないことが判明しました。文字通り、次の XNUMX つだけです。 Explain.depesz.com ヒューバート・ルバチェフスキ著。 「フィード」フィールドに計画のテキスト表現を入力すると、解析されたデータを含む表が表示されます。

• ノード自体の処理時間
• サブツリー全体の合計時間
• 統計的に予想された、取得されたレコードの数
• ノード本体自体

このサービスには、リンクのアーカイブを共有する機能もあります。 あなたはそこに自分の計画を投げ込み、こう言いました。「ねえ、ヴァシャ、ここにリンクがあります。そこに何か問題があります。」

しかし、小さな問題もあります。

まず、膨大な量の「コピペ」。 丸太の一部を取り出し、そこに突き刺すのを、何度も何度も繰り返します。

第二に、 読み取られたデータ量の分析はありません — 出力するのと同じバッファ EXPLAIN (ANALYZE, BUFFERS)、ここでは見られません。 彼は単にそれらを分解し、理解し、それらを扱う方法を知りません。 大量のデータを読み取るときに、ディスクとメモリ キャッシュの割り当てが間違っている可能性があることに気づいた場合、この情報は非常に重要です。

XNUMX 番目のマイナス点は、このプロジェクトの展開が非常に弱いことです。 コミットは非常に小さく、XNUMX か月に XNUMX 回程度が適切で、コードは Perl です。

しかし、これはすべて「歌詞」であり、これで何とかやっていけるのですが、このサービスから大きく遠ざかったことが XNUMX つあります。 これらは、Common Table Expression (CTE) および InitPlan/SubPlan などのさまざまな動的ノードの分析におけるエラーです。

この図を信じるのであれば、個々のノードの合計実行時間は、リクエスト全体の合計実行時間よりも長いということになります。 それは簡単です - この CTE の生成時間は CTE スキャン ノードから差し引かれませんでした。 したがって、CTE スキャン自体にかかった時間についての正確な答えはもはやわかりません。

その後、自分たちで独自のコードを作成する時期が来たことに気づきました。万歳！ すべての開発者はこう言います。「今度は自分たちで作成しましょう。とても簡単です!」

私たちは Web サービスに典型的なスタックを採用しました。Node.js + Express をベースにしたコアで、美しい図を作成するために Bootstrap と D3.js を使用しました。 そして私たちの期待は完全に的中し、最初のプロトタイプを 2 週間で受け取りました。

• カスタムプランパーサー
  つまり、PostgreSQL によって生成されたプランから任意のプランを解析できるようになりました。
• 動的ノードの正しい分析 - CTE スキャン、InitPlan、SubPlan
• バッファー分布の分析 - データ ページがメモリから読み取られる場所、ローカル キャッシュから読み取られる場所、ディスクから読み取られる場所
• 明確になった
  これは、ログをすべて「掘る」のではなく、画像内で「最も弱いリンク」をすぐに確認できるようにするためです。

構文の強調表示が含まれた、次のような結果が得られました。 しかし、通常、開発者は計画を完全に表現したものではなく、より短いものを使用しています。 結局のところ、すでにすべての数値を解析して左右に投げており、中央には、それがどのような種類のノードであるかを示す最初の行だけが残されています: 何らかの符号によると、CTE スキャン、CTE 生成、または Seq スキャン。

これは私たちが呼んでいる省略表現です 計画テンプレート.

他に便利なものは何でしょうか？ 合計時間のどの割合がどのノードに割り当てられているかを確認し、横に「貼り付ける」だけで便利です。 円グラフ.

ノードを指して見てみると、Seq Scan にかかった時間は合計の 3 分の 4 未満で、残りの XNUMX/XNUMX が CTE Scan にかかっていることがわかりました。 ホラー！ これは、クエリで CTE スキャンを積極的に使用する場合の CTE スキャンの「発射速度」に関する小さなメモです。 これらはそれほど高速ではありません。通常のテーブル スキャンよりも劣ります。 [記事] [記事]

しかし、通常、そのような図は、セグメントをすぐにポイントして、たとえば、半分以上の時間で一部の Seq Scan が「食べた」ことがわかると、より興味深く、より複雑になります。 さらに、内部にはある種のフィルターがあり、それに応じて多くのレコードが破棄されました...この写真を開発者に直接投げて、次のように言うことができます。 考えてみてください、何かが間違っています!」

当然のことながら、いくつかの「熊手」が関係していました。

私たちが最初に遭遇したのは丸めの問題でした。 計画内の個々のノードの時間は 1 μs の精度で示されます。 そして、ノード サイクル数が、たとえば 1000 を超えると、PostgreSQL の実行後、「精度の範囲内で」除算され、逆算すると合計時間は「0.95 ミリ秒から 1.05 ミリ秒の間」になります。 カウントがマイクロ秒に達する場合は問題ありませんが、すでに[ミリ秒]になっている場合は、「誰がどのくらい消費したか」計画のノードからリソースを「切り離す」ときに、この情報を考慮する必要があります。

2 番目のポイントは、より複雑ですが、動的ノード間でのリソース (バッファー) の分散です。 これにより、プロトタイプの最初の 4 週間に加えて、さらに XNUMX 週間かかりました。

この種の問題は非常に簡単に発生します。CTE を実行し、その中で何かを読み取ると考えられます。 実際、PostgreSQL は「賢い」ので、そこから直接何かを読み取ることはありません。 次に、そこから最初のレコードを取得し、そこに同じ CTE からの XNUMX 番目のレコードを取得します。

計画を見て理解します。奇妙なことに、Seq スキャンで 3 つのバッファ (データ ページ) が「消費」され、CTE スキャンでさらに 1 つ、2 番目の CTE スキャンでさらに 6 つが「消費」されています。 つまり、すべてを単純に合計すると 3 になりますが、タブレットからは XNUMX しか読み取れません。 CTE スキャンはどこからでも何も読み取りませんが、プロセス メモリを直接操作します。 つまり、ここでは明らかに何かが間違っています。

実際、ここに Seq Scan から要求された 3 ページのデータがすべてあり、最初の 1 ページが 1 回目の CTE スキャンを要求し、次に 2 ページ目、そしてさらに 2 ページが読み取られたことがわかりました。 3 ページではなく 6 ページが読み取られたデータです。

そして、この図は、計画の実行はもはやツリーではなく、単なるある種の非循環グラフであるという理解を私たちにもたらしました。 そして、「そもそも何がどこから来たのか」がわかるように、このような図が得られました。 つまり、ここでは pg_class から CTE を作成し、それを 2 回要求しましたが、101 回目の要求時にほぼすべての時間がブランチに費やされました。 タブレットから 1 番目のエントリを読み取ることは、最初のエントリを単に読み取るよりもはるかにコストがかかることは明らかです。

私たちはしばらく息を吐きました。 彼らは言いました：「ネオ、君はカンフーを知っているね！」 私たちの経験があなたの画面上に表示されるようになりました。 これで使えるようになりました。」 [記事]

ログの統合

1000 人の開発者は安堵のため息をつきました。 しかし、「戦闘」サーバーは数百台しかなく、開発者側のこの「コピー＆ペースト」はまったく便利ではないことを私たちは理解していました。 私たちはそれを自分たちで集めなければならないことに気づきました。

一般に、統計を収集できる標準モジュールがありますが、これも構成で有効にする必要があります。 pg_stat_statements モジュール。 しかし、彼は私たちには合わなかった。

まず、同じデータベース内の異なるスキームを使用して同じクエリに割り当てます。 異なるQueryId。 つまり、最初に実行すると、 SET search_path = '01'; SELECT * FROM user LIMIT 1;そして SET search_path = '02'; と同じリクエストの場合、このモジュールの統計には異なるレコードが含まれることになり、スキームを考慮せずに、特にこのリクエスト プロファイルのコンテキストで一般的な統計を収集することはできません。

使用を妨げた XNUMX 番目の点は、 計画の欠如。 つまり、計画はなく、要求自体があるだけです。 何が減速しているのかはわかりましたが、その理由はわかりません。 ここで、急速に変化するデータセットの問題に戻ります。

そして最後の瞬間―― 「事実」の欠如。 つまり、クエリ実行の特定のインスタンスに対処することはできません。存在するのは、集計された統計だけです。 これで作業することは可能ですが、非常に困難です。

そこで、コピペと戦うことを決意し、書き始めました。 コレクター.

コレクターは SSH 経由で接続し、証明書を使用してサーバーとデータベースへの安全な接続を確立します。 tail -F ログファイルに「くっついています」。 そこで今回のセッションでは ログファイル全体の完全な「ミラー」を取得します。、サーバーが生成します。 サーバーでは何も解析せず、トラフィックをミラーリングするだけなので、サーバー自体の負荷は最小限です。

すでに Node.js でインターフェイスの作成を開始していたので、引き続きその中でコレクターを作成しました。 そして、JavaScript を使用して弱いフォーマットのテキスト データ (ログ) を操作するのが非常に便利であるため、このテクノロジ自体が正当化されています。 また、バックエンド プラットフォームとしての Node.js インフラストラクチャ自体により、ネットワーク接続、さらにはあらゆるデータ ストリームを簡単かつ便利に操作できるようになります。

したがって、123 つの接続を「拡張」します。123 つ目はログ自体を「リッスン」して自分自身に取り込み、XNUMX つ目は定期的にベースに問い合わせます。 「しかし、ログには oid XNUMX の記号がブロックされていることが示されています。」 しかし、これは開発者にとっては何の意味もありません。データベースに「そもそも OID = XNUMX とは何ですか?」と尋ねるのが良いでしょう。 そこで私たちは自分自身についてまだ知らないことを定期的に基地に尋ねます。

「考慮していないことが 10 つだけあります。ゾウに似たミツバチの一種がいるということです。」 私たちは、XNUMX 台のサーバーを監視したいと考えたときにこのシステムの開発を開始しました。 私たちの理解の中で最も重要なのは、対処が難しいいくつかの問題が発生した場合です。 しかし、第 XNUMX 四半期には XNUMX 件のモニタリングが行われました。システムが機能し、誰もがそれを望んでおり、誰もが快適だったからです。

これらすべてを合計する必要があり、データ フローは大規模でアクティブです。 実際、私たちが監視しているもの、対処できるものは、私たちが使用しているものです。 データ ストレージとして PostgreSQL も使用しています。 そして、オペレーターほどデータを「注入」するのが速いものはありません。 COPY まだ。

しかし、単にデータを「注ぐ」だけでは、実際には私たちのテクノロジーではありません。 50 台のサーバーで 100 秒あたり約 150 のリクエストがある場合、XNUMX 日あたり XNUMX ～ XNUMX GB のログが生成されるためです。 したがって、ベースを慎重に「カット」する必要がありました。

まず、私たちがやったのは、 日ごとに区切るなぜなら、概して、日間の相関関係には誰も興味がないからです。 今夜アプリケーションの新しいバージョンを公開した場合、そしてすでにいくつかの新しい統計が公開された場合、昨日と比べてどのような違いが生じるでしょうか。

第二に、私たちは学んだ（強制された） を使用して非常に高速に書くことができます COPY。 つまり、それだけではなく、 COPY彼はより速いから INSERT、さらに速くなります。

XNUMX 番目のポイント - しなければならなかった それぞれトリガーと外部キーを放棄します。 つまり、参照整合性がまったくありません。 なぜなら、FK のペアを持つテーブルがあり、データベース構造で「ここに、たとえばレコードのグループに対して FK によって参照されるログ レコードがある」と指定した場合、それを挿入すると、PostgreSQL があとはそれをどう受け止めて正直に実行するかだけだ SELECT 1 FROM master_fk1_table WHERE ... 挿入しようとしている識別子を使用します。これは、このレコードがそこに存在すること、挿入によってこの外部キーが「切断」されていないことを確認するためです。

ターゲット テーブルとそのインデックスに対する XNUMX つのレコードの代わりに、それが参照するすべてのテーブルから読み取るという追加の利点が得られます。 しかし、これはまったく必要ありません。私たちの仕事は、最小限の負荷でできるだけ多くの内容をできるだけ早く記録することです。 それでFK - ダウン！

次のポイントは集約とハッシュです。 最初はデータベースに実装しましたが、結局のところ、レコードが到着したら、ある種のタブレットですぐに実行できるのが便利です。 トリガーのすぐ近くに「プラス XNUMX」。 これは便利ですが、同じように悪い点もあります。レコードを XNUMX つ挿入しても、別のテーブルから何か他のものを読み書きしなければならないということです。 しかも、ただ読み書きするだけではなく、毎回それを行うのです。

ここで、特定のホストを通過したリクエストの数を単純にカウントするテーブルがあると想像してください。 +1, +1, +1, ..., +1。 そして、原則として、これは必要ありません - すべて可能です コレクター上のメモリ内の合計 一度にデータベースに送信します +10.

はい、何らかの問題が発生した場合、論理的な整合性が「崩れる」可能性がありますが、これはほとんど非現実的なケースです。通常のサーバーがあり、コントローラーにバッテリーがあり、トランザクション ログやログが存在するためです。ファイル システム... 一般的に、それだけの価値はありません。 トリガー/FK の実行によって生じる生産性の損失は、発生するコストに見合ったものではありません。

ハッシュ化も同様です。 あるリクエストが飛んできて、データベース内のそれからある識別子を計算してデータベースに書き込み、それをみんなに伝えます。 録音時に、同じことを録音したいという別の人があなたのところに来てブロックされるまでは、すべてがうまくいきます。これはすでに悪いことです。 したがって、一部の ID の生成を (データベースに対して) クライアントに転送できる場合は、これを実行することをお勧めします。

リクエスト、プラン、テンプレートなどのテキストから MD5 を使用するのは私たちにとって完璧でした。コレクター側でそれを計算し、既成の ID をデータベースに「流し込み」ます。 MD5 の長さと毎日のパーティショニングにより、衝突の可能性を心配する必要はありません。

しかし、これらすべてを迅速に記録するには、記録手順自体を変更する必要がありました。

普段どのようにデータを書き込んでいますか? ある種のデータセットがあり、それを複数のテーブルに分割してコピーします。最初に最初のテーブルに、次に XNUMX 番目のテーブルに、そして XNUMX 番目のテーブルにコピーします。XNUMX つのデータ ストリームを XNUMX つのステップで書き込んでいるようなので不便です。順次。 不快。 もっと早くできるでしょうか？ できる！

これを行うには、これらのフローを互いに並行して分解するだけで十分です。 エラー、リクエスト、テンプレート、ブロッキングなどが別々のスレッドで発生しており、それらをすべて並行して書いていることがわかりました。 これで十分です 個々のターゲット テーブルごとに COPY チャネルを常に開いたままにする.

つまり、コレクターでは、 常に流れがある、そこに必要なデータを書き込むことができます。 ただし、データベースがこのデータを認識し、誰かがこのデータが書き込まれるのを待って立ち往生しないようにするために、 COPYは一定間隔で中断する必要がある。 私たちの場合、最も効果的な時間は約 100 ミリ秒でした。これを閉じて、すぐに同じテーブルに再度開きます。 また、いくつかのピーク時に XNUMX つのフローが不足した場合は、一定の制限までプールします。

さらに、このような負荷プロファイルの場合、レコードがバッチで収集される場合の集計は悪であることがわかりました。 古典的な悪は、 INSERT ... VALUES さらに1000件のレコード。 なぜなら、その時点でメディアへの書き込みがピークに達し、ディスクに何かを書き込もうとする他の人はすべて待機することになるからです。

このような異常を取り除くには、単に何も集約しないでください。 まったくバッファリングしないでください。 また、ディスクへのバッファリングが発生した場合は (幸いなことに、Node.js の Stream API を使用するとそれを確認できます)、この接続を延期します。 再び空きイベントを受信したら、蓄積されたキューから書き込みます。 そして、ビジー状態の間に、プールから次の空いているものを取り出して書き込みます。

データ記録にこのアプローチを導入する前は、約 4K の書き込み操作があり、この方法で負荷を 4 分の 6 に削減できました。 現在では、新しい監視対象データベースのおかげでさらに 100 倍に増加し、最大 3MB/秒まで増加しました。 そして現在、過去 10 か月分のログを約 15 ～ XNUMX TB の量で保存しており、開発者がわずか XNUMX か月以内にあらゆる問題を解決できるようになることを期待しています。

私たちは問題を理解しています

しかし、このすべてのデータを単に収集するだけでは優れており、有用で関連性がありますが、十分ではないことを理解する必要があります。 なぜなら、これらは XNUMX 日に何百万もの異なる計画があるからです。

しかし、何百万人も管理することは不可能です。私たちはまず「より小規模」に取り組む必要があります。 そしてまず第一に、この「小さな」ものをどのように整理するかを決める必要があります。

私たちは XNUMX つの重要なポイントを特定しました。

• 誰が このリクエストを送信しました
  つまり、Web インターフェイス、バックエンド、支払いシステム、またはその他のアプリケーションから「到着」しました。
• どこ それは起こった
  特定のサーバー上で? なぜなら、XNUMX つのアプリケーションの下に複数のサーバーがあり、そのうちの XNUMX つが突然「おかしくなった」場合 (「ディスクが腐った」、「メモリ リーク」、その他の問題が原因)、そのサーバーに具体的に対処する必要があるからです。
• 方法 問題は何らかの形で明らかになった

「誰」がリクエストを送信したかを理解するために、標準ツールを使用してセッション変数を設定します。 SET application_name = '{bl-host}:{bl-method}'; — リクエストの送信元のビジネス ロジック ホストの名前と、リクエストを開始したメソッドまたはアプリケーションの名前を送信します。

リクエストの「所有者」を渡した後、リクエストをログに出力する必要があります。このために変数を設定します。 log_line_prefix = ' %m [%p:%v] [%d] %r %a'。 興味のある人にとっては、もしかしたら マニュアルを見てくださいそれは何を意味しますか。 ログで次のことがわかります。

• 時間
• プロセスとトランザクションの識別子
• データベース名
• このリクエストを送信した人のIP
• とメソッド名

その後、異なるサーバー間の XNUMX つのリクエストの相関関係を調べるのはあまり面白くないことに気づきました。 XNUMX つのアプリケーションがあちこちで同じように失敗するという状況は、あまりありません。 ただし、同じであっても、これらのサーバーのいずれかを見てください。

それで、これがカットです 「XNUMX サーバー - XNUMX 日」 あらゆる分析にはこれで十分であることがわかりました。

最初の分析セクションは同じです "サンプル" - すべての数値指標を除いた、計画の簡略化された形式のプレゼンテーション。 XNUMX 番目のカットはアプリケーションまたはメソッドで、XNUMX 番目のカットは問題を引き起こした特定の計画ノードです。

特定のインスタンスからテンプレートに移行すると、次の XNUMX つの利点が同時に得られました。

• 分析対象のオブジェクト数を大幅に削減
  私たちはもはや何千ものクエリや計画ではなく、数十のテンプレートによって問題を分析する必要があります。
• タイムライン
  つまり、「事実」を一定のセクション内にまとめることで、その日の様子を表示することができます。 ここで、ある種のパターンが、たとえば XNUMX 時間に XNUMX 回発生するが、それは XNUMX 日に XNUMX 回発生するはずである場合、何が問題だったのか、誰が原因で、なぜ発生したのかを考える必要があることが理解できます。おそらく、それはここにあるはずです。そうすべきではありません。 これは、もう XNUMX つの非数値的で純粋に視覚的な分析方法です。

残りの方法は、計画から抽出した指標に基づいています。つまり、そのようなパターンが発生した回数、合計時間と平均時間、ディスクから読み取られたデータの量、メモリから読み取られたデータの量などです。

たとえば、ホストの分析ページにアクセスすると、ディスク上で何かが過剰に読み取られ始めています。 サーバー上のディスクはそれを処理できません。誰がそこから読み取るのでしょうか?

そして、任意の列で並べ替えて、プロセッサやディスクの負荷、またはリクエストの総数など、今何を扱うかを決めることができます...私たちはそれを並べ替え、「上位」のものを見て、修正し、アプリケーションの新しいバージョンを公開しました。
【ビデオ講義】

そして、次のようなリクエストから同じテンプレートが付属するさまざまなアプリケーションをすぐに確認できます。 SELECT * FROM users WHERE login = 'Vasya'。 フロントエンド、バックエンド、処理...そして、ユーザーが対話していないのに、なぜ処理がユーザーを読み取るのか不思議に思います。

逆の方法は、アプリケーションからその動作をすぐに確認することです。 たとえば、フロントエンドは XNUMX 時間に XNUMX 回、これ、これ、これ、そしてこれです (タイムラインが役に立ちます)。 そしてすぐに疑問が生じます。XNUMX 時間に XNUMX 回何かを行うのはフロントエンドの仕事ではないようです...

しばらくしてから、集計が不足していることに気づきました。 プランノードごとの統計。 テーブル自体のデータを操作する (インデックスによる読み取り/書き込みの有無にかかわらず) ノードのみをプランから分離しました。 実際、前の写真に対して追加された点は XNUMX つだけです - このノードは何件のレコードをもたらしましたか?、および破棄された行の数 (フィルターによって削除された行)。

プレート上に適切なインデックスがなく、それにリクエストを送信すると、インデックスを通り過ぎて Seq Scan に落ちます...100 つを除くすべてのレコードをフィルターで除外しました。 なぜ XNUMX 日あたり XNUMX 億件のフィルター済みレコードが必要なのでしょうか? インデックスをロールアップしたほうがよいのではないでしょうか?

すべての計画をノードごとに分析した結果、計画には疑わしいと思われる典型的な構造がいくつかあることがわかりました。 そして、開発者に次のように伝えるとよいでしょう。「友人、ここでは、まずインデックスで読み取り、次に並べ替え、次に切り取ります。」 - 原則として、レコードは XNUMX つあります。

クエリを作成した人なら誰でも、おそらくこのパターンに遭遇したことがあります: 「Vasya の最後の注文、その日付を教えてください。」 そして、日付によるインデックスがない場合、または使用したインデックスに日付がない場合は、全く同じ「熊手」を踏みます。

しかし、これが「熊手」であることはわかっています。それなら、開発者に何をすべきかをすぐに指示しないのはなぜでしょうか。 したがって、今プランを開くと、開発者はすぐにヒント付きの美しい写真を見て、「あちこちに問題がありますが、これらはこうして解決されます」とすぐに伝えます。

その結果、問題解決に必要な経験値は当初と現在では大幅に減少しています。 これが私たちが持っている種類のツールです。

出所： habr.com