🕣 ⚪️ 👨🏼‍✈️ PostgreSQL：DBAの分析 👩🏼‍⚕️ ❄️ ✝️

多くのPostgreSQL DBMSユーザーは、サーバーが動作中にさまざまな統計を収集することを知っていますが、誰もがそれを分析し、そのためにそれを抽出する方法が有用であることを知っているわけではありません。この小さなツールキットには、常に蓄積されているこの「隠された知識」の使用方法に関する洞察を提供するいくつかの便利なクエリが含まれています。これらのクエリを使用して、PostgreSQLの状態を監視し（手動で、またはNagios、Cacti、Zabbixなどのシステムを監視するプラグインを使用して）、サーバーのボトルネックやその他の多くの同様のタスクを見つけることができます。これは氷山の一角にすぎないことを忘れないでください。ドキュメントでは、数十個のシステムビューの説明を見つけることができます。これは、PostgreSQL管理者にも役立つ場合があります。

ツールキットが正しく機能するには、postgresql.confでstats_block_levelおよびstats_row_levelオプションを有効にし、必要に応じてstats_reset_on_server_startパラメーターを構成する必要があります。 PostgreSQLサーバーを再起動するたびに、その構成のいくつかの重要なパラメーターを変更する場合、行われた変更の影響を追跡するために統計をリセットすることは理にかなっています。長期に関心があり、再起動がPostgreSQLの設定変更によるものではない場合、stats_reset_on_server_startパラメーターをoffに設定します。

ヒット/読み取り率

クエリを実行する際、PostgreSQLはまず共有メモリ（共有バッファ）にクエリ内のデータがあるかどうかを確認します。それらが見つかった場合、ヒットがカウントされ、そうでない場合は、比較的遅いfreadシステムコールがディスクまたはオペレーティングシステムのディスクキャッシュからデータを生成するために行われ、読み取りがカウントされます。平均して、ルールは真です。ヒット/読み取り率が高いほど、PostgreSQLはディスクからほとんど読み取らず、主に共有メモリからデータを取得するため、より適切に構成されます。それほど大きくないデータベースの場合、この比率は5000〜10000の範囲にある必要があります。ただし、shared_buffers設定を人為的に過大評価しないでください。この設定は、ヒット/読み取りを直接決定します。共有メモリサイズが大きすぎると、集中的なレコーディングデータベースのパフォーマンスが低下します。また、データがOSディスクキャッシュにある場合、freadは非常に高速になる可能性があることを覚えておく価値があります。

 選択 
       datname 
      事例 
         blks_read = 0の場合、0 
         ELSE blks_hit / blks_read 
       END AS比率 
    から 
       pg_stat_database;

テーブルで発生した変更の数

テーブルごとのリスト：最後の統計のリセット以降に追加、変更、および削除されたレコードの数。 DBAは、現在のデータベースで最もロードされているテーブルと、それらに対するさまざまなタイプの変更クエリ間の関係を表す必要があります。

    選択 
       relname、 
       n_tup_ins 
       n_tup_upd、 
       n_tup_del 
    から 
       pg_stat_user_tables 
     ORDER BY 
       n_tup_upd DESC;

シーケンススキャン/インデックススキャン統計

テーブルのリスト：シーケンシャルビューで実行されたテーブルへのクエリの数。インデックスを使用して実行されたクエリの数。これら2つの数値の比率も同様です。必要なすべてのインデックスがこのテーブルに作成されているかどうかを評価できます。テーブルに数千行以上ある場合、シーケンシャルスキャンはインデックススキャンよりも遅くなります。したがって、理想的なケースでは、そのようなテーブルにseqscansが存在するべきではありません。まだある場合は、そのようなテーブルのクエリを分析し、対応するインデックスを作成します。無理をしないことが重要です。テーブル列のインデックスが多いほど、データ更新操作のコストが高くなります。

また、インデックスを作成した後、テーブルをANALYZEにする必要があることを忘れないでください。そうしないと、クエリプランナーはテーブル構造の変更に気付かないでしょう。

 選択 
       relname、 
       seq_scan、 
       idx_scan、 
      事例 
         idx_scan = 0の場合、100 
         ELSE seq_scan / idx_scan 
       END AS比率 
    から 
       pg_stat_user_tables 
     ORDER BY 
      比率DESC;

インデックス統計

インデックスごとのリスト：このインデックスのクエリでインデックスから返されたレコードの数。親テーブルで同時に表示する必要がある行数。これら2つの数値の比率。これらの統計の本質は単純です：フェッチが多い場合、インデックスは古くなり、クエリを実行するときに、PostgreSQLは最も関連性の高いデータのソースとしてテーブルを直接見るように強制され、その動作が遅くなります。この場合、テーブル内の実際のデータと一致するように、このインデックスを再構築する必要があります。

 選択 
       indexrelname 
       idx_tup_read、 
       idx_tup_fetch、 
      事例 
         idx_tup_fetch = 0の場合、100 
         ELSE idx_tup_read / idx_tup_fetch 
       END AS比率 
    から 
       pg_stat_user_indexes 
     ORDER BY 
      比率DESC;

期間を指定してリクエストを実行する

現在実行中のサーバーリクエストの簡単なリスト。システムを十分に知らない場合、または単にシステムを構成することができなかった場合に役立ちます。サーバーに干渉する「悪い」要求を見つけて中断するために使用できます（procpid列にはプロセスのPIDが含まれ、必要に応じて強制終了できます）。ただし、実行中のクエリを定期的に確認するだけで、優れたpgFouineログアナライザーが置き換えられることはありません。また、このリクエストを実行するプロセスも結果リストに含まれることを忘れないでください。

 選択 
       datname 
       NOW（）-query_start AS期間、 
       procpid 
       current_query	
    から
       pg_stat_activity 
     ORDER BY期間DESC;

現在のロックのリスト

ロックのタイプ、ロックが設定されているテーブルとデータベース、およびロックを設定したトランザクション番号を含む現在のロックのリスト。リクエストがロックの長いリストを提供する場合、恐れてはいけません-それらのすべてが重要ではなく、すべての可能な変更と読み取りからもテーブルをブロックします。ロックのリストを分析するには、PostgreSQLのロックの種類と、それらが発行されるクエリのタイミングと内容に関するドキュメントを確実に理解する必要があります。ロックリストが便利な1つの一般的な使用例：ps aux | grep ^ postgresを見ると、postgresプロセスの1つのステータスが待機していることがわかります-これは、このプロセスがロックの削除を待機していることを意味します。どちらか-このクエリを実行して確認してください。

 選択 
       l.mode、 
       d.datname、 
       c.relname、 
       l.granted、 
       l.transactionid 
    から 
       pg_locks AS l 
     LEFT JOIN pg_database AS d ON l.database = d.oid 
     LEFT JOIN pg_class AS c ON l.relation = c.oid;

PostgreSQL：DBAの分析