pg_stat_activityを使用したアクティブなSQLクエリの分析と診断 - AnalyticDB

pg_stat_activityは、AnalyticDB for PostgreSQLインスタンスでアクティブなSQLクエリを識別できるシステムビューです。 pg_stat_activityビューには、各行にサーバープロセスとその関連セッションとクエリが表示されます。 pg_stat_activityを使用して、アクティブなSQLクエリの診断を分析および実行し、問題をトラブルシューティングできます。

注意事項

スーパーユーザーとレポートプロセスの所有者のみが、pg_stat_activityビューを使用する権限を持っています。

pg_stat_activityビューのフィールド

項目	データ型	説明
datid	oid	データベースのオブジェクト識別子 (OID) 。
datname	name	データベースの名前。
procpid	integer	バックエンドプロセスのID。説明このフィールドは、AnalyticDB for PostgreSQL V4.3でのみサポートされます。
ピッド	integer	バックエンドプロセスのID。説明このフィールドはAnalyticDB for PostgreSQL V6.0でのみサポートされています。
sess_id	integer	セッションの ID
usesysid	oid	ユーザーのOID。
usename	name	ユーザー名を指定します。
current_query	text	実行中のクエリ。既定では、クエリテキストの長さは最大1,024文字にすることができ、追加の文字は切り捨てられます。クエリテキストにさらに多くの文字を表示するには、track_activity_query_sizeパラメーターを指定します。説明このフィールドは、AnalyticDB for PostgreSQL V4.3でのみサポートされます。
query	text	最新のクエリ。The most recent query. `state`フィールドが`active`に設定されている場合、実行中のクエリが表示されます。 stateフィールドがactive以外の値に設定されている場合、最後のクエリが表示されます。既定では、クエリテキストの長さは最大1,024文字にすることができ、追加の文字は切り捨てられます。クエリテキストにさらに多くの文字を表示するには、track_activity_query_sizeパラメーターを指定します。説明このフィールドはAnalyticDB for PostgreSQL V6.0でのみサポートされています。
待っている	Boolean	現在のクエリがロックを待機しているかどうかを示します。有効な値：True と False。
query_start	datetime	アクティブなクエリが開始された時刻。 `state`フィールドが`active`以外の値に設定されている場合、query_startフィールドは最後のクエリが開始された時刻を示します。
backend_start	datetime	現在のバックエンドプロセスが開始された時刻。
backend_xid	xid	現在のバックエンドプロセスのトランザクションID。
backend_xmin	xid	バックエンドのxmin地平線。
client_addr	inet	クライアントの IP アドレス。 `client_addr`が空のままになっている場合、クライアントはサーバー上のUNIXソケットを使用して接続されているか、自動真空などの内部プロセスです。
client_port	integer	クライアントとバックエンド間の通信に使用されるTCPポート。値が-1の場合は、UNIXソケットが使用されていることを示します。
client_hostname	text	クライアントのホスト名。このフィールドは、`client_addr`の逆DNSルックアップによって報告できます。
application_name	text	クライアント上のアプリケーションの名前。
xact_start	timestamptz	現在のトランザクションが開始された時刻。アクティブなトランザクションがない場合、このフィールドは空のままになります。現在のクエリが最初のトランザクションの場合、このフィールドは`query_start`フィールドに相当します。
waiting_reason	text	バックエンドが待っている理由。バックエンドは、ロックまたはノード間のデータの複製を待っています。
です。	text	バックエンドの現在の状態。有効な値: active、idle、idle in transaction、idle in transaction (aborted) 、fastpath関数呼び出し、disabled。説明このフィールドはAnalyticDB for PostgreSQL V6.0でのみサポートされています。
state_change	timestampz	`状態`が最後に変更された時刻。説明このフィールドはAnalyticDB for PostgreSQL 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文を実行して、データベースに接続するユーザーが実行した現在のクエリを表示できます。

AnalyticDB for PostgreSQL V6.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)

AnalyticDB for PostgreSQL V4.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文を実行して、アクティブなクエリを表示できます。

AnalyticDB for PostgreSQL V6.0:

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

AnalyticDB for PostgreSQL V4.3:

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

長期実行クエリの表示

次のSQL文を実行して、長時間実行されるクエリを表示できます。

AnalyticDB for PostgreSQL V6.0:

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

AnalyticDB for PostgreSQL V4.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クエリが結果を返さずに長期間実行されている場合は、クエリがブロックされているかどうかを確認する必要があります。

AnalyticDB for PostgreSQL V6.0:

SELECT datname,usename,query
   FROM pg_stat_activity
   WHERE waiting;

AnalyticDB for PostgreSQL V4.3:

SELECT datname,usename,current_query
   FROM pg_stat_activity
   WHERE waiting;

上記のステートメントは、ロック待ちのためにのみブロックされたSQLクエリを返すことができます。その他の理由でブロックされたクエリは返されません。ほとんどの場合、SQLクエリはロック待機によりブロックされます。ただし、その他の場合には、SQLクエリはI/O操作またはタイマーを待機しているためブロックされます。上記のステートメントでクエリ結果が返された場合、ロック待機により特定のSQLクエリがブロックされます。次のステートメントを実行して、これらのSQLクエリの詳細を表示できます。

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;

上記のステートメントは、ブロックされたSQLクエリとそのプロセスIDを返すことができます。ブロックされたSQLクエリの詳細を確認し、クエリをキャンセルまたは強制終了してブロックを削除できます。次のステートメントを実行して、アクティブなクエリをキャンセルできます。

SELECT pg_cancel_backend(pid)

pg_cancel_backend関数は、アクティブなクエリセッションでのみ有効になります。この関数はアイドルセッションには影響しません。さらに、クエリをキャンセルすると、セッションをクリアしたり、トランザクションをロールバックしたりするのに時間がかかります。 pg_terminate_backendを使用して、アイドルセッションをクリアしたり、クエリを終了したりできます。

SELECT pg_terminate_backend(pid);

指定されたユーザーによって開始された接続は終了します。アクティブなクエリが存在するpidで識別されるプロセスでは、pg_terminate_backend関数を呼び出さないことを推奨します。このトピックで説明した操作を実行するには、スーパーユーザー権限が必要です。