【PostgreSQL】pg_stat_activityを理解する

pg_stat_activityとは？

サーバプロセスあたり1行の形式で、状態や現在の問い合わせなどのプロセスの現在の活動状況に関連した情報を表示する。

pg_stat_activityはシステムの現在の状態を表示するための定義済みのビューの一つであり、その中でも動的統計情報ビューです。
動的統計情報ビューはセキュリティ制限があり、一般ユーザーは自身のセッションに関する全情報だけを参照できる。他セッションに関する行では多くの列がNULLになる。

pg_stat_activityの実行結果

pg_stat_activityの実行結果は以下のとおり
それぞれのカラムの詳細については「pg_stat_activityのカラム」を確認して下さい。

postgres=# select * from pg_stat_activity where datname = 'postgres';
-[ RECORD 1 ]----+-----------------------------------------------------------
datid            | 12971
datname          | postgres
pid              | 179
leader_pid       | 
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2022-09-27 08:55:45.0233+00
xact_start       | 2022-09-27 08:57:18.528099+00
query_start      | 2022-09-27 08:57:18.528099+00
state_change     | 2022-09-27 08:57:18.528099+00
wait_event_type  | 
wait_event       | 
state            | active
backend_xid      | 
backend_xmin     | 732
query_id         | 
query            | select * from pg_stat_activity where datname = 'postgres';
backend_type     | client backend

pg_stat_activityのカラム

それぞれのカラムが何を示しているのかを纏めました。

列	型	説明
datid	oid	バックエンドが接続するデータベースのOIDです。
datname	name	バックエンドが接続するデータベースの名前です。
pid	integer	バックエンドのプロセスIDです。
usesysid	oid	バックエンドにログインしたユーザの識別子です。
usename	name	バックエンドに接続したユーザの名前です。
application_name	text	バックエンドに接続したアプリケーションの名前です。
client_addr	inet	バックエンドに接続したクライアントのIPアドレスです。 このフィールドがNULLである場合、これはクライアントがサーバマシン上のUnixソケット経由で接続されたか、自動バキュームなど内部処理であることを示します。
client_hostname	text	client_addrの逆引き検索により報告された、接続クライアントのホスト名です。 IP接続、かつlog_hostnameが有効である場合にのみこのフィールドは非NULLになります。
client_port	integer	クライアントがバックエンドとの通信に使用するTCPポート、もしUnixソケットを使用する場合は-1です。
backend_start	timestamp with time zone	プロセスが開始した時刻です。 クライアントのバックエンドについては、クライアントがサーバに接続した時刻です。
xact_start	timestamp with time zone	プロセスの現在のトランザクションが開始した時刻です。 活動中のトランザクションがない場合はNULLです。 現在の問い合わせがトランザクションの先頭である場合、この列はquery_start列と同じです。
query_start	timestamp with time zone	現在有効な問い合わせが開始した時刻です。 もしstateがactiveでない場合は直前の問い合わせが開始した時刻です。
state_change	timestamp with time zone	stateの最終変更時刻です。
wait_event_type	text	バックエンドが待機しているイベントがあれば、その型、なければNULLとなります。 以下のいずれかの値を取ることができます。 LWLock: バックエンドは軽量ロックを待っています。 このようなそれぞれのロックは、共有メモリ内の特定のデータ構造を保護します。 wait_eventには軽量ロックの目的を特定する名前が入ります。 （特定の名前がついたロックもあれば、似たような目的のロックのグループの一部となっているものもあります。） Lock: バックエンドは重量ロックを待っています。 ロックマネージャロックや単にロックとしても知られている重量ロックは、主にテーブルのようなSQLで可視なオブジェクトを保護します。 しかし、それらはリレーション拡張のような、なんらかの内部操作のために相互排他的に確保するためにも使用されます。 wait_eventは、待たせているロックの型を識別します。 BufferPin: サーバプロセスは、ほかのどのプロセスもデータバッファを調べることができない期間に、データバッファにアクセスするために待機しています。 バッファピン待機は、他のプロセスが該当のバッファから最後に読み込んだデータのオープンカーソルを保持している場合に長引かされることがあります。 Activity: サーバプロセスはアイドル状態です。 システムプロセスがメインの処理ループ内で活動を待機している時に使用されます。 wait_eventによりその待機点が特定できます。 Extension: サーバプロセスは拡張モジュール内で活動を待機しています。 この分類は、カスタム待機点を追跡するモジュールで役に立ちます。 Client: サーバプロセスはユーザアプリケーションによるソケット上での何らかの活動を待機しており、またサーバはその内部プロセスとは無関係の何かが起きることを期待しています。 wait_eventによりその待機点が特定できます。 IPC: サーバプロセスは、サーバ内の別のプロセスによる何らかの活動を待機しています。 wait_eventによりその待機点が特定できます。 Timeout: サーバプロセスはタイムアウトが満了するのを待機しています。 wait_eventによりその待機点が特定できます。 IO: サーバプロセスは入出力が完了するのを待機しています。 wait_eventによりその待機点が特定できます。
wait_event	text	バックエンドが現在待機している場合は待機イベント名、その他はNULL。詳細は別途まとめます。
state	text	現在のバックエンドの総体的な状態です。 以下のいずれかの値を取ることができます。 active: バックエンドは問い合わせを実行中です。 idle: バックエンドは新しいクライアントからのコマンドを待機しています。 idle in transaction: バックエンドはトランザクションの内部にいますが、現在実行中の問い合わせがありません。 idle in transaction (aborted): この状態はidle in transactionと似ていますが、トランザクション内のある文がエラーになっている点が異なります。 fastpath function call: バックエンドは近道関数を実行中です。 disabled: この状態は、このバックエンドでtrack_activitiesが無効である場合に報告されます。
backend_xid	xid	もしあれば、このバックエンドの最上位のトランザクション識別子。
backend_xmin	xid	現在のバックエンドのxmin。
query	text	バックエンドの最も最近の問い合わせテキストです。 stateがactiveの場合、現在実行中の問い合わせを意味します。 その他のすべての状態では、実行済みの最後の問い合わせを示します。 デフォルトでは問い合わせのテキストは1024バイトで切り捨てられますが、この値はパラメータtrack_activity_query_sizeにより変更できます。
backend_type	text	現在のバックエンドの種別です。 取り得る値はautovacuum launcher、autovacuum worker、logical replication launcher、logical replication worker、parallel worker, background writer、client backend、checkpointer、startup、walreceiver、walsender、walwriterです。 これに加えて、拡張によって登録されたバックグラウンドワーカは追加の型を持つかも知れません。

pg_stat_activityの何をどう見たらいいのか？

pg_stat_activityがどんな統計情報を持っているのかわかったと思います。
ここからはユースケース毎にどんな目的があってpg_stat_activityの何をどう見たらいいのかを纏めていきます。

ケース1：アクティブなセッションの監視

現在のデータベースの使用状況を把握し、どのユーザーがどのようなクエリを実行しているかを知るために実行する。

見るべきカラムは以下のとおり

• usename
• client_addr
• query
• state

以下のような状況が見られたら問題の可能性が高い

• 多数のセッションが同時にアクティブ状態で、これがデータベースのパフォーマンスに影響を与えている場合。
• 特定のユーザーから異常な数の接続が確認される場合（例: セキュリティ侵害の可能性）。
• 不審なネットワークアドレス（client_addr）からの接続が確認される場合。

ケース2:長時間実行されているクエリの特定

パフォーマンスのボトルネックとなっている長時間実行されているクエリを特定する。

見るべきカラムは以下のとおり

• query_start
• query
• state

以下のような状況が見られたら問題の可能性が高い

• query_start が非常に古い（例えば、現在時刻から数時間前）クエリがある場合。
• システムのパフォーマンスに影響を与えるほど長時間実行されているクエリがある場合。

ケース3:接続の管理

データベースに不要に接続されているセッションを特定し、これを終了させる。

見るべきカラムは以下のとおり

• pid
• state
• query
• state_change

以下のような状況が見られたら問題の可能性が高い

• state が長時間 idle（アイドル）のセッションが多数存在する場合（リソースの無駄使い）。
• 特定のクエリがstate_changeから長時間変化していない場合（デッドロックの可能性）。

ケース4:システムのヘルスチェック

データベースの全体的な健全性を確認し、システムの異常やパフォーマンスの問題を早期に特定する。

見るべきカラムは以下のとおり

• datname
• state
• wait_event_type
• wait_event

以下のような状況が見られたら問題の可能性が高い

• wait_event_type と wait_event により、多数のセッションが同じリソース（例: ロック）の待機でブロックされている場合。
• 特定のデータベース（datname）に異常な数のセッションが集中している場合。