Greenplum 性能优化之路 --(三)ANALYZE

Greenplum数据库Analyze最佳实践 Greenplum数据库定时执行ANALYZE的优化建议,旨在确保统计信息准确性、提升执行计划效率并降低CPU使用率: 阅读详情

一、为什么需要 ANALYZE

首先介绍下 RBO 和 CBO,这是数据库引擎在执行 SQL 语句时的2种不同的优化策略。

RBO(Rule-Based Optimizer)

基于规则的优化器,就是优化器在优化查询计划的时候,是根据预先设置好的规则进行的,这些规则无法灵活改变。举个例子,索引优先于扫描,这是一个规则,优化器在遇到所有可以利用索引的地方,都不会选择扫描。这在多数情况下是正确的,但也不完全如此:

比如 一张个人信息表中性别栏目加上索引,由于性别是只有2个值的枚举类,也就是常说的基数非常低的列,在这种列上使用索引往往效果还不如扫描

SELET count(*) FROM person WHERE gender = 'M';

因此 RBO 的优化方式是死板的,粗放的,目前已逐渐被 CBO 方式取代。

 

CBO(Cost Based Optimizer)

基于代价的优化器,就是优化器在优化查询计划的时候,是根据动态计算出来的 Cost(代价)来判断如何进行选择。那如何计算代价呢?这里一般是基于代价模型和统计信息,代价模型是否合理,统计信息是否准确都会影响优化的效果。

还是拿上面员工性别统计为例,在 CBO 的优化方式下,物理计划就不会选择走索引。当然上面的例子比较简单,在 Greenplum 运行的复杂 SQL 中,优化器最核心的还是在 scan 和 join 的各种实现方式中做出选择,这才是能大幅提升性能的关键点。

前面提到 CBO 需要一个代价模型和统计信息,代价模型和规则一样,需要预先设置好,那统计信息是如何收集的?多数基于 CBO 优化的计算引擎,包括 Greenplum,Oracle,Hive,Spark 等都类似,除了可以按一定规则自动收集统计信息外,还都支持手动输入命令进行收集,通常这个命令都叫 ANALYZE。

结论:由于 CBO 优化的需求,因此我们需要使用 ANALYZE 命令去收集统计信息。

 

二、ANALYZE 怎么使用

 

说明

ANALYZE 是 Greenplum 提供的收集统计信息的命令。

ANALYZE 支持三种粒度,列,表,库,如下:

CREATE TABLE foo (id int NOT NULL, bar text NOT NULL) DISTRIBUTED BY (id); // 创建测试表foo
ANALYZE foo(bar);  // 只搜集bar列的统计信息
ANALYZE foo; // 搜集foo表的统计信息
ANALYZE; // 搜集当前库所有表的统计信息,需要有权限才行

限制

ANALYZE 会给目标表加 SHARE UPDATE EXCLUSIVE 锁,也就是与 UPDATE,DELETE,还有 DDL 语句冲突。

速度

ANALYZE 是一种采样统计算法,通常不会扫描表中所有的数据,但是对于大表,也仍会消耗一定的时间和计算资源。

采样统计会有精度的问题,因此 Greenplum 也提供了一个参数 default_statistics_target,调整采样的比例。简单说来,这个值设置得越大,采样的数量就越多,准确性就越高,但是消耗的时间和资源也越多。

default_statistics_target.png

 

直接修改服务器的参数会影响整个集群,通常不建议这样操作。如果确实有需要,可以尝试只修改某列的对应参数,如下:

ALTER TABLE {table_name}  ALTER COLUMN {col_name} SET STATISTICS {-1|0-1000};

时机

根据上文所述,ANALYZE 会加锁并且也会消耗系统资源,因此运行命令需要选择合适的时机尽可能少的运行。根据 Greenplum 官网建议,以下3种情况发生后建议运行 ANALYZE

  • 批量加载数据后,比如 COPY

  • 创建索引之后

  • INSERT, UPDATE, and DELETE 大量数据之后

 

自动化

除了手动运行,ANALYZE 也可以自动化。实际上默认情况下,我们对空表写入数据后, Greenplum 也会自动帮我们收集统计信息,不过之后在写入数据,就需要手动操作了。

有2个参数可以用来调整自动化收集的时机,gp_autostats_mode 和 gp_autostats_on_change_threshold。gp_autostats_mode 默认是 on_no_stats,也就是如果表还没有统计信息,这时候写入数据会导致自动收集,这之后,无论表数据变化多大,都只能手动收集了。如果将 gp_autostats_mode 修改为 on_change ,就是在数据变化量达到 gp_autostats_on_change_threshold 参数配置的量之后,系统就会自动收集统计信息。

分区表

Greenplum 官网对于分区表的 ANALYZE 专门进行了讲解,其实只要保持默认值,不去修改系统参数 optimizer_analyze_root_partition,那么对于分区表的操作并没有什么不同,直接在 root 表上进行 ANALYZE 即可,系统会自动把所有叶子节点的分区表的统计信息都收集起来。

如果分区表的数目很多,那在 root 表上进行 ANALYZE 可能会非常耗时,通常的分区表都是带有时间维度的,历史的分区表并不会修改,因此单独 ANALYZE 数据发生变化的分区,是更好的实践。

 

三、统计信息去了哪里

 

pg_class

表的大小是统计信息里面最直观,也几乎是最重要的,这个信息是放在 pg_catalog.pg_class 系统表中,reltuples 代表元组数(行数),relpages 代表实际占用的 page 数目(Greenplum中一个 page 为32KB)。

需要注意以下3点

1. reltuples 不是准确值,获取表的准确行数还是需要 count。

2. reltuples 和 relpages 需要通过 ANALYZE 进行收集,对于已有数据的表,系统不会自动更新。

3. reltuples 和 relpages 不一定能对齐,比如条数看起来不多的表,实际占用的 page 数目很大,这种一般是由于数据膨胀(bloat)造成,这时候需要 vacuum 等操作。

pg_statistic

关于列的统计信息都是存放在 pg_catalog.pg_statistic 系统表中。其中表的每一列(如果有统计)都会有一行对应的数据。了解并掌握 pg_statistic 的内容,对于深入理解查询优化非常重要。

列的统计信息内容很丰富,但是目的都是让优化器估算出,一个查询条件,能够过滤多少数据。

以下列举了 pg_statistic 的重要字段:

列名      

说明        

stanullfrac    

null 值的比例

stawidth    

列值的平均宽度

stadistinct    

大于0代表去重之后的个数 小于0其绝对值代表去重之后个数的比例 等于0代表无意义

stakindN    

#define STATISTIC_KIND_MCV 1 #define STATISTIC_KIND_HISTOGRAM  2 #define STATISTIC_KIND_CORRELATION 3 #define STATISTIC_KIND_MCELEM  4 #define STATISTIC_KIND_DECHIST 5 #define STATISTIC_KIND_RANGE_LENGTH_HISTOGRAM  6 #define STATISTIC_KIND_BOUNDS_HISTOGRAM  7 #define STATISTIC_KIND_HLL  99

对于 stakindN 字段中的统计方式,这里选择3个最常见的进行说明:

 

1. STATISTIC_KIND_MCV

高频值,在一个列中出现最频繁的值。

高频值统计在很多场景下都有价值,这里举一个数据倾斜的 hash join 例子,如下代码:

/*
 * ExecHashBuildSkewHash
 *
 *		Set up for skew optimization if we can identify the most common values
 *		(MCVs) of the outer relation's join key.  We make a skew hash bucket
 *		for the hash value of each MCV, up to the number of slots allowed
 *		based on available memory.
 */
static void
ExecHashBuildSkewHash(HashJoinTable hashtable, Hash *node, int mcvsToUse){....}

hash join 场景下,我们需要尽可能的把 inner table 构建在内存中,但内存资源是有限的,因此我们需要做出一些选择,什么内容优先放入内存中。如果外表有高频值,那我们可以考虑把高频值对应的内表信息优先放入到内存中,在实践中,Greenplum 是单独构建一个 skew hash table 与 main hash table 并存。

 

2. STATISTIC_KIND_HISTOGRAM

直方图,使用等频直方图来描述一个列中的数据的分布。

直方图主要用于数据分布不均匀的情况下,对按列过滤后能返回多少数据进行预估。

举个例子,一个有3种产品的订单表,商品 A 很热销,订单量在90%,商品 B 一般,订单量在9%,商品 C 只有1%,则该列的 NDV(Number of Distinct Value)值为3,如果一共有1000000条数据,在没有直方图统计的情况下,如果查询商品 C 的订单,优化器会预计要扫描1000000/3≈330000,因此可能选择全表 scan,如果含有直方图统计,优化器就知道实际上 C 商品可能就几千条数据,因此会选择走索引。当然这个例子很简单,实际情况会复杂很多。

 

3. STATISTIC_KIND_CORRELATION

相关系数,记录的是当前列未排序的数据分布和排序后的数据分布的相关性。

用于估算索引扫描代价的,统计值在-1到1,值越大,表示相关性越高,也就是使用索引扫描代价越低。

举个例子,初始化如下2张表

create table t_correlation_asc (id int, number int) DISTRIBUTED BY (id);
INSERT INTO t_correlation_asc SELECT 1, i FROM generate_series(1, 1000) AS i;

create table t_correlation_desc (id int, number int) DISTRIBUTED BY (id);
INSERT INTO t_correlation_desc SELECT 1, 1001-i FROM generate_series(1, 1000) AS i;

在查看表对应的统计信息,可以看出在 number 列,你按升序写入1000个数,该列物理存储的数据实际上就是按升序排序的,反过来降序写入1000个数,由于顺序是相反的,所以相关性是-1

correlation.png

 

四、例子

 

以下将会构造一个大小表 join 的场景,来说明统计信息的收集对于查询计划的影响。

1. 初始化表结构和数据:

CREATE TABLE small_table (id int NOT NULL, bar text NOT NULL) DISTRIBUTED BY (id);
INSERT INTO small_table SELECT i, 'test:' || i FROM generate_series(1, 10) AS i;

CREATE TABLE big_table (id int NOT NULL, bar text NOT NULL) DISTRIBUTED BY (id);
INSERT INTO big_table SELECT i, 'test:' || i FROM generate_series(1, 100000) AS i;

pg_class 中对应的数据如下:

small_table.png

 

big_table.png

 

2. 大小表 join

注意为了构造小表广播的场景,这里关联键需要选择非分布键。

explain1.png

 

3. 给小表插入数据

这里给小表插入数据后,小表的数据量超过大表

INSERT INTO small_table SELECT i, 'test:' || i FROM generate_series(1, 200000) AS i;

在没有 ANALYZE 的情况下,pg_class 中的数据没有发生变化,因此查询计划也没有发生变化。

 

4. 收集统计信息

运行 ANALYZE 收集小表的统计信息,如下:

new_small_table.png 

 

在运行 join 语句,查询计划发生变化:

explain2.png

 

结论:查询优化器在收到新的统计信息之后,发现是2张数据量差不多的表进行 join,因此选择重分布而不是小表广播。

 


 

 

关注“腾讯云大数据”公众号,技术交流、最新活动、服务专享一站Get~

【经验分享】使用analyze收集统计信息的正确姿势 了解更多Greenplum相关内容,欢迎访问Greenplum中文社区网站 统计数据是描述存储在数据库中的数据的元数据,查询规划器需要最... 阅读详情

相关推荐

【青梅快讯】快速迭代,最新版本Greenplum 6.10已发布

了解更多Greenplum技术干货,欢迎访问Greenplum中文社区网站 ​自Greenplum 6.0正式发布以来,Greenplum保持了每月一个小版本的快速迭代速度,持续为用户提供新功能与修复补丁。最新版本6.10已于8月10日发布。现在小编带你回顾一下6.8到6.10版本中,Greenplum都带来了哪些惊喜。 新特性汇总 Greenplum 6.10.1 修复bug列表 修复了interconnect代理的bug,可以启动interconnect代理功能。 修复外表 DELIMITE.

Greenplum中文社区 779

第二章 SQL命令参考-ANALYZE

ANALYZE Collectsstatistics about a database. 概要 ANALYZE [VERBOSE][ROOTPARTITION [ALL]] [table[(column [,...])]] 描述 分析有关的表的数据库中的系统表pg_statistic里的内容,并存储该结果收集统计信息。随后,Greenplum数据引擎使用这些数据来帮助判断查询的最有效的

kygoal的博客 4344

【实录】首次利用GPCC历史数据调优Greenplum 第一部分

数据库性能分析和优化是一个难题,笔者所在的Greenplum研发部门近期正好在解决一个实际用户的全局性能问题,本文记录了分析过程和解决思路。本案例是第一次对实际客户的生产库以GPCC历史数据为核心剖析性能问题,因此有一定的开创性和借鉴意义,故撰文供研发同事、现场工程师、支持工程师参考,同时也适合具备一定GP基础并希望提高的读者阅读。同时为了保护客户的商业秘密,本文不透露任何关于该商业用户的名称、...

Greenplum中文社区 815

Postgresql pg_class查看所有表名relname及记录数reltuples,记录数显示0实际有数据解决

Postgresql pg_class查看所有表名relname及记录数reltuples,记录数显示0实际有数据解决

qq_40985985的博客 4497

固定时间收敛的控制器设计(基础知识)

针对控制系统的有限时间收敛和固定时间收敛给出一些常用定理。

qq_40241332的博客 1万+

Autosar结合simulink的汽车应用层软件开发实例

本文基于汽车应用开发案例,并结合Autosar相关知识,生成具有Autosar标准的汽车应用层开发代码,以供学习和参考。以上就是本篇内容,主要是利用Simulink创建Autosar接口并进行端口映射的方法。

m0_64899535的博客 2155

Greenplum的两种优化器

Greenplum 6.5.0 Greenplum中有两个优化器,一个是Postgres query optimizer(Postgresql自带的优化器,简称PQO),一个是GPORCA(Greenplum自己开发的新一代优化器,简称GPO)。其中GPO在某些方面进行了优化,让其更适合在大数据量的进行数据分析,性能会更好。但是在数据量不是特别大的情况下,两种优化器的差异不是特别大,甚至PQO的性...

gxd520的专栏 2403

告别配置噩梦:用Vcpkg三行命令为Easy3D自动安装Boost、CGAL和Qt(VS2022实测)

本文详细介绍了如何使用Vcpkg工具在VS2022环境下为Easy3D项目一键安装Boost、CGAL和Qt等依赖库,解决传统手动配置的复杂性问题。通过三行简单命令即可自动完成下载、编译和路径配置,大幅提升开发效率,特别适合C++开发者快速搭建3D图形开发环境。

weixin_42525582的博客 213

Pivotal Greenplum® 6.9-管理员指南-Greenplum数据库概念-关于Greenplum数据库中的数据库统计信息

关于Greenplum数据库中的数据库统计信息 Greenplum数据库中通过ANALYZE命令收集统计信息的概述。 统计信息是元数据,它描述了存储在数据库中的数据。查询优化器需要最新的统计信息,才能为查询选择最佳的执行计划。例如,如果一个查询联接了两个表,并且其中一个表必须广播到所有段,则优化器可以选择两个表中较小的一个,以最大程度地减少网络流量。 优化器使用的统计信息将通过以下方式计算并保存在系统目录中:分析命令。有三种启动分析操作的方式: 您可以运行分析直接命令。 您可以运行分析...

uddiqpl的专栏 970

Python实现微信PC端聊天记录自动化监听与智能回复

本文详细介绍了如何使用Python实现微信PC端聊天记录的自动化监听与智能回复。通过win32api和uiautomation库,模拟人工操作,实时监控聊天窗口并抓取新消息。文章重点讲解了核心代码逻辑、环境搭建步骤,并提供了实现关键词触发自动回复的完整方案,旨在提高工作效率与信息管理自动化水平。

weixin_29092031的博客 350

机器学习笔记之玻尔兹曼机(一)基本介绍

在受限玻尔兹曼机——模型表示(Representation)一节中以玻尔兹曼机为引,介绍了受限玻尔兹曼机。从本节开始,将正式介绍玻尔兹曼机。相比于它的表示和推断过程,我们更关注它在求解过程中出现的问题。

静静的学习就好 7924

Greenplum性能优化analyze

  为什么需要ANALYZE   首先介绍下RBO和CBO,这是数据库引擎在执行SQL语句时的2种不同的优化策略。   RBO(Rule-Based Optimizer)   基于规则的优化器,就是优化器在优化查询计划的时候,是根据预先设置好的规则进行的,这些规则无法灵活改变。举个例子,索引优先于扫描,这是一个规则,优化器在遇到所有可以利用索引的地方,都不会选择扫描。这在多数情况下是正确的,但也不完全如此:   比如一张个人信息表中性别栏目加上索引,由于性别是只有2个值的枚举类,也就是常说的基数非常

代元培 2030

PLC批量读写优化:C# Modbus TCP多寄存器批量操作(效率提升50%)

效率提升:请求次数减少90%以上,通信耗时降低50%-95%;资源节约:减少网络带宽占用和PLC/上位机CPU负载;稳定性提升:更少的请求意味着更少的通信异常;适配高频采集:满足工业现场100ms/次的高频率数据采集需求。工业落地时需注意:批量操作的核心是地址连续化规划,提前与PLC工程师协同规划寄存器地址,是优化的前提;同时需配合重试、校验机制,确保批量操作的可靠性。

这里只有干货:从 Modbus 通信到 MES 对接,从 YOLO 训练到工控机部署,带你搞定工业软件开发全流程。 1262

Greenplum性能优化之路 --(三)ANALYZE

为什么需要ANALYZE 首先介绍下RBO和CBO,这是数据库引擎在执行SQL语句时的2种不同的优化策略。 RBO(Rule-Based Optimizer) 基于规则的优化器,就是优化器在优化查询计划的时候,是根据预先设置好的规则进行的,这些规则无法灵活改变。举个例子,索引优先于扫描,这是一个规则,优化器在遇到所有可以利用索引的地方,都不会选择扫描。这在多数情况下是正确的,但也不完全如此: 比如 一张个人信息表中性别栏目加上索引,由于性别是只有2个值的枚举类,也就是常说的基数非常低的列,在这种列上

jc_benben的专栏 909

基于Python的FTA协定内容爬虫实战:从数据抓取到智能分析

本文将详细介绍如何使用Python构建一个高效的FTA(自由贸易协定)内容爬虫,涵盖最新技术如Playwright、异步IO、智能解析等。通过本教程,您将学会如何从各类政府网站和国际组织平台抓取结构化FTA数据,并对其进行清洗、存储和分析。文章包含完整的代码实现和最佳实践指南,适合中高级Python开发者学习网络爬虫开发。

2201_76125261的博客 994

IDEA的使用指南

IDEA基于java的使用手册,可以最快速的上手,在使用中不断的丰富自己的使用技巧.

GreenPlum ANALYZE

ANALYZE Collectsstatistics about a database. 概要 ANALYZE [VERBOSE][ROOTPARTITION [ALL]] [table[(column [,...])]] 描述 分析有关的表的数据库中的系统表pg_statistic里的内容,并存储该结果收集统计信息。随后,Greenplum数据引擎使用这些数据来帮助判断查询的最有效的...

MyySophia的博客 1631

汉书HE8,好用的汇编代码编写工具

汉书HE8,好用的汇编代码编写工具早期编写汇编代码所用功能强大

Greenplum explain analyze 解读 + 深度明细开关

标签 PostgreSQL , Greenplum , explain analyze , 解读 , 深度明细 , explain_memory_verbosity , gp_enable_explain_allstat 背景 解读Greenplum 的explain analyze输出。同时介绍两个深度明细开关explain_memory_verb...

weixin_34195546的博客 1974

SAP B1中文培訓教材_SDK

公司内部培训资料,对开发工具 ,DIAPI, UIAPI,Add-ON有详细解说

Devexpress16.2.6破解工具

Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具

greenplum - 简单常用查看gp状态命令

启动/关闭 gpstart gpstop -M fast gpstop -m gpstop --host sdw1 # 只关一个节点 查看状态 gpstate -b # brief status gpstate -c # show primary to mirror mappings gpstate -e # show segments with mirror status issues gpstate -f # show standby master details gpstate -i # .

chuckchen1222的博客 9314

Deepgreen(Greenplum)数据表集中Analyze脚本

数据加载完成以后,通常需要更新分析计划,因为单独做一个表的Analyze比较麻烦,整库做Analyze会比较耗时,我们可以通过下面这个脚本,指定Analyze部分表。具体实践时,只需修改下面对应的SQL语句、数据库psql命令地址、IP端口及数据库名即可: #!/bin/bash # filename: analyze_table.sh # start ...

weixin_34293141的博客 292

《Greenplum5.0 最佳实践 》SQL 转换

原文链接:点击打开链接 摘要: 本文主要是Greenplum SQL 的优化 改变 SQL 查询 Greenplum 数据库是基于代价的查询优化,查询优化器会选择代价最小的作为执行计划。 像其他的 RDBMS 优化器一样, Greenplum的查询优化器也会考虑如下因素,例如做连接操作涉及的记录数量,索引是否可用, 访问数据的字段基数。查询优化器还要考虑数据的具体位置

qq_40954115的博客 652

Greenplum 数据库调优

Greenplum 数据库调优 目录 Greenplum 数据库调优 1 目录 1 1 Greenplum查询处理回顾 2 1.1 Master 把查询语句分发到segment 2 2 Greenplum数据库调优 3 2.1 系统资源 3 2.2 硬件问题 4 2.3 资源管理 5 2.3.1 查看resource queue的参数 5 2.3.2 设置临时的内存大小 6 ...

知识的力量 1447

GreenPlum优化点之参数篇

针对GP的性能优化目前可以参考做到的有SQL/索引、查询优化以及参数调整等方向,本篇针对参数调优做出详细规划

JxiaoZhang的博客 3817

Greenplum-性能问题常见原因

注:本文翻译自。

数据源的港湾 1020

GreenPlum数据加载方式总结

在GreenPlum中有以下几种方式来进行数据的加载,包括 通过insert命令来实现少量数据的导入; 通过copy命令来实现数据的导入导出; 通过建立外部表及gpfdist实现数据的导入; 通过gpload实现数据的导入导出; 下面将分别介绍这几种数据加载方式。 insert命令 通过简单的insert语句来实现,常用于少量数据的导入,当数据量较大时,会很耗时,从而不适合使用。 insert...

hmxz2nn的博客 3322

GreenPlum数据加载

1. copy命令   对于数据加载,GreenPlum数据库提供copy工具,copy工具源于PostgreSQL数据库,copy命令支持文件与表之间的数据加载和表对文件的数据卸载。使用copy命令进行数据加载,数据需要经过Master节点分发到Segment节点,同样使用copy命令进行数据卸载,数据也需要由Segment发送到Master节点,由Master节点汇总后再写入外部文件,这样就...

weixin_34255055的博客 297
上一篇: Greenplum 性能优化之路 --(二)存储格式
下一篇: Elasticsearch 新机型发布,性能提升 30%
腾讯云大数据
博客等级 码龄6年 348粉丝 220原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值