pg_stat_activity システムビューを使用して実行中の SQL クエリを診断する - AnalyticDB

pg_stat_activity は AnalyticDB for PostgreSQL のシステムビューであり、インスタンスで現在実行中のクエリを表示します。各行はサーバープロセスを示し、関連するユーザーセッションとクエリの詳細を表示します。このビューを使用して、実行中の SQL タスクを分析し、関連する問題をトラブルシューティングします。

前提条件

スーパーユーザーまたはプロセスのオーナーのみが pg_stat_activity ビューでその詳細を表示できます。

フィールドの説明

フィールド	型	説明
datid	oid	データベースの OID。
datname	name	データベース名。
procpid	integer	バックエンドプロセスの ID。説明 v4.3 のみでサポートされています。
pid	integer	バックエンドプロセスの ID。説明 v6.0 のみでサポートされています。
sess_id	integer	セッション ID。
usesysid	oid	ユーザーの OID。
usename	name	ユーザー名。
current_query	text	現在のクエリ。デフォルトでは、テキストは 1024 文字に切り捨てられます。より多く表示するには、`track_activity_query_size` パラメーターを使用します。説明 v4.3 のみでサポートされています。
query	text	直近のクエリのテキスト。`active` 状態の場合は、現在実行中のクエリが表示されます。他の状態の場合は、最後に実行されたクエリが表示されます。デフォルトでは、テキストは 1024 文字に切り捨てられます。より多く表示するには、`track_activity_query_size` パラメーターを使用します。説明 v6.0 のみでサポートされています。
waiting	boolean	バックエンドがロックを待機している場合は `True`、それ以外の場合は `false`。
query_start	datetime	現在アクティブなクエリが開始された時刻。`state` が `active` でない場合、このフィールドには前のクエリの開始時刻が表示されます。
backend_start	datetime	現在のバックエンドプロセスが開始された時刻。
backend_xid	xid	バックエンドプロセスの現在のトランザクション ID。
backend_xmin	xid	バックエンドの xmin ホライズン。
client_addr	inet	クライアントの IP アドレス。null 値は、ローカルの UNIX ソケット経由の接続、または autovacuum などの内部プロセスであることを示します。
client_port	integer	クライアントとバックエンド間の通信用の TCP ポート番号。UNIX ソケットが使用されている場合、値は -1 です。
client_hostname	text	`client_addr` の逆引き DNS ルックアップによって報告されるクライアントのホスト名。
application_name	text	アプリケーション名。
xact_start	timestamptz	現在のトランザクションの開始時刻。アクティブなトランザクションがない場合は NULL です。セッションの最初のトランザクションでは、この値は `query_start` と一致します。
waiting_reason	text	待機理由。ロックの待機やノード間のデータレプリケーションなど。
state	text	バックエンドの現在の状態。指定できる値は、active、idle、idle in transaction、idle in transaction (aborted)、fastpath function call、disabled です。説明 v6.0 のみでサポートされています。
state_change	timestampz	`state` が最後に変更された時刻。説明 v6.0 のみでサポートされています。
rsgid	oid	リソースグループの OID。
rsgname	text	リソースグループ名。
rsgqueueduration	interval	キューに入れられたクエリの場合、キュー内で費やされた合計時間。

接続情報の表示

次の SQL 文を実行して、現在接続しているユーザーとそのクライアントマシンを表示します。

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;

datname  |  usename  |  client_addr   | client_port
---------+----------+---------------+-------------
postgres | joe       |  xx.xx.xx.xx   |       60621
postgres | gpmon     |  xx.xx.xx.xx   |       60312
(9 rows)

SQL クエリ情報の表示

現在のユーザーによって実行された SQL クエリに関する情報を取得するには：

バージョン 6.0：

SELECT datname,usename,query FROM pg_stat_activity ;

 datname  | usename  |                        query
----------+---------+--------------------------------------------------------------
 postgres | postgres | SELECT datname,usename,query FROM pg_stat_activity ;
 postgres | joe      | 
(2 rows)

バージョン 4.3：

SELECT datname,usename,current_query FROM pg_stat_activity ;

 datname  | usename  |                        current_query
----------+---------+--------------------------------------------------------------
 postgres | postgres | SELECT datname,usename,current_query FROM pg_stat_activity ;
 postgres | joe      | <IDLE>
(2 rows)

現在実行中の SQL クエリに関する情報を取得するには：

バージョン 6.0：

SELECT datname,usename,query
   FROM pg_stat_activity
   WHERE state != 'idle' ;

バージョン 4.3：

SELECT datname,usename,current_query
   FROM pg_stat_activity
   WHERE current_query != '<IDLE>' ;

長時間実行クエリの検索

長時間実行されている SQL クエリを検索するには：

バージョン 6.0：

select current_timestamp - query_start as runtime, datname, usename, query
    from pg_stat_activity
    where state != 'idle'
    order by 1 desc;

バージョン 4.3：

select current_timestamp - query_start as runtime, datname, usename, current_query
    from pg_stat_activity
    where current_query != '<IDLE>'
    order by 1 desc;

以下は出力例です：

runtime         |    datname     | usename  |                                current_query
----------------+----------------+----------+------------------------------------------------------------------------------
00:00:34.248426 | tpch_1000x_col | postgres | select
                                             :         l_returnflag,
                                             :         l_linestatus,
                                             :         sum(l_quantity) as sum_qty,
                                             :         sum(l_extendedprice) as sum_base_price,
                                             :         sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
                                             :         sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
                                             :         avg(l_quantity) as avg_qty,
                                             :         avg(l_extendedprice) as avg_price,
                                             :         avg(l_discount) as avg_disc,
                                             :         count(*) as count_order
                                             : from
                                             :         public.lineitem
                                             : where
                                             :         l_shipdate <= date '1998-12-01' - interval '93' day
                                             : group by
                                             :         l_returnflag,
                                             :         l_linestatus
                                             : order by
                                             :         l_returnflag,
                                             :         l_linestatus;
 00:00:00        | postgres       | postgres | select
                                             :        current_timestamp - query_start as runtime,
                                             :        datname,
                                             :        usename,
                                             :        current_query
                                             :     from pg_stat_activity
                                             :     where current_query != '<IDLE>'
                                             :     order by 1 desc;
(2 rows)

この例では、最初のクエリは長時間実行されており、34 秒後もアクティブなままです。

異常な SQL クエリの診断と修正

SQL クエリが長時間実行されても結果が返されない場合は、まだ実行中であるか、ブロックされているかを確認します。

バージョン 6.0：

SELECT datname,usename,query
   FROM pg_stat_activity
   WHERE waiting;

バージョン 4.3：

SELECT datname,usename,current_query
   FROM pg_stat_activity
   WHERE waiting;

これはロックによってブロックされているクエリのみを特定し、I/O などの他の理由で待機しているクエリは特定しないことに注意してください。このクエリが行を返す場合、ロックが原因であることが確認されます。次のステップに進み、ブロックしているプロセスとブロックされているプロセスの詳細を取得します。

SELECT
        w.query as waiting_query,
        w.pid as w_pid,
        w.usename as w_user,
        l.query as locking_query,
        l.pid as l_pid,
        l.usename as l_user,
        t.schemaname || '.' || t.relname as tablename
    from pg_stat_activity w
    join pg_locks l1 on w.pid = l1.pid and not l1.granted
    join pg_locks l2 on l1.relation = l2.relation and l2.granted
    join pg_stat_activity l on l2.pid = l.pid
    join pg_stat_user_tables t on l1.relation = t.relid
    where w.waiting;

前のクエリの結果は、waiting_query と locking_query、およびそれらのプロセス ID (w_pid と l_pid) を特定します。これで、PID を使用してこれらのプロセスのいずれかをキャンセルすることで、ロックを解決できます。
実行中のクエリを正常にキャンセルするには：

SELECT pg_cancel_backend(pid)

このコマンドは、クエリをアクティブに実行しているセッションにのみ有効で、アイドル状態のセッションには無効です。キャンセルプロセスでは、正常なクリーンアップとトランザクションのロールバックが許可されますが、これには時間がかかる場合があります。
アイドル状態のセッションをクリーンアップするか、アクティブなクエリを終了するには：

SELECT pg_terminate_backend(pid);

ユーザーの接続は切断されます。クエリをアクティブに実行している PID での使用は避けてください。