通過pg_stat_activity分析診斷正在執行的SQL - AnalyticDB for PostgreSQL

pg_stat_activity是雲原生資料倉儲AnalyticDB PostgreSQL版用來定位執行個體當前執行查詢的系統檢視表，每行顯示一個伺服器處理序同時詳細描述與之關聯的使用者會話和查詢，可以有效協助使用者分析排查當前啟動並執行SQL任務以及異常問題。

注意事項

只有superuser使用者或者是正在報告的進程的擁有者時，才可以使用pg_stat_activity視圖。

pg_stat_activity視圖的欄位描述

欄位	類型	描述
datid	oid	資料庫OID。
datname	name	資料庫名稱。
procpid	integer	後端進程的進程ID。說明只有4.3版本支援procpid欄位。
pid	integer	後端進程的進程ID。說明只有6.0版本支援pid欄位。
sess_id	integer	會話ID。
usesysid	oid	使用者OID。
usename	name	使用者名稱。
current_query	text	當前正在執行的查詢。預設情況下，查詢文本最多顯示1024個字元，超出部分會被截斷，如需顯示更多字元，可以通過參數track_activity_query_size配置。說明只有4.3版本支援current_query欄位。
query	text	最近查詢的文本。如果`state`為`active`，顯示當前正在執行的查詢。在其他狀態下，顯示上一個執行的查詢。預設情況下，查詢文本最多顯示1024個字元，超出部分會被截斷，如需顯示更多字元，可以通過參數track_activity_query_size配置。說明只有6.0版本支援query欄位。
waiting	boolean	如果當前SQL在鎖等待，值為True，否則為False。
query_start	datetime	當前活動查詢開始執行的時間。如果`state`不是`active`，顯示上一個查詢的開始時間。
backend_start	datetime	當前後端進程的開始時間。
backend_xid	xid	後端進程當前的事務ID。
backend_xmin	xid	後端的xmin範圍。
client_addr	inet	用戶端的IP地址。如果`client_addr`為空白，表示用戶端通過伺服器上的Unix通訊端串連，或者表示進程是內部進程（例如AUTOVACUUM）。
client_port	integer	用戶端和後端通訊的TCP連接埠號碼。如果使用Unix通訊端，值為-1。
client_hostname	text	用戶端主機名稱，通過`client_addr`的反向DNS尋找報告。
application_name	text	用戶端應用程式名稱。
xact_start	timestamptz	當前事務的啟動時間。如果沒有活動事務，值為空白。如果當前查詢是第一個事務，值與`query_start`的值相同。
waiting_reason	text	當前執行等待的原因，可能是等鎖或者等待節點間資料的複製。
state	text	後端的目前狀態，取值範圍：active，idle，idle in transaction，idle in transaction (aborted)，fastpath function call，disabled。說明只有6.0版本支援state欄位。
state_change	timestampz	上次`state`狀態切換的時間。說明只有6.0版本支援state_change欄位。
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)

可以看到第一個查詢耗時較久，已經運行了34s還沒有結束。

異常SQL診斷及修複

如果一個SQL運行很長時間沒有返回結果，需要檢查該SQL還在運行中還是已經被Block：

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;

需要注意的是上述返回結果只能擷取當前因為Lock而被Block的SQL，無法擷取因為其他原因被Block的SQL。絕大多數情況下SQL都是因為Lock而被Block，但也會存在其他情況，例如等待I/O、定時器等。如果上述SQL有返回結果，說明有SQL被Lock阻塞，需要進一步明確相互Block的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的返回結果可以確認相互Block的SQL和對應的執行pid。在明確了SQL的阻塞資訊後，可以通過Cancel或者Kill Query的方式進行恢複。通過Cancel取消一個正在啟動並執行Query：

SELECT pg_cancel_backend(pid)

需要在一個運行Query的Session中執行，如果Session本身就是Idle的，執行不起作用。另外取消這個Query需要花費一定的時間來做清理和事務的復原。使用pg_terminate_backend來清理Idle Session，也可以用來終止Query：

SELECT pg_terminate_backend(pid);

該使用者的串連會斷開。盡量避免在正在運行Query的進程pid上執行。需要注意的是文中提到操作需要使用者有superuser的許可權。