PolarDB：ePQ Hint Injection

前提條件

ePQ Hint Injection依賴pg_hint_plan 外掛程式，您需要修改shared_preload_libraries參數，將pg_hint_plan添加到shared_pre_load_libraries 參數值中。修改參數具體操作，請參見設定叢集參數。

簡介

ePQ Hint Injection功能適用於跨機並行查詢（ePQ），資料庫營運時，在無法直接給SQL增加PX Hint的情況下，可以通過ePQ Hint Injection功能對指定SQL注入PX Hint 。在不改造SQL的情況下，可以讓指定的SQL運行或者不運行ePQ。

使用指南

建立外掛程式並設定開關

create extension polar_sql_mapping;
create extension pg_hint_plan;
alter database postgres set pg_hint_plan.enable_hint TO on;

SQL改寫功能

通過以下SQL樣本說明改寫功能。

explain select count(*) from t1;
select count(*) from t1;

準備資料

create table t1(c1 int,c2 int);
insert into t1 select generate_series(1,1000), generate_series(1,1000);
-- 將 t1 表的 px_workers 開啟
alter table t1 set(px_workers=1000);

將SQL寫入mapping表

-- 建立外掛程式
create extension polar_sql_mapping;
create extension pg_hint_plan;
alter database postgres set pg_hint_plan.enable_hint TO on;
-- 將 SQL 寫入 px mapping 表
select polar_sql_mapping.insert_px_mapping('explain select count(*) from t1;');
select polar_sql_mapping.insert_px_mapping('select count(*) from t1;');
-- 將 SQL 寫入 nonpx mapping 表
select polar_sql_mapping.insert_nonpx_mapping('explain select count(*) from t1;');
select polar_sql_mapping.insert_nonpx_mapping('select count(*) from t1;');

開啟改寫功能

推薦根據資料庫來設定參數，假設您當前在使用的資料庫名為postgres。

-- 開啟改寫功能
-- 注意，alter postgres 只對新會話生效，需要重啟串連, postgres 可替換為你當前的資料庫名
alter database postgres set polar_sql_mapping.use_px_sql_mapping=true;

-- 開啟 Notice，方便看提示資訊
SET pg_hint_plan.debug_print TO on;
SET pg_hint_plan.message_level TO notice;
SET polar_sql_mapping.log_usage = notice;
SET client_min_messages TO notice;

-- 進行改寫
explain select count(*) from t1;

得到如下的輸出，說明改寫成功：

postgres=# explain select count(*) from t1;
NOTICE:  sql mapping exist. The id = 1
NOTICE:  px sql mapping: change sql to '/*+PX()*/explain select count(*) from t1;'.
NOTICE:  pg_hint_plan:
used hint:
PX()
not used hint:
duplication hint:
error hint:

                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=0.00..431.00 rows=1 width=8)
   ->  PX Coordinator 6:1  (slice1; segments: 6)  (cost=0.00..431.00 rows=1 width=8)
         ->  Partial Aggregate  (cost=0.00..431.00 rows=1 width=8)
               ->  Partial Seq Scan on t1  (cost=0.00..431.00 rows=167 width=1)
 Optimizer: PolarDB PX Optimizer
(5 rows)

postgres=# select count(*) from t1;
NOTICE:  sql mapping exist. The id = 2
NOTICE:  px sql mapping: change sql to '/*+PX()*/select count(*) from t1;'.
NOTICE:  pg_hint_plan:
used hint:
PX()
not used hint:
duplication hint:
error hint:

 count
-------
  1000
(1 row)

在polar_enable_px開啟的情況下，則會被改寫為不帶PX的查詢：

set polar_enable_px=1;
explain select count(*) from t1;

顯示結果如下：

postgres=# set polar_enable_px=1;
SET
postgres=# select count(*) from t1;
NOTICE:  sql mapping exist. The id = 2
NOTICE:  px sql mapping: change sql to '/*+NoPX()*/select count(*) from t1;'.
NOTICE:  pg_hint_plan:
used hint:
NoPX(0)
not used hint:
duplication hint:
error hint:

 count
-------
  1000
(1 row)

postgres=# explain select count(*) from t1;
NOTICE:  sql mapping exist. The id = 1
NOTICE:  px sql mapping: change sql to '/*+NoPX()*/explain select count(*) from t1;'.
NOTICE:  pg_hint_plan:
used hint:
NoPX(0)
not used hint:
duplication hint:
error hint:

                         QUERY PLAN
------------------------------------------------------------
 Aggregate  (cost=17.50..17.51 rows=1 width=8)
   ->  Seq Scan on t1  (cost=0.00..15.00 rows=1000 width=0)
(2 rows)

開啟parse_tree匹配

ePQ Hint Injection預設採用字串匹配，需要空格、換行等精確匹配。ePQ Hint Injection目前提供parse_tree匹配模式，可以忽略空格、換行的影響，但是會增加匹配時間，約為字串匹配的兩倍。開啟檔案如下，參數為 polar_sql_mapping.use_px_parse_match：

-- 多了空格，無法匹配
explain select count(*) from t1;
-- 開啟 parse_tree 匹配
alter database postgres set polar_sql_mapping.use_px_parse_match =true;
-- 重啟會話
-- 可以匹配
explain select count(*) from t1;

顯示結果如下：

postgres=# explain select count(*) from t1;
                         QUERY PLAN
------------------------------------------------------------
 Aggregate  (cost=17.50..17.51 rows=1 width=8)
   ->  Seq Scan on t1  (cost=0.00..15.00 rows=1000 width=0)
(2 rows)

postgres=# alter database postgres set polar_sql_mapping.use_px_parse_match=true;
ALTER DATABASE

postgres=# explain select count(*) from t1;
NOTICE:  Parse tree match hit. Sql mapping exist. The id = 1
NOTICE:  px sql mapping: change sql to '/*+PX()*/explain select count(*) from t1;'.
NOTICE:  pg_hint_plan:
used hint:
PX()
not used hint:
duplication hint:
error hint:

                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=0.00..431.00 rows=1 width=8)
   ->  PX Coordinator 6:1  (slice1; segments: 6)  (cost=0.00..431.00 rows=1 width=8)
         ->  Partial Aggregate  (cost=0.00..431.00 rows=1 width=8)
               ->  Partial Seq Scan on t1  (cost=0.00..431.00 rows=167 width=1)
 Optimizer: PolarDB PX Optimizer
(5 rows)

無法匹配的情況

如果出現無法匹配的情況，可以開啟以下參數，通過Notice日誌，查看是否正確匹配。

-- 開啟 Notice，方便看提示資訊
SET pg_hint_plan.debug_print TO on;
SET pg_hint_plan.message_level TO notice;
SET polar_sql_mapping.log_usage = notice;
SET client_min_messages TO notice;

extend協議和PreparedStmt的SQL

對於大部分的應用程式都是使用PreparedStmt，在寫新的SQL時，需要注意預留位置應該是$n而不是'?'，樣本如下：

SQL中含有引號的情況

如果SQL中含有引號的情況，需要增加一個引號進行匹配。

樣本如下：

select instr(varchar_test,'arh') from test_datatype_list where varchar_test is not null order by 1 ;

則需要修改為以下SQL：

select polar_sql_mapping.insert_px_mapping('select instr(varchar_test,''arh'') from test_datatype_list where varchar_test is not null order by 1 ');

查看參與mapping的SQL

-- 查看執行 ePQ 查詢的映射表內容
postgres=# select * from polar_sql_mapping.polar_px_mapping_table;
 id |                   source_sql                   |                       target_sql

----+------------------------------------------------+---------------------------------
------------------------
  1 | select * from polar_sql_mapping.px_query_info; | /*+PX()*/select * from polar_sql
_mapping.px_query_info;
  2 | select count(*) from t1;                       | /*+PX()*/select count(*) from t1
;
(2 rows)

-- 查看不執行 ePQ 查詢的映射表內容
postgres=# select * from polar_sql_mapping.polar_nonpx_mapping_table;
 id | source_sql | target_sql
----+------------+------------
(0 rows)

慢SQL採集功能

慢SQL採集是一個協助工具功能，會將執行時間>=閾值或者<=閾值的最新N條SQL（N表示px_max_num） 採集到hash table當中，通過 select polar_sql_mapping.insert_px_mapping_id(id);即可將採集的SQL添加到mapping表當中，無需輸入整條SQL。參數如下：

查詢採集表

外掛程式內建函數可以查詢完整的採集表，px_query_info是一個視圖，視圖通過函數px_psm_query_info_internal() 來進行遍曆雜湊表。

select * from polar_sql_mapping.px_query_info;

清空採集表

外掛程式目前只提供清空採集表的功能，通過如下函數來進行觸發。

select polar_sql_mapping.px_query_info_clear();

樣本

-- 根據 DB 設定參數
alter database postgres set polar_sql_mapping.px_record_query=1;
alter database postgres set polar_sql_mapping.px_record_upper_or_lower=1;
-- 大於 3s 的 SQL 會被採集
alter database postgres set polar_sql_mapping.px_record_duration_time=3000;

-- 執行 >3 的 SQL
select sum(c1) from t1 where c1=(select count(pg_sleep(3)));

查詢 polar_sql_mapping.px_query_info，得到如下結果，說明已採集到。

select * from polar_sql_mapping.px_query_info;
 id |                            query                             | execution_time | calls
----+--------------------------------------------------------------+----------------+-------
  1 | select sum(c1) from t1 where c1=(select count(pg_sleep(3))); |           3004 |     1
(1 row)

將SQL添加到mapping表

-- 添加到 polar_px_mapping_table 中
select polar_sql_mapping.insert_px_mapping_id(1);
-- 添加到 polar_nonpx_mapping_table 中
select polar_sql_mapping.insert_nonpx_mapping_id(1);

將所有SQL添加到mapping表

-- 添加所有 query 到 polar_px_mapping_table 中
select polar_sql_mapping.insert_px_mapping_id(id) from polar_sql_mapping.px_query_info;
-- 添加所有 query 到 polar_nonpx_mapping_table 中
select polar_sql_mapping.insert_nonpx_mapping_id(id) from polar_sql_mapping.px_query_info;

進行改寫

結果如下所示：

postgres=# select sum(c1) from t1 where c1=(select count(pg_sleep(3)));
NOTICE:  Parse tree match hit. Sql mapping exist. The id = 6
NOTICE:  px sql mapping: change sql to '/*+PX()*/select sum(c1) from t1 where c1=(select count(pg_sleep(3)));'.
NOTICE:  pg_hint_plan:
used hint:
PX()
not used hint:
duplication hint:
error hint:

 sum
-----
   1
(1 row)

關閉採集功能

為了提升效能，不使用時，需要將採集功能關閉。

alter database postgres set polar_sql_mapping.px_record_query=0;