一文掌握数仓中auto analyze的使用-低调大师

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

2022-07-04 477

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

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

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

1. 自动收集场景

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

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

2. 自动收集原理

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

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

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

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

3. 自动收集阈值

3.1 全局阈值

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

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

3.2 表级阈值

--设置表级阈值
ALTER TABLE item SET (autovacuum_analyze_threshold=50);
ALTER TABLE item SET (autovacuum_analyze_scale_factor=0.1);

--查询阈值
postgres=# select pg_options_to_table(reloptions) from pg_class where relname='item';
          pg_options_to_table          
---------------------------------------
 (autovacuum_analyze_threshold,50)
 (autovacuum_analyze_scale_factor,0.1)
(2 rows)

--重置阈值
ALTER TABLE item RESET (autovacuum_analyze_threshold);
ALTER TABLE item RESET (autovacuum_analyze_scale_factor);

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

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

postgres=# select pg_stat_get_local_analyze_status('t_analyze'::regclass);
 pg_stat_get_local_analyze_status 
----------------------------------
 Analyze not needed
(1 row)

4. 自动收集方式

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

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

5.冻结统计信息

5.1 冻结表的distinct值

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

postgres=# alter table lineitem alter l_orderkey set (n_distinct=0.9);
ALTER TABLE

postgres=# select relname,attname,attoptions from pg_attribute a,pg_class c where c.oid=a.attrelid and attname='l_orderkey';
 relname  |  attname   |    attoptions    
----------+------------+------------------
 lineitem | l_orderkey | {n_distinct=0.9}
(1 row)

postgres=# alter table lineitem alter l_orderkey reset (n_distinct);
ALTER TABLE

postgres=# select relname,attname,attoptions from pg_attribute a,pg_class c where c.oid=a.attrelid and attname='l_orderkey';
 relname  |  attname   | attoptions 
----------+------------+------------
 lineitem | l_orderkey | 
(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（串行版，适用于所有历史版本）

-- the function for get all pg_stat_activity information in all CN of current cluster.
CREATE OR REPLACE FUNCTION pg_catalog.pgxc_stat_table_need_analyze(in table_name text)
RETURNS BOOl
AS $$
DECLARE
    row_data record;
    coor_name record;
    fet_active text;
    fetch_coor text;
    relTuples int4;
    changedTuples int4:= 0;
    rel_anl_threshold int4;
    rel_anl_scale_factor float4;
    sys_anl_threshold int4;
    sys_anl_scale_factor float4;
    anl_threshold int4;
    anl_scale_factor float4;
    need_analyze bool := false;
    BEGIN
        --Get all the node names
        fetch_coor := 'SELECT node_name FROM pgxc_node WHERE node_type=''C''';
        FOR coor_name IN EXECUTE(fetch_coor) LOOP 
            fet_active := 'EXECUTE DIRECT ON (' || coor_name.node_name || ') ''SELECT pg_stat_get_tuples_changed(oid) from pg_class where relname = ''''|| table_name ||'''';''';
            FOR row_data IN EXECUTE(fet_active) LOOP 
                changedTuples = changedTuples + row_data.pg_stat_get_tuples_changed;
            END LOOP;
        END LOOP;

        EXECUTE 'select pg_stat_get_live_tuples(oid) from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into relTuples;
        EXECUTE 'show autovacuum_analyze_threshold;' into sys_anl_threshold;
        EXECUTE 'show autovacuum_analyze_scale_factor;' into sys_anl_scale_factor;

        EXECUTE 'select (select option_value from pg_options_to_table(c.reloptions) where option_name = ''autovacuum_analyze_threshold'') as value 
        from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into rel_anl_threshold;

        EXECUTE 'select (select option_value from pg_options_to_table(c.reloptions) where option_name = ''autovacuum_analyze_scale_factor'') as value 
        from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into rel_anl_scale_factor;
 
        --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||';');
        if rel_anl_threshold IS NOT NULL then
            anl_threshold = rel_anl_threshold;
        else
            anl_threshold = sys_anl_threshold;
        end if;
        if rel_anl_scale_factor IS NOT NULL then
            anl_scale_factor = rel_anl_scale_factor;
        else
            anl_scale_factor = sys_anl_scale_factor;
        end if;

        if changedTuples > anl_threshold + anl_scale_factor * relTuples then
            need_analyze := true;
        end if;

        return need_analyze;
    END; $$
LANGUAGE 'plpgsql';

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

-- the function for get all pg_stat_activity information in all CN of current cluster.
--SELECT sum(a) FROM pg_catalog.pgxc_parallel_query('cn', 'SELECT 1::int FROM pg_class LIMIT 10') AS (a int); 利用并发执行框架
CREATE OR REPLACE FUNCTION pg_catalog.pgxc_stat_table_need_analyze(in table_name text)
RETURNS BOOl
AS $$
DECLARE
    relTuples int4;
    changedTuples int4:= 0;
    rel_anl_threshold int4;
    rel_anl_scale_factor float4;
    sys_anl_threshold int4;
    sys_anl_scale_factor float4;
    anl_threshold int4;
    anl_scale_factor float4;
    need_analyze bool := false;
    BEGIN
        --Get all the node names
        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;
        EXECUTE 'select pg_stat_get_live_tuples(oid) from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into relTuples;

        EXECUTE 'show autovacuum_analyze_threshold;' into sys_anl_threshold;
        EXECUTE 'show autovacuum_analyze_scale_factor;' into sys_anl_scale_factor;

        EXECUTE 'select (select option_value from pg_options_to_table(c.reloptions) where option_name = ''autovacuum_analyze_threshold'') as value 
        from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into rel_anl_threshold;

        EXECUTE 'select (select option_value from pg_options_to_table(c.reloptions) where option_name = ''autovacuum_analyze_scale_factor'') as value 
        from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into rel_anl_scale_factor;
 
        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||';');
        if rel_anl_threshold IS NOT NULL then
            anl_threshold = rel_anl_threshold;
        else
            anl_threshold = sys_anl_threshold;
        end if;
        if rel_anl_scale_factor IS NOT NULL then
            anl_scale_factor = rel_anl_scale_factor;
        else
            anl_scale_factor = sys_anl_scale_factor;
        end if;

        if changedTuples > anl_threshold + anl_scale_factor * relTuples then
            need_analyze := true;
        end if;

        return need_analyze;
    END; $$
LANGUAGE 'plpgsql';

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

-- the function for get all pg_stat_activity information in all CN of current cluster.
CREATE OR REPLACE FUNCTION pg_catalog.pgxc_stat_table_need_analyze(in table_name text, int anl_threshold, float anl_scale_factor)
RETURNS BOOl
AS $$
DECLARE
    relTuples int4;
    changedTuples int4:= 0;
    need_analyze bool := false;
    BEGIN
        --Get all the node names
        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;
        EXECUTE 'select pg_stat_get_live_tuples(oid) from pg_class c where c.oid = '''|| table_name ||'''::REGCLASS;' into relTuples;

        if changedTuples > anl_threshold + anl_scale_factor * relTuples then
            need_analyze := true;
        end if;

        return need_analyze;
    END; $$
LANGUAGE 'plpgsql';

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

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

微信关注我们

原文链接：https://my.oschina.net/u/4526289/blog/5548794

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

LiteFlow 里程碑版本 2.8.0 发版注记！全新的 DSL 会惊艳到你吗？

一 New version! 2.8.0 go!!! 这是我在提交中央仓库前写下的简短description。我希望这个版本能把LiteFlow带向更远的地方。曾经在半年前就计划的新的DSL计划，但是因为底层还不完善，花了半年时间几乎重写了整个底层慢慢迭代，在今天终于完成全新DSL！ LiteFlow今天正式推出里程碑版本2.8.0！正式迈入了2.8.X系列。这次LiteFlow带来了全新设计的规则表达式，带来质的飞跃！任何复杂的DAG图用LiteFlow去编排都已经是轻而易举的事了。并且2.8.0在规则层面是向下兼容的。如果你在用之前的版本，只需很小的代价便可切换到2.8.X中。在官网中也提供了升级版本的说明文档。如果你是第一次见到LiteFlow，那么我可以给你这么形容： LiteFlow是一个灵动的，高成长性的，社区驱动的，丝滑且正在变得越来越好用的国产开源规则引擎。你如果对LiteFlow感兴趣的话，请移步官网进行了解：官网：https://liteflow.yomahub.com/ Gitee托管仓库：https://gitee.com/dromara/liteFl...

2022-07-04

478

开源中国DevOps社区D-Day系列第三次直播重磅来袭！！！纵观行业发展，自动化软件开发运动已然来临，DevOps 平台也正在集成各类自动化工具，尽可能减少客户的工作量，驱动行业创新，提供一站式需求解决服务，这些工具让DevOps 得以从一个概念慢慢变成现实。开源中国DevOps社区 D-Day系列直播第三期，我们将以自动化工具为主角，介绍借助自动化工具落地DevOps的挑战和方法，以及低代码技术与DevOps的新探索。直播主题如何借助自动化工具落地DevOps 直播时间 2022年7月13日20：00-21：00 内容亮点 DevOps与传统迭代对比 -- 介绍传统迭代方式的弊端 -- 介绍DevOps迭代方式和优点如何借助自动化工具落地DevOps -- 常用自动化工具介绍 -- 企业落地DevOps面临常见挑战 -- 如何把控落地DevOps关键环节低代码技术与DevOps的新探索讲师介绍姓名：王海虎职位：云智慧前端技术团队负责人简介：王海虎，AlOps 社区专家团成员。全栈工程师，低代码方向。开源项目 AntV 贡献者，开源项目 FlyFis...

2022-07-04

471

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。