DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

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

pg_stat_activityを使用して、アクティブなPostgreSQLクエリ、長時間トランザクション、ロック待機、およびキャンセルが必要なセッションを特定します。

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

データベースが突然遅くなった場合、PostgreSQLが現在何をしているかを知る必要があります。pg_stat_activityは、アクティブなクエリ、アイドルセッション、ロック待機、およびオープントランザクションを表示し、遅いクエリとブロックされたクエリを区別できます。

インシデント時に使用するだけでなく、ルーチンチェック用にいくつかの保存クエリを用意しておくと便利です。以下の例は、検査する必要があるセッションのアクティビティを読み取る権限があるPostgreSQLシステムに焦点を当てています。

pg_stat_activityビューの理解

pg_stat_activityは動的なシステムビューで、データベースクラスタに接続されているサーバープロセスごとに1行のデータを持ちます。これには、クライアントバックエンド、バックグラウンドワーカー、およびアイドル状態だが接続されたままのセッションが含まれます。

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

パフォーマンス分析のための主要なカラム

pg_stat_activityには多数のカラムがありますが、パフォーマンス問題を診断する際に重要なものは以下のとおりです。

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

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

pg_stat_activityの真の力は、データをフィルタリングして特定のパフォーマンスに関する質問に答えることにあります。

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

現在ステートメントを実行中のプロセス（アイドル状態ではない）のみを表示するには、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;

長時間実行クエリの特定

予想以上に長く実行されているクエリを特定することは、パフォーマンスチューニングの最初のステップとなることがよくあります。これらのクエリはリソースを消費し、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;

ワークロードに合ったしきい値を使用してください。OLTPアプリでは5秒のチェックアウトクエリは深刻かもしれませんが、レポートクエリがオフアワーに実行される場合、5分は正常かもしれません。

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

idle in transaction状態の接続は、トランザクションを開始したものの、コミットもロールバックもしていません。クライアントが次のコマンドを送信するのを待っています。これらのセッションはロックを保持し、古い行バージョンを可視状態に保つ可能性があり、autovacuumによるクリーンアップを遅らせ、テーブルの肥大化の原因となることがあります。

-- アイドル状態だがオープントランザクションを保持しているセッションを検索
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;

トランザクションを数分または数時間にわたって開いたまま保持しているセッションを見つけた場合は、トランザクションを開いたアプリケーションパスを確認してください。一般的な原因には、例外発生後のロールバック処理の欠落、クリーンアップ前のコネクションプールへの戻り、またはインタラクティブな管理セッションが開いたままになっていることなどがあります。

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

クエリがハングした場合、多くの場合、別のプロセスが保持しているロックを待っています。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;

「誰が誰をブロックしているか」を簡単に確認するために、PostgreSQLはpg_blocking_pids()も提供しています。

-- ブロックされたセッションとそれらをブロックしているセッションを表示
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

これにより、長時間の管理更新を待っているブロックされたWebリクエストが返された場合、管理クエリをキャンセルする方がアプリケーションセッションを終了するよりも安全な場合があります。

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

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

pg_cancel_backendによるクエリのキャンセル

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

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

pg_terminate_backendによるセッションの終了

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

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

セッションが単に悪いクエリを実行しているだけの場合は、最初にpg_cancel_backendを試してください。スタックしている、放棄されている、または正常にクリーンアップできないオープントランザクションを保持しているセッションには、pg_terminate_backendを使用してください。大規模なトランザクションのロールバックには時間がかかり、I/O負荷が増加する可能性があるため、意図的に実行してください。

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

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

本番環境でのデフォルトの習慣として、SELECT * FROM pg_stat_activityを避けてください。出力はノイズが多く、アプリケーションがバインドパラメータの代わりにリテラルを送信する場合、queryテキストが機密値を公開する可能性があります。必要なカラムのみを選択し、state、datname、application_name、または実行時間でフィルタリングしてください。

自動監視にツールを使用する

手動チェックはインシデント時に役立ちますが、傾向は監視に任せるべきです。PostgreSQLダッシュボードで、アクティブセッション、待機セッション、長時間トランザクション、およびidle in transactionの数を追跡してください。

ステートメントロギングを構成する

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

まとめ

3つの保存チェックを用意しておきましょう。実行時間順のアクティブクエリ、トランザクション経過時間順のアイドルトランザクション、およびブロッカーを含むブロックされたセッションです。PostgreSQLが遅いと感じたとき、これらのビューは、SQLをチューニングするか、トランザクション処理を修正するか、ブロックしているセッションをクリアするかを教えてくれます。