本文由簡體中文內容自動轉碼而成。阿里雲不保證此自動轉碼的準確性、完整性及時效性。本文内容請以簡體中文版本為準。
首頁AnalyticDBAnalyticDB for PostgreSQL最佳實務通過pg_stat_activity分析診斷正在執行的SQL
搜尋幫助內容

通過pg_stat_activity分析診斷正在執行的SQL

更新時間：2024-02-05 10:27
社區

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

注意事項

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

pg_stat_activity視圖的欄位描述

欄位類型描述
欄位類型描述
datidoid資料庫OID。
datnamename資料庫名稱。
procpidinteger後端進程的進程ID。
說明
只有4.3版本支援procpid欄位。
pidinteger後端進程的進程ID。
說明
只有6.0版本支援pid欄位。
sess_idinteger會話ID。
usesysidoid使用者OID。
usenamename使用者名稱。
current_querytext當前正在執行的查詢。預設情況下，查詢文本最多顯示1024個字元，超出部分會被截斷，如需顯示更多字元，可以通過參數track_activity_query_size配置。
說明
只有4.3版本支援current_query欄位。
querytext最近查詢的文本。如果state為active，顯示當前正在執行的查詢。在其他狀態下，顯示上一個執行的查詢。 預設情況下，查詢文本最多顯示1024個字元，超出部分會被截斷，如需顯示更多字元，可以通過參數track_activity_query_size配置。
說明
只有6.0版本支援query欄位。
waitingboolean如果當前SQL在鎖等待，值為True，否則為False。
query_startdatetime當前活動查詢開始執行的時間。如果state不是active，顯示上一個查詢的開始時間。
backend_startdatetime當前後端進程的開始時間。
backend_xidxid後端進程當前的事務ID。
backend_xminxid後端的xmin範圍。
client_addrinet用戶端的IP地址。如果client_addr為空白，表示用戶端通過伺服器上的Unix通訊端串連，或者表示進程是內部進程（例如AUTOVACUUM）。
client_portinteger用戶端和後端通訊的TCP連接埠號碼。如果使用Unix通訊端，值為-1。
client_hostnametext用戶端主機名稱，通過client_addr的反向DNS尋找報告。
application_nametext用戶端應用程式名稱。
xact_starttimestamptz當前事務的啟動時間。如果沒有活動事務，值為空白。如果當前查詢是第一個事務，值與query_start的值相同。
waiting_reasontext當前執行等待的原因，可能是等鎖或者等待節點間資料的複製。
statetext後端的目前狀態，取值範圍：active，idle，idle in transaction，idle in transaction (aborted)，fastpath function call，disabled。
說明
只有6.0版本支援state欄位。
state_changetimestampz上次state狀態切換的時間。
說明
只有6.0版本支援state_change欄位。
rsgidoid資源群組OID。
rsgnametext資源群組名稱。
rsgqueuedurationinterval對於排隊查詢，查詢排隊的總時間。

查看串連資訊

通過下述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的許可權。

上一篇：通過Realtime ComputeFlink整合向量資料下一篇：匯入中特殊符號處理
  • 本頁導讀 （1, M）
  • 注意事項
  • pg_stat_activity視圖的欄位描述
  • 查看串連資訊
  • 查看SQL運行資訊
  • 查看耗時較長的查詢
  • 異常SQL診斷及修複
文檔反饋
phone 聯絡我們

立即和Alibaba Cloud在線服務人員進行交談，獲取您想了解的產品信息以及最新折扣。

alicare alicarealicarealicare