教你處理數倉慢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等待事件，進一步優化，

點擊關注，第一時間了解華為云新鮮技術~

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/522985.html

標籤：SQL Server

上一篇：SQL SERVER 查詢所有表統計每張表的大小

下一篇：「MySQL高級篇」explain分析SQL，索引失效&&常見優化場景