PostgreSQL の内部統計について詳しく説明します。 アレクセイ・レソフスキー

Alexey Lesovsky による 2015 年のレポート「PostgreSQL の内部統計の詳細」の転写

レポート作成者からの免責事項: このレポートの日付は 2015 年 4 月であることに注意してください。すでに 9.4 年以上が経過し、かなりの時間が経過しています。 レポートで説明されているバージョン 4 はサポートされなくなりました。 過去 5 年間で XNUMX つの新しいリリースがリリースされ、統計に関する多くの革新、改善、変更が行われましたが、一部の資料は古くなって関連性がありません。 レビューする際に、読者を誤解させないように、これらの場所にマークを付けるようにしました。 これらの場所は私が書き直したわけではなく、たくさんあります。その結果、まったく異なるレポートが得られます。

PostgreSQL DBMS は巨大なメカニズムであり、このメカニズムは多くのサブシステムで構成されており、その調整された作業が DBMS のパフォーマンスに直接影響します。 運用中にコンポーネントの動作に関する統計や情報が収集されるため、PostgreSQL の有効性を評価し、パフォーマンスを向上させるための対策を講じることができます。 ただし、この情報は大量にあるため、かなり簡略化された形式で表示されています。 この情報を処理して解釈することは、完全に簡単な作業ではない場合があり、ツールやユーティリティの「動物園」は、上級 DBA であっても簡単に混乱してしまうことがあります。

こんにちは私の名前はアレクセイです。 Ilya が言ったように、PostgreSQL の統計について話します。

PostgreSQL アクティビティの統計。 PostgreSQL には XNUMX つの統計があります。 アクティビティ統計については後で説明します。 データ分散に関するスケジューラー統計。 特に、パフォーマンスを判断し、何らかの方法で改善できるようにする PostgreSQL アクティビティ統計について説明します。

あなたが抱えている、または抱えている可能性のあるさまざまな問題を解決するために統計を効果的に使用する方法を説明します。

レポートに記載されないものは何ですか? レポートでは、スケジューラの統計については触れません。 これは、データがデータベースにどのように保存されるか、およびクエリ プランナーがこのデータの定性的および量的特性についてどのように把握するかについての別のレポートの別のトピックです。

また、ツールのレビューはありません。ある製品を別の製品と比較することもありません。 広告はありません。 落としてみましょう。

統計を使うことが役に立つことを示したいと思います。 これは必要である。 恐れずに使ってください。 必要なのは、単純な SQL と SQL の基本的な知識だけです。

そして、問題を解決するためにどの統計を選択するかについて説明します。

PostgreSQL を調べ、オペレーティング システム上でコマンドを実行してプロセスを表示すると、「ブラック ボックス」が表示されます。 何かを実行するいくつかのプロセスが表示され、名前によって、それらがそこで何をしているのか、何をしているのかを大まかに想像できます。 しかし、実際には、これはブラックボックスであり、内部を見ることはできません。

CPU 負荷を確認できます。 top、一部のシステム ユーティリティでメモリ使用率を確認できますが、PostgreSQL の内部を確認することはできません。 このためには他のツールが必要です。

さらに続けて、どこに時間がかかっているかを説明します。 PostgreSQL をこのようなスキームの形で表現すると、どこに時間がかかっているかを答えることができます。 これらは XNUMX つのことです。アプリケーションからのクライアント要求の処理と、PostgreSQL を実行し続けるために PostgreSQL が実行するバックグラウンド タスクです。

左上隅を見始めると、クライアントのリクエストがどのように処理されるかがわかります。 リクエストはアプリケーションから送信され、さらなる作業のためにクライアント セッションが開かれます。 リクエストはスケジューラに渡されます。 プランナーはクエリ プランを作成します。 さらに実行のために送信します。 テーブルとインデックスに関連付けられたある種のブロック I/O データがあります。 必要なデータはディスクから「共有バッファ」と呼ばれる特別な領域のメモリに読み込まれます。 クエリ結果は、更新や削除の場合、WAL のトランザクション ログに記録されます。 一部の統計情報はログまたは統計コレクターに保存されます。 そして、リクエストの結果がクライアントに返されます。 その後、クライアントは新しいリクエストですべてを繰り返すことができます。

バックグラウンド タスクとバックグラウンド プロセスには何が含まれているのでしょうか? データベースを通常の動作モードで稼働し続けるプロセスがいくつかあります。 レポートでは、自動バキューム、チェックポイント作成者、レプリケーションに関連するプロセス、バックグラウンド ライターなどのプロセスについても説明します。 それぞれについて触れながらレポートしていきます。

統計にはどのような問題があるのでしょうか?

• 情報がたくさんあります。 PostgreSQL 9.4 は、統計データを表示するための 109 のメトリクスを提供します。 ただし、データベースに多くのテーブル、スキーマ、データベースが格納されている場合は、これらすべてのメトリクスに、対応するテーブル、データベースの数を乗算する必要があります。 つまり、さらに多くの情報があります。 そして、それに溺れてしまうのはとても簡単です。
• 次の問題は、統計がカウンターで表されることです。 これらの統計を見ると、カウンターが継続的に増加していることがわかります。 また、統計がリセットされてからかなりの時間が経過すると、数十億の値が表示されることになります。 そして彼らは私たちに何も教えてくれません。
• 歴史はありません。 15 ～ 30 分前に何かが落ちたなど、何らかの障害が発生した場合、統計を使用して 15 ～ 30 分前に何が起こったかを確認することはできません。 これは問題です。
• PostgreSQL にツールが組み込まれていないことが問題です。 カーネル開発者はユーティリティを提供しません。 彼らにはそのようなものは何もありません。 彼らはデータベース内の統計を提供するだけです。 それを使って、それにリクエストをして、何でもしてください。
• PostgreSQL にはツールが組み込まれていないため、別の問題が発生します。 サードパーティのツールがたくさんあります。 多かれ少なかれ直接手を握っているすべての企業は、独自のプログラムを作成しようとしています。 その結果、コミュニティには統計を扱うために使用できるツールが多数あります。 また、一部のツールにはいくつかの機能があり、他のツールには他の機能がないか、いくつかの新機能があります。 そして、異なる機能を持つ XNUMX つ、XNUMX つ、または XNUMX つのツールを互いに重複して使用する必要がある状況が発生します。 これは非常に迷惑です。

これから何が起こるでしょうか？ プログラムに依存せずに統計を直接取得できることが重要です。または、何らかの方法でこれらのプログラムを自分で改善し、利点を得るためにいくつかの機能を追加することができます。

また、SQL の基本的な知識が必要です。 統計からデータを取得するには、SQL クエリを作成する必要があります。つまり、選択と結合がどのように行われるかを知る必要があります。

統計はいくつかのことを教えてくれます。 それらはカテゴリに分類できます。

• 最初のカテゴリは、データベース内で発生するイベントです。 これは、データベース内で何らかのイベントが発生したときです。クエリ、テーブル アクセス、自動バキューム、コミットなど、これらはすべてイベントです。 これらのイベントに対応するカウンターがインクリメントされます。 そして、これらのイベントを追跡することができます。
• XNUMX 番目のカテゴリは、テーブルやデータベースなどのオブジェクトのプロパティです。 それらには特性があります。 これはテーブルのサイズです。 テーブルの増加やインデックスの増加を追跡できます。 ダイナミクスの変化がわかります。
• XNUMX 番目のカテゴリは、イベントに費やされた時間です。 リクエストはイベントです。 独自の期間の尺度があります。 ここで始まり、ここで終わりました。 追跡できます。 ディスクからのブロックの読み取り時または書き込み時。 こういったものも追跡されます。

統計のソースは次のように表示されます。

• 共有メモリ (共有バッファ) には、静的データを配置するためのセグメントがあり、特定のイベントが発生したり、データベースの操作中に発生した瞬間に常に増加するカウンタもあります。
• これらのカウンターはすべて、ユーザーは使用できず、管理者も使用できません。 これらは低レベルのことです。 これらにアクセスするために、PostgreSQL は SQL 関数の形式でインターフェイスを提供します。 これらの関数を使用して選択を行い、ある種のメトリクス (またはメトリクスのセット) を取得できます。
• ただし、これらの関数を使用すると必ずしも便利であるとは限らないため、関数はビュー (VIEW) の基礎となります。 これらは、特定のサブシステム、またはデータベース内の一連のイベントに関する統計を提供する仮想テーブルです。
• これらの組み込みビュー (VIEW) は、統計を操作するための主要なユーザー インターフェイスです。 これらは追加の設定を行わずにデフォルトで利用可能であり、すぐに使用したり、監視したり、そこから情報を取得したりできます。 そして投稿者もいます。 投稿は公式のものです。 postgresql-contrib パッケージ (postgresql94-contrib など) をインストールし、必要なモジュールを構成にロードし、パラメータを指定して PostgreSQL を再起動すると、使用できるようになります。 （ノート。 ディストリビューションによっては、contrib の最近のバージョンでは、パッケージはメイン パッケージの一部です。).
• そして非公式の投稿もあります。 これらは標準の PostgreSQL ディストリビューションには付属していません。 これらはコンパイルするか、ライブラリとしてインストールする必要があります。 この非公式投稿の開発者が何を思いついたかによって、オプションは大きく異なる場合があります。

このスライドでは、PostgreSQL 9.4 で使用できるすべてのビュー (VIEW) といくつかの関数を示します。 ご覧のとおり、それらはたくさんあります。 そして、初めて経験する場合は混乱するのは非常に簡単です。

しかし、先ほどの写真を撮ってみると、 Как тратится время на PostgreSQL このリストと一致すると、次の図が得られます。 PostgreSQL を実行している場合、各ビュー (VIEW) または各関数を何らかの目的で使用して、適切な統計を取得できます。 そして、サブシステムの動作に関する情報をすでに取得できています。

まず最初に見ていきます。 pg_stat_database。 ご覧のとおり、これは表現です。 たくさんの情報が含まれています。 最も多様な情報。 そして、データベース内で何が起こっているかについての非常に役立つ知識を提供します。

そこから何が得られるでしょうか？ 最も単純なことから始めましょう。

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

まず最初に確認できるのは、キャッシュ ヒット率です。 キャッシュ ヒット率は有用な指標です。 これにより、共有バッファ キャッシュから取得されるデータの量と、ディスクから読み取られるデータの量を見積もることができます。

それは明らかです キャッシュヒットが多ければ多いほど良い。 この指標はパーセンテージとして評価されます。 そして、たとえば、これらのキャッシュ ヒットの割合が 90% を超えていれば、これは良好です。 90% を下回る場合は、データのホットヘッドをメモリ内に保持するのに十分なメモリがありません。 そして、このデータを使用するために、PostgreSQL はディスクにアクセスする必要があり、これはデータがメモリから読み取られる場合よりも遅くなります。 そして、メモリの増加について考える必要があります。共有バッファを増やすか、鉄のメモリ (RAM) を増やすかのいずれかです。

select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;

このパフォーマンスから他に何が得られるでしょうか？ データベース内で発生している異常を確認できます。 ここには何が表示されていますか? コミット、ロールバック、一時ファイルの作成、そのサイズ、デッドロック、競合などが発生します。

このリクエストを使用できます。 この SQL は非常に単純です。 そして私たちはこのデータを自分自身で見ることができます。

そして、これが閾値です。 コミットとロールバックの比率を調べます。 コミットとは、トランザクションが成功したことを確認することです。 ロールバックはロールバックです。つまり、トランザクションが何らかの作業を行い、データベースに負担をかけ、何かを検討した後、障害が発生し、トランザクションの結果は破棄されます。 つまり ロールバックの数が増え続けるのは問題です。 そして、何らかの方法でそれらを回避し、これが起こらないようにコードを編集する必要があります。

競合はレプリケーションに関連しています。 そしてそれらも避けるべきです。 レプリカ上で実行されるクエリがあり、競合が発生した場合は、これらの競合を分析して、何が起こったかを確認する必要があります。 詳細はログで確認できます。 また、競合状況を解決して、アプリケーションのリクエストがエラーなしで機能するようにします。

デッドロックも悪い状況です。 リクエストがリソースをめぐって競合する場合、XNUMX つのリクエストは XNUMX つのリソースにアクセスしてロックを取得し、XNUMX 番目のリクエストは XNUMX 番目のリソースにアクセスしてロックも取得し、その後、両方のリクエストが互いのリソースにアクセスし、隣接するリクエストがロックを解放するのを待ってブロックされます。 これも問題のある状況です。 これらは、アプリケーションの書き換えとリソースへのアクセスのシリアル化のレベルで対処する必要があります。 デッドロックが継続的に増加していることがわかった場合は、ログの詳細を確認し、発生した状況を分析して、何が問題なのかを確認する必要があります。

一時ファイル (temp_files) もダメです。 ユーザー要求に、運用上の一時データを収容するのに十分なメモリがない場合、ディスク上にファイルが作成されます。 そして、メモリ内の一時バッファーで実行できるすべての操作は、すでにディスク上で実行され始めています。 遅いですね。 これにより、クエリの実行時間が増加します。 そして、PostgreSQL にリクエストを送信したクライアントは、少し遅れてレスポンスを受け取ります。 これらすべての操作がメモリ内で実行される場合、Postgres の応答ははるかに速くなり、クライアントの待ち時間は短くなります。

pg_stat_bgwriter - このビューは、XNUMX つの PostgreSQL バックグラウンド サブシステムの動作を説明します。 checkpointer и background writer.

まず、コントロール ポイント、いわゆるコントロール ポイントを分析しましょう。 checkpoints。 チェックポイントとは何ですか? チェックポイントは、ログ内でコミットされたすべてのデータ変更がディスク上のデータと正常に同期されていることを示すトランザクション ログ内の位置です。 このプロセスはワークロードと設定によっては時間がかかる場合があり、ほとんどの場合、共有バッファ内のダーティ ページとディスク上のデータ ファイルを同期することで構成されます。 それはなんのためですか？ PostgreSQL が常にディスクにアクセスしてそこからデータを取得し、アクセスするたびにデータを書き込むと、速度が遅くなります。 したがって、PostgreSQL にはメモリ セグメントがあり、そのサイズは構成内のパラメータによって異なります。 Postgres は、さらなる処理やクエリのためにこのメモリに運用データを割り当てます。 データ変更リクエストの場合は変更されます。 そして、XNUMX つのバージョンのデータを取得します。 XNUMX つはメモリ上にあり、もう XNUMX つはディスク上にあります。 そして、このデータを定期的に同期する必要があります。 メモリ内で変更された内容をディスクに同期する必要があります。 これにはチェックポイントが必要です。

チェックポイントは共有バッファを通過し、チェックポイントに必要なダーティ ページにマークを付けます。 次に、共有バッファーを介した XNUMX 番目のパスを開始します。 そして、チェックポイントとしてマークされたページは、すでに同期されています。 したがって、データはすでにディスクと同期されています。

制御点には XNUMX 種類あります。 タイムアウト時に XNUMX つのチェックポイントが実行されます。 このチェックポイントは便利で良いものです - checkpoint_timed。 オンデマンドのチェックポイントもあります - checkpoint required。 このようなチェックポイントは、非常に大きなデータ レコードがある場合に発生します。 大量のトランザクションログを記録しました。 そして PostgreSQL は、これらすべてをできるだけ早く同期し、チェックポイントを作成して次に進む必要があると考えています。

そして統計を見てみると pg_stat_bgwriter そしてあなたが持っているものを見てください checkpoint_reqがcheckpoint_timedよりもはるかに大きい場合、これは問題です。 どうして悪いのですか？ これは、PostgreSQL がディスクにデータを書き込む必要があるときに、継続的なストレスにさらされていることを意味します。 タイムアウトによるチェックポイントはストレスが少なく、内部スケジュールに従って実行され、いわば時間の経過とともに実行されます。 PostgreSQL には、ディスク サブシステムに負担をかけずに作業を一時停止する機能があります。 これはPostgreSQLに便利です。 また、チェックポイント中に実行されるリクエストは、ディスク サブシステムがビジーであるという事実によるストレスを受けません。

チェックポイントを調整するには XNUMX つのパラメータがあります。

• сheckpoint_segments.

• сheckpoint_timeout.

• сheckpoint_competion_target.

これらを使用すると、コントロール ポイントの操作を制御できます。 しかし、私はそれらにこだわるつもりはありません。 彼らの影響力は別の問題です。

警告： レポートで考慮されているバージョン 9.4 は、もはや関連性がありません。 PostgreSQL の最新バージョンでは、パラメータ checkpoint_segments パラメータに置き換えられる min_wal_size и max_wal_size.

次のサブシステムはバックグラウンドライターです- background writer。 彼は何をしているの？ 常に無限ループで実行されます。 ページを共有バッファにスキャンし、見つかったダーティ ページをディスクにフラッシュします。 このようにして、チェックポイント作成者がチェックポイント作成中に行う作業を減らすことができます。

彼は他に何の必要があるのでしょうか？ これにより、データを収容するために共有バッファーにクリーンなページが突然 (大量かつ即時に) 必要になった場合に備えて、クリーンなページが必要になります。 リクエストにクリーンなページが必要で、それらのページがすでに共有バッファーにあるという状況が発生したとします。 ポストグレ backend 彼はそれらを取り出して使用するだけで、自分で何かを掃除する必要はありません。 しかし、突然そのようなページがなくなると、バックエンドは一時停止してページの検索を開始し、ページをディスクにフラッシュして独自の必要に応じて取得します。これは、現在実行中のリクエストの時間に悪影響を及ぼします。 パラメータがあることがわかった場合 maxwritten_clean 大きい場合、これはバックグラウンド ライターがその仕事を行っていないため、パラメーターを増やす必要があることを意味します。 bgwriter_lru_maxpagesXNUMX サイクルでより多くの作業を実行できるように、より多くのページをクリアします。

そして、もう XNUMX つの非常に便利な指標は、 buffers_backend_fsync. バックエンドは遅いため fsync を実行しません。 これらは fsync を IO スタック チェックポイントに渡します。 チェックポインタには独自のキューがあり、定期的に fsync を処理し、メモリ内のページをディスク上のファイルと同期します。 チェックポインタのキューが大きくていっぱいの場合、バックエンドは強制的に fsync 自体を実行することになり、バックエンドの速度が低下します。つまり、クライアントは応答を受信できるよりも遅くなります。 この値がゼロより大きいことがわかった場合、これはすでに問題となっており、 バックグラウンド ライタの設定に注意を払い、ディスク サブシステムのパフォーマンスも評価する必要があります。

警告： _次のテキストでは、レプリケーションに関連する統計ビューについて説明します。 Postgres 10 では、ほとんどのビュー名と関数名の名前が変更されました。名前変更の本質は、 xlog на wal и location на lsn 関数/ビュー名など特定の例、機能 pg_xlog_location_diff() 名前が変更されました pg_wal_lsn_diff()._

ここにもたくさんあります。 ただし、必要なのは場所に関連する項目だけです。

すべての値が等しい場合、これは理想的であり、レプリカはマスターに遅れをとっていません。

ここでの XNUMX 進数の位置は、トランザクション ログ内の位置です。 データベース内に挿入や削除などのアクティビティがある場合、この値は常に増加します。

сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

これらが異なる場合は、何らかの遅れが生じます。 ラグはマスターからのレプリカの遅れです。つまり、データはサーバー間で異なります。

遅延の理由は次の XNUMX つです。

• ファイル同期書き込みを処理できないのはディスク サブシステムです。
• これらは、データがレプリカに到達する時間がなく、データを再現できない場合に、ネットワーク エラーまたはネットワーク過負荷が発生する可能性があります。
• そしてプロセッサー。 プロセッサーは非常にまれなケースです。 私もそのような場面をXNUMX、XNUMX度見ましたが、そういうことも起こり得るのです。

そして、統計を使用できるようにする XNUMX つのクエリを次に示します。 トランザクション ログに記録される量を見積もることができます。 そんな機能あるんですね pg_xlog_location_diff レプリケーションの遅延をバイトと秒で見積もることができます。 これには、このビュー (VIEW) の値も使用します。

注意： _pg_xlog_location の代わりにdiff() 関数では、減算演算子を使用して、ある位置を別の位置から減算できます。 快適。

秒単位の遅れがあると、一瞬が存在します。 マスターにアクティビティがない場合、トランザクションは約 15 分前に存在していてアクティビティはありません。レプリカでこのラグを見ると、15 分のラグがわかります。 これは覚えておいて損はありません。 そして、この遅れを見ていると、呆然としてしまう可能性があります。

pg_stat_all_tables も便利なビューです。 テーブルの統計を表示します。 データベース内にテーブルがあり、それに伴う何らかのアクティビティやアクションが存在する場合、このビューからこの情報を取得できます。

select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;

まず最初に注目するのは、順次テーブル スキャンです。 これらの文章の後の数字自体は必ずしも悪いものではなく、すでに何かをする必要があることを示しているわけではありません。

ただし、1 番目のメトリクス、seq_tup_read があります。 これは、順次スキャンから返される行数です。 平均数が 000、10、000、50 を超える場合、これはすでに、アクセスがインデックスによって行われるようにどこかにインデックスを構築する必要がある可能性があることを示しています。または、そのような順次スキャンを使用するクエリを最適化して、こんなことは起こらない。

簡単な例として、大きな OFFSET と LIMIT を含むリクエストに価値があるとします。 たとえば、テーブル内の 100 行がスキャンされ、その後必要な 000 行が取得され、以前にスキャンされた行は破棄されます。 これも悪いケースです。 そして、そのようなリクエストは最適化する必要があります。 そして、これを見て、受け取った数値を評価できる単純な SQL クエリを次に示します。

select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;

テーブル サイズは、このテーブルと追加関数を使用して取得することもできます。 pg_total_relation_size(), pg_relation_size().

一般に、メタコマンドがあります dt и di、PSQL で使用でき、テーブルとインデックスのサイズも確認できます。

ただし、関数を使用すると、インデックスを考慮しても、またはインデックスを考慮せずにテーブルのサイズを確認し、データベースの成長に基づいて、つまり、データベースがどのように成長するかをすでに推定するのに役立ちます。どのような強度であるか、そしてサイジングの最適化についてすでにいくつかの結論を導き出しています。

アクティビティを書き込みます。 レコードとは何ですか? 操作を見てみましょう UPDATE – テーブル内の行を更新する操作。 実際、更新には XNUMX つの操作 (またはそれ以上) が必要です。 これは、新しい行バージョンを挿入し、古い行バージョンを廃止としてマークします。 後で、autovacuum が来て、これらの古いバージョンの行を削除し、この場所を再利用可能としてマークします。

また、更新はテーブルを更新するだけではありません。 まだインデックス更新中です。 テーブルに多数のインデックスがある場合、更新では、クエリで更新されたフィールドが参加するすべてのインデックスも更新する必要があります。 これらのインデックスには、クリーンアップが必要な古い行バージョンも含まれます。

select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;

また、その設計により、UPDATE は負荷の高い操作です。 しかし、もっと簡単にすることはできます。 食べる hot updates。 これらは PostgreSQL バージョン 8.3 で登場しました。 そして、これは何ですか？ これは、インデックスの再構築を行わない軽量の更新です。 つまり、レコードを更新しましたが、ページ内のレコード (テーブルに属する) のみが更新され、インデックスは依然としてページ内の同じレコードを指しています。 非常に興味深い作業ロジックがあり、真空が発生すると、これらの連鎖が発生します。 hot 再構築すると、インデックスを更新せずにすべてが動作し続け、すべてがリソースの無駄を減らして行われます。

そして、あなたが持っているとき n_tup_hot_upd 大きい、とても良いです。 これは、軽量アップデートが普及しており、リソースの点で私たちにとっては安くなり、すべてがうまくいくことを意味します。

ALTER TABLE table_name SET (fillfactor = 70);

音量を上げる方法 hot updateああ？ 使用できます fillfactor。 これは、INSERT を使用してテーブル内のページを埋めるときに予約される空き領域のサイズを決定します。 挿入が表に挿入されると、ページが完全に埋められ、ページ内に空のスペースが残りません。 次に、新しいページが強調表示されます。 データが再度入力されます。 これがデフォルトの動作で、fillfactor = 100% です。

fillfactor を 70% に設定できます。 つまり、挿入では新しいページが割り当てられましたが、ページの 70% しか埋まっていませんでした。 そして、残り30％が予備として残っています。 更新を行う必要がある場合、ほとんどの場合、同じページで更新が行われ、行の新しいバージョンが同じページに収まります。 そしてhot_updateが行われます。 これにより、表に書きやすくなります。

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

自動バキュームキュー。 Autovacuum は、PostgreSQL に統計がほとんどないサブシステムです。 現時点でのバキュームの数は、pg_stat_activity のテーブルでのみ確認できます。 ただし、外出先でキューにどれだけのテーブルがあるかを把握することは非常に困難です。

注意： _Postgres 10 以降、真空の追跡状況は大幅に改善されました - pg_stat_progress ビューが表示されましたこれにより、自動バキューム監視の問題が大幅に簡素化されます。

この簡略化されたクエリを使用できます。 そして、真空引きをいつ行うべきかが分かります。 しかし、掃除機はいつ、どのように開始すればよいのでしょうか? これらは、前に説明した文字列の古いバージョンです。 更新が発生し、行の新しいバージョンが挿入されました。 文字列の古いバージョンが登場しました。 テーブル pg_stat_user_tables そんなパラメータあるよ n_dead_tup。 「無効な」行の数を示します。 そして、無効な行の数が特定のしきい値を超えるとすぐに、自動バキュームが実行されます。

そして、この閾値はどのように計算されるのでしょうか? これは、テーブル内の総行数に対する非常に具体的な割合です。 パラメータがあります autovacuum_vacuum_scale_factor。 それによって割合が決まります。 10% + 追加の基本しきい値として 50 行があるとします。 そして何が起こるでしょうか？ テーブル内の全行の「10% + 50」よりも多くの無効な行がある場合、テーブルを自動バキュームに設定します。

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

ただし、ポイントが XNUMX つあります。 パラメータの基本しきい値 av_base_thresh и av_scale_factor 個別に割り当てられる場合があります。 したがって、しきい値はグローバルではなく、テーブルごとに個別になります。 したがって、計算するには、トリックやトリックを使用する必要があります。 ご興味がございましたら、Avito の同僚の体験談をご覧ください (スライド上のリンクは無効であり、本文内で更新されています)。

彼らはこう書いた むにんプラグインこれらのことを考慮したものになります。 足布がXNUMX枚付いています。 しかし、彼の考え方は正しく、非常に効果的に、真空がほとんどないテーブルのどこに多くの真空が必要かを評価することを可能にします。

それについて何ができるでしょうか? 長いキューがあり、自動バキュームが対応できない場合は、バキューム作業員の数を増やすか、単にバキュームをより積極的にすることができます。より早くトリガーされるように、テーブルを細かく処理します。 そうすれば行列も減ります。 - ここでの主な目的は、ディスクの負荷を監視することです。 SSD / NVMe デバイスの出現により、この問題はあまり目立たなくなりましたが、真空の問題は無料ではありません。

pg_stat_all_indexes はインデックスに関する統計です。 彼女は大きくありません。 そして、そこからインデックスの使用に関する情報を取得できます。 たとえば、どのインデックスが余っているのかを判断できます。

すでに言ったように、 update はテーブルを更新するだけでなく、インデックスも更新します。 したがって、テーブルに多数のインデックスがある場合、テーブル内の行を更新するときに、インデックス付きフィールドのインデックスも更新する必要があります。 インデックス スキャンが行われていない未使用のインデックスがある場合、それらはバラストとしてハングします。 そして、それらを取り除く必要があります。 このためにはフィールドが必要です idx_scan。 インデックス スキャンの数だけを見てみましょう。 比較的長期間の統計保存期間 (少なくとも 2 ～ 3 週間) にわたってインデックスのスキャンがゼロの場合は、不良インデックスである可能性が高いため、削除する必要があります。

注意： ストリーミング レプリケーション クラスターの場合に未使用のインデックスを検索するときは、クラスターのすべてのノードを確認する必要があります。 統計はグローバルではないため、インデックスがマスターで使用されていない場合は、(負荷がある場合) レプリカで使用できます。

XNUMX つのリンク:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

これらは、未使用のインデックスを検索する方法についてのより高度なクエリの例です。

XNUMX 番目のリンクはかなり興味深いクエリです。 そこには非常に自明ではないロジックが含まれています。 復習用にオススメします。

インデックスによって他に何を合計する必要がありますか?

• 未使用のインデックスは不良です。

• スペースを取ります。

• 更新操作を遅くします。

• 掃除機の追加作業。

使用されていないインデックスを削除すれば、データベースが改善されるだけです。

次の景色は、 pg_stat_activity。 これはユーティリティの類似物です ps、PostgreSQL のみ。 もしも ps「ああ、オペレーティング システムのプロセスを監視すると、 pg_stat_activity PostgreSQL 内のアクティビティが表示されます。

そこから何が得られるでしょうか？

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

データベース内で起こっている全体的なアクティビティを確認できます。 新しい展開を行うことができます。 そこではすべてが爆発し、新しい接続は受け入れられず、アプリケーションにエラーが押し寄せます。

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

このようなクエリを実行すると、最大接続制限に対する接続の合計パーセンテージが表示され、最も多くの接続を持っている人がわかります。 この特定のケースでは、ユーザーが cron_role 508 個の接続を開きました。 そして、彼に何かが起こりました。 それに対処して確認する必要があります。 そして、これはある種の異常な接続数である可能性が十分にあります。

OLTP ロードがある場合、クエリは高速かつ非常に高速である必要があり、長いクエリがあってはなりません。 ただし、長期にわたるリクエストの場合は、短期的には心配する必要はありませんが、 長期的には、長いクエリはデータベースに悪影響を及ぼし、テーブルの断片化が発生したときにテーブルの肥大化の影響を増大させます。 肥大化したクエリと長いクエリは両方とも破棄する必要があります。

select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;

注: このようなリクエストでは、長いリクエストとトランザクションを定義できます。 関数を使用します clock_timestamp() 労働時間を決めるため。 長いリクエストを見つけたので、覚えて実行できます explain、計画を見て、何とか最適化します。 現在の長いリクエストを撮影して生きています。

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

不正なトランザクションは、トランザクション内でアイドル状態、およびトランザクション内でアイドル状態 (中止された) のトランザクションです。

どういう意味ですか？ トランザクションには複数の状態があります。 そして、これらの状態のいずれかがいつでも発生する可能性があります。 状態を定義するフィールドがあります state この見方では。 そしてそれを状態を決定するために使用します。

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

そして、上で述べたように、これら XNUMX つの州は トランザクション中のアイドルとトランザクション中のアイドル (中止) は不良です。 それは何ですか？ これは、アプリケーションがトランザクションを開始し、いくつかのアクションを実行し、業務を開始したときです。 トランザクションはオープンのままです。 Postrges トランザクション エンジンのアーキテクチャにより、ハングし、何も起こらず、接続が取られ、変更された行がロックされ、他のテーブルがさらに肥大化する可能性があります。 そして、いずれにしても一般に有害であるため、そのような取引も排除されるべきです。

データベース内に 5 ～ 10 ～ 20 を超えるそれらが存在する場合は、心配してそれらを使って何かを始める必要があります。

ここでも計算時間に使用します clock_timestamp()。 私たちはトランザクションを記録し、アプリケーションを最適化します。

上で述べたように、ロックとは、XNUMX つ以上のトランザクションが XNUMX つまたはグループのリソースをめぐって競合することです。 このためにフィールドがあります waiting ブール値付き true または false.

True - これはプロセスが待機中であり、何かを行う必要があることを意味します。 プロセスが待機しているときは、そのプロセスを開始したクライアントも待機しています。 ブラウザ内のクライアントも座って待機します。

警告： _Postgres 9.6 以降、フィールド waiting 削除され、さらに XNUMX つの情報フィールドに置き換えられました wait_event_type и wait_event._

何をしますか？ 長い間真実であるとわかっている場合は、そのような要求を取り除く必要があります。 私たちはそのようなトランザクションを撮影するだけです。 私たちは、リソースの競合が起こらないように、何らかの方法で最適化する必要があるものを開発者に書きます。 そして開発者は、このようなことが起こらないようにアプリケーションを最適化します。

そして、極端だが同時に致命的ではない可能性のあるケースは、 デッドロックの発生。 XNUMX つのトランザクションが XNUMX つのリソースを更新し、その後、すでに反対側のリソースに再度アクセスします。 この場合、PostgreSQL はトランザクション自体を取得して終了し、他のトランザクションが引き続き動作できるようにします。 これは行き止まりの状況であり、彼女は自分自身を理解していません。 したがって、PostgreSQL は極端な措置を講じることを余儀なくされています。

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

次に、ロックを追跡できる XNUMX つのクエリを示します。 ビューを使用します pg_locks、重いロックを追跡できるようになります。

最初のリンクはリクエスト テキストそのものです。 かなり長いですね。

XNUMX 番目のリンクはロックに関する記事です。 読むと役に立ちますし、とても面白いです。

それで、何が見えるでしょうか？ XNUMX つのリクエストが表示されます。 との取引 ALTER TABLE ブロッキングトランザクションです。 開始されましたが終了せず、このトランザクションを投稿したアプリケーションはどこかで他のことを行っています。 そして XNUMX 番目のリクエストは更新です。 変更テーブルが完了するのを待ってから作業を続行します。

これにより、誰が誰を閉じ込めたのか、誰が誰を拘束しているのかを知ることができ、さらに対処することができます。

次のモジュールは pg_stat_statements。 先ほども言いましたが、これはモジュールです。 これを使用するには、そのライブラリを構成にロードし、PostgreSQL を再起動し、(XNUMX つのコマンドで) モジュールをインストールする必要があります。そうすれば、新しいビューが表示されます。

Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;

Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;

そこから何が得られるでしょうか？ 簡単なことについて言えば、クエリの平均実行時間を取得できます。 時間が伸びているということは、PostgreSQL の応答が遅くなっているため、何かを行う必要があることを意味します。

共有バッファー内のデータを変更する、データベース内で最もアクティブな書き込みトランザクションが確認できます。 そこで誰がデータを更新または削除したかを確認します。

そして、これらのクエリのさまざまな統計を確認するだけです。

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

我々 pg_stat_statements レポートの作成に使用されます。 統計は XNUMX 日に XNUMX 回リセットされます。 積み上げていきましょう。 次回統計をリセットする前に、レポートを作成します。 ここにレポートへのリンクがあります。 見ることができます。

私たちは何をしているのでしょうか？ すべてのリクエストの全体的な統計を計算します。 次に、クエリごとに、この全体的な統計に対する個々の寄与をカウントします。

そして何が見えるでしょうか？ 他のすべてのリクエストの背景に対して、特定のタイプのすべてのリクエストの合計実行時間を確認できます。 全体像と関連して CPU と I/O の使用状況を確認できます。 そして、これらのリクエストはすでに最適化されています。 私たちはこのレポートに基づいて上位のクエリを構築しており、何を最適化するかを検討する材料をすでに得ています。

舞台裏では何があるのでしょうか? 時間が限られているため、検討できなかった提案がまだいくつかあります。

あり pgstattuple これは、標準の contribs パッケージの追加モジュールでもあります。 評価できるようになります bloat いわゆるテーブル。 テーブルの断片化。 断片化が大きい場合は、断片化を削除し、別のツールを使用する必要があります。 そして機能 pgstattuple 長く働きます。 テーブルが多ければ多いほど、動作時間は長くなります。

次の投稿は pg_buffercache。 これにより、共有バッファを検査でき、どのテーブルのバッファ ページがどの程度集中的に使用されているかを確認できます。 また、共有バッファを調べて、そこで何が起こっているかを評価できるようになります。

次のモジュールは pgfincore。 システムコールを通じて低レベルのテーブル操作を実行できます。 mincore()つまり、テーブルを共有バッファにロードしたり、アンロードしたりできます。 また、特に、オペレーティング システムのページ キャッシュ、つまりページ キャッシュや共有バッファー内でテーブルがどれだけ占有されているかを検査することができ、単純にテーブルの負荷を評価することもできます。

次のモジュールは pg_stat_kcache。 システムコールも使用します getrusage()。 そして、リクエストが実行される前後にそれを実行します。 そして、取得された統計により、リクエストがディスク I/O、つまりファイル システムの操作にどれだけ費やしたかを推定し、プロセッサの使用状況を確認することができます。 ただし、このモジュールはまだ新しく (khe-khe)、その動作には PostgreSQL 9.4 と、前に述べた pg_stat_statements が必要です。

• 統計を使用できる機能は便利です。 サードパーティのソフトウェアは必要ありません。 見る、見る、何かをする、実行することができます。

• 統計の使用は簡単で、単純な SQL です。 リクエストを収集し、編集し、送信し、確認しました。

• 統計は疑問の解決に役立ちます。 質問がある場合は、統計に目を向けます。結果を見て、結論を導き出し、分析します。

• そして実験してみましょう。 たくさんのリクエスト、たくさんのデータ。 既存のクエリはいつでも最適化できます。 オリジナルよりも自分に合った独自のバージョンのリクエストを作成して使用できます。

リファレンス

記事内で見つかった有効なリンクは、それに基づいてレポートに含まれていました。

著者はもっと書く
https://dataegret.com/news-blog (英語)

統計コレクター
https://www.postgresql.org/docs/current/monitoring-stats.html

システム管理機能
https://www.postgresql.org/docs/current/functions-admin.html

投稿モジュール
https://www.postgresql.org/docs/current/pgstatstatements.html
https://www.postgresql.org/docs/current/pgstattuple.html
https://www.postgresql.org/docs/current/pgbuffercache.html
https://github.com/klando/pgfincore
https://github.com/dalibo/pg_stat_kcache

SQL ユーティリティと SQL コードの例
https://github.com/dataegret/pg-utils

ご清聴ありがとうございました！

出所： habr.com