診斷與分析慢查詢-雲原生資料倉儲AnalyticDB-阿里雲

AnalyticDB PostgreSQL版提供了慢查詢診斷功能，旨在協助您對慢查詢進行診斷、分析和採取最佳化措施。在使用該功能前，您需要開啟慢查詢功能。開啟後，系統將自動記錄超過閾值為1秒的SQL語句，您可以串連到postgres庫進行查詢和分析。

前提條件

慢查詢診斷功能僅支援儲存彈性模式7.0版本，且核心版本必須為V7.0.5.0及以上。如何查看和升級核心版本以及核心小版本，請參見查看核心小版本和版本升級。

注意事項

慢查詢日誌預設保留一個星期的資料，但不會記錄失敗的查詢。它記錄所有執行時間超過GUCslow_query_min_duration參數設定（預設值是1秒）的SQL語句，即記錄執行時間超過1秒的所有查詢（包括DDL），為了保障系統的穩定性，建議不要修改該參數。
慢查詢日誌將不會記錄文本長度超過1024位元組的慢SQL語句，超過該數值的SQL文本將被截斷。

開啟或關閉慢查詢

--on代表開啟, off代表關閉
SHOW adbpg_feature_enable_query_monitor;
--業務庫開啟慢查詢診斷功能
ALTER database {業務庫} SET adbpg_feature_enable_query_monitor to ON;

使用樣本

系統將預設採集執行時間大於1秒的查詢以及所有的DDL，慢查詢日誌存放在postgres系統庫中。如需查詢當前執行個體的慢查詢情況，請切換到postgres庫，通過查詢qmonitor.instance_slow_queries（執行個體層級）和qmonitor.host_slow_queries（節點層級）這兩個視圖來進行慢查詢的診斷。以下將為您介紹進行慢查詢診斷的典型情境，主要包括：

說明

詳細欄位資訊請參考：附錄

查詢某時間範圍內所有的慢Query語句。

查詢最近某個時間段（如30分鐘）所有慢Query語句。您也可以根據業務需求修改具體時間，查詢目標時間段消耗較高的慢Query語句。

SELECT 
       query_start AS "開始時間",
       query_end AS "結束時間",
       query_duration_ms AS "耗時(ms)",
       query_id AS "查詢ID",
       query AS "查詢SQL"
 FROM qmonitor.instance_slow_queries
 WHERE query_start >= now() - interval '30 min';

查詢2024年02月26日這天（慢查詢日誌預設保留最近7天的資料）所有慢Query語句。

SELECT 
       query_start AS "開始時間",
       query_end AS "結束時間",
       query_duration_ms AS "耗時(ms)",
       query_id AS "查詢ID",
       query AS "查詢SQL"
 FROM qmonitor.instance_slow_queries
 WHERE query_start >= '2024-02-26 00:00:00'
 AND query_end <= '2024-02-27 00:00:00';

查詢某時間範圍內執行個體層級某資源（CPU使用、記憶體使用量或者落地檔案大小）TopN的慢Query語句。

查詢執行個體某個時間段（如30分鐘）CPU消耗排名前20的慢Query語句。您也可以根據業務需求修改具體時間，查詢目標時間段消耗較高的慢Query語句。

SELECT 
       (cpu_time_ms/1000)::text || ' s' AS "CPU時間",
       query_start AS "開始時間",
       query_end AS "結束時間",
       query_duration_ms AS "耗時(ms)",
       query_id AS "查詢ID",
       query AS "查詢SQL"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '30 min'
ORDER BY cpu_time_ms DESC
LIMIT 20;

查詢執行個體某個時間段（如30分鐘）記憶體消耗排名前20的慢Query語句。您也可以根據業務需求修改具體時間，查詢目標時間段消耗較高的慢Query語句。
```
SELECT 
       pg_size_pretty(memory_bytes) AS "記憶體使用量",
       query_start AS "開始時間",
       query_end AS "結束時間",
       query_duration_ms AS "耗時(ms)",
       query_id AS "查詢ID",
       query AS "查詢SQL"
 FROM qmonitor.instance_slow_queries
 WHERE query_start >= now() - interval '30 min'
 ORDER BY memory_bytes DESC
 LIMIT 20;
```
說明
在進行統計查詢時，需要考慮記憶體的消耗情況。在查詢過程中，記憶體的消耗呈現出一定的波動。因此，僅採集查詢過程中的尖峰記憶體使用量作為記憶體的消耗數值，這並非精確值，而是對Query記憶體使用量量的粗略體現。

查詢2024年02月26日0點至12點CPU消耗排名前10的慢Query語句：

SELECT 
       (cpu_time_ms/1000)::text || ' s' AS "CPU時間",
       query_start AS "開始時間",
       query_end AS "結束時間",
       query_duration_ms AS "耗時(ms)",
       query_id AS "查詢ID",
       query AS "查詢SQL"
FROM qmonitor.instance_slow_queries
WHERE query_start >= '2024-02-26 00:00:00'
AND query_end <= '2024-02-26 12:00:00'
ORDER BY cpu_time_ms DESC
LIMIT 10;

查詢某時間範圍內節點層級某資源（CPU使用、記憶體使用量或者落地檔案大小）TopN的慢Query語句。

查詢某節點某個時間段（如30分鐘）CPU消耗排名前20的慢Query語句。您也可以根據業務需求修改具體時間，查詢目標時間段消耗較高的慢Query語句：

SELECT 
        (host_cpu_time_ms/1000)::text || ' s' as "CPU時間",
       query_start as "開始時間",
       query_end as "結束時間",
       query_duration_ms as "耗時(ms)",
       query_id as "查詢ID",
       query as "查詢SQL"
FROM qmonitor.host_slow_queries
WHERE hostname = '節點hostname' 
AND query_start >= now() - interval '30 min'
ORDER BY host_cpu_time_ms DESC
LIMIT 20;

查詢某節點某個時間段（如30分鐘）記憶體消耗排名前20的慢Query語句。您也可以根據業務需求修改具體時間，查詢目標時間段消耗比較高的慢Query語句：

SELECT 
        pg_size_pretty(host_mem_bytes) as "記憶體使用量",
       query_start as "開始時間",
       query_end as "結束時間",
       query_duration_ms as "耗時(ms)",
       query_id as "查詢ID",
       query as "查詢SQL"
 FROM qmonitor.host_slow_queries
 WHERE hostname = '節點hostname'
 AND query_start >= now() - interval '30 min'
 ORDER BY host_mem_bytes DESC
 LIMIT 20;

查詢某個慢查詢日誌的具體資訊。

SELECT * FROM qmonitor.instance_slow_queries WHERE query_id = '<某查詢的QueryId>';

查詢某時間範圍內每個使用者的慢Query情況。
- 查詢某時間範圍內（如10分鐘）每個使用者的慢Query情況，您也可以根據業務需求修改具體時間，查詢目標時間段內每個使用者的慢Query情況：
```
SELECT user_name AS "使用者",
COUNT(1) AS "Query個數"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '10 min'
GROUP BY user_name
ORDER BY COUNT(1) DESC;
```

匯出慢查詢日誌

AnalyticDB PostgreSQL版支援使用SELECT語句將慢查詢日誌（qmonitor.instance_slow_queries）或（qmonitor.host_slow_queries）中的資料匯出到使用者自訂的內部表或者MaxCompute、OSS等外部表格中，詳細請參考資料湖分析。為了正確和高效地匯出慢查詢日誌中的資料，您需要注意：

在AnalyticDB PostgreSQL版中，慢查詢日誌會根據查詢開始時間query_start建立索引並進行分區表欄位的設定。當按時間範圍匯出時，在查詢條件中包含query_start列，從而實現更佳的效能和更少的資源消耗。例如想匯出2024年2月26日下午1點到4點的慢日誌, 則可添加條件：query_start >= '2024-02-26 13:00:00' and query_start <= '2024-02-26 16:00:00' 。

配置項

通過配置項可以修改慢Query語句的預設閾值，方便您查詢出不同條件下的慢查詢日誌。

說明

只支援超級使用者（Superuser）修改該配置項。

slow_query_min_duration
該配置項記錄的慢Query語句的閾值預設是1秒，也可以通過修改該配置項記錄小於1秒的慢Query語句。某個語句的執行時間大於或等於該設定值時，慢查詢日誌會記錄該語句、執行時間以及其它相關資訊。將該參數設定為-1則表示不記錄任何慢查詢。
樣本如下;
1. 設定當前資料庫記錄大於5秒的慢Query語句，需要高許可權使用者進行設定。
```
ALTER DATABASE '<資料庫名稱>' SET slow_query_min_duration = '5s';
```
2. 設定當前Session記錄大於5秒的慢Query語句，普通使用者可以執行。
```
SET slow_query_min_duration = '5s';
```

slow_query_plan_min_duration
該配置項記錄的執行計畫資訊，系統將預設展示大於等於10秒的慢執行計畫。當某個語句的執行時間大於等於該數值時，慢查詢日誌會記錄該語句的執行計畫。通常情況下，可以通過explain命令串連SELECT語句即時查看執行計畫，無需記錄。該參數取值設定為-1則表示關閉對執行計畫的記錄。
樣本如下：
1. 設定記錄大於10秒的執行計畫，需要高許可權使用者進行設定。
```
ALTER DATABASE '<資料庫名稱>' SET slow_query_plan_min_duration = '10s';
```
2. 設定記錄大於10秒的執行計畫，普通使用者可以執行。
```
SET slow_query_plan_min_duration = '10s';
```

附錄

qmonitor.instance_slow_queries（執行個體層級）視圖的各個欄位如下：

欄位	類型	說明
query_id	text	查詢ID，代表Query的唯一性。
session_id	integer	查詢會話的ID。
db_name	character varying(128)	查詢的資料庫名。
user_name	character varying(128)	查詢的使用者名稱。
application_name	character varying(128)	查詢應用類型。
client_hostname	character varying(128)	Query的來源用戶端Hostname地址。
client_addr	character varying(128)	Query的來源用戶端IP地址。
client_port	character varying(32)	Query的來源用戶端連接埠。
rsg_name	character varying(128)	如果開啟了資源管理resource group，則查詢的是所在resource group的組名。
query_start	timestamptz	查詢開始時間。
query_end	timestamptz	查詢結束時間。
query_duration_ms	bigint	查詢的耗時（ms)。 query_duration代表SQL的總耗時時間，其中包含： optimizer_duration_ms lock_wait_time_ms queue_wait_time_ms executor_duration_ms
optimizer_duration_ms	bigint	產生執行計畫的耗時（ms），耗時較高通常是因為SQL較複雜。
lock_wait_time_ms	bigint	查詢等鎖耗時（ms）。
queue_wait_time_ms	bigint	查詢等待資源隊列耗時（ms）。
executor_duration_ms	bigint	該查詢在執行引擎上運行耗時（ms）。
query	text	查詢常值內容。
is_plpgsql	boolean	查詢是否為一個PL/PGSQL預存程序。
query_optimizer	character varying(16)	查詢所使用的最佳化器（ORCA or Planner）。
access_tables	text	查詢所訪問的表名。
result_rows	bigint	返回查詢的行數。如果是INSERT命令，則返回插入的行數。
num_segments	integer	查詢所在計算節點的個數。
num_slices	integer	查詢計劃產生slice的個數。
cpu_time_ms	numeric	總的CPU使用時間（ms）。包含如下時間：協調節點Master上消耗的CPU時間。所有計算節點上任務所耗費CPU時間的累加值。
mem_bytes	numeric	各節點上使用記憶體峰值的累加值為非精確值，可以粗略地體現Query的記憶體使用量量。
spill_bytes	numeric	由於在計算過程中，記憶體不足可能導致資料落盤。落盤峰值的累加值並非精確值，反映了查詢在所有計算節點上落盤檔案峰值的累加情況，即查詢操作所使用的落盤空間。

qmonitor.host_slow_queries（節點層級）視圖的各個欄位如下：

欄位	類型	說明
hostname	character varying(128)	節點所在的hostname。
hostrole	text	節點的角色包含以下兩種： master（協調節點） segment（計算節點）
query_id	text	查詢ID，代表Query的唯一性。
db_name	character varying(128)	查詢資料庫名。
user_name	character varying(128)	查詢使用者名稱。
query_start	timestamptz	查詢開始時間。
query_end	timestamptz	查詢結束時間。
query	text	查詢常值內容。
query_duration_ms	bigint	查詢耗時（ms）。
optimizer_duration_ms	bigint	生產執行計畫的耗時（ms），通常耗時較高是因為SQL較為複雜。
host_cpu_time_ms	numeric	該節點上查詢使用CPU時間（ms）。
host_mem_bytes	numeric	該節點上查詢使用的記憶體峰值。
host_spill_bytes	numeric	該節點上查詢過程中落盤的峰值。