Greenplum 统计信息收集参数 - 暨统计信息不准引入的broadcast motion一例

3 minute read

背景

数据库执行计划的好坏,与数据库的SQL优化器息息相关。Greenplum有两套优化器,legacy query optimizer 与 ORCA。

这两个优化器都是CBO优化器,都需要依赖统计信息,如果统计信息不准确,可能生成的执行计划就不准确。

例如我们有一个这样的QUERY,发现怎么跑都跑不出来。

观察执行计划,发现有一个节点用到了broadcast motion,也就是广播。

->  Broadcast Motion 512:512  (slice1; segments: 512)  (cost=0.00..6.13 rows=1 width=16)  
  ->  Append-only Columnar Scan on xxxx  (cost=0.00..1.00 rows=1 width=16)  

当JOIN字段并非分布键时,Greenplum会根据表的大小,选择重分布或广播。(小表广播,大表的话多阶段JOIN)。

《HybridDB PostgreSQL “Sort、Group、distinct 聚合、JOIN” 不惧怕数据倾斜的黑科技和原理 - 多阶段聚合》

而这个执行计划跑偏的SQL,恰恰是在一个大表上触发了广播(broadcast motion),这不正常。

select count(*) xxxx;  
  
返回有31亿数据。  

查询pg_class,值有1条记录,占用0个BLOCK

select * from pg_class where relname='xxxx';  
  
relpages       | 1  
reltuples      | 0  

执行analyze,收集统计信息,执行计划恢复。

(Greenplum对于超级大表,收集统计信息时,会构建临时表来进行采样分析)

digoal=> analyze verbose xxxxxxxx;  
  
INFO:  Executing SQL: select sum(gp_statistics_estimate_reltuples_relpages_oid(c.oid))::float4[] from gp_dist_random('pg_class') c where c.oid=112293  
INFO:  ANALYZE estimated reltuples=3091824896.000000, relpages=47509120.000000 for table xxxxxxxx  
INFO:  ANALYZE building sample table of size 173762 on table xxxxxxxx because it has too many rows.  
INFO:  Executing SQL: create table pg_temp.pg_analyze_112293_59 as (  select Ta.xx,....Ta.xxx  from public.xxxxxxxx as Ta where random() < 0.00005620053343591280281543731689453125 limit 173762  ) distributed randomly  
INFO:  Created sample table pg_temp.pg_analyze_112293_59 with nrows=173762  
INFO:  ANALYZE computing statistics on attribute xx  
INFO:  Executing SQL: select count(*)::float4 from pg_temp_440803.pg_analyze_112293_59 as Ta where Ta.xx is null  
INFO:  nullfrac = 0.178474  
INFO:  Executing SQL: select avg(pg_column_size(Ta.xx))::float4 from pg_temp_440803.pg_analyze_112293_59 as Ta where Ta.xx is not null  
INFO:  avgwidth = 21.418087  
INFO:  Executing SQL: select count(*)::float4 from (select Ta.xx from pg_temp_440803.pg_analyze_112293_59 as Ta group by Ta.xx) as Tb  
INFO:  count(ndistinct()) gives 142751.000000 values.  
INFO:  Executing SQL: select count(v)::float4 from (select Ta.xx as v, count(Ta.xx) as f from pg_temp_440803.pg_analyze_112293_59 as Ta group by Ta.xx) as foo where f > 1  
INFO:  ndistinct = -1.000000  
..........  

收集统计信息后,执行计划恢复,没有broadcast了,执行也秒级返回了。

让Greenplum自动收集统计信息

参数:gp_autostats_mode

注意,对于在函数内 或 函数外执行DML时,内核会跟踪表的记录数变更影响的数据量,我们可以设置什么时候收集统计信息:

none:不收集

on_no_stats:没有统计信息时,收集

on_change:当写入、更新量超过阈值(gp_autostats_on_change_threshold参数设置的行数,默认为20亿)后,自动收集统计信息。

Automatic Statistics Collection  
  
Greenplum Database can be set to automatically run ANALYZE on a table that either has no statistics or has  
changed significantly when certain operations are performed on the table. For partitioned tables, automatic  
statistics collection is only triggered when the operation is run directly on a leaf table, and then only the leaf  
table is analyzed.  
  
Automatic statistics collection has three modes:  
• none disables automatic statistics collection.  
• on_no_stats triggers an analyze operation for a table with no existing statistics when any of the  
commands CREATE TABLE AS SELECT, INSERT, or COPY are executed on the table.  
• on_change triggers an analyze operation when any of the commands CREATE TABLE AS SELECT,  
UPDATE, DELETE, INSERT, or COPY are executed on the table and the number of rows affected exceeds  
the threshold defined by the gp_autostats_on_change_threshold configuration parameter.  
  
The automatic statistics collection mode is set separately for commands that occur within a procedural  
language function and commands that execute outside of a function:  
• The gp_autostats_mode configuration parameter controls automatic statistics collection behavior  
outside of functions and is set to on_no_stats by default.  
• The gp_autostats_mode_in_functions parameter controls the behavior when table operations are  
performed within a procedural language function and is set to none by default.  
  
With the on_change mode, ANALYZE is triggered only if the number of rows affected exceeds the threshold  
defined by the gp_autostats_on_change_threshold configuration parameter. The default value for this  
parameter is a very high value, 2147483647, which effectively disables automatic statistics collection;  
you must set the threshold to a lower number to enable it. The on_change mode could trigger large,  
unexpected analyze operations that could disrupt the system, so it is not recommended to set it globally. It  
could be useful in a session, for example to automatically analyze a table following a load.  
  
To disable automatic statistics collection outside of functions, set the gp_autostats_mode parameter to  
none:  
  
gpconfigure -c gp_autostats_mode -v none  
  
To enable automatic statistics collection in functions for tables that have no statistics, change  
gp_autostats_mode_in_functions to on_no_stats:  
  
gpconfigure -c gp_autostats_mode_in_functions -v on_no_stats  
  
Set the log_autostats system configuration parameter to on if you want to log automatic statistics  
collection operations.  

为了让Greenplum数据库产生准确的执行计划,建议要么用户自己调度analyZE收集统计信息,要么配置gp_autostats_mode自动收集。

同样的Greenplum数据库用户,建议垃圾清理也最好业务上调度来清理,《Greenplum 清理垃圾、修改存储模式(行列变换) 平滑方法 - 交换数据、交互分区》

而对于PostgreSQL数据库用户来说,只要配置了autovacuum即可,不需要再业务上调度(包括清理垃圾和ANALYZE都是自动的)。

Flag Counter

digoal’s 大量PostgreSQL文章入口