采集自动组合(ADBforPostgreSQL的CBO优化器基于表的统计信息做出合理决策)

　　采集自动组合(ADBforPostgreSQL的CBO优化器基于表的统计信息做出合理决策)

　　1.亚行PG介绍

　　AnalyticDB for PostgreSQL 是阿里云上的 MPP 数据仓库服务。其内核采用PostgreSQL引擎，支持标准SQL 2003，兼容PostgreSQL/Greenplum，高度兼容Oracle语法生态；具有存储计算分离、在线弹性平滑扩展的特点；支持任意维度的在线分析和探索，也支持高性能的离线数据处理；是互联网、金融、证券、保险、银行、数字政府、新零售等行业具有竞争力的数据仓库解决方案。

　　AnalyticDB for PostgreSQL采用MPP架构，实例由多个计算节点组成。存储容量随节点数线性增长，查询响应时间不变。

　　ADB PG 的 CBO 优化器根据表的统计信息为查询选择最佳查询计划。本次发布的Auto Analyze功能解决了ADB PG实例在使用过程中，未能及时执行ANALYZE采集统计，导致CBO优化器生成计划降级，业务分析变慢的问题。

　　2.分析重要性

　　目前的ADB PG cost-based优化器（以下简称CBO）依靠我们评估一个成本值来衡量每个候选计划的成本，成本评估依赖于采集到的统计信息。在我们看来，CBO 和统计信息之间的关系就像*敏*感*词*支和*敏*感*词*之间的关系。再好的*敏*感*词*，如果没有足够的*敏*感*词*，就等于是一个不能做饭没有饭的聪明女人。统计信息的采集是为了给CBO提供足够的合理信息，使CBO能够根据这些统计信息做出合理的决策。作为一个简单的例子，假设我们有表 t 和 idx_t_z 如下：

　　create table t(i int , j int, z int);

create index idx_t_z on t(z);

insert into t select i, i, i from generate_series(1, 2) i; -- 1

insert into t select i, i, i from generate_series(1, 3333333) i; -- 2

insert into t select i, i, 20181218 from generate_series(1, 10) i;

　　这里的第一次插入将触发 ADB PG AutoStats 机制。这时候表t会被ANALYZEd一次，并采集相关的统计信息。然后我们使用 EXPLAN ANALYZE 执行一个简单的查询并输出查询的执行计划：

　　tmp=# explain analyze select * from t where z = 20181218;

Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..2.02 rows=1 width=12) (actual time=287.952..743.833 rows=10 loops=1)

-> Seq Scan on t (cost=0.00..2.02 rows=1 width=12) (actual ti me=287.428..287.430 rows=5 loops=1)

Filter: (z = 20181218)

Planning time: 1.242 ms

(slice0) Executor memory: 59K bytes.

(slice1) Executor memory: 42K bytes avg x 3 workers, 42K byt es max (seg0).

Memory used: 128000kB

Optimizer: Postgres query optimizer

Execution time: 744.675 ms

　　可以看到，由于ANALYZE只在表t创建后的第一次insert时触发，数据更新后没有及时更新统计信息，所以优化器在看到的统计信息中记录了表t的总行数在优化过程中。数字是 2，使得 CBO 优化器错误地认为 SeqScan 比 IndexScan 更有效。但是如果我们在这里手动执行 ANALYZE：

　　tmp=# ANALYZE t;

ANALYZE

tmp=# explain analyze select * from t where z = 20181218;

Gather Motion 3:1 (slice1; segments: 3) (cost=0.18..8.20 rows=1 width=12) (actual time=0.429..0.439 rows=10 loops=1)

-> Index Scan using idx_t_z on t (cost=0.18..8.20 rows=1 widt h=12) (actual time=0.014..0.016 rows=5 loops=1)

Index Cond: (z = 20181218)

Planning time: 1.305 ms

(slice0) Executor memory: 92K bytes.

(slice1) Executor memory: 60K bytes avg x 3 workers, 60K byt es max (seg0).

Memory used: 128000kB

Optimizer: Postgres query optimizer

Execution time: 1.322 ms

　　可以看出，由于CBO使用了更准确的统计信息，也生成了更好的执行计划，将查询执行时间从700ms降低到1ms。

　　更准确的统计信息不仅可以帮助优化器生成更高效的执行计划；也可以让ADB PG最近发布的多维排序得到更好的排序结果。排序效果越好，查询加速越明显。

　　3.AutoStats 介绍

　　如上图，由于ANALYZE的重要性，为了提升用户体验，ADB PG引入了AutoStats机制。AutoStats机制有以下三种工作模式，由配置gp_autostats_mode控制。

　　ON_NO_STATS。这意味着在用户执行Insert/Update/Delete等DML操作后，ADB PG会在DML之前查询DML目标表的状态。如果目标表在 DML 之前为空，那么在 DML 之后，ADB PG 会在同一事务内对目标表触发 ANALYZE 操作。这也是目前ADBPG在线的默认配置。ON_CHANGE。这意味着在用户执行 DML 操作后，ADB PG 将确定受此 DML 操作影响的行数。如果受影响的行数超过某个阈值，则会触发对目标表的 ANALYZE 操作。没有任何。这意味着关闭 AutoStats 系统。

　　可以看出，AutoStats 仅根据最近一次 DML 操作的结果来判断是否触发目标表的 ANALYZE 操作，因此 AutoStats 更适合 ETL 业务。但是，随着ADB PG HTAP能力的提升以及与周边生态系统链接的开放，越来越多的用户倾向于以流式方式将数据导入ADB PG，例如使用阿里云数据传输服务。这导致 AutoStats 变得越来越弱。从上面的例子也可以看出，AutoStats只会在第一次插入后触发ANALYZE操作，这样在二、的第三次插入后，表t的统计数据与实际情况完全不一致，这也是直截了当的结果是，CBO 未能生成更好的执行计划。

　　4.介绍自动分析

　　为此，亚行PG开发了更适合更广泛场景、对流媒体插入更友好的Auto Analyze系统。Auto Analyze会为每个表记录自上次Analyze以来所有Insert/Update/Delete受影响行的累计值，然后根据这个累计值，结合表本身的大小，决定是否对表执行Analyze操作。桌子。此外，自动分析将异步执行分析操作。与AutoStats在用户业务事务中同步执行Analyze操作相比，异步Analyze执行基本对用户业务不敏感，不会再出现Analyze操作同步执行可能导致的死亡。其他问题，例如锁。

　　在开启Auto Analyze的前提下，我们再次模拟并执行文章开头提到的例子。如下图，执行第三次insert后，Auto Analyze系统也触发了对表t的ANALYZE操作。

　　tmp=# select objid::regclass, staactionname, stasubtype from pg_stat_last_operation where objid = 't'::regclass order by statime desc;

objid | staactionname | stasubtype

-------+---------------+------------

t | ANALYZE | AUTO

t | CREATE | TABLE

(2 rows)

　　此时，用户不再需要手动执行 ANALYZE，还可以启用 CBO 生成更好的执行计划：

　　Gather Motion 3:1 (slice1; segments: 3) (cost=0.18..8.20 rows=1 width=12) (actual time=0.765..0.773 rows=10 loops=1)

-> Index Scan using idx_t_z on t (cost=0.18..8.20 rows=1 width=12) (actual time=0.013..0.015 rows=5 loops=1)

Index Cond: (z = 20181218)

Planning time: 1.034 ms

(slice0) Executor memory: 92K bytes.

(slice1) Executor memory: 60K bytes avg x 3 workers, 60K bytes max (seg0).

Memory used: 128000kB

Optimizer: Postgres query optimizer

Execution time: 1.647 ms

　　5.自动分析实现

　　5.1 PG 自动分析实现

　　在介绍 ADB PG Auto Analyze 的实现之前，我们先来看看 Auto Analyze 在 PostgreSQL 中是如何实现的。PostgreSQL Auto Analyze 的实现依赖于两个组件：Statistics Collector 和 Autovacuum。其中Autovacuum组件，简单来说就是周期性的遍历各个库。对于库中的每个表，根据Statistics Collector中与该表相关的统计信息，判断是否需要对该表触发Analyze、Vacuum等操作。Statistics Collector 组件负责采集、保存和持久化 PG 操作过程中产生的各种指标信息，如表的增删改查等。Statistics Collector 采集的所有信息都存储在内存中。当 Statistics Collector 进程关闭时，内存中的统计信息将持久化到磁盘文件中。当 Statistics Collector 进程启动时，它还会从磁盘文件中读取先前持久化的统计信息到内存中。Statistics Collector 本身也是一个 udp 服务器，监控特定的端口。当PG运行时，后端会将自己采集的metrics打包成udp消息，并在适当的时候发送给Statistics Collector。我们以PgStat_StatTabEntry中的信息采集为例来演示这个过程：Statistics Collector 本身也是一个 udp 服务器，监控特定的端口。当PG运行时，后端会将自己采集的metrics打包成udp消息，并在适当的时候发送给Statistics Collector。我们以PgStat_StatTabEntry中的信息采集为例来演示这个过程：Statistics Collector 本身也是一个 udp 服务器，监控特定的端口。当PG运行时，后端会将自己采集的metrics打包成udp消息，并在适当的时候发送给Statistics Collector。我们以PgStat_StatTabEntry中的信息采集为例来演示这个过程：

　　pgStatTabList 指针指向的结构体等价于 LinkedList

　　, 每个后端都会将自己采集到的表级指标存储在数组对应的PgStat_TableStatus中。

　　PgStat_TableStatus::trans 指针指向的结构体等价于 Vec

　　, 其中通过PgStat_TableXactStatus 存储当前表在各个事务级别的统计信息。PgStat_SubXactStatus 结构体存储了特定事务级别的所有 PgStat_TableXactStatus 结构，并存储了事务级别中发生的所有统计信息。pgStatXactStack 始终指向与当前事务级别对应的 PgStat_SubXactStatus 结构。当后端在某个事务级别打开表并准备执行增删改查时，将从 pgStatTabList 指向的数组中选择一个 PgStat_TableStatus 元素并将其分配给 RelationData::pgstat_info。后来在进行宫内节育器（插入/更新/删除）操作时，backend 会在当前事务级别更新特定表对应的 PgStat_TableXactStatus 结构体。每当子事务提交/回滚时，该级别事务中的所有 PgStat_TableXactStatus 统计信息都将合并到父事务中。在顶级事务提交/回滚中，PgStat_TableXactStatus 的统计信息会被合并到 PgStat_TableStatus.t_counts 中。最后，每当后端进入空闲状态（或退出）时，pgStatTabList 中所有有效的 PgStat_TableStatus 都会被打包成一个 udp 消息发送到统计采集器，然后 pgStatTabList 中的统计信息将被清除。在顶级事务提交/回滚中，PgStat_TableXactStatus 的统计信息会被合并到 PgStat_TableStatus.t_counts 中。最后，每当后端进入空闲状态（或退出）时，pgStatTabList 中所有有效的 PgStat_TableStatus 都会被打包成一个 udp 消息发送到统计采集器，然后 pgStatTabList 中的统计信息将被清除。在顶级事务提交/回滚中，PgStat_TableXactStatus 的统计信息会被合并到 PgStat_TableStatus.t_counts 中。最后，每当后端进入空闲状态（或退出）时，pgStatTabList 中所有有效的 PgStat_TableStatus 都会被打包成一个 udp 消息发送到统计采集器，然后 pgStatTabList 中的统计信息将被清除。

　　5.2 ADB PG 自动分析实现

　　因此，ADB PG Auto Analyze的实现主要是采集执行Insert/Update/Delete后每个计算节点在各自节点返回的Insert/Update/Delete影响的行数，然后相加得到Insert/Update/Delete 的效果达到的总行数，然后根据PG Statistics Collector中的实践，将此信息记录在对应的PgStat_TableXactStatus结构体中。最后，它会在适当的时候发送到 ADB PG 主节点的 Statistics Collector 进程。具体细节感兴趣的同学可以参考我们在向社区贡献ADB PG Auto Analyze时提到的Pull Request。

　　6.未来展望

　　Auto Analyze 的引入使用户在使用 ADB PG 实例时能够及时采集统计信息。这也让用户的业务分析总能得到更好的执行计划，业务分析SQL的执行性能也不会因为统计信息过时而急剧下降。另外，在Auto Analyze搭建的基础设施上，结合网上用户在使用ADB PG时遇到的一些问题，我们还有下一个目标：实现Auto Vacuum功能。与Analyze 操作一样，Vacuum 在ADB PG 中也扮演着重要角色。相信Auto Vacuum的推出将进一步提升ADB PG的用户体验。有需要的同学可以扫码进入钉钉群“云原生数据仓库AnalyticDB PostgreSQL版本交流群”

　　/action/joingroup?code=v1,k1,I6WT+7/8fVW7k3S2um7oHER/P+1GwLFRSqkAJdwiYso=（自动识别二维码）