pg_stat_activityでリアルタイムにアクティブなトランザクションを追跡する

pg_stat_activityは、まさに今データベースで何が起きてるかをリアルタイムで覗けるウィンドウみたいなもの。前回の講義で基本はやったから、今回はこの強力なツールをもっと深掘りしていくよ。

基本的なpg_stat_activityへのクエリ例:

SELECT * 
FROM pg_stat_activity;

このクエリは全てのアクティブな接続と現在のクエリを表示するよ。いい感じ！でも、データが多すぎて全部見るのは大変。だから重要な情報だけをフィルタリングするのが便利なんだ。

pg_stat_activityの主なフィールド

すでに知ってるものに加えて、役立つキーとなるフィールドを見てみよう。query_startはクエリの実行開始時刻を示してて、長い処理を見つけるのに超重要。pidは接続プロセスのIDで、コネクションの管理（たとえば終了）に使う。state_changeは今の接続状態がセットされた時刻を示してて、長く続く問題状態の分析に特に便利だよ。

アクティブなプロセスのサンプルクエリ:

SELECT pid, usename, state, query, query_start 
FROM pg_stat_activity
WHERE state = 'active';

長いクエリをどうやって見つける？

例えば、君がDB管理者で、突然サーバーの負荷が爆上がりしたとしよう。どうする？まずはどのクエリがリソースを食いまくってるかを把握しないと。そんな時はpg_stat_activityで「食いしん坊」なクエリを探そう。

SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '10 seconds';

このクエリは10秒以上実行されてるクエリを全部表示するよ。intervalの値は自分のニーズに合わせて調整してね。

問題のあるクエリを終了する

じゃあ、もう長すぎてDBの邪魔になってるクエリをどうやって消すか見てみよう。pg_terminate_backend()関数を使えばプロセスを強制終了できるよ。

特定のPIDのプロセスを終了する例:

SELECT pg_terminate_backend(12345);

ここで12345はpg_stat_activityのpidフィールドの値だよ。

重要: プロセスを終了すると、正しく終わらなかったトランザクションはrollbackされるから、慎重にやろう。

もし「ハング」してるプロセス、たとえばidleトランザクションを自動で全部終了したいなら、次のPL/pgSQLブロックを使ってみて。もうプログラミングは勉強したから、loop（ループ）っていうのは、条件が満たされてる間やデータセットの処理が終わるまで、特定の命令を繰り返す構造だって知ってるよね：

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT pid 
        FROM pg_stat_activity 
        WHERE state = 'idle in transaction' 
          AND (now() - state_change) > interval '5 minutes'
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

このダイナミックなやり方で、問題のあるトランザクションをシステムからクリーンアップできるよ。FORループはクエリ結果の各レコードを回って、見つかったPIDごとにプロセス終了を実行するんだ。

もうすぐPL/pgSQLの勉強に入るから、あとちょっと待っててね :P

トランザクション状態でフィルタリング

時にはアクティブなクエリだけじゃなくて、たとえばidleやidle in transactionみたいな特別な状態の接続も知りたいことがあるよね。これで問題が深刻化する前に発見できるかも。

idle in transaction状態のトランザクションを見つけるクエリ例:

SELECT pid, usename, query, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction';

state_changeフィールドはその状態になった時刻を示してる。だから、何もしてないのに長く生きてるトランザクションを見つけて、DBリソースをブロックしてるかもって気づけるよ。

実践的な使い方

本番環境で長いクエリを監視: 一定時間を超えたクエリを定期的に監視して、SlackやTelegramとか好きな通知ツールでアラートを飛ばすように設定できるよ。これでパフォーマンス問題にすぐ対応できる。

インシデント時のクエリ分析: サーバーが重くなったら、まずpg_stat_activityを見て原因を探そう。これはパフォーマンス問題対応の標準プロトコルにしよう。

データベースのメンテナンス: pg_stat_activityを定期的に分析して、非効率なクエリを見つけて最適化しよう（たとえばインデックス追加やクエリの書き換えなど）。

監視のとき、間違ったフィルタやデータの解釈ミスでエラーが起きることもある。たとえばactive状態だけでフィルタすると、idle in transaction状態のクエリを見逃して、これがリソースブロックの原因になることも。もう一つのミスは、プロセスを勢いで終了しすぎて、トランザクションの予期しないロールバックやデータ損失を招くこと。いつも状況をよく見てから思い切ったアクションを取ろう。

追加の監視テクニック

もっと高度な監視をしたいなら、ユーザー別・データベース別・クエリタイプ別の統計を出す複雑なクエリも作れるよ。たとえば、各ユーザーがクエリ実行に平均どれくらい時間を使ってるかとか、アクティブ接続数が一番多いデータベースを探すとかね。

さらに、log_min_duration_statementやlog_statementみたいなPostgreSQLの設定パラメータを使って、長いクエリを自動でログファイルに記録するのも便利。これであとからパフォーマンス問題を分析したり、アプリの挙動パターンを見つけたりできるよ。