當Hologres執行個體出現響應或查詢速度變慢時,您可以查看與分析慢Query日誌快速定位問題所在。慢Query的查看與分析可以協助您對系統中發生的慢Query或失敗Query進行識別、診斷和分析,並進一步採取最佳化措施,以提高系統整體效能。本文為您介紹在Hologres中如何查看慢Query日誌並分析。
版本說明
Hologres版本號碼 | 功能 | 說明 |
V0.10 | 新增慢Query日誌查看與分析功能。 |
|
V2.2.7 | 最佳化log_min_duration_statement參數預設值 | 慢Query日誌記錄所有的DDL語句、所有執行失敗的SQL語句以及執行時間大於 從Hologres V2.2.7版本開始,將 |
使用限制
在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),彙總資料僅記錄成功的記錄。 |
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 | 查詢的類型。 包括:
| 彙總周期內,同彙總主鍵第一個Query的查詢的類型。 |
duration | integer | 查詢的耗時(ms)。
系統預設只展示大於1s的DML和所有DDL,可以通過GUC參數 | 彙總周期內,同彙總主鍵所有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 | 查詢應用類型。 其中常見的應用類型如下:
其餘應用建議業務上在串連Hologres時,串連串上顯式指定 | 查詢應用類型。 |
engine_type | text[] | 查詢用到的引擎。 包含如下引擎類型:
| 彙總周期內,同彙總主鍵第一個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三個耗時之外的其他耗時,可以通過
| 彙總周期內,同彙總主鍵第一個Query的其他詳細耗時。 |
plan | text | 查詢對應的執行計畫。 執行計畫長度上限是102400個字元,超過可能會被截斷,可以通過GUC參數 | 彙總周期內,同彙總主鍵第一個Query的查詢對應的執行計畫。 |
statistics | text | 查詢對應的執行統計資訊。 統計資訊長度上限是102400個字元,超過可能會被截斷,可以通過GUC參數 | 彙總周期內,同彙總主鍵第一個Query的查詢對應的執行統計資訊。 |
visualization_info | text | 查詢Plan可視化資訊。 | 彙總周期內,同彙總主鍵第一個Query的查詢Plan可視化資訊。 |
query_detail | text | 查詢的其他擴充資訊(JSON格式儲存)。 說明 擴充資訊長度上限是10240個字元,超過可能會被截斷。 | 彙總周期內,同彙總主鍵第一個Query的查詢的其他擴充資訊。 |
query_extinfo | text[] |
說明 本地帳號登入、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命令查看。
登入HoloWeb控制台,詳情請參見串連HoloWeb並執行查詢。
單擊頂部導覽列的診斷與最佳化。
在左側導覽列單擊歷史慢Query。
在歷史慢Query頁面頂部,編輯查詢條件。
參數說明請參見歷史慢Query。
單擊查詢,在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總查詢數(預設為近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外部表格。
在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);
在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生效,對於已經採集的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';
該配置項記錄慢Query的最短耗時,系統預設採集大於100ms的Query,但是查詢時預設僅展示大於1s的Query,如需修改預設的展示時間,可以通過該配置項控制。需要注意的是:
log_min_duration_query_stats
配置項說明
修改此配置項僅對新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';
該配置項記錄Query執行時的統計資訊,系統預設記錄大於10s的Query執行時的統計資訊,需要注意的是:
log_min_duration_query_plan
配置項說明
修改此配置項僅對新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';
該配置項記錄Query的執行計畫資訊,系統將會預設展示大於等於10s的慢Query日誌的執行計畫,需要注意:
常見問題
問題現象:
在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管理。