让SQL优化器更准确!AnalyticDB PG版发布统计信息自动收集功能

本文涉及的产品
阿里云百炼推荐规格 ADB PostgreSQL,4核16GB 100GB 1个月
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
简介: 本次发布的 Auto Analyze 功能解决了在 ADB PG 实例使用过程中,由于未能及时执行 ANALYZE 收集统计信息导致了 CBO 优化器生成计划退化进而导致业务分析变慢的问题。

作者:王伟(盏一)



1. ADB PG 简介

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


ADBPG架构.png


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


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



2. Analyze 重要性

当前 ADB PG 基于代价的优化器(以下简称 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;

注:*左右滑动阅览


这里第1处 insert 会触发 ADB PG AutoStats 机制,此时会对表 t 进行一次 ANALYZE,并收集相关统计信息。接着我们使用 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

注:*左右滑动阅览


可以看到,由于在表 t 创建之后,仅在第一次 insert 时触发了 ANALYZE,在数据更新后未能及时更新统计信息,使得优化器在优化时看到的统计信息中记录着表 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 控制。


  1. ON_NO_STATS。此时意味着在用户执行了 Insert/Update/Delete 等 DML 操作之后,ADB PG 会查询 DML 目标表在 DML 之前的状态,若目标表在 DML 之前是空表,那么在 DML 之后,此时 ADB PG 会在同一事务内对目标表触发一次 ANALYZE 操作。这也是当前 ADBPG 线上默认配置。
  2. ON_CHANGE。此时意味着在用户执行了 DML 操作之后,ADB PG 会判断本次 DML 操作影响到的行数,若影响行数超过一定阈值,便会对目标表触发一次 ANALYZE 操作。
  3. NONE。此时意味着关闭 AutoStats 系统。


可以看到 AutoStats 判断是否对目标表触发 ANALYZE 操作只依据了最近一次 DML 操作的结果,所以 AutoStats 更适合 ETL 业务。但随着 ADB PG HTAP 能力的提升,以及与周边生态系统链路的打通,越来越多的用户倾向于以流式的方式将数据导入到 ADB PG 中,比如使用阿里云数据传输服务。这导致了 AutoStats 越来越乏力。从上面举的例子也可以看到,AutoStats 只会在第一次 Insert 之后触发一次 ANALYZE 操作,使得在第二、三次 insert 之后,表 t 的统计信息与实际情况完全不符,这也直接导致了 CBO 未能生成更优的执行计划。



4. 引入 Auto Analyze

为此 ADB PG 开发了适合场景更广、对流式插入更友好的 Auto Analyze 系统。Auto Analyze 会为每张表记录自上次 Analyze 以来所有 Insert/Update/Delete 影响到行数的累加值,之后基于这个累加值,结合表本身大小,来决策是否需要为表执行 Analyze 操作。另外 Auto Analyze 会异步地执行 Analyze 操作,与 AutoStats 在用户业务事务内同步地执行 Analyze 操作相比,异步 Analyze 执行对用户业务基本上无感,也不会再有同步执行 Analyze 操作而可能导致的死锁等其他问题。而且秉承着拥抱、回馈、融合、回报的开源思想,ADB PG Auto Analyze 也已经贡献并合入了 Greenplum master 分支。


在开启 Auto Analyze 的前提下,我们再一次模拟执行文章开头中举的例子,如下所示在执行完第3条 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. Auto Analyze 实现


5.1 PG Auto Analyze 实现


在介绍 ADB PG Auto Analyze 实现之前,我们首先看下 PostgreSQL 中是如何实现 Auto Analyze 的。PostgreSQL Auto Analyze 的实现依赖着两个组件:Statistics Collector,Autovacuum。其中 Autovacuum 组件,简单来说,就是周期性遍历每个库,对于库中每个表,根据 Statistics Collector 中这张表相关的统计信息来判断是否需要为这张表触发 Analyze, Vacuum 等操作。Statistics Collector 组件负责收集、保存、持久化 PG 运行中产生的各种 metric 信息,如表的增、删、改行数等。Statistics Collector 收集到的所有信息都保存在内存中。在 Statistics Collector 进程关闭时,会将内存中的统计信息持久化到磁盘文件中。在 Statistics Collector 进程启动时,也会从磁盘文件中读取之前持久化的统计信息到内存中。Statistics Collector 自身同时也是一个 udp server,监听着一个特定端口。在 PG 运行中,backend 会在适当时候将自身收集到的 metric 打包成 udp message 发送给 Statistics Collector。下面以 PgStat_StatTabEntry 中信息的收集为例演示一下此过程:


2.5头条2.png


其中 pgStatTabList 指针指向的结构等同LinkedList<PgStat_TableStatus>,每个 backend 都会将自身收集到表级别的 metric 存放在该数组相应 PgStat_TableStatus 中。


PgStat_TableStatus::trans 指针指向的结构等同Vec<PgStat_TableXactStatus> ,其内通过 PgStat_TableXactStatus 存放着当前表在每个事务级别内的统计信息。PgStat_SubXactStatus 结构存放着一个特定事务级别内所有 PgStat_TableXactStatus 结构,存放着该事务级别内发生的所有统计信息。pgStatXactStack 总是指向着当前事务级别对应的 PgStat_SubXactStatus 结构。当在某个事务级别内 backend 打开表准备执行增删改时,会从 pgStatTabList 指向的数组中选择一个 PgStat_TableStatus 元素赋值给 RelationData::pgstat_info。之后在执行 IUD(Insert/Update/Delete) 操作时,backend 会更新当前事务级别上特定表对应的 PgStat_TableXactStatus 结构。每当一个子事务 commit/rollback 时都会将本级别事务内所有 PgStat_TableXactStatus 统计信息合并到父事务中。在顶层事务 commit/rollback 时会将 PgStat_TableXactStatus 的统计信息合并到 PgStat_TableStatus.t_counts 中。最后每当 backend 进入 idle 状态(或者退出)时,会将 pgStatTabList 中所有有效的 PgStat_TableStatus 打包成 udp message 发送给 Statistics Collector,之后将 pgStatTabList 中统计信息清零。


5.2 ADB PG Auto Analyze 实现


所以 ADB PG Auto Analyze 的实现主要便是在 Insert/Update/Delete 执行结束之后,收集各个计算节点返回的 Insert/Update/Delete 在各自节点内影响到的行数,之后累加得到 Insert/Update/Delete 影响到的总行数,之后将这个信息按照 PG Statistics Collector 中做法记录在对应的 PgStat_TableXactStatus 结构中。最后会在适当时候发送给 ADB PG master 节点的 Statistics Collector 进程。具体细节感兴趣的同学可以参考我们将 ADB PG Auto Analyze 贡献给社区时提的 Pull Requesthttps://github.com/greenplum-db/gpdb/pull/10515



6. 未来展望

Auto Analyze 的引入使得用户在使用 ADB PG 实例的过程中,统计信息总是能被及时地收集。这也使得用户的业务分析将总是能得到较优的执行计划,业务分析 SQL 的执行性能将不再会由于统计信息过时而急剧下降。另外在 Auto Analyze 搭建的基础设施上,结合着线上用户在使用 ADB PG 中遇到的一些问题,我们也有了接下来的目标:实现 Auto Vacuum 功能。与 Analyze 操作一样,Vacuum 在 ADB PG 也扮演着重要的角色,相信 Auto Vacuum 的引入也会进一步加强 ADB PG 的用户体验。有需要的同学可以扫码进入钉钉群“云原生数据仓库AnalyticDB PostgreSQL版交流群”在线沟通。


ADB钉群二维码.png


相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
目录
相关文章
|
4月前
|
XML SQL 数据格式
XML动态sql查询当前时间之前的信息报错
XML动态sql查询当前时间之前的信息报错
55 2
|
16天前
|
SQL 监控 关系型数据库
SQL语句当前及历史信息查询-performance schema的使用
本文介绍了如何使用MySQL的Performance Schema来获取SQL语句的当前和历史执行信息。Performance Schema默认在MySQL 8.0中启用,可以通过查询相关表来获取详细的SQL执行信息,包括当前执行的SQL、历史执行记录和统计汇总信息,从而快速定位和解决性能瓶颈。
|
1月前
|
SQL
开启慢SQL设置long_query_time=0.1为啥会统计的sql却存在小于100毫秒的sql
开启慢SQL设置long_query_time=0.1为啥会统计的sql却存在小于100毫秒的sql
30 1
|
6月前
|
分布式计算 关系型数据库 数据挖掘
实时数仓 Hologres产品使用合集之当使用动态分区管理功能按日期进行分区后,通过主键和segment_key进行时间范围查询性能变差是什么原因
实时数仓Hologres的基本概念和特点:1.一站式实时数仓引擎:Hologres集成了数据仓库、在线分析处理(OLAP)和在线服务(Serving)能力于一体,适合实时数据分析和决策支持场景。2.兼容PostgreSQL协议:Hologres支持标准SQL(兼容PostgreSQL协议和语法),使得迁移和集成变得简单。3.海量数据处理能力:能够处理PB级数据的多维分析和即席查询,支持高并发低延迟查询。4.实时性:支持数据的实时写入、实时更新和实时分析,满足对数据新鲜度要求高的业务场景。5.与大数据生态集成:与MaxCompute、Flink、DataWorks等阿里云产品深度融合,提供离在线
|
6月前
|
分布式计算 关系型数据库 数据挖掘
实时数仓 Hologres产品使用合集之误删Hologres一张表的数据,可以支持闪回功能吗
实时数仓Hologres的基本概念和特点:1.一站式实时数仓引擎:Hologres集成了数据仓库、在线分析处理(OLAP)和在线服务(Serving)能力于一体,适合实时数据分析和决策支持场景。2.兼容PostgreSQL协议:Hologres支持标准SQL(兼容PostgreSQL协议和语法),使得迁移和集成变得简单。3.海量数据处理能力:能够处理PB级数据的多维分析和即席查询,支持高并发低延迟查询。4.实时性:支持数据的实时写入、实时更新和实时分析,满足对数据新鲜度要求高的业务场景。5.与大数据生态集成:与MaxCompute、Flink、DataWorks等阿里云产品深度融合,提供离在线
|
3月前
|
SQL Java
使用java在未知表字段情况下通过sql查询信息
使用java在未知表字段情况下通过sql查询信息
35 1
|
2月前
|
SQL 存储 关系型数据库
mysql 数据库空间统计sql
mysql 数据库空间统计sql
47 0
|
4月前
|
SQL 流计算
Flink SQL 在快手实践问题之由于meta信息变化导致的state向前兼容问题如何解决
Flink SQL 在快手实践问题之由于meta信息变化导致的state向前兼容问题如何解决
51 1
|
4月前
|
SQL 存储 OLAP
OneSQL OLAP实践问题之Flink SQL Gateway的功能如何解决
OneSQL OLAP实践问题之Flink SQL Gateway的功能如何解决
40 1
|
4月前
|
SQL JSON Go
Go - 基于 GORM 获取当前请求所执行的 SQL 信息
Go - 基于 GORM 获取当前请求所执行的 SQL 信息
69 3

热门文章

最新文章

相关产品

  • 云原生数据仓库AnalyticDB MySQL版