本文將為您介紹如何對執行個體中的Query進行診斷和管理。
Query管理概述
Hologres相容PostgreSQL,可以通過查詢hg_stat_activity(pg_stat_activity)視圖資訊來查看執行個體Query的運行資訊,以達到分析和診斷運行SQL的目的。具體涉及的操作內容如下所示:
使用SQL查看活躍Query:查看SQL運行資訊,更好的管理SQL語句。
HoloWeb可視化活躍Query管理:通過HoloWeb可視化查看和管理活躍Query。
排查鎖:通過活躍Query排查當前SQL是否有鎖或者被鎖。
終止Query:使用命令語句終止不符合預期的Query。
修改活躍Query逾時時間:修改活躍Query運行逾時時間,防止引發死結。
修改空閑Query逾時時間:修改空閑Query運行逾時時間,防止引發死結。
查詢慢Query日誌:通過慢Query的查詢可以對慢Query或失敗Query進行診斷、分析和採取最佳化措施。
常見問題:出現
ERROR: canceling statement due to statement timeout
報錯的原因和解決方案。
查詢hg_stat_activity(pg_stat_activity)視圖資訊
從Hologres V2.0版本開始,pg_stat_activity升級為hg_stat_activity視圖。hg_stat_activity是pg_stat_activity視圖的擴充,可以通過hg_stat_activity查詢到活躍SQL更詳細的運行資訊,包括query id
、query engine
以及資源消耗等資訊,從而協助更進一步診斷活躍Query。您可以執行如下命令查看Query的運行資訊。其他Postgres相容管理工具,依舊可以查詢pg_stat_activity擷取SQL運行態資訊。
--2.0及以上推薦文法
SELECT * FROM hg_stat_activity;
--1.3及以下版本文法
SELECT * FROM pg_stat_activity;
hg_stat_activity視圖的欄位說明如下所示:
欄位 | 描述 | hg_stat_activity視圖支援情況 | pg_stat_activity視圖支援情況 |
datid | Hologres後端串連到的資料庫的OID。 | 支援 | 支援 |
datname | Hologres後端串連到的資料庫的名稱。 | 支援 | 支援 |
pid | Hologres後端的進程ID。 | 支援 | 支援 |
query_id | 當前執行SQL的ID,一個SQL對應一個query_id。 | 支援 | 不支援 |
transaction_id | 當前SQL所屬的事務ID,一個事務中可能有多個SQL。 | 支援 | 不支援 |
usesysid | 當前活躍會話使用者的系統標識符。 | 支援 | 支援 |
usename | 當前串連的使用者名稱。 | 支援 | 支援 |
application_name | 用戶端的應用類型。 其中常見的應用類型如下:
其餘應用建議業務上在串連Hologres時串連串上顯式指定 | 支援 | 支援 |
running_info | 包含SQL執行過程各種狀態資訊,格式為JSON格式。其子欄位包括:
| 支援 | 不支援 |
extend_info | 包含SQL執行過程資源消耗等的擴充資訊,為JSON格式。其子欄位包括:
說明 be_lock_waiters和be_lock_converting_waiters採集的都是backend的鎖,而不是frontend的鎖,排查鎖請參見鎖以及排查鎖。 | 支援 | 不支援 |
state | 表示串連的狀態。常見的狀態如下:
| 支援 | 支援 |
query_start | Query開始執行的時間,如果active不是active,取值為上一個查詢開始的時間。 | 支援 | 支援 |
client_addr | 用戶端的IP地址。 顯示的IP地址可能是被解析過的,不保證一定是源端IP。 | 支援 | 支援 |
client_hostname | 用戶端的主機名稱。 | 支援 | 支援 |
client_port | 用戶端的連接埠。 | 支援 | 支援 |
backend_start | 後台進程開始的時間。 在Hologres中無需過多關注。 | 支援 | 支援 |
xact_start | 該進程的當前事務被啟動的時間。
在Hologres中無需過多關注。 | 支援 | 支援 |
state_change | 串連的狀態(state)上一次被改變的時間。 在Hologres中無需過多關注。 | 支援 | 支援 |
wait_event_type | 後端正在等待的事件類型,如果不存在則為NULL。可能的取值有:
| 支援 | 支援 |
wait_event | 如果後端當前正在等待,則是等待事件的名稱,否則為 NULL。 | 支援 | 支援 |
backend_xid | Hologres後端的頂層事務標識符。 | 支援 | 支援 |
backend_xmin | 當前後端的xmin範圍。 | 支援 | 支援 |
query | 後端最近執行的查詢。如果state為 | 支援 | 支援 |
backend_type | 當前後端的類型。可能的類型為autovacuum launcher、autovacuum worker、logical replication launcher、logical replication worker、parallel worker、background writer、client backend、checkpointer、startup、walreceiver、walsender以及 walwriter。除此之外還包括後端的動作項目,例如PQE等。 說明 需要關注 | 支援 | 支援 |
使用SQL查看活躍Query
若您更傾向於使用SQL方式查詢活躍Query,可以使用以下SQL進行操作:
查看當前活躍Query以及對應的執行階段和資源消耗:
說明Superuser可以查看所有使用者的SQL運行資訊,RAM使用者只能查看自己的SQL運行資訊。
--2.0及以上版本文法 SELECT query,state,query_id,transaction_id,running_info, extend_info FROM hg_stat_activity WHERE state = 'active' AND backend_type = 'client backend' AND application_name != 'hologres' --1.3及以下版本文法 SELECT query,state,pid FROM pg_stat_activity WHERE state = 'active' AND backend_type = 'client backend' AND application_name != 'hologres'
樣本返回結果:
------------------------------------------------------------------------------- query | insert into test_hg_stat_activity select i, (i % 7) :: text, (i % 1007) from generate_series(1, 10000000)i; state | active query_id | 100713xxxx transaction_id | 100713xxxx running_info | {"current_stage" : {"stage_duration_ms" :5994, "stage_name" :"EXECUTE" }, "engine_type" :"{HQE,PQE}", "fe_id" :1, "warehouse_id" :0 } extend_info | {"affected_rows" :9510912, "scanned_rows" :9527296 }
當前啟動並執行Query按照CPU消耗排序:
--2.0及以上文法 SELECT query,((extend_info::json)->'total_cpu_max_time_ms')::text::bigint AS cpu_cost,state,query_id,transaction_id FROM hg_stat_activity WHERE state = 'active' ORDER BY 2 DESC;
樣本返回結果:
--------------------------------------------------------------------------------- query | select xxxxx cpu_cost | 523461 state | active query_id | 10053xxxx transaction_id | 10053xxxx --------------------------------------------------------------------------------- query | insert xxxx cpu_cost | 4817 state | active query_id | 1008305xxx transaction_id | 1008305xxx
當前啟動並執行Query按照記憶體消耗排序:
--2.0及以上文法 SELECT query,((extend_info::json)->'total_mem_max_bytes')::text::bigint AS mem_max_cost,state,query_id,transaction_id FROM hg_stat_activity WHERE state = 'active' ORDER BY 2 DESC;
樣本返回結果:
--------------------------------------------------------------------------------- query | update xxxx; mem_max_cost | 5727634542 state | active query_id | 10053302784827629 transaction_id | 10053302784827629 --------------------------------------------------------------------------------- query | select xxxx; mem_max_cost | 19535640 state | active query_id | 10083259096119559 transaction_id | 10083259096119559
查看當前執行個體正在運行且耗時較長的Query:
--2.0及以上文法 SELECT current_timestamp - query_start AS runtime, datname::text, usename, query, query_id FROM hg_stat_activity WHERE state != 'idle' AND backend_type = 'client backend' AND application_name != 'hologres' ORDER BY 1 DESC; --1.3及以下文法 SELECT current_timestamp - query_start AS runtime, datname::text, usename, query, pid FROM pg_stat_activity WHERE state != 'idle' AND backend_type = 'client backend' AND application_name != 'hologres' ORDER BY 1 DESC;
樣本返回結果:
runtime | datname | usename | query_id | current_query -----------------+----------------+----------+------------------------------------ 00:00:24.258388 | holotest | 123xxx | 1267xx | UPDATE xxx; 00:00:1.186394 | testdb | 156xx | 1783xx | select xxxx;
可以看到update耗時較久,已經運行了24s還沒有結束。
HoloWeb可視化活躍Query管理
您可以通過HoloWeb可視化查看活躍Query,並進行管理。
登入HoloWeb控制台,詳情請參見串連HoloWeb並執行查詢。
單擊頂部導覽列的診斷與最佳化。
在左側導覽列選擇活躍資訊管理 > 活躍Query。
進入活躍Query頁面,單擊查詢,查看當前執行個體的活躍Query及對活躍Query進行管理。
查詢結果清單包含如下資訊:
參數
說明
Query Start
查詢開始時間。
Runtime
查詢已耗用時間。
PID
查詢服務進程ID。
Query
查詢執行的SQL語句。
State
當前串連的狀態。常見的狀態如下:
active:活躍。
idle:空閑。
idle in transaction:長事務中的空閑狀態。
idle in transaction(Aborted):已失敗事務中的空閑狀態。
\N: 狀態為空白,表示非使用者串連的進程,一般屬於系統背景維護進程,可以忽略。
User Name
當前串連的使用者名稱。
Application
查詢應用類型。
Client Address
查詢用戶端的IP地址。
如果Query長時間運行不結束,您可以單擊對應Query操作列的取消,終結Query。也可以多選Query,執行批量取消操作。
(可選)單擊目標查詢操作列的詳情,查看當前查詢的詳細資料。
您可在詳情頁面,執行如下操作:
複製:複製當前查詢執行的SQL語句。
格式化:格式化當前查詢執行的SQL語句。
排查鎖
可以通過活躍Query排查當前SQL是否有鎖或者被鎖,詳情請參見鎖以及排查鎖。
終止Query
不符合預期的Query需要終止,可以通過以下命令語句操作。
終止單個Query:
SELECT pg_cancel_backend(<pid>);
批量終止Query:
SELECT pg_cancel_backend(pid) ,query ,datname ,usename ,application_name ,client_addr ,client_port ,backend_start ,state FROM pg_stat_activity WHERE length(query) > 0 AND pid != pg_backend_pid() AND backend_type = 'client backend' AND application_name != 'hologres'
修改活躍Query逾時時間
Hologres支援您通過如下方式修改活躍Query運行逾時時間。
文法樣本
SET statement_timeout = <time>;
參數說明
time:逾時時間取值範圍為0~2147483647ms,單位預設為ms(當time後加單位時需要使用單引號,否則會報錯)。當前預設逾時時間為8小時,該設定針對session層級生效。
說明SET statement_timeout = <time> 和要修改逾時時間的SQL語句一起執行方可生效。
使用樣本
設定逾時時間為5000min,其中具體時間帶單位,5000min需要整體添加單引號。
SET statement_timeout = '5000min' ; SELECT * FROM tablename;
設定逾時時間為5000ms。
SET statement_timeout = 5000 ; SELECT * FROM tablename;
修改空閑Query逾時時間
參數idle_in_transaction_session_timeout描述了事務進入idle狀態後的逾時行為,如果不設定參數值,預設不會做事務逾時的釋放,容易發生事務不釋放,導致查詢被鎖死的情況。Hologres支援您通過如下方式修改空閑Query運行逾時時間。
應用情境
當Query執行產生死結時,需要設定逾時時間。例如如下代碼,未執行
commit
,開啟了一個事務,但是沒有提交,會造成事務泄漏,進而引發資料庫層級的死結,影響服務正常使用。BEGIN; SELECT * FROM t;
當出現這種死結情境時,可以通過設定idle_in_transaction_session_timeout逾時時間來解決。當一個帶事務的空閑串連超過idle_in_transaction_session_timeout設定的時間還未提交或者復原事務,系統將自動根據逾時時間復原事務,並關閉串連。
文法樣本
--session修改空閑事務逾時時間 SET idle_in_transaction_session_timeout=<time>; --DB層級修改空閑事務逾時時間 ALTER database db_name SET idle_in_transaction_session_timeout=<time>;
參數說明
time:逾時時間取值範圍為0~2147483647ms,單位預設為ms(當time後加單位時需要使用單引號,否則會報錯)。在Hologres V0.10及以下版本,預設值為0,即不會自動清理;在Hologres V1.1版本,預設值為10分鐘,超過10分鐘後將會復原事務。
說明不建議逾時時間設定過短,如果過短容易錯誤復原正在使用中的事務。
使用樣本
設定逾時時間為300000ms。
--session修改空閑事務逾時時間 SET idle_in_transaction_session_timeout=300000; --DB層級修改空閑事務逾時時間 ALTER database db_name SET idle_in_transaction_session_timeout=300000;
查詢慢Query日誌
從Hologres V0.10版本開始,支援進行慢query日誌查詢,詳情請參見慢Query日誌查看與分析。
常見問題
問題現象
執行SQL後出現報錯:
ERROR: canceling statement due to statement timeout
。問題原因及解決方案
原因1:用戶端或Hologres執行個體設定了逾時時間,常見的逾時時間如下。
通過資料服務產生API,資料服務的逾時時間為
10s
,不可以修改,建議最佳化SQL以降低執行時間。HoloWeb或DataWorks的Hologres SQL模組執行的Query,逾時時間為
1h
,不可以修改,建議最佳化SQL以降低執行時間。為Hologres執行個體設定的逾時時間,可以通過以下SQL查看設定的執行個體逾時時間,如果是執行個體逾時時間導致,可以根據業務情況重新設定合理的逾時時間。
SHOW statement_timeout;
用戶端或應用設定的逾時時間,需要業務排查用戶端的設定。如果是用戶端設定的逾時時間導致,可以根據業務情況重新設定合理的逾時時間。
原因2:執行DML SQL時對錶執行了DROP或者Truncate操作導致逾時。
Truncate的原理是
drop+create
即先刪除表再建立表。當執行DML SQL時,會擷取行鎖或表鎖,鎖相關內容請參見鎖以及排查鎖。此時再對這個表同時執行DROP或者Truncate,DROP或者Truncate操作會搶DML的鎖,然後系統會將DML SQL取消,即出現statement timeout
的報錯。解決方案:通過慢Query日誌排查是否同一時間對該表執行
drop
或truncate
操作,樣本如下,需避免此類操作。--樣本查詢過去一天某張表執行的drop/truncate記錄 SELECT * FROM hologres.hg_query_log WHERE command_tag IN ('DROP TABLE','TRUNCATE TABLE') AND query LIKE '%xxx%' AND query_start >= now() - interval '1 day';