摘要：GaussDB(DWS)查詢(xún)過(guò)濾器（黑名單）提供查詢(xún)過(guò)濾功能，支持自動(dòng)隔離反復(fù)被終止的查詢(xún)，防止?fàn)€SQL再次執(zhí)行。

本文分享自華為云社區(qū)《GaussDB(DWS)查詢(xún)過(guò)濾器原理與應(yīng)用》，作者：門(mén)前一棵葡萄樹(shù) 。

一、概述

GaussDB(DWS)查詢(xún)過(guò)濾器（黑名單）提供查詢(xún)過(guò)濾功能，支持自動(dòng)隔離反復(fù)被終止的查詢(xún)，防止?fàn)€SQL再次執(zhí)行。

主要應(yīng)用場(chǎng)景包含以下兩種：

【資料圖】

1. 異常熔斷機(jī)制

配置異常規(guī)則后，查詢(xún)觸發(fā)異常規(guī)則后，異常信息將被記錄在dbms_om.gs_blocklist_query系統(tǒng)表中。同一個(gè)查詢(xún)觸發(fā)異常規(guī)則次數(shù)超限（query_exception_count_limit）后，查詢(xún)自動(dòng)加入黑名單，黑名單信息同樣保存在dbms_om.gs_blocklist_query系統(tǒng)表中。加入黑名單后，該查詢(xún)將被隔離，拒絕執(zhí)行。

2. 緊急攔截

作業(yè)引發(fā)CORE、hang或性能大幅下降等問(wèn)題時(shí)，需要緊急規(guī)避時(shí)，可以將作業(yè)加入黑名單進(jìn)行過(guò)濾。

原理介紹

查詢(xún)過(guò)濾器使用作業(yè)Unique SQL ID保存和識(shí)別作業(yè)黑名單和異常信息，在SQL中常數(shù)值發(fā)生變化時(shí)作業(yè)Unique SQL ID不會(huì)隨之發(fā)生變化。Unique SQL ID是遍歷查詢(xún)解析樹(shù)計(jì)算出來(lái)的一個(gè)整數(shù)值，用于標(biāo)識(shí)一類(lèi)SQL。通常對(duì)于DML語(yǔ)句，在計(jì)算Unique SQL ID的過(guò)程中會(huì)忽略常量值。但對(duì)于DDL、DCL以及設(shè)置參數(shù)等語(yǔ)句，常量值不會(huì)忽略。例如，以下兩個(gè)查詢(xún)：

select * from t1 where id = 1;select * from t1 where id = 2;

這兩條SQL除過(guò)濾條件中的常量不同外，其他全部相同，由此生成的解析樹(shù)拓?fù)渫耆嗤虼薝nique SQL ID相同。Unique SQL ID的計(jì)算只會(huì)忽略常數(shù)值，而不會(huì)忽略其他差異，SQL語(yǔ)句“select * from t2 where id = 1;”與上述兩個(gè)SQL的Unique SQL ID就不相同。

將作業(yè)加入黑名單主要有以下兩種方式：

• 在GUC參數(shù)query_exception_count_limit≥0情況下，作業(yè)觸發(fā)異常次數(shù)超過(guò)該閾值后自動(dòng)將作業(yè)加入黑名單；
• 調(diào)用內(nèi)置函數(shù)gs_append_blocklist(unique_sql_id int8)將作業(yè)加入黑名單。

作業(yè)執(zhí)行前判斷作業(yè)是否在黑名單中，如果作業(yè)在黑名單中，拒絕作業(yè)執(zhí)行，直接報(bào)錯(cuò)退出。

作業(yè)被拒絕執(zhí)行后，對(duì)作業(yè)加入黑名單原因進(jìn)行分析，問(wèn)題解決后調(diào)用內(nèi)置函數(shù)gs_remove_blocklist(unique_sql_id int8)將作業(yè)移除黑名單。

二、應(yīng)用示例

2.1 異常熔斷示例

1. 設(shè)置異常熔斷閾值。假設(shè)設(shè)置query_exception_count_limit=1，即只要作業(yè)觸發(fā)異常規(guī)則作業(yè)就會(huì)被加入黑名單。

2. 配置異常規(guī)則

創(chuàng)建CPU平均使用率異常規(guī)則cpu_percent_except，作業(yè)運(yùn)行時(shí)間超過(guò)2000秒且CPU使用率達(dá)到30%時(shí)觸發(fā)異常退出：

CREATE EXCEPT RULE cpu_percent_except WITH(ELAPSEDTIME=2000, CPUAVGPERCENT=30);

異常規(guī)則還支持BLOCKTIME、ALLCPUTIME、SPILLSIZE等異常的識(shí)別處理，具體可參考：異常規(guī)則簡(jiǎn)介與演變。

3. 創(chuàng)建資源池respool1關(guān)聯(lián)異常規(guī)則cpu_percent_except。

CREATE RESOURCE POOL respool1 WITH(except_rule="cpu_percent_except");

資源池支持最多關(guān)聯(lián)63個(gè)異常規(guī)則集，每個(gè)異常規(guī)則集間獨(dú)立生效，互不影響。

4. 創(chuàng)建業(yè)務(wù)用戶(hù)usr1，關(guān)聯(lián)資源池respool1：

CREATE USER usr1 RESOURCE POOL "respool1" PASSWORD "XXXXXX";

5. 用戶(hù)usr1運(yùn)行作業(yè)，作業(yè)運(yùn)行時(shí)間超過(guò)2000秒且CPU使用率達(dá)到30%時(shí)觸發(fā)“cpu_percent_except”異常規(guī)則，作業(yè)觸發(fā)異常規(guī)則后資源管理對(duì)作業(yè)進(jìn)行以下處理：

• 將作業(yè)異常信息保存至系統(tǒng)表GS_BLOCKLIST_QUERY中；
• 如果作業(yè)觸發(fā)異常熔斷，將系統(tǒng)表GS_BLOCKLIST_QUERY中作業(yè)黑名單標(biāo)志置為true；
• 更新GS_BLOCKLIST_QUERY中作業(yè)黑名單信息。

6. 查詢(xún)作業(yè)黑名單和異常信息：

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. 用戶(hù)usr1再次運(yùn)行作業(yè)觸發(fā)異常熔斷，GaussDB(DWS)的異常熔斷機(jī)制禁止該作業(yè)執(zhí)行。

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. 優(yōu)化用戶(hù)usr1所運(yùn)行ID為4066836196的SQL后，將ID為4066836196的SQL從黑名單移除。

確認(rèn)SQL異常原因，如果異常規(guī)則配置不合理，修改異常規(guī)則；如果異常規(guī)則合理，對(duì)SQL進(jìn)行優(yōu)化后重新運(yùn)行。確認(rèn)問(wèn)題解決后將SQL移除黑名單。

select gs_remove_blocklist(4066836196); gs_remove_blocklist--------------------- t(1 row)

2.2 緊急攔截示例

查詢(xún)過(guò)濾器使用作業(yè)Unique SQL ID識(shí)別和保存黑名單信息，為有效運(yùn)用查詢(xún)過(guò)濾器緊急攔截功能，建議TopSQL開(kāi)啟，在作業(yè)引發(fā)CORE、報(bào)錯(cuò)、性能下降等問(wèn)題時(shí)可以快速獲取作業(yè)Unique SQL ID。

2.2.1 獲取作業(yè)Unique SQL ID

獲取作業(yè)Unique SQL ID的幾種方法：

1. 作業(yè)引發(fā)報(bào)錯(cuò)/性能下降

CN日志中獲取作業(yè)query_id，執(zhí)行以下命令查詢(xún)作業(yè)Unique SQL ID。

select queryid,unique_sql_id,query from pgxc_wlm_session_info where queryid=query_id;

2. 作業(yè)引發(fā)CN示例CORE

解析CORE打印內(nèi)存中保存的Unique SQL ID對(duì)應(yīng)的變量參數(shù)值。

3. 作業(yè)引發(fā)DN實(shí)例CORE

作業(yè)引發(fā)DN實(shí)例CORE時(shí)，CN側(cè)體現(xiàn)為作業(yè)報(bào)錯(cuò)，Unique SQL ID獲取方式可以參考作業(yè)報(bào)錯(cuò)時(shí)Unique SQL ID獲取方式。

4. EXPLAIN VERBOSE獲取Unique SQL ID（通用方法，但是僅821及以上版本支持）

EXPLAIN VERBOSE不會(huì)實(shí)際執(zhí)行SQL，因此一般不會(huì)導(dǎo)致問(wèn)題發(fā)生，使用EXPLAIN VERBOSE XXX;可以打印得到作業(yè)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, reltoastidxid, reldeltarelid, reldeltaidx, relcudescrelid, relcudescidx, relhasindex, relisshared, relpersistence, relkind, relnatts, relchecks, relhasoids, relhaspkey, relhasrules, 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 將作業(yè)加入黑名單

獲取到作業(yè)Unique SQL ID后，調(diào)用內(nèi)置函數(shù)gs_append_blocklist(unique_sql_id int8)將作業(yè)加入黑名單：

postgres=# select * from gs_append_blocklist(2307078791); gs_append_blocklist--------------------- t(1 row)

2.2.3 查詢(xún)黑名單信息

作業(yè)加入黑名單后，查詢(xún)系統(tǒng)表確認(rèn)黑名單加入是否成功：

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 再次執(zhí)行作業(yè)觸發(fā)緊急攔截

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 問(wèn)題解決，將作業(yè)移出黑名單

postgres=# select gs_remove_blocklist(2307078791); gs_remove_blocklist--------------------- t(1 row)

點(diǎn)擊關(guān)注，第一時(shí)間了解華為云新鮮技術(shù)~