PostgreSQLクエリパフォーマンス-ステップバイステップ

イリヤ・コスモデミャンスキー（ ハイドロバイオント ）

始めに、議論されることについてのほんの少しの言葉。 まず、クエリの最適化とは何ですか？ 人々が処方することはめったになく、彼らがしていることの理解をしばしば過小評価することがしばしば起こります。 特定のリクエストを高速化することもできますが、必ずしも最適化とは限りません。 このトピックについて少し理論化してから、この問題へのアプローチ方法、最適化を開始するタイミング、方法、クエリまたはクエリのセットが何らかの方法で最適化できないことを理解する方法について説明します。それをやり直すだけです。 奇妙なことに、クエリを最適化する方法の例はほとんど示しません。100の例でもソリューションに近づかないからです。





通常、ボトルネックは、どちらの側にこの問題にアプローチするかについての誤解です。 誰もが「EXPLAINを読んでEXPLAINのクエリをチェックします」と言いますが、EXPLAINを見た後、何を見て、何をすべきかについてよく質問されますか？ これについてもう少しお話しましょう。



報告書のエピグラフとして、船を造っていたアカデミシャンのクリロフから引用したいと思います。この引用は100年以上前のものですが、何も変わっていません。







データベース（DB）の主な問題は、データベースに不要なものがあるか、不要なものがあるか、その操作に対する何らかの誤ったアプローチがあることです。 原則として、巨大なキャッシュと超高価なSSDドライブを備えたある種の超大型RAIDコントローラーを提供できますが、ヘッドをオンにしないと悲惨な結果になります。



「クエリの最適化」とはどういう意味ですか？ 原則として、このようなタスクはありません。 問題は通常、「すべてが悪い！」ということです。 私たちは素晴らしいプロジェクトを書いています。すべてが機能し、誰もが満足していますが、ある時点でもう少し広告を注文し、もう少しユーザーが来て、すべてが落ちました。 プロジェクトを迅速に開発する場合、通常は「rails」、「django」などを使用するためです。 「額に」と書く-製品をより早く提供することが重要です。 これは実際に正しいです 動作しない完全に「なめられた」プロジェクトを必要とする人はいません。 次に、何が起こっているのかを理解する必要があります。 最適化するこれらの遅いクエリは、これが遅くなっていることを示すインターフェイスにすぎませんが、その理由は簡単に別のものになる可能性があります。 悪いハードウェアがあるかもしれません、未構成のベースがあるかもしれません、そして、この時点で、クエリ最適化から始めて、原則として、それは価値がありません。 まず、データベースに何が起こるかを確認する必要があります。



データベースのチューニングには単純に大きなエラーがあり、修正する前は、ボトルネックが別の場所にあるため、クエリを最適化することは役に立ちません。 たとえば、Postgresについて話している場合、autovacuumは無効になっている可能性があります。 何らかの理由で、人々はこれを行うことがあります（これを行うことは決してできません！）。しかし、無効にすると、テーブルの断片化が非常に大きくなります。 10万レコードのテーブルは、10億レコードのテーブルのサイズに簡単になります。 当然、それに対するクエリは予想よりも遅くなります。 したがって、まずデータベースを構成し、すべてが正常に機能することを確認する必要があります。



もう1つのよくある間違いは、アプリケーションからの接続が非常に多く、接続ブローカーがないために1000人のPostgresワーカーが動作することです。 500の接続がある場合は、実行しているサーバーに500のコアが必要であることを理解する必要があります。 そうでない場合、これらの接続は互いに干渉し、常に待機します。 これらのナンセンスを修正したら（非常に多くの場合がありますが、主なもの-5〜10個-メモリ、ディスク、自動バキュームの正しい設定...）、クエリの最適化に進むことができます。 そしてその時だけ。 他にあなたがしていることだけを最適化しようとしないでください。



アルゴリズムとは、つまり クエリ最適化のアプローチ方法 まず、設定を確認する必要があります。次に、何らかの方法で、最適化するクエリを選択します（これは重要なポイントです！）。そして実際に最適化します。 最も遅いクエリを「キュア」した後、それらは速くなります。古いトップがそれらに取って代わったので、すぐに新しい遅いクエリになります。 その結果、このアルゴリズムを繰り返しながら、段階的に徐々に、遅いクエリを取り除きます。 すべてがシンプルです。







非常に重要なポイントは、最適化するクエリを知ることです。 行のすべてを最適化すると、どのクエリが最も問題があるかを推測できない可能性があります。 あなたは多くの時間を費やすだけであり、高い確率であなたは正しい仕事に着かないでしょう。 したがって、問題が発生したときにクエリを最適化する必要があります。 動作が停止した場所を見て、遅くて悪くなりました-この部分を最適化します。 めったに使用されないものがある場合は、触れないでください。時間を無駄にしないでください。



選択したトップに対してリクエストが行われ、それらで何ができるかが見えます。 これを行うには、特にバージョン9.4では、EXTENSION pg_stat_statementsを使用するのが正しい方法です。これはすべてオンラインでオンザフライで実行され、これをすべて見ることができます。



これについて詳しく説明しましょう。







ページの一番下のアドレスで-pg-utils（無料で入手可能）をダウンロードして使用できます。 このフォルダーには、標準のカウンターベースpg_stat_statementsを囲む特定のバインディングがあります。これにより、データベースでの1日の作業についてこれらのレポートを生成でき、そこで何が起こったのかを確認できます。 したがって、いくつかの上位クエリが何らかの形でランク付けされています。



最初の位置、2番目（通常はさらに10個）があることを知ることが重要であり、いくつかのパラメーターについては最初に特定のリクエストが出ていることがわかります-たとえば、ベース負荷の24％がかかります。 これは非常に多く、何らかの形で最適化する必要があります。 リクエストを見て、「彼はプロジェクトにいくらお金を持っていますか？」と考えます。 彼がプロジェクトに多額のお金を持ち込み、それから半分の負荷を食べたとしても、もし彼がお金を持ち込まず、半分のリソースを食べたら、これは悪いことです。何かをする必要があります。 したがって、前日の上位クエリを見て、それに対して何をすべきかを考えます。



プロジェクトで何かをする開発チームがいて、cronで1日に1回、このようなレポートがすべてのDBA、すべての開発者、すべての管理者に届くのは良い習慣と考えられています。



遅いクエリとは何ですか？ まず、これは最上位にあるクエリです（いずれにしても、何らかの方法で最適化する必要があります）。 しかし、純粋に時間の問題は常に問題です。 ミリ秒の小数部で機能するクエリでさえ、依然として遅い可能性があります。 たとえば、これらの要求が多数あり、その結果として多くの小さな要求がデータベースリソースの非常に大きな割合を消費する場合です。



したがって、リクエスト時間は相対的なものであり、ここでは、このリクエストが機能する頻度を調べる必要があります。 これがホームページで何かが返され、このリクエストに1秒かかる場合、このページに加えてこのページを形成するために必要なすべてのオーバーヘッドがあることを理解する必要があります。 これは、ユーザーに1秒よりも遅い結果が保証されて表示されることを意味します。オンラインの負荷の高いWebの場合、これらは許容できない結果です。 何らかの分析のためにクエリが夜間に追跡される場合、非同期レポートが誰かに送信され、おそらく彼はゆっくりと作業する余裕があります。 つまり、データを常に把握し、このリクエストが機能するために許容される期間を常に考慮する必要があります。 繰り返しますが、ベースでの負荷の性質は重要です。 たとえば、長くて重い要求があり、ピーク時にそれを操作します。これは、ある種の分析、マネージャーなどに対する要求です。



ベースの負荷プロファイルを確認します。 pg_stat_statementsがあり、その上に、たとえば午前2時から午後4時までの遅いクエリのトップを見ることができ、現時点では長い分析クエリを駆動しません。



この問題がどれだけのお金をもたらし、多くのデータベースリソースを占有する権利があるかを忘れないでください。 プロジェクトで何も得られないクールな機能を作成し、このリクエストがリソースの50％を使い果たした場合、悪いリクエストを書き、このアイデアをやり直し、時には技術的に非常に難しいことでリソースを使い果たしてしまう理由を説明する必要さえあります。 誰もが、ゴールデンフィッシュを私の敷地内に置いてほしいと言いますが、サーバーは鉄製であり、一定の制限があり、ゴムを伸ばすことはできません。 クラウドを作成する人々は何が可能かを教えてくれますが、私は退屈な管理者として、それは不可能だと言います。



一般に、1つの特定のリクエストの実行に問題があるのはどこですか？ まず、クライアントからのデータ転送である可能性があり、見た目ほど面白いものではありません。 次のスライドは、犬をそこに埋めることができる場所を示しています。







Rubyで書いて、あらゆる種類のcなORMを使用した人は誰でも、このリクエストによってDjangoを識別できることを知っています。 これは企業のアイデンティティであり、無線事業者の手書きは何とも混同されません。



私の人生で見たリストの最大長は何だと思いますか？ ギガバイト単位で考慮する必要があります！ ORMの機能を確認しないと、数ギガバイトを簡単に取得でき、このリクエストは実行されません。 これは悪いことであり、データアクセスが完全に最適化されていないことを意味します。 この要求は他の多くの理由で悪いですが、主な理由は、Postgresが人生でクロールしないような長さになる可能性があることです。



2番目のポイントは解析です。 単純に長時間解析される非常に華やかなリクエストを作成できます。 Postgresの新しいバージョンでは、私が間違えなければ、EXPLAINに解析時間があり、どれくらい時間がかかるかを理解できるようになります。 これで、EXPLAINを作成して、それぞれタイミング、リクエストの実行にかかった量、および解析までの時間を確認できます。



次に、リクエストを最適化する必要があります。 オプティマイザは非常に複雑なアルゴリズムであるため、これは見かけほど簡単なタスクではありません。 たとえば、2つのテーブルを結合したいとします。 彼は1つのテーブルを取得し、必要なデータにアクセスする方法を選択して、次のテーブルを添付します。 もう1つのテーブルとの別の結合がある場合は、最初に2つの結合があり、次にResultSetがもう1つ結合されます。 512 Join'ovでリクエストを書いた場合、非常に興味深い「パセリ」はこのすべての最適化から始まります。



Joinのn！の数に応じて、最適なJoinのパスを整理します。 計画のオプションが選択されます。 したがって、多くのJoin'ovがある場合、最適化プロセス自体が非常に長くなる可能性があることをすぐに理解できます。



次は直接パフォーマンスかもしれません。 リクエストが10 GBのデータを返す場合、それがミリ秒単位で機能するという事実を当てにすることは困難です。 魔法は彼を作ることができません。 したがって、大量のデータを提供する必要がある場合は、魔法がないことに注意してください。 NoSQLの世界で起こりますが、ここにはありません。



さて、結果を返します。 繰り返しますが、ネットワーク上で数ギガバイトのデータを駆動している場合は、ネットワークが通過できる量に一定の制限があるため、速度が遅くなることに備えてください。 このような場合、これらすべての結果が必要かどうかを時々考えるのは理にかなっていますか？ これは非常に一般的な問題です。



このプレゼンテーションの最も重要なスライド：







これは説明です。



上位のクエリを強調表示した後、何らかの方法でこれらのクエリが遅いことを確認し、クエリを使用して何かを実行するには、EXPLAINを実行する必要があります。



多くの人がこの段階に達し、それからひっかかった。 「さて、私たちはEXPLAINを見て、次にそれをどうするか？」と不満を言う。 これは私があなたに今話すことです。



EXPLAINのスライド2では、構文が少し異なります。



これは次のように行うことができます：Explain（分析オン、バッファーオン）、たとえば、単にExplain分析と書くことができます。



EXPLAINは、あなたがそれがどうあるべきかについての計画を単にあなたに与えることを理解することが重要です。 したがって、ANALYZEも指定した場合、このリクエストは実際に実行され、実行方法に関するデータが表示されます。 単に説明するだけでなく、実際には何が起こっているのか、何らかの痕跡もあります。



遅いクエリのトップを選択した場合、EXPLAIN ANALYZEを使用するのが適切です。最適なプランを選択した可能性があり、統計が収集されないなどがあるためです。



Postgresはスーパートランザクションです。クエリを記述している場合、何かを最適化するときにこれらの結果を書き留めたくない、たとえば「開始」、クエリを開始します（ただし、一般的に何が起こるかを確認することをお勧めします-重いクエリピーク時には、戦闘基地で常に良いとは限りません）、「rallback」と発声して、このデータが記録されないようにします。



いくつかの数値がここに表示され、そのいくつかはEXPLAINに属し、いくつかはANALYZEに属します。 これらは重要な数字です。 EXPLAINには、「コスト」と呼ばれる条件付きの「オウム」があります。 デフォルトでは、Postgresの1つのコストは、順次順次スキャンで8 KBの単一ブロックを抽出するのにかかる時間です。 原則として、この値はマシンに依存するため、条件付きであるため便利です。 高速ドライブを使用している場合は、低速-低速の場合は高速になります。 コスト= 9.54であることを理解することが重要です-これは、サイズが8 KBの1ブロックを取得するよりも9.54倍遅いことを意味します。



2つの数字があります。1つ目は、最初の結果が返されるまでにどれだけ時間が経過するか、2つ目は、結果全体が返されるまでにどれだけ時間が経過するかを示します。 大量のデータを抽出する場合、最初の数字は比較的小さく、2番目の数字は非常に大きくなります。 これは実際にかかった実際の時間です。 何らかの理由でコストが非常に小さく、この時間が非常に大きい場合、統計の収集にいくつかの問題があります。autovacuumがオンになっているかどうかを確認する必要があります。オプティマイザー。



EXPLAINはそのようなツリーです。大まかに言って、ディスクからデータを取得する方法はもっと低いオプションがあります。これはプレートのスキャン、インデックスのスキャンなどです。 上位のオプションは、ある種の集約、結合などが上に重ねられている場合です。 このようなEXPLAINを見ると、タスクは非常に簡単です。

• それがどのくらい速く動作するかを理解し、ランタイムを見て、そこで何が起こったかを見る。
• このツリーのどのノードが最も高価かを確認してください。 一番下の段階でコストが十分に大きい場合、実際の時間が長い場合、この部分を最適化する必要があります。 たとえば、テーブル全体をスキャンした場合、そこにインデックスを作成する必要がある場合があります。 これは、あなたが本当に最適化する必要がある場所であり、あなたはそれを見る必要がある。

たとえば、スライドの場合のように集約がおかしい場合は、集約がより重いので、それを取り除く方法を考える必要があります。



だからあなたはEXPLAINを見て、最も高価な場所を見つけます。 EXPLAINを半年間​​見た後、これらの場所を肉眼で見ることを学び、すでに頭の中にレシピのセットがあります。 詳細についてはまだ検討しません。



どのようなテクニックを使用できますか？ インデックスを作成できます。 インデックスのアイデアは、大きなテーブルをクロールするのではなく、スキャンが便利な小さなデータ配列であるということです。 したがって、すべてのプログラマーはインデックスが大好きで、あらゆる場合にインデックスを作成し、それが役立つと考えています。 インデックスが無料ではないため、これは間違っています。 インデックスはスペースを占有します。テーブル内の各エントリはインデックスが再構築され、バランスが取れており、すべてが空いているわけではありません。



使用されていないインデックスでテーブル全体がハングしている場合、高い確率でそれらの一部を破棄でき、高速になります。 それでも、たとえばテーブルからデータの半分を抽出するためにクエリが必要な場合、かなり正確な場所にインデックスを配置してこのデータを取得するのが理にかなっているため、インデックスは使用されない可能性が非常に高くなります。 サイズがテーブルに匹敵する大きな「シート」が必要な場合、最初にインデックススキャンを実行し、次に別の操作を実行する必要があるため、テーブル自体の順次スキャンは常にインデックススキャンよりも高速になります。



ほとんどの場合、オプティマイザーはそのようなことを間違えていません。 インデックスを作成し、なぜそれが使用されないのか疑問に思っている場合、インデックスがないと単純に高速になるためです。



Postgresにはそのようなパラメータがあります-セッション変数、インデックススキャンをオフに設定、または逆にシーケンシャルスキャンをオフに設定すると、インデックスの有無にかかわらず高速/低速になります。 この方法でクエリを「戦闘中」に最適化することはお勧めしません。これは非常に難しい「松葉杖」であり、オプティマイザの機能の非常に深刻な制限ですが、実験して確認するのに役立ちます リクエストを作成し、インデックスを作成し、機能すると思い、シーケンシャルスキャンをオフにし、オプティマイザーはインデックス付きのプランを選択するように強制され、Postgresが提供するものよりも遅くなったかどうかを確認します。 ほとんどの場合、これはまさにそうです。



次に、リクエストの作成方法が重要です。 このようなものがある場合-（counter + 1 = 46）-インデックスは取得されず、Postgresはこの操作を自動的に実行できません。 単純な追加のように見えますが、同じ成功を収めることで、オプティマイザーに差分も解決させることができます。 Postgresには多数のデータ型があり、演算子を定義したり、代数などのアクションを定義したり、オプティマイザーはこれらすべてのタイプについてこのアクションを実行する方法を知っている必要がありますが、彼にとっては難しいタスクです。動作しません。



以下は、たとえば、結合がうまく機能しない理由です。 これは重要なノードの1つで、誰もが使用します。



Joinにはさまざまなタイプがあり、LEFT、RIGHT、INNERなどについてではなく、Joinが実行されるアルゴリズムについて説明しています。 Postgresには3つの主な結合アルゴリズムがあります。つまり、ネストループ（名前からわかるように、1つのテーブルからデータを取得してサイクルで結合します）、ハッシュインデックス（1つ、多くの場合小さいハッシュテーブル、およびこのハッシュ別のテーブルとの結合）と結合の結合（これがどのように機能するかは明らかです）。



これらの結合は常に同じように役立つわけではなく、オプティマイザーはそれらを選択できます。 たとえば、2つのテーブルJoinがあり、オプティマイザがHash Joinを選択しますが、動作が遅いことを理解しているため、あなたには向いていません。 Join'iteのフィールドにインデックスを付けているかどうかを確認するのは理にかなっています。 , Nested Loop, . , Nested Loop, .



– - Nested Loop, , , – Hash Join , . , work mem'. つまり worker Postgres'. , , 100 , work mem' 30 , worker . work mem' , Hash Join .



, , , .



« » ( workload ), .



:







. , , - Postgres , – , - ( , Postgres', , , ). WHERE , ORM' . , Postgres - . , , .



EXPLAIN :







, , , Index Scan , - , , .



, . , Postgres Hash Join, . «» , -Join'. , .



VALUES, ResultSet:







Index Scan Hash Join. , .



, , , , , :







.



, , EXPLAIN, . , , .



– , . –count(*).



, -, – , , . , count , ? , , , . , - , , , , . - , , , count .



count – , Postgres, , , .



– count'. , . – count. PG-, -select', analyze', . , PG- – select value . - PG-, , «security definer» , - security.



- – Join 300 . , 300! , Join . , Join 300 , , , - , . Join – , . , , . Join' , .



– 1 000 000 . Google? ? , , , - 1 . , – - ? 10, 20 , , 100, 1 . . , , - , , , - , .



, ORM' - , , , , 10%. limit offset - , 1 . – , , , - .

連絡先

hydrobiont

— HighLoad++ .



また、これらの資料の一部は、高負荷システムHighLoadの開発に関するオンライントレーニングコースで使用されますガイドは、特別に選択された文字、記事、資料、ビデオのチェーンです。 私たちの教科書にはすでに30以上のユニークな資料があります。 接続してください！



— " - ", , HighLoad++ Junior .