全部產品
Search
文件中心

Hologres:慢Query日誌查看與分析

更新時間:Sep 13, 2024

當Hologres執行個體出現響應或查詢速度變慢時,您可以查看與分析慢Query日誌快速定位問題所在。慢Query的查看與分析可以協助您對系統中發生的慢Query或失敗Query進行識別、診斷和分析,並進一步採取最佳化措施,以提高系統整體效能。本文為您介紹在Hologres中如何查看慢Query日誌並分析。

版本說明

Hologres版本號碼

功能

說明

V0.10

新增慢Query日誌查看與分析功能。

  • 該功能僅Hologres V0.10及以上版本支援,請在Hologres管理主控台的執行個體詳情頁查看當前執行個體版本,如果您的執行個體是V0.10以下版本,請您使用自助升級或加入HologresDingTalk交流群反饋,詳情請參見如何擷取更多的線上支援?

  • Hologres V0.10版本的FAILED Query日誌,不顯示記憶體、讀盤、讀取量、CPU時間、query_stats等運行時的統計資訊。

V2.2.7

最佳化log_min_duration_statement參數預設值

慢Query日誌記錄所有的DDL語句、所有執行失敗的SQL語句以及執行時間大於log_min_duration_statement的SQL語句(例如INSERT、SELECT、UPDATE、DELETE等)。

從Hologres V2.2.7版本開始,將log_min_duration_statement預設值由1s最佳化至100ms,無需您再手動修改。若您的執行個體是V2.2.7以下版本,可使用log_min_duration_statement進行手動修改,支援最小修改為100ms。

使用限制

在Hologres中查詢慢Query日誌,具體限制如下:

  • 慢Query日誌預設保留一個月的資料,但是HoloWeb僅支援查最近7天的資料,如果需要查詢一個月內的資料請使用SQL進行查詢。

  • 為了保證系統穩定性,避免流量超載,單次查詢最多返回10000條慢Query日誌。慢Query日誌中的部分欄位有長度限制,詳情請參見query_log表欄位說明。

  • 慢Query日誌屬於Hologres的元倉系統,慢Query日誌查詢失敗不會影響執行個體中的業務Query運行,故慢Query日誌的穩定性不在產品的SLA保護範圍內。

查看query_log表

Hologres的慢Query查詢日誌儲存於hologres.hg_query_log系統資料表中,其中:

  • 在執行個體升級至V0.10版本後,將會預設採集執行時間大於100 ms的慢DML Query以及所有的DDL操作。

  • 自V3.0.2版本開始,支援在原有的hologres.hg_query_log中,使用彙總方式記錄執行時間小於100 ms的DML和查詢的記錄。

    • 系統會將執行時間小於100 ms,成功且有指紋的Query(DQL和DML)操作進行彙總記錄。

    • 彙總的主要列為:server_addr、usename、datname、warehouse_id、application_name、digest。

    • 每個串連每分鐘會匯總一次後上報。

hologres.hg_query_log系統資料表,包含的欄位資訊介紹如下。

欄位

欄位類型

100 ms以上DML和DQL明細記錄含義說明

100 ms以下DML和DQL彙總記錄含義說明

usename

text

查詢的使用者名稱。

查詢的使用者名稱。

status

text

查詢的最終狀態,成功或失敗。

  • 成功:SUCCESS。

  • 失敗:FAILED。

查詢的最終狀態,成功(SUCCESS),彙總資料僅記錄成功的記錄。

query_id

text

Query的ID,代表Query的唯一性。

失敗的Query一定會有query_id,成功的Query不一定會有。

彙總周期內,同彙總主鍵第一個Query的Query ID。

digest

text

SQL指紋編碼。

對於SELECT、INSERT、DELETE、UPDATE類型的Query,系統會計算一個MD5雜湊值作為該Query的SQL指紋。SQL指紋採集和計算規則詳情,請參見SQL指紋

說明

從Hologres V2.2版本開始,新增digest列以展示SQL指紋編碼。若您的執行個體為V2.1或以下版本,可聯絡Hologres支援人員升級執行個體。

SQL指紋編碼。

datname

text

查詢的資料庫名稱。

查詢的資料庫名稱。

command_tag

text

查詢的類型。

包括:

  • DML類:COPY/DELETE/INSERT/SELECT/UPDATE等

  • DDL類:ALTER TABLE/BEGIN/COMMENT/COMMIT/CREATE FOREIGN TABLE/CREATE TABLE/DROP FOREIGN TABLE/DROP TABLE/IMPORT FOREIGN SCHEMA/ROLLBACK/TRUNCATE TABLE

  • 其他:CALL/CREATE EXTENSION/EXPLAIN/GRANT/SECURITY LABEL/等

彙總周期內,同彙總主鍵第一個Query的查詢的類型。

duration

integer

查詢的耗時(ms)。

duration代表SQL的總耗時時間,包含:

  • Optimization Cost:產生執行計畫的耗時,耗時較高通常是因為SQL較複雜。

  • Start Query Cost:Query啟動時間,耗時較高通常是因為在等鎖或者等待資源。

  • Get Result Cost:Query執行時間,耗時較高通常是因為計算量太大,執行時間較長,可根據業務情況最佳化SQL。

  • 其他耗時:可以通過extend_cost欄位進一步查看,通常包括如下耗時:

    • build_dag : 構建執行引擎所需的計算DAG圖的耗時,如訪問外部表格,這一步會擷取外部表格中繼資料,耗時較高通常是因為訪問外部表格中繼資料耗時間長度。

    • prepare_reqs : 準備發往執行引擎請求的耗時,包括準備啟動並執行組件、擷取各個分區的地址等,耗時較高通常是因為向內部服務擷取分區地址耗時間長度。

系統預設只展示大於1s的DML和所有DDL,可以通過GUC參數log_min_duration_statement修改採集時間,使用詳情請參見配置項

彙總周期內,同彙總主鍵所有Query的查詢耗時的平均值。

message

text

報錯的資訊。

空。

query_start

timestamptz

查詢開始時間。

彙總周期內,同彙總主鍵第一個Query的查詢開始時間。

query_date

text

查詢開始日期。

彙總周期內,同彙總主鍵第一個Query的查詢開始日期。

query

text

查詢的常值內容。

Query的長度上限是51200個字元,超過可能會被截斷。

彙總周期內,同彙總主鍵第一個Query的查詢的常值內容。

result_rows

bigint

返回的行數。

如果是INSERT命令,則返回插入的行數。

彙總周期內,同彙總主鍵所有Query的result_rows的平均值。

result_bytes

bigint

返回的位元組數。

彙總周期內,同彙總主鍵所有Query的result_bytes的平均值。

read_rows

bigint

讀取的資料行數。

非精確值。如果使用了Bitmap索引,跟實際的掃描行數有差別。

彙總周期內,同彙總主鍵所有Query的read_rows的平均值。

read_bytes

bigint

讀取的位元組數。

彙總周期內,同彙總主鍵所有Query的read_bytes的平均值。

affected_rows

bigint

DML影響的行數。

彙總周期內,同彙總主鍵所有Query的affected_rows的平均值。

affected_bytes

bigint

DML影響的位元組數。

彙總周期內,同彙總主鍵所有Query的affected_bytes的平均值。

memory_bytes

bigint

各節點上使用的記憶體峰值的累加值(非精確)。

代表Query在所有運行節點上使用的記憶體峰值的累加值。粗略地體現Query的資料讀取量。

彙總周期內,同彙總主鍵所有Query的memory_bytes的平均值。

shuffle_bytes

bigint

資料Shuffle估計位元組數(非精確)。

粗略地體現資料在網路中的傳輸量。

彙總周期內,同彙總主鍵所有Query的shuffle_bytes的平均值。

cpu_time_ms

bigint

總的CPU時間,單位:毫秒(ms),非精確。

體現所有計算任務所耗費的CPU時間,是多個CPU Core計算時間的累加,粗略地體現複雜度。

彙總周期內,同彙總主鍵所有Query的cpu_time_ms的平均值

physical_reads

bigint

物理讀的次數。

反映從磁碟讀取Record Batch的次數,粗略地體現記憶體緩衝未命中數量。

彙總周期內,同彙總主鍵所有Query的physical_reads的平均值。

pid

integer

查詢服務進程ID。

彙總周期內,同彙總主鍵第一個Query的查詢服務進程ID。

application_name

text

查詢應用類型。

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

  • 阿里雲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。

  • Auto Analyze發起的SQL,用於定期更新統計資料:AutoAnalyze。

  • Quick BI:QuickBI_public_{version}。

  • DataWorks調度Hologres任務:{client_version}_dwscheduler_{tenant_id}_{scheduler_id}_{scheduler_task_id}_{bizdate}_{cyctime}_{scheduler_alisa_id},其中:

    • client_version:JDBC驅動版本。

    • scheduler_id:從環境變數${SKYNET_ID}擷取,如果為空白,則設定為-

    • scheduler_task_id:從環境變數${SKYNET_TASKID}擷取,如果為空白,則設定為-

    • scheduler_alisa_id:從環境變數${ALISA_TASK_ID}擷取,不會為空白。

    • bizdate:從環境變數${SKYNET_BIZDATE}擷取,如果為空白,則設定為-

    • cyctime:從環境變數${SKYNET_CYCTIME}擷取,如果為空白,則設定為-

    • tenant_id:從環境變數${SKYNET_TENANT_ID}擷取,不會為空白。

  • 資料保護傘:dsg。

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

查詢應用類型。

engine_type

text[]

查詢用到的引擎。

包含如下引擎類型:

  • HQE:Hologres的原生自研引擎,大多數查詢通過HQE實現,HQE執行效率較高。

  • PQE:PostgreSQL引擎,出現PQE則有SQL運算元在PQE執行,一般是含有HQE未原生支援的運算元或運算式,可根據最佳化查詢效能改寫函數方法,讓其走HQE,提高執行效率。

  • SDK:Fixed Plan的執行引擎,可以高效的執行點讀、點寫、PrefixScan等偏Serving類型的SQL,詳情請參見Fixed Plan加速SQL執行

    說明

    從Hologres V2.2版本開始,SDK執行引擎正式更名為FixedQE。

  • PG:Frontend本地計算,一般是讀取系統資料表的中繼資料查詢,不會讀取使用者表資料,僅佔用極少數系統資源。但需要注意的是,DDL也是用PostgreSQL引擎。

彙總周期內,同彙總主鍵第一個Query的查詢用到的引擎。

client_addr

text

Query的來源地址。

代表應用的出口IP,不一定是真實的應用IP。

彙總周期內,同彙總主鍵第一個Query的Query的來源地址。

table_write

text

SQL寫入的表。

彙總周期內,同彙總主鍵第一個Query的SQL寫入的表。

table_read

text[]

SQL讀取的表。

彙總周期內,同彙總主鍵第一個Query的SQL讀取的表。

session_id

text

Session ID。

彙總周期內,同彙總主鍵第一個Query的Session ID。

session_start

timestamptz

Session開啟時間。

代表串連開始建立的時間。

彙總周期內,同彙總主鍵所有Query的Session開啟時間。

command_id

text

命令或語句編號ID。

彙總周期內,同彙總主鍵所有Query的命令或語句編號ID。

optimization_cost

integer

查詢執行計畫的耗時。

耗時較高通常情況是SQL較複雜。

彙總周期內,同彙總主鍵所有Query的查詢執行計畫的耗時。

start_query_cost

integer

查詢啟動耗時。

耗時較高通常是因為在等鎖或者等待資源。

彙總周期內,同彙總主鍵所有Query的查詢啟動耗時。

get_next_cost

integer

查詢執行耗時。

耗時較高通常是因為計算量太大,執行時間較長,可根據業務情況最佳化SQL。

彙總周期內,同彙總主鍵所有Query的查詢執行耗時。

extended_cost

text

查詢的其他詳細耗時。

除optimization_cost 、start_query_cost 、get_next_cost三個耗時之外的其他耗時,可以通過extend_cost欄位進一步查看,通常包括如下耗時。

  • build_dag:構建執行引擎所需的計算DAG圖的耗時,如訪問外部表格,這一步會擷取外部表格中繼資料,耗時較高通常是因為訪問外部表格中繼資料耗時間長度。

  • prepare_reqs:準備發往執行引擎請求的耗時,包括準備啟動並執行組件、擷取各個分區的地址等,耗時較高通常是因為向內部服務擷取分區地址耗時間長度。

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

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

  • serverless_resource_used_time_ms:實際使用Serverless資源執行Query的時間長度,不包含資源等待排隊時間長度。單位:毫秒(ms)。

彙總周期內,同彙總主鍵第一個Query的其他詳細耗時。

plan

text

查詢對應的執行計畫。

執行計畫長度上限是102400個字元,超過可能會被截斷,可以通過GUC參數log_min_duration_query_plan修改長度上限,使用詳情請參見配置項

彙總周期內,同彙總主鍵第一個Query的查詢對應的執行計畫。

statistics

text

查詢對應的執行統計資訊。

統計資訊長度上限是102400個字元,超過可能會被截斷,可以通過GUC參數log_min_duration_query_stats修改長度上限,使用詳情請參見配置項

彙總周期內,同彙總主鍵第一個Query的查詢對應的執行統計資訊。

visualization_info

text

查詢Plan可視化資訊。

彙總周期內,同彙總主鍵第一個Query的查詢Plan可視化資訊。

query_detail

text

查詢的其他擴充資訊(JSON格式儲存)。

說明

擴充資訊長度上限是10240個字元,超過可能會被截斷。

彙總周期內,同彙總主鍵第一個Query的查詢的其他擴充資訊。

query_extinfo

text[]

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

  • 查詢的其他擴充資訊(ARRAY格式儲存),自V2.0.29版本開始,query_extinfo欄位會採集賬戶的AccessKey ID,用於輔助判斷賬戶歸屬人。

說明

本地帳號登入、SLR登入、STS登入不會記錄AccessKey ID。若使用臨時帳號登入,僅記錄臨時AccessKey ID。

彙總周期內,同彙總主鍵第一個Query的其他擴充資訊。

calls

INT

因為明細記錄無彙總行為,所以為1。

表示在彙總周期內,相同彙總主鍵的查詢數量。

agg_stats

JSONB

空。

記錄在彙總周期內,相同彙總主鍵的查詢的duration、memory_bytes、cpu_time_ms、physical_reads、optimization_cost、start_query_cost、get_next_cost等數值欄位的MIN、MAX、AVG等統計值。

說明

calls、agg_stats欄位為V3.0.2版本新增欄位。

授予查看許可權

慢Query日誌需要有一定的許可權才能查看,其許可權規則和授權方式說明如下:

  • 查看執行個體所有DB的慢Query日誌。

    • 授予使用者Superuser許可權。

      Superuser帳號可以查看執行個體所有DB的全部慢Query日誌,給使用者授予Superuser使用者的許可權,使使用者有許可權查看執行個體所有DB的慢Query日誌。

      ALTER USER "雲帳號ID" SUPERUSER;--將“雲帳號ID”替換為實際使用者名稱。如果是RAM使用者,帳號ID前需要添加“p4_”。
    • 將使用者添加到pg_read_all_stats使用者組。

      除Superuser外,Hologres還支援pg_read_all_stats使用者組查看所有DB慢Query日誌,普通使用者如果需要查看所有日誌,可以聯絡Superuser授權加入該使用者組。授權方式如下:

      GRANT pg_read_all_stats TO "雲帳號ID";--專家許可權模型授權
      CALL spm_grant('pg_read_all_stats', '雲帳號ID');  -- SPM許可權模型
      CALL slpm_grant('pg_read_all_stats', '雲帳號ID'); -- SLPM許可權模型
  • 查看本DB的慢Query日誌。

    開啟簡單許可權模型(SPM)或基於Schema層級的簡單許可權模型(SLPM),將使用者加入db_admin使用者組,db_admin角色可以查看本DB的query日誌。

    CALL spm_grant('<db_name>_admin', '雲帳號ID');  -- SPM許可權模型
    CALL slpm_grant('<db_name>.admin', '雲帳號ID'); -- SLPM許可權模型
  • 普通使用者只能查詢當前帳號對應DB下自己執行的慢Query日誌。

HoloWeb可視化查看慢Query

您可以通過HoloWeb可視化查看慢Query日誌。

說明
  • 當前HoloWeb支援查看最多七天的歷史慢Query日誌。

  • 僅支援Superuser帳號查看,普通有許可權的帳號請使用SQL命令查看。

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

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

  3. 在左側導覽列單擊歷史慢Query

  4. 歷史慢Query頁面頂部,編輯查詢條件。

    參數說明請參見歷史慢Query

  5. 單擊查詢,在Query趨勢分析Query列表地區展示查詢結果。

    • Query趨勢分析

      Query趨勢分析通過可視化的方式展示近期慢Query、失敗Query發生的趨勢,可以重點監控慢Query發生頻率較高的階段,更好的定位問題高發期並集中解決問題。

    • Query列表

      Query列表可視化展示慢Query、失敗Query的詳細資料,參數說明請參見歷史慢Query。也可以單擊自訂欄選擇Query列表需要展示的列資訊。

SQL指紋

自Hologres V2.2版本起,存放慢Query查詢日誌的系統資料表hologres.hg_query_log中,新增digest列以展示SQL指紋。對於SELECT/INSERT/DELETE/UPDATE類型的Query,系統會計算一個MD5雜湊值作為該Query的SQL指紋,以協助業務進一步Query歸類分析。

SQL指紋的採集和計算規則如下:

  • 預設僅採集SELECT、INSERT、DELETE以及UPDATE四種類型Query的指紋。

  • 對於INSERT語句,以常量方式插入表時,SQL指紋不受插入資料量的影響。

  • SQL指紋計算時,對SQL語句字母大小寫處理規則和Hologres在查詢時SQL語句字母大小寫規則保持一致。

  • SQL指紋計算時,會忽略查詢中所有空白符(如空格、換行、定位字元等)的影響,只考慮SQL語句內的結構資訊。

  • SQL指紋計算時,會忽略查詢中具體的常數值對於指紋的影響。

    樣本:查詢SELECT * FROM t WHERE a > 1;與查詢SELECT * FROM t WHERE a > 2;所對應的SQL指紋是一致的。

  • 對於Query中的常數陣列,SQL指紋的計算不受數組中元素個數的影響。

    樣本:查詢SELECT * FROM t WHERE a IN (1, 2);和查詢SELECT * FROM t WHERE a IN (3, 4, 5);的SQL指紋是相同的。

  • SQL指紋計算時,會考慮資料庫名稱,並自動補全每張表的模式(Schema)屬性,以表名和表的屬性共同區分不同查詢中的表。

    樣本:對於查詢SELECT * FROM t;和查詢SELECT * FROM public.t;而言,只有當表t位於public模式並且兩個查詢對應同一張表時,其SQL指紋才相同。

診斷Query

您可以通過查詢hologres.hg_query_log表,擷取慢Query日誌。如下將為您介紹進行Query日誌診斷常用的固定SQL語句。主要包括:

  • 查詢query_log總查詢數

  • 查詢每個使用者的慢Query情況

  • 查詢某個慢Query的具體資訊

  • 查詢最近10分鐘消耗比較高的Query

  • 查看最近3小時內每小時的Query訪問量和資料讀取總量

  • 查看與昨天同一時間對比過去3小時的資料訪問量情況

  • 查詢最近10分鐘Query各階段耗時比較高的Query

  • 查詢最先失敗的Query

  • 查詢query_log總查詢數(預設為近1個月內的資料)。

    SELECT count(*) FROM hologres.hg_query_log;

    執行結果如下,表示近1個月內總共執行了44個耗時大於指定閾值的Query。

    count
    -------
        44
    (1 row)
  • 查詢每個使用者的慢Query個數。

    SELECT usename AS "使用者",
           count(1) as "Query個數"
    FROM hologres.hg_query_log
    GROUP BY usename
    order by count(1) desc;

    執行結果如下,其中count(1)的單位是個數。

    使用者                   | Query個數
    -----------------------+-----
     1111111111111111      |  27
     2222222222222222      |  11
     3333333333333333      |   4
     4444444444444444      |   2
    (4 rows)
  • 查詢某個慢Query的具體資訊。

    SELECT * FROM hologres.hg_query_log WHERE query_id = '13001450118416xxxx';

    執行結果如下,更多關於各參數的說明,請參見查看query_log表

           usename      | status  |      query_id      | datname| command_tag | duration | message |      query_start       | query_date |                                             query                                                  | result_rows | result_bytes | read_rows |read_bytes | affected_rows | affected_bytes | memory_bytes | shuffle_bytes | cpu_time_ms | physical_reads |   pid   | application_name | engine_type |  client_addr  | table_write | table_read |   session_id   |     session_start      | command_id | optimization_cost | start_query_cost | get_next_cost | extended_cost | plan | statistics | visualization_info | query_detail | query_extinfo
    -----------------------+---------+--------------------+---------+-------------+----------+---------+------------------------+------------+---------------------------------------------------------------------------------------------------------+-------------+--------------+-----------+------------+---------------+----------------+--------------+---------------+-------------+----------------+---------+------------------+-------------+---------------+-------------+------------+-----------------+------------------------+------------+-------------------+------------------+---------------+---------------+------+------------+--------------------+--------------+---------------
     p4_11111111111xxxx | SUCCESS | 13001450118416xxxx | dbname | SELECT      |      149 |         | 2021-03-30 23:45:01+08 | 20210330   | explain analyze SELECT  * FROM tablename WHERE user_id = '20210330010xxxx' limit 1000;             |        1000 |       417172 |         0 |         0 |            -1 |             -1 |     26731376 |     476603616 |      321626 |              0 | 1984913 | psql             | {HQE}      | 33.41.xxx.xxx |             |            | 6063475a.1e4991 | 2021-03-30 23:44:26+08 | 0          |                58 |               22 |            67 |               |      |            |                    |              |
    (1 row)
  • 查詢最近某個時間段(如10分鐘)消耗比較高的Query。您也可以根據業務需求修改具體時間,查詢目標時間段消耗比較高的Query。

    SELECT status AS "狀態",
           duration AS "耗時(ms)",
           query_start AS "開始時間",
           (read_bytes/1048576)::text || ' MB' AS "讀取量",
           (memory_bytes/1048576)::text || ' MB' AS "記憶體",
           (shuffle_bytes/1048576)::text || ' MB' AS "Shuffle",
           (cpu_time_ms/1000)::text || ' s' AS "CPU時間",
           physical_reads as "讀盤",
           query_id as "QueryID",
           query::char(30)
     FROM hologres.hg_query_log
     WHERE query_start >= now() - interval '10 min'
     ORDER BY duration DESC,
              read_bytes DESC,
              shuffle_bytes DESC,
              memory_bytes DESC,
              cpu_time_ms DESC,
              physical_reads DESC
    LIMIT 100;

    執行結果如下:

    狀態     |耗時(ms) |    開始時間            | 讀取量 | 記憶體  | Shuffle | CPU時間 | 讀盤 |      QueryID       |             query
    ---------+---------+------------------------+--------+-------+---------+---------+------+--------------------+--------------------------------
     SUCCESS |  149    | 2021-03-30 23:45:01+08 | 0 MB   | 25 MB | 454 MB  | 321 s   |    0 | 13001450118416xxxx | explain analyze SELECT  * FROM
     SUCCESS |  137    | 2021-03-30 23:49:18+08 | 247 MB | 21 MB | 213 MB  | 803 s   | 7771 | 13001491818416xxxx | explain analyze SELECT  * FROM
     FAILED  |   53    | 2021-03-30 23:48:43+08 | 0 MB   | 0 MB  | 0 MB    | 0 s     |    0 | 13001484318416xxxx | SELECT ds::bigint / 0 FROM pub
    (3 rows)
  • 查看昨日新增的Query數量。

    • 統計昨日新增的Query總量。

      SELECT
          COUNT(1)
      FROM ( SELECT DISTINCT
              t1.digest
          FROM
              hologres.hg_query_log t1
          WHERE
              t1.query_start >= CURRENT_DATE - INTERVAL '1 day'
              AND t1.query_start < CURRENT_DATE
              AND NOT EXISTS (
                  SELECT
                      1
                  FROM
                      hologres.hg_query_log t2
                  WHERE
                      t2.digest = t1.digest
                      AND t2.query_start < CURRENT_DATE - INTERVAL '1 day')
              AND digest IS NOT NULL
       ) AS a;

      執行結果如下,表示昨日新增Query總量為10個。

      count  
      -------
          10 
      (1 row)
    • 按照查詢的類型(command_tag)分組統計昨日新增的Query數量。

      SELECT
          a.command_tag,
          COUNT(1)
      FROM ( SELECT DISTINCT
              t1.digest,
              t1.command_tag
          FROM
              hologres.hg_query_log t1
          WHERE
              t1.query_start >= CURRENT_DATE - INTERVAL '1 day'
              AND t1.query_start < CURRENT_DATE
              AND NOT EXISTS (
                  SELECT
                      1
                  FROM
                      hologres.hg_query_log t2
                  WHERE
                      t2.digest = t1.digest
                      AND t2.query_start < CURRENT_DATE - INTERVAL '1 day')
                  AND t1.digest IS NOT NULL) AS a
      GROUP BY
          1
      ORDER BY
          2 DESC;

      執行結果如下,表示昨日新增8個INSERT和2個SELECT類型的Query。

      command_tag	| count  
      ------------+--------
      INSERT	    |    8   
      SELECT	    |    2
      (2 rows)
  • 查看昨日新增的Query明細。

    SELECT
        a.usename,
        a.status,
        a.query_id,
        a.digest, 
        a.datname,
        a.command_tag,
        a.query,
        a.cpu_time_ms,
        a.memory_bytes
    FROM (
        SELECT DISTINCT
            t1.usename,
            t1.status,
            t1.query_id,
            t1.digest, 
            t1.datname,
            t1.command_tag,
            t1.query,
            t1.cpu_time_ms,
            t1.memory_bytes
        FROM
            hologres.hg_query_log t1
        WHERE
             t1.query_start >= CURRENT_DATE - INTERVAL '1 day' 
            AND t1.query_start < CURRENT_DATE 
            AND NOT EXISTS (
                SELECT
                    1
                FROM
                    hologres.hg_query_log t2
                WHERE
                    t2.digest = t1.digest
                    AND t2.query_start < CURRENT_DATE - INTERVAL '1 day'
            )
            AND t1.digest IS NOT NULL
    ) AS a; 

    執行結果如下:

    usename	         |status  |query_id	           |digest	                            |datname|command_tag	|query	                            |cpu_time_ms   |memory_bytes
    -----------------+--------+--------------------+------------------------------------+-------+-------------+-----------------------------------+--------------+--------------
    111111111111xxxx |SUCCESS |100100425827776xxxx |md58cf93d91c36c6bc9998add971458ba1a |dbname	|INSERT	      |INSERT INTO xxx SELECT * FROM xxx  | 	   		 1748|	   898808596
    111111111111xxxx |SUCCESS |100100425827965xxxx |md5f7e87e2c9e0b3d9eddcd6b3bc7f04b3b |dbname	|INSERT       |INSERT INTO xxx SELECT * FROM xxx  | 	   		59891|	  6819529886
    111111111111xxxx |SUCCESS |100100425829654xxxx |md55612dc09d2d81074fd5deed1aa3eca9b |dbname	|INSERT	      |INSERT INTO xxx SELECT * FROM xxx  |   	   		  3|	     2100039
    111111111111xxxx |SUCCESS |100100425829664xxxx |md58d3bf67fbdf2247559bc916586d40011 |dbname	|INSERT	      |INSERT INTO xxx SELECT * FROM xxx  |     		10729|	  2052861937
    111111111111xxxx |SUCCESS |100100425830099xxxx |md503bd45d6b2d7701c2617d079b4d55a10 |dbname	|INSERT	      |INSERT INTO xxx SELECT * FROM xxx  |	   		   2196|     897948034
    111111111111xxxx |SUCCESS |100100425830186xxxx |md5c62169eaf3ea3a0c59bdc834a8141ac4 |dbname	|INSERT	      |INSERT INTO xxx SELECT * FROM xxx  |	   		   5268|    1734305972
    111111111111xxxx |SUCCESS |100100425830448xxxx |md59aa0c73b24c9c9eba0b34c8fdfc23bb0 |dbname	|INSERT	      |INSERT INTO xxx SELECT * FROM xxx  |	   		      2|       2098402
    111111111111xxxx |SUCCESS |100100425830459xxxx |md57d22c1d37b68984e9472f11a4c9fd04e |dbname	|INSERT	      |INSERT INTO xxx SELECT * FROM xxx  |	   		    113|      76201984
    111111111111xxxx |SUCCESS |100100525468694xxxx |md5ac7d6556fae123e9ea9527d8f1c94b1c |dbname	|SELECT	      |SELECT * FROM xxx limit 200	      |     		    6|	     1048576
    111111111111xxxx |SUCCESS |100101025538840xxxx |md547d09cdad4d5b5da74abaf08cba79ca0 |dbname	|SELECT	      |SELECT * FROM xxx limit 200	      |\N	           |\N        
    (10 rows)
  • 查看昨日新增的Query趨勢(按小時展示)。

    SELECT
        to_char(a.query_start, 'HH24') AS query_start_hour,
        a.command_tag,
        COUNT(1)
    FROM (
        SELECT DISTINCT
            t1.query_start,
            t1.digest,
            t1.command_tag
        FROM
            hologres.hg_query_log t1
        WHERE
             t1.query_start >= CURRENT_DATE - INTERVAL '1 day' 
             AND t1.query_start < CURRENT_DATE 
             AND NOT EXISTS (
                SELECT
                    1
                FROM
                    hologres.hg_query_log t2
                WHERE
                    t2.digest = t1.digest
                    AND t2.query_start < CURRENT_DATE - INTERVAL '1 day'
             )
             AND t1.digest IS NOT NULL
    ) AS a 
    GROUP BY 1, 2
    ORDER BY 
    	3 DESC; 

    執行結果如下,表示在昨日的11時、13時、21時分別新增了1個SELECT、1個SELECT、8個INSERT類型的Query。

    query_start_hour	|command_tag	|count
    ------------------+-------------+-----
    	            21	|INSERT	      |    8
    	            11	|SELECT	      |    1
    	            13	|SELECT	      |    1
    (3 rows)
  • 歸類慢Query的次數。

    SELECT
        digest,
        command_tag,
        count(1)
    FROM
        hologres.hg_query_log
    WHERE
        query_start >= CURRENT_DATE - INTERVAL '1 day'
        AND query_start < CURRENT_DATE
    GROUP BY
        1,2
    ORDER BY
        3 DESC;
  • 查詢過去1天CPU平均耗時前十的Query。

    SELECT
        digest,
        avg(cpu_time_ms)
    FROM
        hologres.hg_query_log
    WHERE
        query_start >= CURRENT_DATE - INTERVAL '1 day'
        AND query_start < CURRENT_DATE
        AND digest IS NOT NULL
        AND usename != 'system'
        AND cpu_time_ms IS NOT NULL
    GROUP BY
        1
    ORDER BY
        2 DESC
    LIMIT 10;
  • 查看過去一周記憶體(memory_bytes)平均消耗量前十的Query。

    SELECT
        digest,
        avg(memory_bytes)
    FROM
        hologres.hg_query_log
    WHERE
        query_start >= CURRENT_DATE - INTERVAL '7 day'
        AND query_start < CURRENT_DATE
        AND digest IS NOT NULL
        AND memory_bytes IS NOT NULL
    GROUP BY
        1
    ORDER BY
        2 DESC
    LIMIT 10;
  • 查看最近3小時內每小時的Query訪問量和資料讀取總量,用於對比判斷每個小時是否有資料量變化。

    SELECT
        date_trunc('hour', query_start) AS query_start,
        count(1) AS query_count,
        sum(read_bytes) AS read_bytes,
        sum(cpu_time_ms) AS cpu_time_ms
    FROM
        hologres.hg_query_log
    WHERE
        query_start >= now() - interval '3 h'
    GROUP BY 1;
  • 查看與昨天同一時間對比過去3小時的資料訪問量情況,用於判斷相較於昨日是否有資料量訪問變化。

    SELECT
        query_date,
        count(1) AS query_count,
        sum(read_bytes) AS read_bytes,
        sum(cpu_time_ms) AS cpu_time_ms
    FROM
        hologres.hg_query_log
    WHERE
        query_start >= now() - interval '3 h'
    GROUP BY
        query_date
    UNION ALL
    SELECT
        query_date,
        count(1) AS query_count,
        sum(read_bytes) AS read_bytes,
        sum(cpu_time_ms) AS cpu_time_ms
    FROM
        hologres.hg_query_log
    WHERE
        query_start >= now() - interval '1d 3h'
        AND query_start <= now() - interval '1d'
    GROUP BY
        query_date;
  • 查詢最近時間段(如最近10分鐘)Query各階段耗時比較高的Query。您也可以根據業務需求修改具體時間,查詢目標時間段Query各階段耗時比較高的Query。

    SELECT
        status AS "狀態",
        duration AS "耗時(ms)",
        optimization_cost AS "最佳化耗時(ms)",
        start_query_cost AS "啟動耗時(ms)",
        get_next_cost AS "執行耗時(ms)",
        duration - optimization_cost - start_query_cost - get_next_cost AS "其他耗時(ms)",
        query_id AS "QueryID",
        query::char(30)
    FROM
        hologres.hg_query_log
    WHERE
        query_start >= now() - interval '10 min'
    ORDER BY
        duration DESC,
        start_query_cost DESC,
        optimization_cost,
        get_next_cost DESC,
        duration - optimization_cost - start_query_cost - get_next_cost DESC
    LIMIT 100;

    執行結果如下:

    狀態     | 耗時(ms) | 最佳化耗時(ms) | 啟動耗時(ms) | 執行耗時(ms) | 其他耗時(ms) |      QueryID       |             query
    ---------+----------+--------------+--------------+--------------+--------------+--------------------+--------------------------------
     SUCCESS |     4572 |          521 |          320 |         3726 |            5 | 6000260625679xxxx  | -- /* user: wang ip: xxx.xx.x
     SUCCESS |     1490 |          538 |           98 |          846 |            8 | 12000250867886xxxx | -- /* user: lisa ip: xxx.xx.x
     SUCCESS |     1230 |          502 |           95 |          625 |            8 | 26000512070295xxxx | -- /* user: zhang ip: xxx.xx.
    (3 rows)
  • 查詢最先失敗的Query。

    SELECT
        status AS "狀態",
        regexp_replace(message, '\n', ' ')::char(150) AS "報錯資訊",
        duration AS "耗時(ms)",
        query_start AS "開始時間",
        query_id AS "QueryID",
        query::char(100) AS "Query"
    FROM
        hologres.hg_query_log
    WHERE
        query_start BETWEEN '2021-03-25 17:00:00'::timestamptz AND '2021-03-25 17:42:00'::timestamptz + interval '2 min'
        AND status = 'FAILED'
    ORDER BY
        query_start ASC
    LIMIT 100;

    執行結果如下:

    狀態    |                                                                     報錯資訊                                                                           | 耗時(ms)  |        開始時間        |     QueryID       | Query
    --------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------+------------------------+-------------------+-------
     FAILED | Query:[1070285448673xxxx] code: kActorInvokeError msg: "[holo_query_executor.cc:330 operator()] HGERR_code XX000 HGERR_msge internal error: status { c |  1460 | 2021-03-25 17:28:54+08 | 1070285448673xxxx | S...
     FAILED | Query:[1016285560553xxxx] code: kActorInvokeError msg: "[holo_query_executor.cc:330 operator()] HGERR_code XX000 HGERR_msge internal error: status { c |   131 | 2021-03-25 17:28:55+08 | 1016285560553xxxx | S...
    (2 rows)

慢Query日誌匯出

Hologres支援使用INSERT語句將慢Query日誌(hg_query_log)中的資料匯出到您自訂的內部表或者MaxCompute、OSS等外部表格。

  • 注意事項。

    為了正確和高效地匯出慢Query日誌中的資料,您需要注意以下幾點。

    • 執行INSERT INTO ... SELECT ... FROM hologres.hg_query_log;的帳號,需要有hologres.hg_query_log表的相應存取權限,詳情請參見授予查看許可權。如果需要匯出全執行個體的慢Query日誌,則需要執行INSERT命令的帳號擁有Superuser或者pg_read_all_stats許可權,否則查詢的hologres.hg_query_log表資料不全,導致匯出的資料不完整。

    • query_start是慢Query日誌表的索引,按照時間範圍匯出時,查詢條件請帶上query_start列來選擇時間範圍,這樣效能更好,資源消耗更少。

    • 使用query_start篩選時間範圍時,不要在query_start上再巢狀表格達式,否則會妨礙索引命中。例如WHERE to_char(query_start, 'yyyymmdd') = '20220101';更好的寫法應該是:WHERE query_start >= to_char('20220101', 'yyyy-mm-dd') and query_start < to_char('20220102', 'yyyy-mm-dd');

  • 樣本一:匯出至Hologres內部表。

    在Hologres中執行如下命令將慢Query日誌匯出至query_log_download內部表。

    --Hologres SQL
    CREATE TABLE query_log_download (
        usename text,
        status text,
        query_id text,
        datname text,
        command_tag text,
        duration integer,
        message text,
        query_start timestamp with time zone,
        query_date text,
        query text,
        result_rows bigint,
        result_bytes bigint,
        read_rows bigint,
        read_bytes bigint,
        affected_rows bigint,
        affected_bytes bigint,
        memory_bytes bigint,
        shuffle_bytes bigint,
        cpu_time_ms bigint,
        physical_reads bigint,
        pid integer,
        application_name text,
        engine_type text[],
        client_addr text,
        table_write text,
        table_read text[],
        session_id text,
        session_start timestamp with time zone,
        trans_id text,
        command_id text,
        optimization_cost integer,
        start_query_cost integer,
        get_next_cost integer,
        extended_cost text,
        plan text,
        statistics text,
        visualization_info text,
        query_detail text,
        query_extinfo text[]
    );
    
    INSERT INTO query_log_download SELECT
      usename,
      status,
      query_id,
      datname,
      command_tag,
      duration,
      message,
      query_start,
      query_date,
      query,
      result_rows,
      result_bytes,
      read_rows,
      read_bytes,
      affected_rows,
      affected_bytes,
      memory_bytes,
      shuffle_bytes,
      cpu_time_ms,
      physical_reads,
      pid,
      application_name,
      engine_type,
      client_addr,
      table_write,
      table_read,
      session_id,
      session_start,
      trans_id,
      command_id,
      optimization_cost,
      start_query_cost,
      get_next_cost,
      extended_cost,
      plan,
      statistics,
      visualization_info,
      query_detail,
      query_extinfo
    FROM hologres.hg_query_log
    WHERE query_start >= '2022-08-03'
      		AND query_start < '2022-08-04';
  • 樣本二:匯出至MaxCompute外部表格。

    1. 在MaxCompute側使用如下命令建立接收資料的表。

      CREATE TABLE if NOT EXISTS mc_holo_query_log (
          username STRING COMMENT '查詢的使用者名稱'
          ,status STRING COMMENT '查詢的最終狀態,成功或失敗'
          ,query_id STRING COMMENT '查詢ID'
          ,datname STRING COMMENT '查詢的資料庫名'
          ,command_tag STRING COMMENT '查詢的類型'
          ,duration BIGINT COMMENT '查詢的耗時(ms)'
          ,message STRING COMMENT '報錯的資訊'
          ,query STRING COMMENT '查詢的常值內容'
          ,read_rows BIGINT COMMENT '查詢讀取的行數'
          ,read_bytes BIGINT COMMENT '查詢讀取的位元組數'
          ,memory_bytes BIGINT COMMENT '單節點峰值記憶體消耗(非精確)'
          ,shuffle_bytes BIGINT COMMENT '資料Shuffle估計位元組數(非精確)'
          ,cpu_time_ms BIGINT COMMENT '總的CPU時間(毫秒,非精確)'
          ,physical_reads BIGINT COMMENT '物理讀的次數'
          ,application_name STRING COMMENT '查詢應用類型'
          ,engine_type ARRAY<STRING> COMMENT '查詢用到的引擎'
          ,table_write STRING COMMENT 'SQL改寫的表'
          ,table_read ARRAY<STRING> COMMENT 'SQL讀取的表'
          ,plan STRING COMMENT '查詢對應的plan'
          ,optimization_cost BIGINT COMMENT '查詢執行計畫的耗時'
          ,start_query_cost BIGINT COMMENT '查詢啟動耗時'
          ,get_next_cost BIGINT COMMENT '查詢執行耗時'
          ,extended_cost STRING COMMENT '查詢的其他詳細耗時'
          ,query_detail STRING COMMENT '查詢的其他擴充資訊(JSON格式儲存)'
          ,query_extinfo ARRAY<STRING> COMMENT '查詢的其他擴充資訊(ARRAY格式儲存)'
          ,query_start STRING COMMENT '查詢開始時間'
          ,query_date STRING COMMENT '查詢開始日期'
      ) COMMENT 'hologres instance query log daily'
      PARTITIONED BY (
          ds STRING COMMENT 'stat date'
      ) LIFECYCLE 365;
      
      ALTER TABLE mc_holo_query_log ADD PARTITION (ds=20220803);
    2. 在Hologres側使用如下命令將慢Query日誌匯出至MaxCompute外部表格。

      IMPORT FOREIGN SCHEMA project_name LIMIT TO (mc_holo_query_log)
      FROM SERVER odps_server INTO public;
      
      INSERT INTO mc_holo_query_log
      SELECT
          usename AS username,
          status,
          query_id,
          datname,
          command_tag,
          duration,
          message,
          query,
          read_rows,
          read_bytes,
          memory_bytes,
          shuffle_bytes,
          cpu_time_ms,
          physical_reads,
          application_name,
          engine_type,
          table_write,
          table_read,
          plan,
          optimization_cost,
          start_query_cost,
          get_next_cost,
          extended_cost,
          query_detail,
          query_extinfo,
          query_start,
          query_date,
          '20220803'
      FROM
          hologres.hg_query_log
      WHERE
          query_start >= '2022-08-03'
          AND query_start < '2022-08-04';

配置項

為了實現針對性地記錄目標Query,Hologres有如下配置項可供選擇。

log_min_duration_statement

  • 配置項說明

  • 該配置項記錄慢Query的最短耗時,系統預設採集大於100ms的Query,但是查詢時預設僅展示大於1s的Query,如需修改預設的展示時間,可以通過該配置項控制。需要注意的是:

    • 修改此配置項僅對新Query生效,對於已經採集的Query,則按照原預設值展示。

    • 只有超級使用者(Superuser)可以改變這個設定。

    • 設定為-1將則表示不記錄任何查詢。為正數時,最小值支援設定為100ms。

  • 使用樣本

    設定在慢Query日誌中所有已耗用時間等於或者超過250ms的SQL語句可以被查詢出來:

    -- DB層級設定,需要superuser設定
    ALTER DATABASE dbname SET log_min_duration_statement = '250ms';
    
    -- 當前Session層級設定,普通使用者可以執行
    SET log_min_duration_statement = '250ms';

log_min_duration_query_stats

  • 配置項說明

  • 該配置項記錄Query執行時的統計資訊,系統預設記錄大於10s的Query執行時的統計資訊,需要注意的是:

    • 修改此配置項僅對新Query生效,已被記錄的Query按照原預設值展示。

    • 設定為-1則關閉對Query統計資訊的記錄。

    • 此資訊儲存量較高,記錄較多會降低查詢分析慢Query日誌的速度,因此可以在排查具體問題時設定小於10s,否則不建議調整為更小的值。

  • 使用樣本

    設定記錄大於20s的Query執行時的統計資訊:

    --DB層級設定,需要superuser設定
    ALTER DATABASE dbname SET log_min_duration_query_stats = '20s';
    
    -- 當前Session層級設定,普通使用者可以執行
    SET log_min_duration_query_stats = '20s';

log_min_duration_query_plan

  • 配置項說明

  • 該配置項記錄Query的執行計畫資訊,系統將會預設展示大於等於10s的慢Query日誌的執行計畫,需要注意:

    • 修改此配置項僅對新Query生效,已被記錄的Query按照原預設值展示。

    • 如果某個語句的執行時間大於或者等於設定的毫秒數,那麼在慢Query日誌上記錄該語句的執行計畫。

    • 執行計畫一般情況下可以即時地通過explain得到,無需特別記錄。

    • 設定為-1則關閉對Query執行計畫的記錄。

  • 使用樣本

    設定記錄大於10s的Query日誌的執行計畫:

    -- DB層級設定,需要superuser設定
    ALTER DATABASE dbname SET log_min_duration_query_plan = '10s';
    
    -- 當前Session設定,普通使用者可以執行
    SET log_min_duration_query_plan = '10s';

常見問題

  • 問題現象:

    在Hologres V1.1版本中,查看慢Query日誌,無法顯示查詢行數、返回行數等資訊。

  • 可能原因:

    慢Query日誌採集不完整。

  • 解決方案:

    在Hologres V1.1.36至V1.1.49版本可以通過如下GUC參數使其顯示完整。在HologresV1.1.49及以上版本可直接顯示。

    說明

    如果您的Hologres執行個體版本低於 V1.1.36,請您使用自助升級或加入HologresDingTalk交流群反饋,詳情請參見如何擷取更多的線上支援?

    --(建議)資料庫層級,一個db設定一次即可。
    ALTER databse <db_name> SET hg_experimental_force_sync_collect_execution_statistics = ON;
    
    --session層級
    SET hg_experimental_force_sync_collect_execution_statistics = ON;

    db_name為資料庫名稱。

相關文檔

對執行個體中的Query進行診斷和管理,請參見Query管理