教你處理數倉慢SQL常見定位問題

摘要：通常在運維監控出現CPU使用率較高、P80/P95指標較高、慢SQL數量上升等現象，或者業務出現超時報錯時，優先應排查是否出現慢SQL。

本文分享自華為雲社區《GaussDB慢SQL常見定位處理手段》，作者：酷哥。

關鍵指標

通常在運維監控出現CPU使用率較高、P80/P95指標較高、慢SQL數量上升等現象，或者業務出現超時報錯時，優先應排查是否出現慢SQL。

定位慢SQL手段

實時慢SQL查詢

查詢當前執行時間TOP10的SQL，識別長時間未結束的SQL後可以手動中止。

select
    a.pid,
    a.sessionid,
    a.datname,
    a.usename,
    a.application_name,
    a.client_addr,
    a.xact_start,
    a.query_start,
    (now() - a.query_start)::text as query_runtime,
    a.unique_sql_id,
    w.wait_status,
    w.wait_event,
    w.locktag,
    w.lockmode,
    w.block_sessionid,
    a.query
from
    pg_stat_activity a join
    pg_thread_wait_status w on
    a.sessionid = w.sessionid
where
    a.pid <> pg_backend_pid()
    and a.state = 'active'
    and a.client_addr is not null
order by
    query_runtime desc;

根據查詢結果，如果是等待鎖，可以結合鎖等待信息進一步分析，其他情況可以根據unique_query_id關聯WDR報告、statement視圖進一步分析慢的根因。

歷史慢SQL查詢

思路：根據CPU、慢SQL等監控指標，定位慢SQL出現的時間範圍，通過以下幾種方式進一步分析。

整體運行情況分析：WDR報告

通過導出對應時間段的WDR報告，可以分析耗時較長的SQL，WDR報告生成方法參見產品文檔。

單次執行情況分析：statement_history

statement_history記錄了執行時間超過閾值(log_min_duration_statement，預設3 s)的詳細SQL信息，包含計劃生成時間、執行時間、鎖等待時間等信息，其中部分信息與參數track_stmt_stat_level設置的級別（預設為'OFF,L0'）有關。 設置參數track_stmt_stat_level='OFF,L1'後，statement_history中可以記錄計劃信息、鎖等待時間等信息。 必須在postgres庫內查詢，根據時間段查詢慢SQL（按照執行時間排序）

SELECT
  *,
  finish_time - start_time as run_time
FROM
  dbe_perf.statement_history
WHERE
  start_time > '2022-07-08 18:00:00'
   AND start_time < '2022-07-08 19:00:00'
   -- 根據unique_query_id可以過濾出特定的查詢
   -- AND unique_query_id = 123456
ORDER BY
  run_time desc;

單個Query運行情況分析：statement

statement記錄了SQL按照unique_sql_id歸一化的執行信息，包括執行次數、總的執行時間、訪問數據量、記憶體使用等信息。 根據unique_sql_id查詢歷史執行信息

SELECT
  *,
  total_elapse_time / n_calls as avg_elapse_time
FROM
  dbe_perf.statement
WHERE
  unique_query_id = 123456;

動態抓取執行信息（計劃、鎖等待時間等）

為了避免對生產環境產生影響，可以動態抓取SQL執行信息

-- 抓取指定unique_sql_id的全量SQL信息
-- 示例：unique_sql_id為3267119089，全量SQL級別為L2，相當於track_stmt_stat_level='L2,off'
select * from dynamic_func_control('LOCAL', 'STMT', 'TRACK', '{"3267119089", "L2"}');
-- 打開之後，查詢statement_history
-- 關閉抓取，清理
select * from dynamic_func_control('LOCAL', 'STMT', 'UNTRACK', '{"3267119089"}');
select * from dynamic_func_control('LOCAL', 'STMT', 'LIST', '{}');
select * from dynamic_func_control('LOCAL', 'STMT', 'CLEAN', '{}');

查看會話快照信息

SELECT
*
FROM
dbe_perf.local_active_session
WHERE
query_start_time > '2022-07-08 18:00:00'
AND query_start_time < '2022-07-08 19:00:00'
AND unique_query ilike '%%';

常用處理手段

中止慢SQL

根據查詢結果中的pid和sessionid，使用函數中止查詢

select pg_terminate_session(pid,sessionid);

優化SQL

更新統計信息

查看統計信息

select * from pg_stats where tablename = '表名';
select * from pg_stats where tablename = '表名' and attname = '列名';

更新統計信息

analyze tablename;

手動設置列的distinct值（該欄位不同值的數量，選擇率 ~ 總行數/distinct值）

ALTER TABLE tablename ALTER COLUMN colname SET (n_distinct = 實際值);
analyze tablename; -- analyze執行後生效
​
-- 取消設置
ALTER TABLE tablename ALTER COLUMN colname RESET (n_distinct);
analyze tablename; -- analyze執行後生效

使用hint優化計劃

• 通過分析慢SQL的計劃，可以使用hint進行調整，openGaussc常用的hint包括：
• Join順序的Hint，語法示例：/+ leading((t1 t2))/
• Join方式的Hint，語法示例：/+ nestloop(t1 t2)/
• Scan方式的Hint，語法示例：/+ indexscan(t1 index1)/
• 優化器GUC參數的Hint，語法示例：/+ set(param value)/
• Custom Plan和Generic Plan選擇的Hint，語法示例：/+ use_cplan/
• ....

修改參數

根據慢SQL分析結論，可以考慮修改GUC參數，但是修改參數同時也會影響其他查詢的計劃，屬於高風險操作。

其他

對於整體執行慢，可以通過分析WDR報告中TOP等待事件，進一步優化。

點擊關註，第一時間瞭解華為雲新鮮技術~