备案控制台
文档
产品文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB PostgreSQL版（兼容Oracle） 开发指南（Oracle语法兼容1.0） ePQ Hint Injection

ePQ Hint Injection

更新时间：
一键部署
产品详情
相关技术圈
我的收藏

本文介绍了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。

使用指南

创建插件并设置开关

说明

推荐根据DB/角色来设置开关。例如，当前的DB为postgres。

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;');
说明

其中：

  • polar_sql_mapping.insert_px_mapping：表示写入SQL至polar_sql_mapping.polar_px_mapping_table，该表在polar_enable_px关闭时使用，目的是将在polar_px_mapping_table表中匹配到的SQL加上/*+PX()*/。例如，select count(*) from t1;改写为 /*+PX()*/ select count(*) from t1;。简单来说，是为了在ePQ功能关闭的情况下，让某些长查询能够进行ePQ查询。

  • polar_sql_mapping.insert_nonpx_mapping：表示写入SQL至polar_sql_mapping.polar_nonpx_mapping_table，在polar_enable_px打开时使用，目的是将在polar_nonpx_mapping_table表中匹配到的SQL加上/*+NoPX()*/。例如，select count(*) from t1改写为/*+NoPX()*/ select count(*) from t1;。简单来说，是为了在ePQ功能打开的情况下，将某些短查询剔出ePQ查询。

开启改写功能

推荐根据数据库来设置参数，假设您当前在使用的数据库名为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)
说明

进行匹配的SQL和插入select polar_sql_mapping.insert_px_mapping()的SQL必须满足以下三个条件：

  • 含有select的DQL，也可以是explain。

  • 不含有/*+PX()*/和/*+NoPX()*/的hint。

  • 语法正确。

不满足以上三个条件的SQL会触发报错。

无法匹配的情况

如果出现无法匹配的情况，可以打开以下参数，通过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而不是'?'，示例如下：image..png

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。参数如下：

参数

说明

polar_sql_mapping.px_record_query

表示是否开启采集功能，取值如下：

  • true：开启采集功能。

  • false（默认）：不开启采集功能。

polar_sql_mapping.px_record_upper_or_lower

表示需要采集>=阈值的SQL，还是采集<=阈值的SQL，取值如下：

  • true（默认）：采集>=阈值的SQL。

  • false：采集<=阈值的SQL。

polar_sql_mapping.px_record_duration_time

表示记录时间的阈值，默认为10000ms，单位为ms。

polar_sql_mapping.px_max_num

表示能采集的最大SQL，默认为20。

说明

修改该参数需要重启数据库。

polar_sql_mapping.px_record_explain

表示是否采集explain语句。

  • true：采集explain语句。

  • false（默认）：不采集explain语句。

polar_sql_mapping.px_evict_entry_num

表示一次剔除多少个entry，默认为5。

查询采集表

插件内置函数可以查询完整的采集表，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;

  • 本页导读 （0）
文档反馈