• 一文掌握数仓中auto analyze的使用

    摘要：analyze执行的是否及时，在一定程度上直接决定了SQL执行的快慢。

    本文分享自华为云社区《一文读懂autoanalyze使用【这次高斯不是数学家】》，作者： leapdb。

    analyze执行的是否及时，在一定程度上直接决定了SQL执行的快慢。因此，GaussDB(DWS)引入了自动统计信息收集，可以做到让用户不再担心统计信息是否过期。

    1. 自动收集场景

    需要进行自动统计信息收集的场景通常有五个：批量DML结束时，增量DML结束时，DDL结束时，查询开始时和后台定时任务。

    所以，为了避免对DML，DDL带来不必要的性能开销和死锁风险，我们选择了在查询开始前触发analzye。

    2. 自动收集原理

    GaussDB(DWS)在SQL执行过程中，会记录表增删改查相关的运行时统计信息，并在事务提交或回滚后记录到共享的内存种。

    这些信息可以通过 “pg_stat_all_tables视图” 查询，也可以通过下面函数进行查询。

    1. pg_stat_get_tuples_inserted   --表累积insert条数
    2. pg_stat_get_tuples_updated    --表累积update条数
    3. pg_stat_get_tuples_deleted    --表累积delete条数
    4. pg_stat_get_tuples_changed    --表自上次analyze以来，修改的条数
    5. pg_stat_get_last_analyze_time --查询最近一次analyze时间

    因此，根据共享内存中 "表自上次analyze以来修改过的条数" 是否超过一定阈值，就可以判定是否需要做analyze了。

    3. 自动收集阈值

    3.1 全局阈值

    1. autovacuum_analyze_threshold #表触发analyze的最小修改量
    2. autovacuum_analyze_scale_factor #表触发analyze时的修改百分比

    当"表自上次analyze以来修改的条数" >= autovacuum_analyze_threshold + 表估算大小 * autovacuum_analyze_scale_factor时，需要自动触发analyze。

    3.2 表级阈值

    1. --设置表级阈值
    2. ALTER TABLE item SET (autovacuum_analyze_threshold=50);
    3. ALTER TABLE item SET (autovacuum_analyze_scale_factor=0.1);
    4.  
    5. --查询阈值
    6. postgres=# select pg_options_to_table(reloptions) from pg_class where relname='item';
    7.           pg_options_to_table          
    8. ---------------------------------------
    9.  (autovacuum_analyze_threshold,50)
    10.  (autovacuum_analyze_scale_factor,0.1)
    11. (2 rows)
    12.  
    13. --重置阈值
    14. ALTER TABLE item RESET (autovacuum_analyze_threshold);
    15. ALTER TABLE item RESET (autovacuum_analyze_scale_factor);

    不同表的数据特征不一样，需要触发analyze的阈值可能有不同的需求。表级阈值优先级高于全局阈值。

    3.3 查看表的修改量是否超过了阈值（仅当前CN）

    1. postgres=# select pg_stat_get_local_analyze_status('t_analyze'::regclass);
    2.  pg_stat_get_local_analyze_status 
    3. ----------------------------------
    4.  Analyze not needed
    5. (1 row)

    4. 自动收集方式

    GaussDB(DWS)提供了三种场景下表的自动分析。

    • 当查询中存在“统计信息完全缺失”或“修改量达到analyze阈值”的表，且执行计划不采取FQS (Fast Query Shipping)执行时，则通过autoanalyze控制此场景下表统计信息的自动收集。此时，查询语句会等待统计信息收集成功后，生成更优的执行计划，再执行原查询语句。
    • 当autovacuum设置为on时，系统会定时启动autovacuum线程，对“修改量达到analyze阈值”的表在后台自动进行统计信息收集。

    5.冻结统计信息

    5.1 冻结表的distinct值

    当一个表的distinct总是估算不准，例如：数据扎堆儿重复场景。如果表的distinct值固定，可以通过以下方式冻结表的distinct值。

    1. postgres=# alter table lineitem alter l_orderkey set (n_distinct=0.9);
    2. ALTER TABLE
    3.  
    4. postgres=# select relname,attname,attoptions from pg_attribute a,pg_class c where c.oid=a.attrelid and attname='l_orderkey';
    5.  relname  |  attname   |    attoptions    
    6. ----------+------------+------------------
    7.  lineitem | l_orderkey | {n_distinct=0.9}
    8. (1 row)
    9.  
    10. postgres=# alter table lineitem alter l_orderkey reset (n_distinct);
    11. ALTER TABLE
    12.  
    13. postgres=# select relname,attname,attoptions from pg_attribute a,pg_class c where c.oid=a.attrelid and attname='l_orderkey';
    14.  relname  |  attname   | attoptions 
    15. ----------+------------+------------
    16.  lineitem | l_orderkey | 
    17. (1 row)

    5.2. 冻结表的全部统计信息

    如果表的数据特征基本不变，还可以冻结表的统计信息，来避免重复进行analyze。

    alter table table_name set frozen_stats=true;

    6. 手动查看表是否需要做analyze

    a. 不想在业务高峰期时触发数据库后台任务，所以不愿意打开autovacuum来触发analyze，怎么办？

    b. 业务修改了一批表，想立即对这些表马上做一次analyze，又不知道都有哪些表，怎么办？

    c. 业务高峰来临前想对临近阈值的表都做一次analyze，怎么办？

    我们将autovacuum检查阈值判断是否需要analyze逻辑，抽取成了函数，帮助用户灵活主动的检查哪些表需要做analyze。

    6.1 判断表是否需要analyze（串行版，适用于所有历史版本）

    1. -- the function for get all pg_stat_activity information in all CN of current cluster.
    2. CREATE OR REPLACE FUNCTION pg_catalog.pgxc_stat_table_need_analyze(in table_name text)
    3. RETURNS BOOl
    4. AS $$
    5. DECLARE
    6.     row_data record;
    7.     coor_name record;
    8.     fet_active text;
    9.     fetch_coor text;
    10.     relTuples int4;
    11.     changedTuples int4:= 0;
    12.     rel_anl_threshold int4;
    13.     rel_anl_scale_factor float4;
    14.     sys_anl_threshold int4;
    15.     sys_anl_scale_factor float4;
    16.     anl_threshold int4;
    17.     anl_scale_factor float4;
    18.     need_analyze bool := false;
    19.     BEGIN
    20.         --Get all the node names
    21.         fetch_coor := 'SELECT node_name FROM pgxc_node WHERE node_type=''C''';
    22.         FOR coor_name IN EXECUTE(fetch_coor) LOOP 
    23.             fet_active := 'EXECUTE DIRECT ON (' || coor_name.node_name || ') ''SELECT pg_stat_get_tuples_changed(oid) from pg_class where relname = ''''|| table_name ||'''';''';
    24.             FOR row_data IN EXECUTE(fet_active) LOOP 
    25.                 changedTuples = changedTuples + row_data.pg_stat_get_tuples_changed;
    26.             END LOOP;
    27.         END LOOP;
    28.  
    29.         EXECUTE 'select pg_stat_get_live_tuples(oid) from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into relTuples;
    30.         EXECUTE 'show autovacuum_analyze_threshold;' into sys_anl_threshold;
    31.         EXECUTE 'show autovacuum_analyze_scale_factor;' into sys_anl_scale_factor;
    32.  
    33.         EXECUTE 'select (select option_value from pg_options_to_table(c.reloptions) where option_name = ''autovacuum_analyze_threshold'') as value 
    34.         from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into rel_anl_threshold;
    35.  
    36.         EXECUTE 'select (select option_value from pg_options_to_table(c.reloptions) where option_name = ''autovacuum_analyze_scale_factor'') as value 
    37.         from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into rel_anl_scale_factor;
    38.  
    39.         --dbms_output.put_line('relTuples='||relTuples||'; sys_anl_threshold='||sys_anl_threshold||'; sys_anl_scale_factor='||sys_anl_scale_factor||'; rel_anl_threshold='||rel_anl_threshold||'; rel_anl_scale_factor='||rel_anl_scale_factor||';');
    40.         if rel_anl_threshold IS NOT NULL then
    41.             anl_threshold = rel_anl_threshold;
    42.         else
    43.             anl_threshold = sys_anl_threshold;
    44.         end if;
    45.         if rel_anl_scale_factor IS NOT NULL then
    46.             anl_scale_factor = rel_anl_scale_factor;
    47.         else
    48.             anl_scale_factor = sys_anl_scale_factor;
    49.         end if;
    50.  
    51.         if changedTuples > anl_threshold + anl_scale_factor * relTuples then
    52.             need_analyze := true;
    53.         end if;
    54.  
    55.         return need_analyze;
    56.     END; $$
    57. LANGUAGE 'plpgsql';

    6.2 判断表是否需要analyze（并行版，适用于支持并行执行框架的版本）

    1. -- the function for get all pg_stat_activity information in all CN of current cluster.
    2. --SELECT sum(a) FROM pg_catalog.pgxc_parallel_query('cn', 'SELECT 1::int FROM pg_class LIMIT 10') AS (a int); 利用并发执行框架
    3. CREATE OR REPLACE FUNCTION pg_catalog.pgxc_stat_table_need_analyze(in table_name text)
    4. RETURNS BOOl
    5. AS $$
    6. DECLARE
    7.     relTuples int4;
    8.     changedTuples int4:= 0;
    9.     rel_anl_threshold int4;
    10.     rel_anl_scale_factor float4;
    11.     sys_anl_threshold int4;
    12.     sys_anl_scale_factor float4;
    13.     anl_threshold int4;
    14.     anl_scale_factor float4;
    15.     need_analyze bool := false;
    16.     BEGIN
    17.         --Get all the node names
    18.         EXECUTE 'SELECT sum(a) FROM pg_catalog.pgxc_parallel_query(''cn'', ''SELECT pg_stat_get_tuples_changed(oid)::int4 from pg_class where relname = ''''|| table_name ||'''';'') AS (a int4);' into changedTuples;
    19.         EXECUTE 'select pg_stat_get_live_tuples(oid) from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into relTuples;
    20.  
    21.         EXECUTE 'show autovacuum_analyze_threshold;' into sys_anl_threshold;
    22.         EXECUTE 'show autovacuum_analyze_scale_factor;' into sys_anl_scale_factor;
    23.  
    24.         EXECUTE 'select (select option_value from pg_options_to_table(c.reloptions) where option_name = ''autovacuum_analyze_threshold'') as value 
    25.         from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into rel_anl_threshold;
    26.  
    27.         EXECUTE 'select (select option_value from pg_options_to_table(c.reloptions) where option_name = ''autovacuum_analyze_scale_factor'') as value 
    28.         from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into rel_anl_scale_factor;
    29.  
    30.         dbms_output.put_line('relTuples='||relTuples||'; sys_anl_threshold='||sys_anl_threshold||'; sys_anl_scale_factor='||sys_anl_scale_factor||'; rel_anl_threshold='||rel_anl_threshold||'; rel_anl_scale_factor='||rel_anl_scale_factor||';');
    31.         if rel_anl_threshold IS NOT NULL then
    32.             anl_threshold = rel_anl_threshold;
    33.         else
    34.             anl_threshold = sys_anl_threshold;
    35.         end if;
    36.         if rel_anl_scale_factor IS NOT NULL then
    37.             anl_scale_factor = rel_anl_scale_factor;
    38.         else
    39.             anl_scale_factor = sys_anl_scale_factor;
    40.         end if;
    41.  
    42.         if changedTuples > anl_threshold + anl_scale_factor * relTuples then
    43.             need_analyze := true;
    44.         end if;
    45.  
    46.         return need_analyze;
    47.     END; $$
    48. LANGUAGE 'plpgsql';

    6.3 判断表是否需要analyze（自定义阈值）

    1. -- the function for get all pg_stat_activity information in all CN of current cluster.
    2. CREATE OR REPLACE FUNCTION pg_catalog.pgxc_stat_table_need_analyze(in table_name text, int anl_threshold, float anl_scale_factor)
    3. RETURNS BOOl
    4. AS $$
    5. DECLARE
    6.     relTuples int4;
    7.     changedTuples int4:= 0;
    8.     need_analyze bool := false;
    9.     BEGIN
    10.         --Get all the node names
    11.         EXECUTE 'SELECT sum(a) FROM pg_catalog.pgxc_parallel_query(''cn'', ''SELECT pg_stat_get_tuples_changed(oid)::int4 from pg_class where relname = ''''|| table_name ||'''';'') AS (a int4);' into changedTuples;
    12.         EXECUTE 'select pg_stat_get_live_tuples(oid) from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into relTuples;
    13.  
    14.         if changedTuples > anl_threshold + anl_scale_factor * relTuples then
    15.             need_analyze := true;
    16.         end if;
    17.  
    18.         return need_analyze;
    19.     END; $$
    20. LANGUAGE 'plpgsql';

    通“优化器触发的实时analyze”和“后台autovacuum触发的轮询analyze”，GaussDB(DWS)已经可以做到让用户不再关心表是否需要analyze。建议在最新版本中试用。

    点击关注，第一时间了解华为云新鲜技术~

  • 相关阅读:
    科研人如何做PPT、答辩、演讲
    labelme标注格式的数据集转COCO格式脚本
    VR云游：让游客足不出户享受旅行的乐趣
    电脑商城系统
    VL-Adapter： 针对视觉和语言（Vision-and-Language）的参数高效迁移学习
    函数计算｜如何使用层解决依赖包问题?
    基于 Vue 实现页面中文本的复制功能
    【JAVA】06 封装、继承、多态 总结（初级）
    【IMX6ULL学习笔记之驱动学习02】LED字符设备驱动
    Echarts绘制地图，且可以下钻到省区
  • 原文地址：https://blog.csdn.net/devcloud/article/details/125595831