使用异步与同步模式自动收集统计信息-云原生数据仓库AnalyticDB-阿里云

及时准确地统计信息是查询优化器生成高效查询计划的必要条件。云原生数据仓库 AnalyticDB PostgreSQL 版提供了自动收集统计信息的功能（AUTO ANALYZE）。该功能会根据某张表插入或更新数据的比例自动触发ANALYZE收集统计信息。现支持异步自动收集统计信息和同步自动收集统计信息两种方式。本文介绍两种自动收集统计信息的工作机制和使用方法。

版本限制

云原生数据仓库 AnalyticDB PostgreSQL 版内核小版本为v1.0.5.0及以上。

说明

您可以在控制台实例的基本信息页查看内核小版本。如不满足上述版本要求，需要您升级内核小版本。

异步自动收集统计信息

工作机制

异步自动收集统计信息会定期检查存在大量数据插入、更新或删除的表。当满足以下触发规则时，将异步自动执行ANALYZE命令。

触发规则：数据变更行数 > 表总行数 * 触发比例阈值 + 触发行数阈值。

数据变更行数：该信息在最后一次收集统计信息执行ANALYZE后会清零，之后表中有插入、更新、删除数据时会重新统计该信息。
表总行数：表的总行数为该表上次执行ANALYZE时统计的行数。
触发比例阈值：由autovacuum_analyze_scale_factor参数控制，默认为10%。
触发行数阈值：由autovacuum_analyze_threshold参数控制，默认为50行。

即默认情况下：当某张表最后一次收集统计信息后，再次变更约10%的数据时，会自动执行ANALYZE收集统计信息。

锁冲突

异步自动收集统计信息持有表的四级锁。在异步自动收集统计信息工作期间，如果业务需要持有的锁与AUTO ANALYZE需要获取的锁冲突时，AUTO ANALYZE进程会主动退让锁，不影响业务执行。

分区表

对于分区表，异步自动收集统计信息会按照子分区触发收集，之后会将子分区的统计信息合并到父分区。

查询当前进程

异步自动收集统计信息进程由Auto Vacuum调度。

在AnalyticDB PostgreSQL 6.0版中，使用如下SQL语句查看正在工作的Auto Vacuum进程。
```
SELECT * FROM  pg_stat_activity WHERE query LIKE 'autovacuum%';
```
在AnalyticDB PostgreSQL 7.0版中，使用如下SQL语句查看正在工作的Auto Vacuum进程。
```
SELECT * FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';
```

查询当前需要执行ANALYZE的表。

下面SQL语句中，autovacuum_analyze_scale_factor的参数值为10%，autovacuum_analyze_threshold的参数值为50。

SELECT schemaname, c.relname, c.reltuples, n_mod_since_analyze, last_autoanalyze, (n_mod_since_analyze::float / c.reltuples::float) AS analyze_ratio FROM pg_stat_all_tables s, pg_class c WHERE c.oid = s.relid AND c.reltuples > 50  AND (n_mod_since_analyze::float >  50 + c.reltuples::float * 0.1) ORDER BY (n_mod_since_analyze::float/ c.reltuples::float) DESC;

实例级别阈值说明

阈值名称	描述	默认值
autovacuum_enabled	是否开启ANALYZE功能。	TRUE
autovacuum_naptime	AUTO ANALYZE检查周期。单位：分钟。	1
autovacuum_analyze_scale_factor	AUTO ANALYZE触发的比例阈值。该数值越大，表示表中变更更多数据时才会触发异步自动收集统计信息。	10%
autovacuum_analyze_threshold	AUTO ANALYZE触发的行数阈值。该数值越大，表示表中数据插入、更新或删除的行数越多，会触发异步自动收集统计信息。单位：行。	50
autovacuum_max_execute_workers	AUTO ANALYZE的执行并发数。	max(3,cpucores/2) cpucores为当前实例的单计算节点CPU核数。

说明

通常不建议调整实例级别的AUTO ANALYZE参数。如果需要调整某个参数阈值，可以提交工单联系工作人员调整。

表级别阈值说明

阈值名称	描述
autovacuum_analyze_scale_factor	AUTO ANALYZE触发的比例阈值。该数值越大，表示表中数据插入、更新或删除的比例越高，会触发异步自动收集统计信息。
autovacuum_analyze_threshold	AUTO ANALYZE触发的行数阈值。该数值越大，表示表中数据插入、更新或删除的行数越多，会触发异步自动收集统计信息。单位：行。

设置阈值

语法

ALTER TABLE <test_table> SET (<autoanalyze_options>=<expect_value>)

参数说明

<test_table>：需要调整的表的名称。
<autoanalyze_options>：需要调整的AUTO ANALYZE表级别阈值。
<expect_value>：需要调整的值。

使用示例

关闭test表的AUTO ANALYZE。
```
ALTER TABLE test SET (autovacuum_enabled=false);
```
说明
autovacuum_enabled=false同时会关闭表的Auto Vacuum功能。建议通过调高触发比例来实现只关闭AUTO ANALYZE。

调高test表AUTO ANALYZE的触发比例到80%。

ALTER TABLE test SET (autovacuum_analyze_scale_factor=0.8);

恢复阈值默认值

语法

ALTER TABLE <test_table> RESET (<autoanalyze_options>);

参数说明

<test_table>：需要调整的表的名称。
<autoanalyze_options>：需要调整的AUTO ANALYZE表级别阈值。

使用示例

恢复test表的AUTO ANALYZE的触发比例为默认值。

ALTER TABLE test RESET (autovacuum_analyze_scale_factor);

同步自动收集统计信息

由于异步自动收集统计信息存在延时（约1分钟），作为异步自动收集统计信息的补充，云原生数据仓库 AnalyticDB PostgreSQL 版也有同步自动收集统计信息的功能。即在表中数据发生变更的命令执行结束前，同步地自动收集统计信息。

说明

同步自动收集信息不支持分区表。

工作机制

同步自动收集统计信息有以下三种模式。

none：禁止同步自动收集统计信息。即所有统计信息的收集需要依赖异步或者手动执行命令完成。
on_no_stats：当对没有统计信息的表执行CREATE TABLE AS SELECT、INSERT或者COPY时，就会自动收集该表的统计信息。但是除此以外的其他统计信息收集需求，仍然依赖异步或者手动执行命令完成。
on_change：执行CREATE TABLE AS SELECT、INSERT、UPDATE、DELETE或者COPY命令时，如果影响的行数超过了gp_autostats_on_change_threshold内核参数所设置的阈值时，自动触发表的统计信息收集。该模式完全自动化，不需要额外手动执行统计信息收集命令。

阈值说明

参数名称	描述	默认值	取值范围
gp_autostats_mode	适用于除函数或存储过程外的其他表的同步自动收集统计信息。	on_no_stats	none on_no_stats on_change
gp_autostats_mode_in_functions	适用于在函数或存储过程中同步自动收集统计信息。	none	none on_no_stats on_change
gp_autostats_on_change_threshold	on_change模式下，如果某个命令影响的行数超过该值，则会在执行命令时同步地执行`ANALYZE`收集统计信息。单位：行。	2147483647	[0, 2147483647]

说明

通常不建议调整实例级别的同步自动收集统计信息参数。如果想要调整某个参数阈值，可以在会话级别设置。

会话级别调整设置

可以在会话级别调整同步收集统计信息的相关设置。

当前会话的函数或存储过程中，当对没有统计信息的表执行CREATE TABLE AS SELECT、INSERT或COPY，以下设置会触发一次自动收集统计信息。
```
SET gp_autostats_mode_in_functions = on_no_stats;
```

关闭当前会话中同步收集统计信息的设置。

SET gp_autostats_mode_in_functions = on_no_stats;
SET gp_autostats_mode = NONE;

当前会话在执行CREATE TABLE AS SELECT、INSERT、UPDATE、DELETE或COPY时，如果某张表变更行数超过十万行，以下设置会同步执行一次统计信息收集。
```
SET gp_autostats_mode = on_change;
SET gp_autostats_on_change_threshold = 100000;
```