ANALYZE和AUTO ANALYZE

本文将为您介绍如何使用Analyze命令,以及更加简单的Auto Analyze的相关机制。

Analyze

统计信息决定是否能够生成正确的执行计划。Hologres需要收集数据的采样统计信息,包括数据的分布和特征、表的统计信息、列的统计信息、行数、列数、字段宽度、基数、频度、最大值、最小值、高频值、分桶分布特征等信息。这些信息将为优化器更新算子执行预估COST、搜索空间裁剪、估算最优JOIN ORDER、估算内存开销、估算并行度,从而生成更优的执行计划。

Analyze命令用于收集数据库中表内容的统计信息,优化器会根据这些统计信息生成最佳的查询计划,从而提高查询效率。

  • 使用语法

    -- 更新某个表的统计信息,默认会收集表中所有列的统计信息
    analyze <tablename>;
    
    -- 更新某个列的统计信息,会比更新表时采样的数据更多,更精准,主要用于更新管理条件的列
    analyze <tablename>(<colname>, <colname>);
  • 参数说明

    tablename为更新统计信息的表名称,colname为更新统计信息的列名称。

  • 语法说明

    两个Analyze命令的说明如下。

    • 相同点

      • 对列统一收集包括行数、列宽、列的最常用值(Most Common Values)、列的直方图(Histogram)信息,列的非重复值的个数(Number of Distinct Value,NDV)在内的信息。

      • 两个命令都会相互覆盖指定列的统计信息,但不会覆盖其他列的信息。例如analyze <tablename>(<colname1>); 命令会覆盖(更新)之前colname1列收集的统计信息,但并不会改变colname2列的统计信息。

    • 不同点

      • analyze <tablename>;基于采样数据,计算得出统计信息。

      • analyze <tablename>(<colname>, <colname>);会对列的Number of Distinct Value(NDV)进行APPROX_COUNT_DISTINCT计算,在很多情况下,这样计算的值相比采样更准确,但开销比采样表更大,因此只适合对重点列进行指定ANALYZE。NDV以外的Histogram、Width等信息,仍然通过采样得到。

      因此对于具有两列的table (colname1, colname2)analyze table;不完全等价于analyze table(colname1, colname2);

      对于常用的Join列、Group By列,推荐使用analyze <tablename>(<colname>, <colname>);命令进行额外的统计信息收集。

  • 需要执行Analyze的情况

    推荐您在如下情况下运行analyze <tablename>;命令。

    • 在表执行大量的INSERT、UPDATE以及DELETE操作之后,包括导入数据。

    • 在性能下降的情况下,多表Join查询之前,对Join的列、Group by的列进行Analyze。

    • 执行CREATE FOREIGN TABLE命令后,通过Analyze收集当前外部表统计信息。

    • 执行IMPORT FOREIGN SCHEMA后,对后续需要查询的表进行Analyze。

  • 注意事项

    • 在Hologres V0.10和V1.1版本中,如果有对父表的查询,需要Analyze分区父表;如果直接对子表查询,请对子表Analyze;如果两者都有,建议两者都进行Analyze,否则可能会有缺失统计信息的情况。

    • 如果遇到以下问题,您需要先执行Analyze,再运行导入任务,可以系统地提升效率。

      • 多表JOIN超出内存OOM:通常会产生Query executor exceeded total memory limitation xxxxx: yyyy bytes used报错。

      • 导入效率较低:在Hologres查询或导入数据时,效率较低,运行的任务长时间不结束。

    • 如果有超宽列(例如Bitmap等Bytea数据,超过1KB的Text数据等),这些超宽列的统计信息没有作用,还会使采样更消耗内存。因此对于具有上述超宽列的表,尽量避免执行analyze <tablename>;命令,而是采用analyze <tablename>(<colname>, <colname>);避开超宽列,转为Analyze必要的列(例如上面推荐的Join的列、Group by的列和Filter列等)。

      说明

      1KB是经验值,宽度标准可以根据业务情况自行决定。

Auto Analyze

为了减少重复、手动的Analyze,从Hologres V0.10版本开始,支持Auto Analyze机制。开启auto analyze后,系统会根据用户的建表、数据写入和修改情况等来判断是否需要对相关的表在后台自动Analyze,无需再手动对表进行Analyze,降低操作复杂度,同时减少遗漏Analyze而导致缺失统计信息的情况。

  • 使用语法

    • 查看是否开启Auto Analyze

      SHOW hg_enable_start_auto_analyze_worker;  -- V1.1及以上版本语法,查看当前开启/关闭状态
      
      SHOW hg_experimental_enable_start_auto_analyze_worker;  -- V0.10语法,查看当前开启/关闭状态
    • 开启/关闭语法如下,需要Superuser执行。

      -- DB级别,执行后整个DB生效,V1.1及以上版本开启/关闭语法
      ALTER DATABASE dbname SET hg_enable_start_auto_analyze_worker = ON;  -- 开启(默认)
      ALTER DATABASE dbname SET hg_enable_start_auto_analyze_worker = OFF; -- 关闭
      
      -- DB级别,执行后整个DB生效,V0.10开启/关闭语法
      ALTER DATABASE dbname SET hg_experimental_enable_start_auto_analyze_worker = ON;  -- 开启(默认)
      ALTER DATABASE dbname SET hg_experimental_enable_start_auto_analyze_worker = OFF; -- 关闭
  • 使用限制

    在Hologres中使用Auto Analyze,具体限制如下:

    • Auto Analyze功能仅Hologres V0.10及以上版本支持,请在Hologres管理控制台的实例详情页查看当前版本,如果您的实例是V0.10以下版本,请您使用自助升级或加入Hologres钉钉交流群反馈,详情请参见如何获取更多的在线支持?

    • 仅支持Superuser执行开启或关闭Auto Analyze操作。

    • Auto Analyze对分区表的限制如下。

      • 分区子表发生改变,需要Auto Analyze时,会统一Analyze其父表。

      • 分区表有扫描行数限制,采样数据时默认扫描的最大记录数是224条(16,777,216条),即若所有分区子表的记录数总和超过16,777,216条,会做一定的分区裁剪,只对其中若干分区(总和不超过16,777,216条)进行采样。

        说明

        分区列统计信息总是全的,不受裁剪影响,但是这可能会影响与分区列同分布的列(例如极端情况是,与分区列数据一样的列)的统计信息,即一部分值采样不到,行数估计可能不准确。如果有需求可以搜索(钉钉群号:32314975)加入实时数仓Hologres交流群联系技术支持,技术侧根据实例情况评估调整扫描的最大记录数。

    • Auto Analyze默认最大收集256列的统计信息,如表超过256列,取前256列。可通过调整hg_experimental_auto_analyze_max_columns_count改变此值。

    • Auto Analyze默认单个Worker限制的内存是4 GB,如果存在超宽的列,采样可能超出内存而导致Analyze失败。可调整auto_analyze_work_memory_mb参数改变其大小,但是要注意对系统内存的影响。实例规格越大,Worker数越多,Auto Analyze可用内存限制越大。

  • Auto Analyze工作原理

    当开始Auto Analyze后,系统后台会定期巡检是否有表需要执行Analyze。

    • 普通表(内部表,包括单表和分区表)

      • 每隔1分钟巡检是否有表的最新动作(主要是INSERT、UPDATE、DELETE等DML操作,可能改变了数据量)。满足以下条件,系统后台触发表的Analyze,收集表的统计信息。

        • 表有DML执行完成且数据条数变化超过当前表的数据条数的10%。若表是分区子表,则是指变化条数超过此分区数据条数的10%。

        • TRUNCATE TABLE清空表。

        • 表的DDL发生变更。例如CREATE TABLE新建表,ALTER TABLE修改表结构等,不包括CALL SET_TABLE_PROPERTY修改表属性。

      • 每隔10分钟检测所有内部表的数据变化,如果满足数据条数变化超过上一次检测的10%,则后台触发该表的Analyze。

        说明

        这一步骤是为了检测到非显式DML(例如通过Flink、数据集成、HoloClient实时写入)更新的数据。

    • 外部表

      • 当前仅支持Analyze MaxCompute外部表,其他引擎的外部表暂不支持Analyze和Auto Analyze。

      • 每隔4小时定期巡检外部表元数据或数据变化情况。满足以下条件,系统后台触发表的Analyze,收集统计信息。

        外部表对应的外部系统的表(例如MaxCompute表)在两次巡检间隔(例如4小时内)改变过,改变的标准是对应MaxCompute表的last_modify_time处于巡检间隔之间。

    说明

    巡检和执行在同一个调度任务中,所以下一次巡检调度开始依赖Analyze执行结束,只要离上一次开始巡检的时间满足调度周期,就可以进入下一次巡检。

  • 配置参数

    开启Auto Analyze后,系统默认会自动周期性巡检,决定需要执行Analyze的表,并进行采样计算,收集统计信息,对系统资源有一定的消耗。

    在某些业务场景下,默认的机制可能不适用于业务场景,例如数据写入更新不频繁场景,可以通过修改默认参数来减少自动Analyze的频率。诸如此类可以根据业务情况更改默认参数,以此达到部分性能调优的目的。

    说明

    只有Superuser能调整Auto Analyze的默认行为,且都需要数据库级别设置参数,且在下一分钟后生效。

    • 使用语法

      --Superuser修改Auto Analyze参数的默认值
      ALTER DATABASE <dbname> SET <GUC>=<values>;

      dbname为数据库名称;GUC为参数名称;values为参数值。

    • 参数列表

      参数

      参数描述

      支持版本

      默认值

      使用示例

      autovacuum_naptime

      巡检是否有表的最新动作的周期,单位是秒(s)。

      V1.1.0及以上版本

      说明

      需后台调整,如需调整请搜索(钉钉群号:32314975)加入实时数仓Hologres交流群申请。

      60s

      • ALTER DATABASE <dbname> SET autovacuum_naptime = 60;

      • ALTER DATABASE <dbname> SET autovacuum_naptime = '60s';

      • ALTER DATABASE <dbname> SET autovacuum_naptime = '10min';

      hg_auto_check_table_changes_interval

      检查所有内部表的数据变化的周期,单位是秒(s)。

      V1.1.0及以上版本

      600s(10min)

      --V1.1及以上版本命令语法
      ALTER DATABASE <dbname> SET hg_auto_check_table_changes_interval = '600s';
      --V0.10版本命令语法
      ALTER DATABASE <dbname> SET hg_experimental_auto_check_table_changes_interval = '600s';

      hg_auto_check_foreign_table_changes_interval

      检查所有外部表的数据变化的周期,单位是秒(s)。

      V1.1.0及以上版本

      14400s(4小时)

      --V1.1及以上版本命令语法
      ALTER DATABASE <dbname> SET hg_auto_check_foreign_table_changes_interval = '14400s';
      --V0.10版本命令语法
      ALTER DATABASE <dbname> SET hg_experimental_auto_check_foreign_table_changes_interval = '14400s';

      hg_experimental_auto_analyze_max_columns_count

      自动收集统计信息的列数,单位是个。

      V1.1.0及以上版本

      256个

      ALTER DATABASE <dbname> SET hg_experimental_auto_analyze_max_columns_count =300;

      auto_analyze_work_memory_mb

      Auto Analyze单个表的内存限制,单位是MB。

      V1.1.54及以上版本

      默认单个Worker 4096 MB,即4GB,实例规格越大,Worker越多,真实内存限制越大。

      Auto Analyze单个表的内存限制修改为9GB。

      ALTER DATABASE <dbname> SETauto_analyze_work_memory_mb =9216;

      hg_experimental_auto_analyze_start_time

      Auto-Analyze在每天运行的开始时间

      说明

      需要与end_time是同一时区,并且start time要小于等于end_time

      V1.1.54及以上版本

      00:00 +0800

      修改为仅需要在0~6点执行Auto-Analyze,白天内外部表数据不变,无需Analyze的情况。

      • ALTER DATABASE <dbname> SET hg_experimental_auto_analyze_start_time = '00:00 +0800';
      • ALTER DATABASE <dbname> SET hg_experimental_auto_analyze_end_time = '06:00 +0800';

      hg_experimental_auto_analyze_end_time

      Auto Analyze在每天运行的结束时间。

      V1.1.54及以上版本

      23:59 +0800

      autovacuum_enabled

      表Auto Analyze的开启状态。

      V1.1.54及以上版本

      true,即默认全部开启。

      关闭某表的Auto Analyze,以后Analyze将跳过此表。

      说明

      仅支持使用如下命令为Hologres内部表关闭Auto Analyze。

      ALTER TABLE <tablename> SET (autovacuum_enabled = false);

查询统计信息

表的统计信息被记录在hologres_statistic.hg_table_statistic表中,可以通过检查该表信息了解Analyze的状态,命令如下。

说明

如果需要查最近一次Analyze的信息,根据analyze_timestamp排序即可。

SELECT schema_name,                -- 表的Schema
       table_name,                 -- 表名称
       schema_version,             -- 表的版本
       statistic_version,          -- 最近一次ANALYZE的统计信息版本
       total_rows,                 -- 最近一次ANALYZE的行数
       analyze_timestamp           -- 最近一次ANALYZE的结束时间
FROM   hologres_statistic.hg_table_statistic
WHERE  table_name = '<tablename>'
ORDER BY analyze_timestamp DESC;
  • 每个表在hologres_statistic.hg_table_statistic表中有0~n条记录。0条表示从未进行过Analyze,1条及以上表示运行过Analyze。

  • 若出现两条及以上的情况,两条记录的schema_version一定不一样,因为表的Schema变化了(例如执行ADD COLUMN等命令会产生新的版本),会增加一条统计信息记录,老的schema_version对应的记录不再被使用。

  • 示例查询结果如下,同一个表有两条记录,而第二条记录的schema_version低于第一条,那么第二条将作废,不会被使用,也无需关注。

     schema_name |    table_name    | schema_version | statistic_version | total_rows |  analyze_timestamp
    -------------+------------------+----------------+-------------------+------------+---------------------
     public      | tbl_name_example |             13 |              8580 |      10002 | 2022-04-29 16:03:18
     public      | tbl_name_example |             10 |              8576 |      10002 | 2022-04-29 15:41:20
    (2 rows)
  • Hologres V0.10和V1.1版本暂不会清理hg_table_statistic表中的历史过期记录,同时不用关心老的数据。

查看缺失统计信息的表

通过HG_STATS_MISSING视图,可以查看当前数据库中缺失统计信息的表,详情请参见HG_STATS_MISSING View

常见问题

出现如下情况,代表Auto Analyze工作未正常,请参照解决方法进行处理。

  • 表的统计信息是0条

    • 问题现象:通过hologres_statistic.hg_table_statistic表查看表的统计信息,没有数据。

    • 可能原因:

      • Auto Analyze没有工作,或者该表不符合Auto Analyze触发条件。

      • Auto Analyze本身的问题导致,需要搜索(钉钉群号:32314975)加入实时数仓Hologres交流群询问专业人员具体排查原因。

    • 解决方法:手动触发一次Analyze。

  • analyze_timestamp过小

    • 问题现象:查询结果中analyze_timestamp过小(即比当前时间小很多),代表长时间没有进行过Analyze。

    • 可能原因:

      • 某种原因未能正常执行Auto Analyze。

      • 手动关闭过Auto Analyze。

    • 解决方法:先手动触发Analyze,再搜索(钉钉群号:32314975)加入实时数仓Hologres交流群询问专业人员排查原因。