关键时刻HINT出彩 - PG优化器的参数优化、执行计划固化CASE

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: 背景 有过数据库使用经验的童鞋可曾遇到过SQL执行计划不准确,或者SQL执行计划抖动的问题。 PostgreSQL的执行计划与大多数的企业数据库是一样的,都是基于成本优化。 基于成本优化的优化器,在算法靠谱,统计信息准确的前提下,通常得到的执行计划是比较准确的。 那么什么时候执行

背景

有过数据库使用经验的童鞋可曾遇到过SQL执行计划不准确,或者SQL执行计划抖动的问题。

PostgreSQL的执行计划与大多数的企业数据库是一样的,都是基于成本优化。

基于成本优化的优化器,在算法靠谱,统计信息准确的前提下,通常得到的执行计划是比较准确的。

那么什么时候执行计划可能不准确呢?

  • 成本估算的算法不好
    这个需要内核的不断改进,完善。

在没有合理的算法支撑的情况下,内核中往往会带有一些经验值,或者将这些经验值开放给用户设置。

  • 统计信息不准确
    PG的统计信息收集调度是几个参数共同决定的,也可以设置为表级别的调度。

如果发生了大批数据的变化,而且在调度来临之前没有及时的更新统计信息,就可能出现统计信息不准确的时间窗口。
还好PG的统计信息收集是动态调度的,一般不会出问题。

另外,如果SQL过于复杂,索引过多,可选用plan路径过多,在算法不好或者统计信息不准确的情况下,成本估算的误差成倍放大,可能造成选择的最优执行计划不准确。

如何让执行计划更准确

既然执行计划和算法、统计信息有关。要让执行计划准确,当然是有方法的。

PostgreSQL 影响执行计划的几种方法

  • 因子
    在计算成本的公式中,会用到一些因子,这些因子会影响最终计算到的成本。
# - Planner Cost Constants -
#seq_page_cost = 1.0                    # measured on an arbitrary scale
#random_page_cost = 4.0                 # same scale as above
#cpu_tuple_cost = 0.01                  # same scale as above
#cpu_index_tuple_cost = 0.005           # same scale as above
#cpu_operator_cost = 0.0025             # same scale as above
#effective_cache_size = 4GB

例如random_page_cost 表示使用索引扫描时,单个数据块的扫描成本。
seq_page_cost 表示全表扫描时,单个数据块的扫描成本。
如果你想让执行计划准确,就需要校准这些因子。
我之前写过成本因子校准的文章,大家可以阅读一下
http://blog.163.com/digoal@126/blog/static/163877040201310255717379
校准因子是非常通俗且恰当的方法。

  • 开关
    PostgreSQL提供了一些开关,允许用户关闭或打开,从而建议优化器允许或不允许使用一些扫描方法。(并非强制,当没有其他可选方法时,则依然会使用)

例如,关闭全表扫描 set enable_seqscan=off , 则优化器在能走其他扫描方法的时候,就不会走全表扫描,否则还是会走全表扫描的。

# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
  • join与子查询固化或rewrite
    join或子查询的优化,属于优化器优化JOIN的范畴。

当用户的QUERY涉及到多个JOIN对象,或者涉及到多个子查询时,优化器可以选择是否改变当前的SQL,产生更多的plan选择更优的执行计划。

#from_collapse_limit = 8
当from列表的对象少于from_collapse_limit时,优化器可以将子查询提升到上层进行JOIN,从而可能选择到更优的执行计划。  
#join_collapse_limit = 8                # 1 disables collapsing of explicit
                                        # JOIN clauses
当使用显示的JOIN时(除了full join),例如a join b join c join d,优化器可以重排JOIN的顺序,以产生更多的PLAN选择更优的执行计划。  
如果join_collapse_limit=1,则不重排,使用SQL写法提供的顺序。  

如果用户要固化JOIN顺序,请使用显示的JOIN,同时将join_collapse_limit设置为1。
如果用户不打算提升子查询,同样的,将from_collapse_limit 设置为1即可。

  • 约束排除
    通过设置以下参数,在SQL查询时,通过逻辑推理排除不需要查询的表。

《PostgreSQL 优化器逻辑推理能力 源码解析》
https://yq.aliyun.com/articles/6821

#constraint_exclusion = partition       # on, off, or partition
  • 游标建议
    通过设置以下参数,在使用游标时,告诉优化器是快速的返回部分行,还是快速返回所有行。
#cursor_tuple_fraction = 0.1            # range 0.0-1.0
  • 遗传算法
    PostgreSQL在遇到很多表的JOIN时,如果表的数量超过了参数geqo_threshold 设置的阈值,则会使用遗传算法,选择一个JOIN顺序。

为什么要这样呢?
因为默认情况下,PG是使用穷举法对所有的JOIN排列组合进行成本估算,如果表很多的话,可以想象一下,执行计划要估算很多个PLAN的成本,使得成本计算的开销非常庞大。

PostgreSQL 影响统计信息的几种方法

  • 自动垃圾回收进程
    PostgreSQL的自动垃圾回收进程,除了回收垃圾,还负责收集统计信息。

通过调整阈值,可以控制统计信息的收集调度。

单个垃圾回收进程可以使用的最大内存  
#autovacuum_work_mem      # min 1MB, or -1 to use maintenance_work_mem
# 
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
#
是否打开自动垃圾回收
#autovacuum = on                        # Enable autovacuum subprocess?  'on'
最多允许多少个并行的垃圾回收进程
#autovacuum_max_workers = 3             # max number of autovacuum subprocesses
                                        # (change requires restart)
垃圾回收进程检查完所有数据库的周期,例如有4个数据库,周期为1分钟,则每个数据库每25秒被检查一次是否有需要被垃圾回收的对象。  
数据库不多,并且数据被频繁更新的话,建议把这个值调小到合理范围,例如2秒。  
确保快速的回收垃圾。  
#autovacuum_naptime = 1min              # time between autovacuum runs
自动进行垃圾回收的最少影响行,例如表已经删除了50条记录,则可能触发垃圾回收,(同时还要满足另一个条件#autovacuum_vacuum_scale_factor)
#autovacuum_vacuum_threshold = 50       # min number of row updates before
                                        # vacuum
自动收集统计信息的最少影响行
#autovacuum_analyze_threshold = 50      # min number of row updates before
                                        # analyze
当超过0.2(即20%)的记录发生删除或更新时,同时满足超过#autovacuum_vacuum_threshold ,则触发自动垃圾回收
#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum
同上,只是自动收集统计信息
#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze
# 当自动垃圾回收的进程,在垃圾回收的过程中,所有的垃圾回收进程所产生的开销总和,超过#autovacuum_vacuum_cost_limit时,则所有的垃圾回收进程都会进入SLEEP状态
# #autovacuum_vacuum_cost_delay表示sleep的时间
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                                        # autovacuum, in milliseconds;
                                        # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for
                                        # autovacuum, -1 means use
                                        # vacuum_cost_limit
计算vacuum的开销,系数。
垃圾页在shared buffer中命中
#vacuum_cost_page_hit = 1               # 0-10000 credits
垃圾页未在shared buffer中命中
#vacuum_cost_page_miss = 10             # 0-10000 credits
垃圾页是脏页, 需要先write到磁盘
#vacuum_cost_page_dirty = 20            # 0-10000 credits

很多值是可以表级别设置的。

  • 统计信息收集的细粒度
    统计信息收集的粒度,表示一共产生多少个bucket,越大,越细,但是analyze需要扫描的块也越多。

    #default_statistics_target = 100        # range 1-10000

可以表级别设置

通过调整以上参数,可以使得统计信息够准确,成本计算的因子够准确,解决大多数的执行计划问题。

如果因为SQL过于复杂,已经出发了遗传算法,又或者某些确实是成本估算的算法不够好,导致的执行计划不准确,还有什么方法呢?

又或者你想节约数据库在执行计划优化阶段的CPU开销,有办法么?

如何绑定SQL执行计划

阿里云ApsaraDB for RDS PG,提供了一个插件叫pg_hint_plan,通过这个插件,可以设置SQL HINT,绑定执行计划。
用法可参考
http://pghintplan.osdn.jp/pg_hint_plan.html
http://pghintplan.osdn.jp/hint_list.html
https://yq.aliyun.com/articles/17212
特别注意,pg_hint_plan是用到了hook的,所以一定要调用so中的_PG_init(void)初始化一下hook,

见pg_hint_plan源码  
pg_hint_plan.c

         /* Install hooks. */
         prev_ProcessUtility = ProcessUtility_hook;
         ProcessUtility_hook = pg_hint_plan_ProcessUtility;
         prev_planner = planner_hook;
         planner_hook = pg_hint_plan_planner;
         prev_get_relation_info = get_relation_info_hook;
         get_relation_info_hook = pg_hint_plan_get_relation_info;
         prev_join_search = join_search_hook;
         join_search_hook = pg_hint_plan_join_search;
 
         /* setup PL/pgSQL plugin hook */
         var_ptr = (PLpgSQL_plugin **) find_rendezvous_variable("PLpgSQL_plugin");
         *var_ptr = &plugin_funcs;
 
         RegisterResourceReleaseCallback(plpgsql_query_erase_callback, NULL);

因此用到了 alter role all set session_preload_libraries='pg_hint_plan';
或者 alter role 自己 set session_preload_libraries='pg_hint_plan';
并且会话需要重新连接,以加载hook。

例子

创建extension

=> create extension pg_hint_plan;
CREATE EXTENSION

以下QUERY为例

 Schema |      Name      | Type  | Owner  |  Size   | Description                    
--------+----------------+-------+--------+---------+---------
 public | test01 | table | digoal | 7273 MB | 
 public | test02 | table | digoal | 560 MB  | 
 public | test03 | table | digoal | 368 MB  | 

SELECT xxxx
    FROM
  "test01" AS rd
    INNER JOIN "test02" AS d ON (rd.test02_uuid = d.uuid)
    INNER JOIN "test03" AS ir ON (d.test03_uuid = ir.uuid)
    WHERE
  d.status = 'normal'
    AND ir.u_uuid = 'ttttttttt' and (d.test02_status in ('test02ed','checked')) 
      and d.is_sub = false and d.is_filter = false ORDER BY d.test02_time desc limit 10 offset 0

需要固定为以下执行计划

 Limit  (cost=1204.30..1204.33 rows=10 width=276) (actual time=11.131..11.132 rows=10 loops=1)
   ->  Sort  (cost=1204.30..1204.49 rows=75 width=276) (actual time=11.131..11.131 rows=10 loops=1)
         Sort Key: d.test02_time
         Sort Method: quicksort  Memory: 33kB
         ->  Nested Loop  (cost=1.28..1202.68 rows=75 width=276) (actual time=0.085..11.095 rows=17 loops=1)
               ->  Nested Loop  (cost=0.85..1117.07 rows=62 width=101) (actual time=0.066..10.879 rows=13 loops=1)
                     ->  Index Scan using "abc" on test03 ir  (cost=0.42..14.89 rows=10 width=38) (actual time=0.026..0.166 rows=96 loops=1)
                           Index Cond: ((u_uuid)::text = 'ttttttttt'::text)
                     ->  Index Scan using "bcd" on test02 d  (cost=0.43..109.86 rows=36 width=80) (actual time=0.109..0.111 rows=0 loops=96)
                           Index Cond: ((test03_uuid)::text = (ir.uuid)::text)
                           Filter: ((NOT is_sub) AND (NOT is_filter) AND ((test02_status)::text = ANY ('{test02ed,checked}'::text[])) AND ((status)::text = 'normal'::text))
                           Rows Removed by Filter: 67
               ->  Index Scan using "def" on test01 rd  (cost=0.43..1.32 rows=6 width=192) (actual time=0.014..0.014 rows=1 loops=13)
                     Index Cond: ((test02_uuid)::text = (d.uuid)::text)

分析以上执行计划,需要固定的包括 索引,嵌套循环,以及JOIN的顺序,驱动顺序。

SQL语句改成

/*+ 
  NestLoop(ir d) 
  NestLoop(ir d rd) 
  Leading(((ir d) rd)) 
  IndexScan(rd "def") 
  IndexScan(d "bcd") 
  IndexScan(ir "abc") 
*/ 
SELECT xxxx
    FROM
  "test01" AS rd
    INNER JOIN "test02" AS d ON (rd.test02_uuid = d.uuid)
    INNER JOIN "test03" AS ir ON (d.test03_uuid = ir.uuid)
    WHERE
  d.status = 'normal'
    AND ir.u_uuid = 'ttttttttt' and (d.test02_status in ('test02ed','checked')) 
      and d.is_sub = false and d.is_filter = false ORDER BY d.test02_time desc limit 10 offset 0

pg_hint_plan语法解释

整个query的第一个comment (/+ /) 被pg_hint_plan用来固定执行计划。
如果有多个comment,也只认第一个,请务必注意。

/*+ 
  NestLoop(ir d)  # 表示ir d两个表使用嵌套循环JOIN,如果QUERY用了别名,请使用别名。 

  NestLoop(ir d rd)  # 表示ir与d join完后再与rd JOIN。  
  所以如果要固定JOIN顺序,可以分多个JOIN hint来写,就像上面这样。  

  Leading(((ir d) rd))    # 表示JOIN顺序和驱动顺序,  
  每一对JOIN对象都需要用括号表示,  
  括号内左边的元素为outer、driver表,右边的元素为inner、driven表。  
  (hash 和 nestloop JOIN 请务必注意括号内的别名或表名顺序)。     
  如果QUERY用了别名,请使用别名。    

  IndexScan(rd "def")   #  指定索引,如果表名使用了别名,请使用别名。    

  IndexScan(d "bcd") 

  IndexScan(ir "abc") 

  务必注意,  如果对象名用了 小写和下划线 以外的字符。  必须使用""引用起来。  
*/ 

pg_hint_plan插件支持的所有 hint

上面的例子使用了其中的一部分,其他的请用户自行阅读参考。

Format Description
Scan method
SeqScan(table) Forces sequential scan on the table
TidScan(table) Forces TID scan on the table.
IndexScan(table[ index...]) Forces index scan on the table. Restricts to specified indexes if any.
IndexOnlyScan(table[ index...]) Forces index only scan on the table. Rstricts to specfied indexes if any. Index scan may be used if index only scan is not available. Available for PostgreSQL 9.2 and later.
BitmapScan(table[ index...]) Forces bitmap scan on the table. Restoricts to specfied indexes if any.
NoSeqScan(table) Forces not to do sequential scan on the table.
NoTidScan(table) Forces not to do TID scan on the table.
NoIndexScan(table) Forces not to do index scan and index only scan (For PostgreSQL 9.2 and later) on the table.
NoIndexOnlyScan(table) Forces not to do index only scan on the table. Available for PostgreSQL 9.2 and later.
NoBitmapScan(table) Forces not to do bitmap scan on the table.
Join method
NestLoop(table table[ table...]) Forces nested loop for the joins consist of the specifiled tables.
HashJoin(table table[ table...]) Forces hash join for the joins consist of the specifiled tables.
MergeJoin(table table[ table...]) Forces merge join for the joins consist of the specifiled tables.
NoNestLoop(table table[ table...]) Forces not to do nested loop for the joins consist of the specifiled tables.
NoHashJoin(table table[ table...]) Forces not to do hash join for the joins consist of the specifiled tables.
NoMergeJoin(table table[ table...]) Forces not to do merge join for the joins consist of the specifiled tables.
Join order
Leading(table table[ table...]) Forces join order as specified.
Leading() Forces join order and directions as specified. A join pair is a pair of tables and/or other join pairs enclosed by parentheses, which can make a nested structure.
Row number correction
Rows(table table[ table...] correction) Corrects row number of a result of the joins consist of the specfied tables. The available correction methods are absolute (#), addition (+), subtract (-) and multiplication (*). should be a string that strtod() can read.
GUC
Set(GUC-param value) Set the GUC parameter to the value while planner is running.

举例

postgres=# /*+
postgres*#     Set(random_page_cost 2.0)
postgres*#  */
postgres-# SELECT * FROM table1 t1 WHERE key = 'value';

postgres=# /*+ Rows(a b #10) */ SELECT... ; Sets rows of join result to 10
postgres=# /*+ Rows(a b +10) */ SELECT... ; Increments row number by 10
postgres=# /*+ Rows(a b -10) */ SELECT... ; Subtracts 10 from the row number.
postgres=# /*+ Rows(a b *10) */ SELECT... ; Makes the number 10 times larger.

绑定SQL执行计划的缺点

绑定SQL执行计划可以解决一些问题,但是也会引入一些问题。

SQL执行计划一旦绑定,则会严格按照绑定的执行计划执行。

例如使用了绑定变量的情况,如果SQL的条件发送变化,绑定的执行计划可能不适合变化后的条件。

小结

  1. pg_hint_plan是一种辅助的手段,帮助用户解决因为优化器算法不好,或者统计信息不准确导致的执行计划不准确的问题。
  2. 用户应该尽量的通过校准成本因子,设置好统计信息收集的参数等手段,使得优化器能够更加精准的评估成本,选择合理的执行计划。
  3. pg_hint_plan需要用到PostgreSQL的hook,所以必须预加载的方式调用_PG_init(void)。
    通过修改postgresql.conf的shared_preload_libraries预加载,或者设置会话级自动加载参数,都是可行的。

祝大家玩得开心,欢迎随时来 阿里云促膝长谈 业务需求 ,恭候光临。

阿里云的小伙伴们加油,努力做 最贴地气的云数据库

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
5月前
|
达摩院 开发者 容器
「达摩院MindOpt」优化形状切割问题(MILP)
在制造业,高效地利用材料不仅是节约成本的重要环节,也是可持续发展的关键因素。无论是在金属加工、家具制造还是纺织品生产中,原材料的有效利用都直接影响了整体效率和环境影响。
「达摩院MindOpt」优化形状切割问题(MILP)
|
5月前
|
人工智能 自然语言处理 达摩院
MindOpt 云上建模求解平台:多求解器协同优化
数学规划是一种数学优化方法,主要是寻找变量的取值在特定的约束情况下,使我们的决策目标得到一个最大或者最小值的决策。
|
3月前
|
人工智能 算法 调度
优化问题之如何选择合适的优化求解器
优化问题之如何选择合适的优化求解器
|
3月前
|
调度 决策智能
优化问题之优化求解器有哪些主要的评估特性
优化问题之优化求解器有哪些主要的评估特性
|
达摩院 调度
使用达摩院MindOpt优化交通调度_最大化通行量—线性规划问题
在数学规划中,网络流问题是指一类基于网络模型的流量分配问题。网络流问题的目标是在网络中分配资源,使得网络的流量满足一定的限制条件,并且使得某些目标函数最小或最大化。网络流问题通常涉及一个有向图,图中每个节点表示一个资源,每条边表示资源之间的关系。边上有一个容量值,表示该边上最多可以流动的资源数量。流量从源节点开始流出,经过一系列中间节点,最终到达汇节点。在这个过程中,需要遵守一定的流量守恒和容量限制条件。
|
5月前
|
存储 达摩院 调度
「达摩院MindOpt」优化FlowShop流水线作业排班问题
在企业在面临大量多样化的生产任务时,如何合理地安排流水线作业以提高生产效率及确保交货期成为了一个重要的问题。
「达摩院MindOpt」优化FlowShop流水线作业排班问题
|
11月前
|
API Python
MindOpt V1.0优化种植计划问题,新的建模方法
种植计划是指农业生产中针对不同农作物的种植时间、面积和种植方式等方面的规划安排。根据具体情况进行合理的规划和安排,以实现农作物的高产、优质和可持续发展。
MindOpt V1.0优化种植计划问题,新的建模方法
|
5月前
|
达摩院 自然语言处理 Java
MindOpt APL:一款适合优化问题数学建模的编程语言
本文将以阿里达摩院研发的MindOpt建模语言(MindOpt Algebra Programming Language, MindOptAPL,简称为MAPL)来讲解。MAPL是一种高效且通用的代数建模语言,当前主要用于数学规划问题的建模,并支持调用多种求解器求解。
|
10月前
|
SQL 监控 关系型数据库
Trace分析优化器执行计划与Sys schema视图的使用详解
Trace分析优化器执行计划与Sys schema视图的使用详解
69 0
|
达摩院 供应链 JavaScript
网络流:优化仓储物流调度问题-达摩院MindOpt
仓储物流调度是指在物流供应链中,对仓储和运输(运输路线、成本)进行协调和安排的过程。主要包含物流计划、运输调度、运发管理、库存管理等重要环节。随着网络、电商行业的迅速发展,仓储物流调度对于企业来说也非常重要,优秀的调度方案可以帮助降低库存成本、物流配送的效率、成本等等等,从而给企业带来降本增效。
网络流:优化仓储物流调度问题-达摩院MindOpt
下一篇
无影云桌面