アクティブクエリの監視: パフォーマンスチューニングのための `pg_stat_activity` の利用

データベースのパフォーマンスは、効果的なリソース管理とボトルネックの迅速な特定に大きく依存します。PostgreSQLの管理者や開発者にとって、組み込みのシステムビューである pg_stat_activity は、リアルタイム監視と即時的なパフォーマンスチューニングのための最も重要なツールです。

このガイドでは、pg_stat_activity を活用して、すべてのアクティブなバックエンドプロセスを調査し、長時間実行されるクエリを特定し、接続の問題を診断し、ロック競合のトラブルシューティングを行い、健全で応答性の高いデータベース環境を維持する方法を探ります。

`pg_stat_activity` ビューの理解

pg_stat_activity は、データベースクラスタに接続されているすべてのサーバープロセス（バックエンド）について1行を提供する動的なシステムビューです。これには、クエリを実行中のクライアント、バックグラウンドワーカー、および接続を開いたままアイドル状態のプロセスが含まれます。

このビューを監視することで、データベースが現在何をしているのかを正確に把握でき、突然のパフォーマンス低下のデバッグや、標準的なログファイルでは効果的に捕捉できない一時的な競合問題の診断に非常に役立ちます。

パフォーマンス分析に不可欠な列

pg_stat_activity には数十の列が含まれていますが、パフォーマンスの問題を診断する際には、以下の列が不可欠です。

列名	説明	チューニングにおける関連性
`pid`	バックエンドのプロセスID。	セッションのキャンセルまたは終了に必要。
`datname`	バックエンドが接続しているデータベースの名前。	マルチデータベース環境での監視の範囲を絞るのに役立つ。
`usename`	接続を開始したユーザー。	特定のアプリケーションまたはユーザーアクティビティを特定する。
`application_name`	接続しているアプリケーション名（クライアントによって設定されている場合）。	特定のマイクロサービスからの接続を特定するのに優れている。
`state`	現在のアクティビティステータス（例: `active`、`idle`、`idle in transaction`）。	バックエンドが何をしているかの主要なインジケータ。
`query`	実行中の現在のクエリ（`state` が `idle` の場合は最後のクエリ）。	問題のあるSQLステートメントを特定する。
`query_start`	現在のクエリ実行が開始されたタイムスタンプ。	クエリの継続時間を計算するために使用される。
`wait_event_type` & `wait_event`	プロセスが待機しているものの詳細（例: ロック取得、I/O）。	競合やブロッキングの診断に不可欠。

実践的な監視ユースケース

pg_stat_activity の真の力は、特定のパフォーマンスの質問に答えるためにデータをフィルタリングすることから生まれます。

1. すべてのアクティブクエリの表示

アイドル状態ではなく、現在ステートメントを実行中のプロセスのみを表示するには、state 列でビューをフィルタリングします。

-- 現在実行中のすべてのクエリを表示
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

2. 長時間実行されるクエリと低速クエリの特定

予期したよりも長く実行されているクエリを特定することは、パフォーマンスチューニングの最初のステップであることがよくあります。これらのクエリはリソースを消費し、I/Oスパイクを引き起こしたり、ロックを保持したりする可能性があります。

特定のしきい値（例: 5秒）よりも長く実行されているクエリを特定するには、now() と query_start を使用して間隔の減算を行います。

-- 5秒より長く実行されているクエリを見つける
SELECT
    pid,
    usename,
    datname,
    state,
    (now() - query_start) AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '5 seconds'
ORDER BY duration DESC;

ヒント: 標準的なワークロードに基づいて、しきい値（5 seconds）をカスタマイズしてください。OLTP環境では、1秒を超えるものは遅いと見なされる場合があります。

3. アイドルイントランザクションセッションの診断

idle in transaction の接続は、トランザクションブロック（BEGIN）を開始したが、まだコミットまたはロールバックしておらず、現在、次のコマンドを発行するクライアントアプリケーションを待機していることを意味します。これらのセッションは、ロックを保持し、Vacuum操作を妨げ、ブロートやトランザクションIDの枯渇につながる可能性があるため、危険です。

-- 開いたトランザクションを保持したままアイドル状態のセッションを見つける
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - xact_start AS txn_duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY txn_duration DESC;

数分または数時間開いたトランザクションを保持しているセッションを見つけた場合、クライアントアプリケーションにロジックエラー（例: 例外後のコミットの失敗）があるか、設定ミス（例: 接続プーリングの問題）がある可能性があります。

4. ロック競合とブロッキングの分析

クエリがハングアップする場合、多くは別のプロセスによって保持されているロックを待機しています。競合を診断するには、pg_stat_activity ビューと pg_locks の組み合わせが不可欠です。

現在リソース（ロック、I/Oなど）を待機しているセッションを見つけるには、wait_event 列を確認します。セッションがブロックされている場合、その wait_event_type は通常 Lock になります。

-- 現在ロックによってブロックされているプロセスを特定する
SELECT
    a.pid,
    a.usename,
    a.query_start,
    a.query,
    a.wait_event,
    a.wait_event_type
FROM pg_stat_activity a
WHERE a.state = 'active'
  AND a.wait_event IS NOT NULL
ORDER BY a.query_start;

完全なロック分析（誰が誰を待っているか）を行うには、pg_stat_activity と pg_locks を結合し、ロックを保持しているプロセス（granted = true）とそれを待っているプロセス（granted = false）を相関させる必要があります。

問題のあるセッションの管理

プロセスID（pid）を使用して問題のあるクエリやセッションが特定されたら、PostgreSQLはそれを管理するための2つの関数を提供します。

1. クエリのキャンセル (`pg_cancel_backend`)

この関数は、特定のクエリの実行を優雅に停止しようとします。セッション自体は接続されたままで、将来のクエリに使用できます。

-- 例: PID 12345 で実行中のクエリをキャンセル
SELECT pg_cancel_backend(12345);

2. セッションの終了 (`pg_terminate_backend`)

この関数は、バックエンドプロセスをサーバーから強制的に切断します。セッションがトランザクションの途中であった場合、PostgreSQLは自動的にトランザクションをロールバックします。

-- 例: PID 54321 のセッションを強制終了
SELECT pg_terminate_backend(54321);

⚠️ 警告: 終了は控えめに使用してください

常に最初に pg_cancel_backend を試みてください。セッションの強制終了（pg_terminate_backend）は、応答しない、またはリソースを浪費しているセッションのために予約しておくべきです。大規模なトランザクションのロールバックは、場合によってはかなりのI/Oリソースを消費し、時間を要することがあるためです。

監視のベストプラクティス

積極的にフィルタリングする

本番サーバーで数千の接続がある場合に SELECT * FROM pg_stat_activity を実行しないでください。出力は通常圧倒的であり、クエリ自体がわずかなオーバーヘッドを追加する可能性があります。調査に集中するために、常に WHERE 句（例: WHERE state = 'active'）を使用してください。

自動化された監視にツールを使用する

手動チェックはトラブルシューティングに不可欠ですが、標準的な監視ツール（Prometheus、DataDog、または専用のPostgreSQLダッシュボードなど）に pg_stat_activity データを統合し、クエリ継続時間、平均アクティブ接続数、idle in transaction カウントの傾向を経時的に追跡します。

ステートメントロギングを設定する

リアルタイム監視と履歴データを組み合わせます。log_min_duration_statement のようなパラメータを設定して、特定のしきい値を超えるクエリをログに記録し、クエリが実行を終了した後でも分析用のデータを提供します。

結論

pg_stat_activity は、PostgreSQL DBAにとってサーバーのリアルタイム操作を把握するための不可欠な窓です。このビューを定期的にクエリおよびフィルタリングすることにより、パフォーマンスの問題を診断し、非効率的なSQLを特定し、ブロッキング状況を迅速に解決するために必要な即座の可視性を得ることができます。state、duration、wait_event の解釈を習得することで、パフォーマンスチューニングは受動的な作業からプロアクティブな管理プロセスへと変貌します。

アクティブクエリの監視: パフォーマンスチューニングのための pg_stat_activity の利用

pg_stat_activity ビューの理解