greenplumMPP数架构 MPP架构是将任务并行的分散到多个服务器和节点上，在每个节点上计算完成后，将各自部分的结

特点：MPP数架构

MPP架构是将任务并行的分散到多个服务器和节点上，在每个节点上计算完成后，将各自部分的结果汇总在一起得到最终的结果。

我们为什么要用GP：

传统数据库无法支持大规模集群与PB级别数据量

单台机器性能受限、成本高昂，扩展性受限

支持复杂的结构化查询 （这里是重点）

复杂查询经常使用多表联结、全表扫描等，牵涉的数据量往往十分庞大；支持复杂sql查询和支持大数据规模；

架构

主要包含Master Node和Compute Node两大组件，中间通过Interconnect进行互联通信和数据交换传输。master 负责sql的解析和优化，并生成分布式执行计划，发送给segment节点并执行。

协调节点Master：分为一个Main Master和多个Secondary Master，其接受客户端请求，并进行SQL的解析和优化。同时Main Master构建了GTM全局事务管理模块，维护全局统一的事务ID （Global XID）和当前活跃事务列表（Snapshot），从而实现严格的SERIALIZABLE、READ COMMITTED 隔离级别，保证各个节点间事务的强一致性。 Master节点含有采用Cascade架构SQL优化器，将RBO(Rule-Based Optimization 基于规则的优化器)和CBO(Cost-Based Optimization 基于代价的优化器)统一结合，同时可以自动的优化改写关联子查询等复杂SQL语句，实现计算按最优的分布式计划执行，规避复杂SQL的手工调优改写。

计算节点 Segment：Segment节点可水平扩展，节点支持多副本存储，表支持按行或者按列进行数据存储。当面向交易场景时，行存储提供高吞吐的事务处理能力，面向分析场景时，列存储及多种索引机制等，提供高性能的聚合分析，以及数据高压缩比。

Master Node和Compute Node提供多副本保障服务高可用和数据高可靠，同时均支持通过Scale Out水平扩展来提高集群整体写入查询并发和吞吐。

数据存储与分布

数据和索引（Data & Index）支持行存表，列存表，和外表以及相应索引：

行存表：数据按行存放，支持主键，B+树索引，Bitmap索引，GIN索引等，适合数据实时写入更新删除，点查，范围查，通过MVCC提供事务能力。
列存表：数据按列存放，高压缩比，适合追加写（少量更新删除）场景。通过B+树索引支持高效点查，同时在block级别提供min&max轻量级索引，数据可按多列进行多维排序，支持任意排序列的组合过滤，支持高效分析场景。

Greenplum 还提供以下存储方式（HTAP实锤）:

堆表（Heap Table）：堆表是 Greenplum 的默认存储方式，也是 PostgreSQL 的存储方式。支持高效的更新和删除操作，访问多列时速度快，通常用于 OLTP 型查询。
Append-Optimized 表：为追加而专门优化的表存储模式，通常用于存储数据仓库中的事实表。不适合频繁的更新操作。
AOCO (Append-Optimized, Column Oriented) 表：AOCO 表为列表，具有较好的压缩比，支持不同的压缩算法，适合访问较少的列的查询场景。
外部表：外部表的数据存储在外部（数据不被Greenplum管理），Greenplum 中只有外部表的元数据信息。Greenplum 支持很多外部数据源譬如 S3、HDFS、文件、Gemfire、各种关系数据库等和多种数据格式譬如 Text、CSV、Avro、Parquet 等。(cdc Hive表大都是textfile格式，openORC格式的表对数据下载比较友好，但是对入GP不友好)

根据分布式key进行存储

下图展示了一张用户表显示通过orderKey列hash分布到3个节点，然后在每个节点上按date列进行范围分区，然后再按其他列进行列表分区。图中最右边的每个分区都对应了一份数据存储和索引。这些分区表可以是行存表，也可以是列存表，比如业务上完全可以对最近需要写入的分区使用行存表，过去已经归档的分区使用列存表，出于降低成本考虑，也可以对较少查询的分区使用外表。

将表数据均匀的分布到各个节点中，是发挥集群整体IO性能，提升存储容量，优化计算与网络传输效率的关键。除了默认的哈希分布策略，GP还支持复制分布和随机分布。复制分布是指在每个存储节点上都存放该表的全量数据，通常用于经常被关联查询的小表，在执行相应查询时无需数据广播或重分布环节，提升查询性能。另外也支持随机分布策略，主要场景是当前表字段中无合适字段作为hash分布列（比如会引起各个节点数据倾斜），同时该表也不小（不适合复制策略），随机分布可以让该表数据被均匀摆放到个节点。

在将表数据分布到各个存储节点后，在单个节点上根据业务场景可对表数据进行分区，在执行具体查询时进行分区裁剪，缩小查找和数据处理范围。GP支持范围和列表分区类型，同时支持多级分区。

组件交互与执行计划

下图展示了客户端从建立连接到执行一条完整SQL整个过程中上述主要模块组件的交互和执行流程。

执行计划类似于一棵有节点的树，执行和阅读的顺序是自底而上。计划中的每个节点表示一个操作，例如表扫描、表连接、聚集或者排序。

阅读的顺序是从底向上：每个节点会把结果输出给直接在它上面的节点。一个计划中的底层节点通常是表扫描操作：顺序扫描表、通过索引或者位图索引扫描表等。如果该查询要求那些行上的连接、聚集、排序或者其他操作，就会有额外的节点在扫描节点上面负责执行这些操作。最顶层的计划节点通常是数据库的移动（MOTION）节点：重分布（REDISTRIBUTE）、广播（BROADCAST）或者收集（GATHER）节点，这些操作在查询处理时在实例节点之间移动数据。

EXPLAIN的输出对于执行计划中的每个节点都显示为一行并显示该节点类型和下面的执行的代价估计：

cost：以磁盘页面获取为单位度量。1.0等于一次顺序磁盘页面读取。第一个估计是得到第一行的启动代价，第二个估计是得到所有行的总代价。
rows：这个计划节点输出的总行数。这个数字根据条件的过滤因子会小于被该计划节点处理或者扫描的行数。最顶层节点的是估算的返回、更新或者删除的行数。
width：这个计划节点输出的所有行的总字节数。

举例：

EXPLAIN示例

以下示例描述了如何阅读一个查询的EXPLAIN查询代价：

EXPLAIN SELECT * FROM names WHERE name = 'Joelle';
         QUERY PLAN
------------------------------------------------------------
Gather Motion 4:1 (slice1) (cost=0.00..20.88 rows=1 width=13)
-> Seq Scan on 'names' (cost=0.00..20.88 rows=1 width=13)      
Filter: name::text ~~ 'Joelle'::text

查询优化器会顺序扫描names表，对每一行检查WHERE语句中的filter条件，只输出满足该条件的行。扫描操作的结果被传递给一个Gather Motion操作。Gather Motion是Segment把所有行发送给Master节点。 在这个例子中，有4个Segment节点会并行执行，并向Master节点发送数据。这个计划估计的启动代价是00.00（没有代价）而总代价是20.88次磁盘页面获取。优化器估计这个查询将返回一行数据。

EXPLAIN ANALYZE

EXPLAIN ANALYZE除了显示执行计划还会运行语句。EXPLAIN ANALYZE计划会把实际执行代价和优化器的估计一起显示，同时显示额外的下列信息：

查询执行的总运行时间（以毫秒为单位）。
执行计划每个Slice使用的内存，以及为整个查询语句保留的内存。
计划节点操作中涉及的Segment节点数量，其中只会统计返回行的Segment。
操作产生最多行的Segment节点返回的行最大数量。如果多个Segment节点产生了相等的行数，EXPLAIN ANALYZE会显示那个用了最长结束时间的Segment节点。
为一个操作产生最多行的Segment节点的ID。
相关操作使用的内存量（work_mem）。如果work_mem不足以在内存中执行该操作，计划会显示溢出到磁盘的数据量最少的Segment的溢出数据量。示例如下：

Work_mem used: 64K bytes avg, 64K bytes max (seg0).
Work_mem wanted: 90K bytes avg, 90K byes max (seg0) to lessen
workfile I/O affecting 2 workers.

产生最多行的Segment节点检索到第一行的时间（以毫秒为单位）以及该Segment节点检索到所有行花掉的时间。

下面的例子用同一个查询描述了如何阅读一个EXPLAIN ANALYZE查询计划。这个计划中粗体部分展示了每一个计划节点的实际计时和返回行，以及整个查询的内存和时间统计信息。

EXPLAIN ANALYZE SELECT * FROM names WHERE name = 'Joelle';
QUERY PLAN
------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..20.88 rows=1 width=13)
Rows out: 1 rows at destination with 0.305 ms to first row, 0.537 ms to end, start offset by 0.289 ms.
    -> Seq Scan on names (cost=0.00..20.88 rows=1 width=13)
Rows out: Avg 1 rows x 2 workers. Max 1 rows (seg0) with 0.255 ms to first row,
0.486 ms to end, start offset by 0.968 ms.
        Filter: name = 'Joelle'::text
Slice statistics:
    (slice0) Executor memory: 135K bytes.
    (slice1) Executor memory: 151K bytes avg x 2 workers, 151K bytes max (seg0).
Statement statistics: Memory used: 128000K bytes Total runtime: 22.548 ms

运行这个查询花掉的总时间是22.548毫秒。Sequential scan操作只有Segment（seg0）节点返回了1行，用了0.255毫秒找到第一行且用了0.486毫秒来扫描所有的行。Segment向Master发送数据的Gather Motion接收到1行。这个操作的总消耗时间是0.537毫秒。

SQL调优

1.慢sql定位

pg_stat_activity是adbpg用来定位实例当前执行查询的系统视图，每行显示一个服务器进程同时详细描述与之关联的用户会话和查询。这些列报告当前查询上可用的数据，除非参数stats_command_string被关闭。此外，只有在检查视图的用户是超级用户或者是正在报告的进程的拥有者时，这些列才可见。

名称	类型	描述
datid	oid	数据库OID，可通过pg_database视图获取。
datname	name	数据库名称。
pid	integer	服务进程的进程ID。
sess_id	integer	会话ID。
usesysid	oid	角色ID，可通过pg_authid视图获取。
usename	name	角色名。
current_query	text	进程正在执行的当前查询。
waiting	boolean	如果正在等待一个锁则为true，否则为false。
query_start	timestamptz	查询开始执行的时间。
backend_start	timestamptz	后台进程开始的时间。
client_addr	inet	客户端地址。
client_port	integer	客户端端口。
applicaton_name	text	客户端应用名。
xact_start	timestamptz	事务开始时间。
waiting_reason	text	服务进程正在等待的原因。

您可以通过以下SQL来查询youdata用户在近30分钟内发起的，且当前还没执行完的查询。

select ***** from pg_stat_activity where xact_start < now() - interval '30 min' and waiting = 'f' and usename = ' youdata ' ;

2.算子调优

explain结果中常见的算子集合：

表扫描表扫描操作算子（SCAN）扫描表中的行以寻找一个行的集合，包括以下一些类型：

- Seq Scan ：顺序扫描表中的所有行。
- Append-only Scan ：扫描行存追加优化表。
- Append-only Columnar Scan ：扫描列存追加优化表中的行。
- Index Scan ：遍历一个B树索引以从表中取得行。
- Bitmap Append-only Row-oriented Scan ：从索引中收集仅追加表中行的指针并且按照磁盘上的位置进行排序。
- Dynamic Table Scan — 使用一个分区选择函数来选择分区。
- Function Scan节点包含分区选择函数的名称，可以是下列之一：Function Scan节点将动态选择的分区列表传递给Result节点，该节点又会被传递给Sequence节点。

- - gp_partition_expansion ：选择表中的所有分区。
  - gp_partition_selection ：基于一个等值表达式选择一个分区。
  - gp_partition_inversion ：基于一个范围表达式选择分区。

表连接表连接操作算子（JOIN）包括以下一些类型：

- Hash Join：从较小的表构建一个哈希表，用连接列作为哈希键扫描较大的表，为连接列计算哈希键并寻找具有相同哈希键的行。哈希连接通常是数据库中最快的连接。计划中的Hash Cond标识要被连接的列。
- Nested Loop Join：选择在较大的表作为外表，迭代扫描较小的表中的行。Nested Loop Join要求广播其中的一个小表，这样一个表中的所有行才能与其他表中的所有行进行连接操作。Nested Loop Join在较小的表或者通过使用索引约束的表上执行得不错，但在使用Nested Loop连接大型表时可能会有性能影响。
- Merge Join：排序两个表并且将它们连接起来。 对于预排序好的数据很快。

移动操作移动操作算子（MOTION）在Segment节点之间移动数据，包括以下一些类型：

- Broadcast motion ：每一个Segment节点将自己的行发送给所有其他Segment节点，这样每一个Segment节点都有表的一份完整的本地拷贝。优化器通常只为小型表选择Broadcast motion。对大型表来说，Broadcast motion是会比较慢的。 在连接操作没有按照连接键分布的情况下，可能会将把一个表中所需的行动态重分布到别的Segment节点上。
- Redistribute motion ： 每一个Segment节点重新哈希数据并且把行发送到对应于哈希键的Segment节点上。
- Gather motion ：来自所有Segment的结果数据被合并在一起发送到节点上（通常是Master节点）。对大部分查询计划来说这是最后的操作。

其他算子查询计划中出现的其他操作算子包括：

- Materialize ：优化器将一个子查询结果进行物化。
- InitPlan ：预查询，被用在动态分区消除中，当执行时还不知道优化器需要用来标识要扫描分区的值时，会执行这个预查询。
- Sort ：为另操作（例如Aggregation或者Merge Join）进行所有数据排序。
- Group By ：通过一个或者更多列对行进行分组。
- Group/Hash Aggregate ：使用哈希对行进行聚集操作。
- Append ：串接数据集，例如在整合从分区表中各分区扫描的行时会用到。
- Filter ：使用来自于一个WHERE子句的条件选择行。
- Limit ：限制返回的行数。

根据执行计划指导sql调优

自上而下，梳理痛点从上向下梳理计划，查看时间到底花在了什么算子上面，然后针对具体算子深入分析。
查看代价，对比行数查看比较代价估算的异常（特别小或特别大），对比估算行数和实际执行行数，找到代价估算和行数估算的问题。
耗时算子，尽量避免AP场景很少需要NestLoop、Sort+GroupByAgg，遇到它们需要谨慎。
具体算子，是否合理

- Motion：是否有不必要的Motion？是否可以优化分布键？是否可以使用复制表？
- Join：内外表顺序是否合理？
- Scan：是否可以使用索引？是否可以使用分区表？

内存信息，调整参数查看下盘情况，分析后适当调整statement_mem参数。

Update (cost=0.00..1274.11 rows=1 width=1) (actual time=995096.707..1517097.191 rows=245136 loops=1)
  Executor Memory: 1kB  Segments: 96  Max: 1kB (segment 0)
  ->  Partition Selector for t2 (cost=0.00..1274.04 rows=1 width=842) (actual time=995096.480..1514408.806 rows=245136 loops=1)
    ->  Redistribute Motion 96:96 (slice2; segments: 96) (cost=0.00..1274.04 rows=1 width=838) (actual time=995096.440..1513830.155 rows=245136 loops=1)
          Hash Key: t2.c1, t2.c2
      ->  Split (cost=0.00..1274.04 rows=1 width=838) (actual time=995080.103..1496878.037 rows=245136 loops=1)
            Executor Memory: 1kB  Segments: 96  Max: 1kB (segment 0)
        ->  Hash Join (cost=0.00..1274.04 rows=1 width=1484) (actual time=995080.071..1496625.817 rows=122568 loops=1)
              Hash Cond: ((t1.c1)::text = (t2.c1)::text)
              Executor Memory: 33535270kB  Segments: 96  Max: 349326kB (segment 33)
              work_mem: 33535270kB  Segments: 96  Max: 349326kB (segment 33)  Workfile: (96 spilling)
              Work_mem wanted: 26684983K bytes avg, 26684983K bytes max (seg0) to lessen workfile I/O affecting 96 workers.
          ->  Seq Scan on t1 (cost=0.00..672.28 rows=121412 width=736) (actual time=672.771..1039.167 rows=122568 loops=1)
                Filter: ((t1.c2 = '2019-05-17'::date) AND ((t1.c3)::text = '0'::text))
          ->  Hash (cost=431.00..431.00 rows=1 width=762) (actual time=994417.443..994417.443 rows=34583155 loops=1)
            ->  Broadcast Motion 96:96 (slice1; segments: 96) (cost=0.00..431.00 rows=1 width=762) (actual time=25.562..912862.203 rows=34583155 loops=1)
              ->  Sequence (cost=0.00..431.00 rows=1 width=762) (actual time=34.475..4822.173 rows=361460 loops=1)
                ->  Partition Selector for t2 (dynamic scan id: 1) (cost=10.00..100.00 rows=2 width=4) (never executed)
                      Partitions selected: 27 (out of 27)
                ->  Dynamic Seq Scan on t2 (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=762) (actual time=34.441..4680.938 rows=361460 loops=1)
                      Partitions scanned:  Avg 27.0 (out of 27) x 96 workers.  Max 27 parts (seg0).

上面是explain analyze一个SQL语句输出的执行计划，您可以按照之前的指导来定位问题：

先自上而下梳理耗时算子，通过查看每个算子的actual time可以看到hash join执行时间很长。
从hash join算子往下梳理，发现内表有下盘操作，如下 “work_mem: 33535270kB Segments: 96 Max: 349326kB (segment 33) Workfile: (96 spilling) Work_mem wanted: 26684983K bytes avg, 26684983K bytes max (seg0) to lessen workfile I/O affecting 96 workers.” 。
hash join的内表在build phase时有广播操作，如下 “Broadcast Motion 96:96 (slice1; segments: 96) (cost=0.00..431.00 rows=1 width=762) (actual time=25.562..912862.203 rows=34583155 loops=1)” ，从这里可以看到优化器预估的表t1的行数为1，与实际验证不符。
基于以上诊断可以得出，由于没有及时收集表t1的统计信息，导致优化器认为t1为一张小表，从而在hash join时将t1广播到各个节点，并且以t1作为内表构建hash table导致t1下盘，最终导致整个SQL执行耗时长。

以上问题的解决方案是重新收集一遍t1统计数据：analyze t1;

3.索引相关

在大部分传统的TP型数据库中，索引可以极大的提高数据的访问效率。但是在类似与GreenPlum这样的分布式数据库中，应该谨慎的选择索引的使用。在大部分场景下，GreenPlum更适合快速的顺序扫描，或者结合稀疏索引来进行减少数据的I/O操作。GreenPlum会将数据尽量均匀地分布在所有的计算节点上，因此，在节点足够多的情况下，每一个计算节点只会扫描属于自己的一小部分数据。并且对于BI报表类查询，通常会返回很大的数据集，使用索引在这种场景并不一定有加速查询的效果。

在使用GreenPlum时，首先应该尝试在没有增加任何索引的情况下执行您的查询。索引通常都是更适合于TP场景的，只返回一条记录或者返回极少量数据集的数据。使用索引也会给数据库带来一些额外的开销，比如需要更多的存储，以及数据的写放大，还有包括在进行数据update时的索引维护工作的开销。因此我们需要确保我们为表增加的索引相对于全表扫描，能够切实、有效地提高了查询效率，否则宁愿不建索引。

4.一般SQL优化建议

优化能力

空值IN条件或OR条件的条目数量，过多的条目会导致RCA优化时间加长。
尽量避免在WHERE条件中使用复杂表达式或函数操作，可能导致优化器行数估算不准确。

索引

为避免全表扫描，可以在WHERE条件涉及的列上添加索引。
WHERE条件避免使用 !=或 <> 操作符号，只有在=、 < 、 <=、 > 、 >=、between时才可用到索引。
WHERE条件中尽量避免使用OR条件。随着被OR的条件增多，优化器越发倾向于不适用索引。当您发现一条SQL预期使用某一列上的索引而实际未使用时，重点排查OR是否过多，有条件可以使用union来改写该SQL。

数据类型

尽量使用数值类型，避免使用字符串类型，字符串类型会降低查询和连接性能。
尽量使用varchar(n)替代char(n)，节省存储空间，减少计算内存，加速字符串比较效率。

数据列

尽量避免使用**SELECT *** ，从业务角度优化需要输出的列。

临时表

复杂查询可使用临时表暂存中间结果，一方面方便业务调试，另一方面避免不必要的重复计算。

WHERE条件

如果有IN子句，尽量将出现频率高的值放在IN子句的前面，减少比较次数。

JOIN

一个查询中产于JOIN的表数量控制在12个以内，多于12个表JOIN，可以考虑使用临时表拆分语句。

存储过程或函数

能使用SQL语句实现的，不要用循环去实现。

避免下盘

在GP查询执行过程中，当集群内存不足时，数据库可能会选择将临时结果暂存到磁盘。 由于磁盘操作相对内存访问缓慢，避免查询执行过程中的算子下盘有助于提高查询效率。

下盘原因

在数据量较大的表上执行SORT、JOIN、HASH等操作时，可能由于内存不足导致临时结果落盘。您通过观察执行计划（explain analyze）可以辨认发生了算子下盘：

上图是一个发生了算子落盘的查询计划例子，执行计划中Workfile这一项显示了是否发生了算子落盘。而不发生算子落盘的执行计划对应项会显示为0，如下图所示：

产生算子下盘的常见原因包括：

查询能够使用的内存太小。
查询的计算量过大，需要的内存太大。
产生了数据倾斜。

下面详细介绍三种原因导致的算子下盘场景及解决方法。

常见算子下盘场景及解决方法

查询内存太小导致的算子下盘

通过观察执行计划发现，算子需要的内存并不大，只有几K或几M，但还是发生了算子下盘。这种情况往往是查询能够使用的内存调小导致的，原因可能是收到了resource group或resource queue的限制，或者statement_mem参数本身设置的不合理。

对于这种情况，您可以通过调大statement_mem参数到一个合理的参数来避免算子下盘：

SET statement_mem TO '256MB';

计算量过大导致的算子下盘

在某些时候，我们发现我们已经设置了较大的查询内存（statement_mem）,但我们通过执行计划发现，算子执行过程中需要的内存远远大于我们设置的内存，这个时候往往是计算量过大导致的。这个时候我们需要考虑能够执行analyze、建立索引等方式减少算子执行中的计算量。

以图中的执行计划为例，我们发现较大的算子落盘，进一步分析我们发现，在这个执行计划中，错误了估计了t2子表的行数（rows），导致t2一个大表被估计为1行的小表，进行了broadcast，并做了hashjoin的内标，导致了巨大的计算量。我们对t2表执行了analyze之后，消除了算子下盘。

数据倾斜导致的算子下盘

数据倾斜也是一种常见的会导致算子下盘的因素，数据倾斜会导致单个segment上的数据量和计算量远远超过其他segment，导致可用内存不够算子下盘。

5.空间回收

表中的数据被删除或更新后（UPDATE/DELETE），物理存储层面并不会直接删除数据，而是标记这些数据不可见，所以会在数据页中留下很多“空洞”，在读取数据时，这些“空洞”会随数据页一起加载，拖慢数据扫描速度，需要定期回收删除的空间。

VACUUM [FULL] [FREEZE] [VERBOSE] [table];

VACUUM 会在页内进行整理，VACUUM FULL会跨数据页移动数据。 VACUUM执行速度更快， VACUUM FULL执行地更彻底，但会请求排他锁。建议定期对系统表进行VACUUM（每周一次）。

什么情况下做VACUUM？

不锁表回收空间，只能回收部分空间。
频率：对于有较多实时更新的表，每天做一次。
如果更新是每天一次批量进行的，可以在每天批量更新后做一次。
对系统影响：不会锁表，表可以正常读写。会导致CPU、I/O使用率增加，可能影响查询的性能。

什么情况下做VACUUM FULL？

锁表，通过重建表，回收所有空洞空间。对做了大量更新后的表，建议尽快执行VACUUM FULL。
频率：至少每周执行一次。如果每天会更新几乎所有数据，需要每天做一次。
对系统影响：会对正在进行vacuum full的表锁定，无法读写。会导致CPU、I/O使用率增加。建议在维护窗口进行操作。

查询需要执行VACUUM的表

GP提供了一个gp_bloat_diag视图，统计当前页数和实际需要页数的比例。通过analyze table来收集统计信息之后，查看该视图。

其结果只包括发生了中度或者显著膨胀的表。当实际页面数和预期页面的比率超过4但小于10时，就会报告为中度膨胀。当该比率超过10时就会报告显著膨胀。对于这些表，可以考虑进行VACUUM FULL来回收空间。可以看出，相关的表并未发生中度或者显著膨胀。

📎GP基础.pdf