全部產品
Search
文件中心

Hologres:Query管理

更新時間:Aug 15, 2024

本文將為您介紹如何對執行個體中的Query進行診斷和管理。

Query管理概述

Hologres相容PostgreSQL,可以通過查詢hg_stat_activity(pg_stat_activity)視圖資訊來查看執行個體Query的運行資訊,以達到分析和診斷運行SQL的目的。具體涉及的操作內容如下所示:

查詢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 idquery 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

用戶端的應用類型。

其中常見的應用類型如下:

  • 阿里雲Flink(VVR版):{client_version}_ververica-connector-hologres

  • 開源Flink:{client_version}_hologres-connector-flink

  • DataWorksData Integration離線同步讀取Hologres:datax_{jobId}

  • DataWorksData Integration離線同步寫入Hologres:{client_version}_datax_{jobId}

  • DataWorksData Integration即時同步:{client_version}_streamx_{jobId}

  • HoloWeb:holoweb

  • 在MaxCompute中通過外部表格的方式訪問Hologres:MaxCompute

  • Holo Client發起的讀Hologres Binlog進程:holo_client_replication。該類型的任務不會顯示Query內容。

其餘應用建議業務上在串連Hologres時串連串上顯式指定application_name

支援

支援

running_info

包含SQL執行過程各種狀態資訊,格式為JSON格式。其子欄位包括:

  • current_resource

    Serverless:僅使用Serverless資源執行的Query有該欄位。

  • current_stage

    • stage_name<PARSE|OPTIMIZE|QUEUE|START|EXECUTE|FINISH>:

      • PARSE:SQL解析。

        說明

        如果SQL是PARSE階段,則無法顯示執行引擎engine_type。

      • OPTIMIZE:產生執行計畫。

      • QUEUE:等待資源。

      • START:Query開始階段。

      • EXECUTE:Query執行階段。

      • FINISH:Query完成。

    • stage_duration_ms: 當前Stage到現在消耗的時間(單位毫秒)。

    • queue_time_ms:排隊等待時間長度(單位:毫秒),僅Serverless Query有該欄位。

    • serverless_allocated_cores:申請的Serverless資源量(單位:CU),僅Serverless Query有該欄位。

    • serverless_allocated_workers:申請的Serverless資源Worker數(單位:個),僅Serverless Query有該欄位。

  • engine_type:當前Query的執行引擎。

    • {HQE}:Hologres引擎。

    • {HQE,PQE}:走PQE的SQL。

    • {PG}:系統SQL,無需過多關注。

    • {SDK}:Fixed Plan SQL。

    • {HQE,SQE}:未使用直讀的MaxCompute外部表格SQL。

    • {HQE,HiveQE}: 未使用直讀的OSS外部表格SQL。

    • {HQE,SQE,HiveQE}:未直讀的外部表格SQL。

  • fe_id:FE節點對應的ID。

  • warehouse_id:如果執行個體是warehouse模式,代表warehouse的ID。

支援

不支援

extend_info

包含SQL執行過程資源消耗等的擴充資訊,為JSON格式。其子欄位包括:

  • total_cpu_max_time_ms:SQL累積CPU消耗(單位毫秒)。

  • total_mem_max_bytes:Query中各運算元記憶體最大值之和(單位byte)。

  • scanned_rows:Query已讀取資料的總行數。

  • affected_rows:DML語句影響的行數。

  • be_lock_waiters:當前SQL拿鎖情況,表示當前SQL拿著鎖,誰(Query_id)在等這個SQL釋放鎖。

  • be_lock_converting_waiters:當前SQL被鎖的情況,表示當前SQL被誰(Query_id)鎖住,需要等其釋放鎖。

說明

be_lock_waiters和be_lock_converting_waiters採集的都是backend的鎖,而不是frontend的鎖,排查鎖請參見鎖以及排查鎖

支援

不支援

state

表示串連的狀態。常見的狀態如下:

  • active:活躍。

  • idle:空閑。

  • idle in transaction:長事務中的空閑狀態。

  • idle in transaction(Aborted):已失敗事務中的空閑狀態。

  • \N: 狀態為空白,表示非使用者串連的進程,一般屬於系統背景維護進程,可以忽略。

支援

支援

query_start

Query開始執行的時間,如果active不是active,取值為上一個查詢開始的時間。

支援

支援

client_addr

用戶端的IP地址。

顯示的IP地址可能是被解析過的,不保證一定是源端IP。

支援

支援

client_hostname

用戶端的主機名稱。

支援

支援

client_port

用戶端的連接埠。

支援

支援

backend_start

後台進程開始的時間。

在Hologres中無需過多關注。

支援

支援

xact_start

該進程的當前事務被啟動的時間。

  • 如果沒有活動事務,則為空白。

  • 如果當前查詢是該進程的第一個事務,這一列等於query_start

在Hologres中無需過多關注。

支援

支援

state_change

串連的狀態(state)上一次被改變的時間。

在Hologres中無需過多關注。

支援

支援

wait_event_type

後端正在等待的事件類型,如果不存在則為NULL。可能的取值有:

  • LWLock:後端正在等待一個輕量級鎖。

  • Lock:後端正在等待一個重量級鎖。wait_event將標識等待的鎖的類型。

  • BufferPin:伺服器處理序正在等待訪問一個資料緩衝區,而此時沒有其他進程正在檢查該緩衝區。

  • Activity:伺服器處理序處於閑置狀態。被用於在其主處理迴圈中等待活動的系統進程。

  • Extension:伺服器處理序正在一個擴充模組中等待活動。

  • Client:伺服器處理序正在等待來自使用者應用的某種查詢,並且該伺服器預期某種與其內部處理無關的事情發生。

  • PC:伺服器處理序正在等待來自伺服器中另一個進程的某種活動。

  • Timeout:伺服器處理序正在等待一次逾時發生。

  • IO:伺服器處理序正在等待一次IO完成。

支援

支援

wait_event

如果後端當前正在等待,則是等待事件的名稱,否則為 NULL。

支援

支援

backend_xid

Hologres後端的頂層事務標識符。

支援

支援

backend_xmin

當前後端的xmin範圍。

支援

支援

query

後端最近執行的查詢。如果stateactive,將會顯示當前正在執行的查詢。在其他狀態下,顯示上一個被執行的查詢。

支援

支援

backend_type

當前後端的類型。可能的類型為autovacuum launcher、autovacuum worker、logical replication launcher、logical replication worker、parallel worker、background writer、client backend、checkpointer、startup、walreceiver、walsender以及 walwriter。除此之外還包括後端的動作項目,例如PQE等。

說明

需要關注client backend類型,代表應用的串連。

支援

支援

使用SQL查看活躍Query

若您更傾向於使用SQL方式查詢活躍Query,可以使用以下SQL進行操作:

  1. 查看當前活躍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 }
  2. 當前啟動並執行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
  3. 當前啟動並執行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
  4. 查看當前執行個體正在運行且耗時較長的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,並進行管理。

  1. 登入HoloWeb控制台,詳情請參見串連HoloWeb並執行查詢

  2. 單擊頂部導覽列的診斷與最佳化

  3. 在左側導覽列選擇活躍資訊管理 > 活躍Query

  4. 進入活躍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,執行批量取消操作。

  5. (可選)單擊目標查詢操作列的詳情,查看當前查詢的詳細資料。

    您可在詳情頁面,執行如下操作:

    • 複製:複製當前查詢執行的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日誌排查是否同一時間對該表執行droptruncate操作,樣本如下,需避免此類操作。

      --樣本查詢過去一天某張表執行的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';