摘要：GaussDB(DWS)查詢過濾器（黑名單）提供查詢過濾功能，支持自動隔離反復被終止的查詢，防止爛SQL再次執行，
本文分享自華為云社區《GaussDB(DWS)查詢過濾器原理與應用》，作者：門前一棵葡萄樹 ，
一、概述
GaussDB(DWS)查詢過濾器（黑名單）提供查詢過濾功能，支持自動隔離反復被終止的查詢，防止爛SQL再次執行，
主要應用場景包含以下兩種：
1. 例外熔斷機制
配置例外規則后，查詢觸發例外規則后，例外資訊將被記錄在dbms_om.gs_blocklist_query系統表中，同一個查詢觸發例外規則次數超限（query_exception_count_limit）后，查詢自動加入黑名單，黑名單資訊同樣保存在dbms_om.gs_blocklist_query系統表中，加入黑名單后，該查詢將被隔離，拒絕執行，
2. 緊急攔截
作業引發CORE、hang或性能大幅下降等問題時，需要緊急規避時，可以將作業加入黑名單進行過濾，
原理介紹
查詢過濾器使用作業Unique SQL ID保存和識別作業黑名單和例外資訊，在SQL中常數值發生變化時作業Unique SQL ID不會隨之發生變化，Unique SQL ID是遍歷查詢決議樹計算出來的一個整數值，用于標識一類SQL，通常對于DML陳述句，在計算Unique SQL ID的程序中會忽略常量值，但對于DDL、DCL以及設定引數等陳述句，常量值不會忽略，例如，以下兩個查詢：
select * from t1 where id = 1; select * from t1 where id = 2;
這兩條SQL除過濾條件中的常量不同外，其他全部相同，由此生成的決議樹拓撲完全相同，因此Unique SQL ID相同，Unique SQL ID的計算只會忽略常數值，而不會忽略其他差異，SQL陳述句“select * from t2 where id = 1;”與上述兩個SQL的Unique SQL ID就不相同，
將作業加入黑名單主要有以下兩種方式：
- 在GUC引數query_exception_count_limit≥0情況下，作業觸發例外次數超過該閾值后自動將作業加入黑名單；
- 呼叫內置函式gs_append_blocklist(unique_sql_id int8)將作業加入黑名單，
作業執行前判斷作業是否在黑名單中，如果作業在黑名單中，拒絕作業執行，直接報錯退出，
作業被拒絕執行后，對作業加入黑名單原因進行分析，問題解決后呼叫內置函式gs_remove_blocklist(unique_sql_id int8)將作業移除黑名單，
二、應用示例
2.1 例外熔斷示例
1. 設定例外熔斷閾值，假設設定query_exception_count_limit=1，即只要作業觸發例外規則作業就會被加入黑名單，
2. 配置例外規則
創建CPU平均使用率例外規則cpu_percent_except，作業運行時間超過2000秒且CPU使用率達到30%時觸發例外退出：
CREATE EXCEPT RULE cpu_percent_except WITH(ELAPSEDTIME=2000, CPUAVGPERCENT=30);
例外規則還支持BLOCKTIME、ALLCPUTIME、SPILLSIZE等例外的識別處理，具體可參考：例外規則簡介與演變，
3. 創建資源池respool1關聯例外規則cpu_percent_except，
CREATE RESOURCE POOL respool1 WITH(except_rule='cpu_percent_except');
資源池支持最多關聯63個例外規則集，每個例外規則集間獨立生效，互不影響，
4. 創建業務用戶usr1，關聯資源池respool1：
CREATE USER usr1 RESOURCE POOL 'respool1' PASSWORD 'XXXXXX';
5. 用戶usr1運行作業，作業運行時間超過2000秒且CPU使用率達到30%時觸發“cpu_percent_except”例外規則，作業觸發例外規則后資源管理對作業進行以下處理：
- 將作業例外資訊保存至系統表GS_BLOCKLIST_QUERY中；
- 如果作業觸發例外熔斷，將系統表GS_BLOCKLIST_QUERY中作業黑名單標志置為true；
- 更新GS_BLOCKLIST_QUERY中作業黑名單資訊，
6. 查詢作業黑名單和例外資訊：
SELECT * FROM dbms_om.gs_blocklist_query; unique_sql_id | block_list | except_num | except_time ---------------+------------+------------+---------------------------- 4066836196 | t | 1 | 2022-08-08 18:00:00.596269 (1 row)
7. 用戶usr1再次運行作業觸發例外熔斷，GaussDB(DWS)的例外熔斷機制禁止該作業執行，
ERROR: The query is in the blocklist and cannot be run, unique_sql_id(4066836196). HINT: If you want to run the query later, confirm the reason why the query is blocklisted and remove the query from the blocklist after resolving the problem.
8. 優化用戶usr1所運行ID為4066836196的SQL后，將ID為4066836196的SQL從黑名單移除，
確認SQL例外原因，如果例外規則配置不合理，修改例外規則；如果例外規則合理，對SQL進行優化后重新運行，確認問題解決后將SQL移除黑名單，
select gs_remove_blocklist(4066836196); gs_remove_blocklist --------------------- t (1 row)
2.2 緊急攔截示例
查詢過濾器使用作業Unique SQL ID識別和保存黑名單資訊，為有效運用查詢過濾器緊急攔截功能，建議TopSQL開啟，在作業引發CORE、報錯、性能下降等問題時可以快速獲取作業Unique SQL ID，
2.2.1 獲取作業Unique SQL ID
獲取作業Unique SQL ID的幾種方法：
1. 作業引發報錯/性能下降
CN日志中獲取作業query_id，執行以下命令查詢作業Unique SQL ID，
select queryid,unique_sql_id,query from pgxc_wlm_session_info where queryid=query_id;
2. 作業引發CN示例CORE
決議CORE列印記憶體中保存的Unique SQL ID對應的變數引數值，
3. 作業引發DN實體CORE
作業引發DN實體CORE時，CN側體現為作業報錯，Unique SQL ID獲取方式可以參考作業報錯時Unique SQL ID獲取方式，
4. EXPLAIN VERBOSE獲取Unique SQL ID（通用方法，但是僅821及以上版本支持）
EXPLAIN VERBOSE不會實際執行SQL，因此一般不會導致問題發生，使用EXPLAIN VERBOSE XXX;可以列印得到作業Unique SQL ID，示例：
postgres=# explain verbose select count(1) from pg_class; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------- id | operation | E-rows | E-distinct | E-width | E-costs ----+----------------------------------------+--------+------------+---------+--------- 1 | -> Aggregate | 2 | | 8 | 52.94 2 | -> Seq Scan on pg_catalog.pg_class | 1034 | | 0 | 50.34 Targetlist Information (identified by plan id) ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 --Aggregate Output: count(1) 2 --Seq Scan on pg_catalog.pg_class Output: relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, relallvisible, reltoastrelid, reltoas tidxid, reldeltarelid, reldeltaidx, relcudescrelid, relcudescidx, relhasindex, relisshared, relpersistence, relkind, relnatts, relchecks, relhasoids, relhaspkey, r elhasrules, relhastriggers, relhassubclass, relcmprs, relhasclusterkey, relrowmovement, parttype, relfrozenxid, relacl, reloptions, relreplident, relfrozenxid64 ====== Query Summary ===== -------------------------- Parser runtime: 0.027 ms Planner runtime: 0.561 ms Unique SQL Id: 2307078791 (17 rows)
2.2.2 將作業加入黑名單
獲取到作業Unique SQL ID后，呼叫內置函式gs_append_blocklist(unique_sql_id int8)將作業加入黑名單：
postgres=# select * from gs_append_blocklist(2307078791); gs_append_blocklist --------------------- t (1 row)
2.2.3 查詢黑名單資訊
作業加入黑名單后，查詢系統表確認黑名單加入是否成功：
postgres=# SELECT * FROM dbms_om.gs_blocklist_query; unique_sql_id | block_list | except_num | except_time ---------------+------------+------------+------------- 2307078791 | t | 0 | (1 row)
2.2.4 再次執行作業觸發緊急攔截
postgres=# select count(1) from pg_class; ERROR: The query is in the blocklist and cannot be run, unique_sql_id(2307078791). HINT: If you want to run the query later, confirm the reason why the query is blocklisted and remove the query from the blocklist after resolving the problem.
2.2.5 問題解決，將作業移出黑名單
postgres=# select gs_remove_blocklist(2307078791); gs_remove_blocklist --------------------- t (1 row)
點擊關注，第一時間了解華為云新鮮技術~
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/554638.html
標籤：其他
上一篇：SQL Server 補丁理解及安裝 內附完整版下載地址及sp1/2/3補丁
下一篇：返回列表