首页 > 代码库 > 用HAWQ轻松取代传统数据仓库（十二） —— 查询优化

用HAWQ轻松取代传统数据仓库（十二） —— 查询优化

2024-09-14 23:40:12   220人阅读

        即便对SELECT等数据库查询语句已经很熟悉了，但HAWQ里的查询有其自己的特点，还是需要研究一下。

一、HAWQ的查询处理流程
        理解HAWQ的查询处理过程有助于写出更加优化的查询。与任何其它数据库管理系统类似，HAWQ也有如下的查询执行步骤：
  1. 用户使用客户端应用（如psql）连接到HAWQ master主机上的数据库实例，并向系统提交SQL语句。
  2. master接收到查询后，由查询编译器解析提交的SQL语句，并将生成的查询解析树递交给给查询优化器。
  3. 查询优化器根据查询的磁盘I/O、网络流量等成本信息，生成它认为最优的执行计划，并将查询计划交给查询分发器。
  4. 查询分发器依照查询计划的成本信息，向HAWQ资源管理器请求所需的资源。
  5. 获得资源后，查询分发器在segment上启动虚拟段，并向虚拟段分发查询计划。
  6. 查询执行器使用多个虚拟段并行执行查询，最终结果传送至master，最后向客户端返回查询结果。
        HAWQ基本的查询处理流程如图1所示。
技术分享
图1
1. 查询计划
        一个查询计划是HAWQ为了产生查询结果而要执行的一系列操作。查询计划中的每个节点或步骤，表示一个数据库操作，如表扫描、连接、聚合、排序等等。查询计划被由底向上读取和执行。
        除了通常的扫描、连接等数据库操作，HAWQ还有一种叫做motion的操作类型。查询处理期间，motion操作通过内部互联网络在节点间移动数据。注意并不是每个查询都需要motion操作。
        为了实现查询执行的最大并行度，HAWQ将查询计划分成多个slice，每个slice可以在segment上独立执行。查询计划中的motion操作总是分片的，迁移数据的源和目标上各有一个slice。
        下面的查询连接两个数据库表：
SELECT customer, amount
  FROM sales JOIN customer USING (cust_id)
 WHERE dateCol = ‘04-30-2016‘;
        图2显示了为该查询生成的三个slice。每个segment接收一份查询计划的拷贝，查询计划在多个segment上并行工作。
技术分享
图2
        注意slice 1中的redistribute motion操作，它在段间移动数据以完成表连接。假设customer表通过cust_id字段在segment上分布，而sales表通过sale_id字段分布。为了连接两个表，sales的数据必须通过cust_id重新分布。因此查询计划在每个分片上各有一个redistribute motion操作。
        在这个执行计划中还有一种称为gather motion的motion操作。当segment将查询结果发送回master，用于向客户端展示时，会使用gather motion。因为查询计划中发生motion的部分总是被分片，所以在图2的顶部还有一个隐含的slice 3。并不是所有查询计划都包含gather motion，例如，CREATE TABLE x AS SELECT ... 语句就没有gather motion操作，因为结果数据被发送到新表而不是master。

2. 并行执行
        HAWQ会创建许多数据库进程处理一个查询。master和segment上的查询工作进程分别被称为查询分发器（query dispatcher，QD）和查询执行器（query executor，QE）。QD负责创建和分发查询计划，并返回最终的查询结果。QE在虚拟段中完成实际的查询工作，并与其它工作进程互通中间结果。
        查询计划的每个slice至少需要一个工作进程。工作进程独立完成被赋予的部分查询计划。一个查询执行时，每个虚拟段中有多个并行执行的工作进程。
        工作在不同虚拟段中的相同slice构成一个gang。查询计划被从下往上执行，一个gang的中间结果数据向上流向下一个gang。不同虚拟段的进程间通信是由HAWQ的内部互联组件完成的。
        图3显示了示例中master和segment上的工作进程，查询计划分成了三个slice，两个segment上的相同slice构成了gang。
技术分享
图3

二、GPORCA查询优化器
        当前HAWQ缺省使用的查询优化器是GPORCA，遗留的老优化器与GPORCA是并存的。HAWQ尽可能使用GPORCA生成查询的执行计划，当GPORCA没有启用或无法使用时，HAWQ用老的查询优化器生成执行计划。可以通过EXPLAIN命令的输出确定查询使用的是哪种优化器。GPORCA会忽略与老优化器相关的服务器配置参数，但当查询使用老优化器时，这些参数仍然影响查询计划的生成。相对于老优化器，GPORCA在多核环境中的优化能力更强，并且在分区表查询、子查询、连接、排序等操作上提升了性能。图4显示了HAWQ中的GPORCA。
技术分享
图4

1. GPORCA的改进
（1）分区表查询
        GPORCA在查询分区表时做了以下增强：
  • 改进分区消除。
  • 查询计划中包含了分区选择器操作符。
  • 如果查询中分区键与常量进行比较，GPORCA在EXPLAIN输出中的分区选择器操作符下列出需要扫描的分区数。如果查询中分区键与变量进行比较，只有在查询执行时才能知道需要扫描的分区数，因此EXPLAIN的输出中无法显示选择的分区。
  • 查询计划的大小与分区数量无关。
  • 减少了由于分区数量引起的内存溢出错误（Out of memory，OOM）。
        下面看一个分区表查询的例子。
db1=# create table sales (order_id int, item_id int, amount numeric(15,2), date date, yr_qtr int)  
db1-# partition by range (yr_qtr)  
db1-# ( partition p201701 start (201701) inclusive ,  
db1(#   partition p201702 start (201702) inclusive ,  
db1(#   partition p201703 start (201703) inclusive ,  
db1(#   partition p201704 start (201704) inclusive ,  
db1(#   partition p201705 start (201705) inclusive ,  
db1(#   partition p201706 start (201706) inclusive ,  
db1(#   partition p201707 start (201707) inclusive ,  
db1(#   partition p201708 start (201708) inclusive ,  
db1(#   partition p201709 start (201709) inclusive ,  
db1(#   partition p201710 start (201710) inclusive ,  
db1(#   partition p201711 start (201711) inclusive ,  
db1(#   partition p201712 start (201712) inclusive  
db1(#                   end (201801) exclusive );
...
CREATE TABLE
db1=#
        GPORCA改进了分区表上以下类型的查询：
  • 全表扫描时，查询计划中不罗列分区，只显示分区数量。
    db1=# explain select * from sales;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..431.00 rows=1 width=24)
   ->  Sequence  (cost=0.00..431.00 rows=1 width=24)
         ->  Partition Selector for sales (dynamic scan id: 1)  (cost=10.00..100.00 rows=100 width=4)
               Partitions selected:  12 (out of 12)
         ->  Dynamic Table Scan on sales (dynamic scan id: 1)  (cost=0.00..431.00 rows=1 width=24)
 Settings:  default_hash_table_bucket_number=24
 Optimizer status: PQO version 1.684
(7 rows)
  • 查询中如果包含常量过滤谓词，执行分区消除。下面的查询只需要扫描12个分区中的1个。
    db1=# explain select * from sales where yr_qtr = 201706;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..431.00 rows=1 width=24)
   ->  Sequence  (cost=0.00..431.00 rows=1 width=24)
         ->  Partition Selector for sales (dynamic scan id: 1)  (cost=10.00..100.00 rows=100 width=4)
               Filter: yr_qtr = 201706
               Partitions selected:  1 (out of 12)
         ->  Dynamic Table Scan on sales (dynamic scan id: 1)  (cost=0.00..431.00 rows=1 width=24)
               Filter: yr_qtr = 201706
 Settings:  default_hash_table_bucket_number=24
 Optimizer status: PQO version 1.684
(9 rows)
  • 范围选择同样执行分区消除。下面的查询扫描4个分区。
    db1=# explain select * from sales where yr_qtr between 201701 and 201704 ;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..431.00 rows=1 width=24)
   ->  Sequence  (cost=0.00..431.00 rows=1 width=24)
         ->  Partition Selector for sales (dynamic scan id: 1)  (cost=10.00..100.00 rows=100 width=4)
               Filter: yr_qtr >= 201701 AND yr_qtr <= 201704
               Partitions selected:  4 (out of 12)
         ->  Dynamic Table Scan on sales (dynamic scan id: 1)  (cost=0.00..431.00 rows=1 width=24)
               Filter: yr_qtr >= 201701 AND yr_qtr <= 201704
 Settings:  default_hash_table_bucket_number=24
 Optimizer status: PQO version 1.684
(9 rows)
  •  查询中包含子查询过滤谓词，查询计划中显示扫描全部12个分区，但运行时可以进行动态分区消除。
    db1=# explain select * from sales where yr_qtr = (select 201701);
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Hash Join  (cost=0.00..431.00 rows=1 width=24)
   Hash Cond: "outer"."?column?" = sales.yr_qtr
   ->  Result  (cost=0.00..0.00 rows=1 width=4)
         ->  Result  (cost=0.00..0.00 rows=1 width=1)
   ->  Hash  (cost=431.00..431.00 rows=1 width=24)
         ->  Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..431.00 rows=1 width=24)
               ->  Sequence  (cost=0.00..431.00 rows=1 width=24)
                     ->  Partition Selector for sales (dynamic scan id: 1)  (cost=10.00..100.00 rows=100 width=4)
                           Partitions selected:  12 (out of 12)
                     ->  Dynamic Table Scan on sales (dynamic scan id: 1)  (cost=0.00..431.00 rows=1 width=24)
 Settings:  default_hash_table_bucket_number=24; optimizer=on
 Optimizer status: PQO version 1.684
(12 rows)
（2）子查询
        GPORCA能够更有效地处理子查询，如下面的例子。
SELECT * FROM part WHERE price > (SELECT avg(price) FROM part);
        GPORCA也能高效处理相关子查询（correlated subquery，CSQ）。相关子查询在子查询中引用了外层查询的值，如下面的例子。
SELECT * FROM part p1 WHERE price > (SELECT avg(price) FROM part p2 WHERE  p2.brand = p1.brand);
        GPORCA为下面类型的相关子查询生成更有效的查询计划：
  • 相关子查询出现在SELECT列表中。
    SELECT *,
 (SELECT min(price) FROM part p2 WHERE p1.brand = p2.brand)
 AS foo
FROM part p1;
  • 相关子查询出现在OR过滤中。
    SELECT * FROM part p1 WHERE p_size > 40 OR 
      p_retailprice > 
      (SELECT avg(p_retailprice) 
         FROM part p2 
        WHERE p2.p_brand = p1.p_brand);
  • 多级嵌套相关子查询。
    SELECT * FROM part p1 WHERE p1.p_partkey 
IN (SELECT p_partkey FROM part p2 WHERE p2.p_retailprice = 
     (SELECT min(p_retailprice)
       FROM part p3 
       WHERE p3.p_brand = p1.p_brand));
  • 不等于条件的相关子查询。
    SELECT * FROM part p1 WHERE p1.p_retailprice =
 (SELECT min(p_retailprice) FROM part p2 WHERE p2.p_brand <> p1.p_brand);
  • 返回单行的相关子查询。
    SELECT p_partkey, 
  (SELECT p_retailprice FROM part p2 WHERE p2.p_brand = p1.p_brand )
FROM part p1;
（3）共用表表达式
        GPORCA能处理包含WITH子句的查询。WITH子句又被称为共用表表达式（common table expression，CTE），是在查询时系统自动生成的一个临时表。
db1=# create table t (a int,b int,c int);
CREATE TABLE
db1=# insert into t values (1,1,1), (2,2,2);
INSERT 0 2
db1=# with v as (select a, sum(b) as s from t where c < 10 group by a)
db1-#   select * from  v as v1 ,  v as v2
db1-#   where v1.a <> v2.a and v1.s < v2.s;
 a | s | a | s 
---+---+---+---
 1 | 1 | 2 | 2
(1 row)
        作为查询优化的一部分，GPORCA能将谓词过滤条件下推至CTE，如下面的查询。
db1=# explain 
db1-# with v as (select a, sum(b) as s from t group by a)
db1-#   select *
db1-#   from v as v1, v as v2, v as v3
db1-#   where v1.a < v2.a
db1-#     and v1.s < v3.s
db1-#     and v1.a = 10
db1-#     and v2.a = 20
db1-#     and v3.a = 30;
                            QUERY PLAN                                                        
-------------------------------------------------------------------------
 ...
   ->  Table Scan on t  (cost=0.00..431.00 rows=2 width=8)
         Filter: a = 10 OR a = 20 OR a = 30
 ...
 Settings:  default_hash_table_bucket_number=24
 Optimizer status: PQO version 1.684
(34 rows)
        GPORCA可以处理以下类型的CTE：
  • 一条查询语句中定义多个CTE。
    db1=# with cte1 as (select a, sum(b) as s from t 
db1(#                where c < 10 group by a),
db1-#       cte2 as (select a, s from cte1 where s > 1)
db1-#   select *
db1-#   from cte1 as v1, cte2 as v2, cte2 as v3
db1-#   where v1.a < v2.a and v1.s < v3.s;
 a | s | a | s | a | s 
---+---+---+---+---+---
 1 | 1 | 2 | 2 | 2 | 2
(1 row)
  • 嵌套CTE。
    db1=# with v as (with w as (select a, b from t 
db1(#                       where b < 5) 
db1(#            select w1.a, w2.b 
db1(#            from w as w1, w as w2 
db1(#            where w1.a = w2.a and w1.a > 1)
db1-#   select v1.a, v2.a, v2.b
db1-#   from v as v1, v as v2
db1-#   where v1.a <= v2.a; 
 a | a | b 
---+---+---
 2 | 2 | 2
(1 row)
（4）INSERT语句的提升
  • 查询计划中增加Insert操作符。
  • 引入Assert操作符用于约束检查。
    db1=# drop table t;
DROP TABLE
db1=# create table t (a int not null, b int, c int);
CREATE TABLE
db1=# explain insert into t values (1,1,1);
                            QUERY PLAN                            
------------------------------------------------------------------
 Insert  (cost=0.00..0.08 rows=1 width=12)
   ->  Result  (cost=0.00..0.00 rows=1 width=20)
         ->  Assert  (cost=0.00..0.00 rows=1 width=20)
               Assert Cond: NOT a IS NULL
               ->  Result  (cost=0.00..0.00 rows=1 width=20)
                     ->  Result  (cost=0.00..0.00 rows=1 width=1)
 Settings:  default_hash_table_bucket_number=24
 Optimizer status: PQO version 1.684
(8 rows)
（5）去重聚合
        GPORCA提升了一类去重聚合查询的性能。当查询中包含有去重限定的聚合操作（distinct qualified aggregates，DQA），并且没有分组列，表也不是以聚合列做的分布，则GPORCA在三个阶段计算聚合函数，分别是本地、中间和全局聚合。
db1=# explain select count(distinct b) from t;
                                               QUERY PLAN                                                 
----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=0.00..431.00 rows=1 width=8)
   ->  Gather Motion 1:1  (slice2; segments: 1)  (cost=0.00..431.00 rows=2 width=4)
         ->  GroupAggregate  (cost=0.00..431.00 rows=2 width=4)
               Group By: b
               ->  Sort  (cost=0.00..431.00 rows=2 width=4)
                     Sort Key: b
                     ->  Redistribute Motion 1:1  (slice1; segments: 1)  (cost=0.00..431.00 rows=2 width=4)
                           Hash Key: b
                           ->  GroupAggregate  (cost=0.00..431.00 rows=2 width=4)
                                 Group By: b
                                 ->  Sort  (cost=0.00..431.00 rows=2 width=4)
                                       Sort Key: b
                                       ->  Table Scan on t  (cost=0.00..431.00 rows=2 width=4)
 Settings:  default_hash_table_bucket_number=24
 Optimizer status: PQO version 1.684
(15 rows)
        optimizer_prefer_scalar_dqa_multistage_agg配置参数控制处理DQA的行为，该参数缺省是启用的。
[gpadmin@hdp3 ~]$ hawq config -s optimizer_prefer_scalar_dqa_multistage_agg
GUC		: optimizer_prefer_scalar_dqa_multistage_agg
Value		: on
[gpadmin@hdp3 ~]$
        启用该参数会强制GPORCA使用三阶段DQA计划，保证DQA查询具有可预测的性能。如果禁用该参数，则GPORCA使用基于成本的方法生成执行计划。

2. 启用GPORCA
        预编译版本的HAWQ缺省启用GPORCA查询优化器，不需要额外配置。当然也可以手工启用GPORCA，这需要设置以下两个配置参数。
  • 设置optimizer_analyze_root_partition参数收集分区表的根分区统计信息。
  • 设置optimizer参数启用GPORCA。
        分区表上使用GPORCA时必须用ANALYZE ROOTPARTITION命令收集根分区的统计信息。该命令只收集根分区统计信息，而不收集叶分区。作为一项例行的数据库维护工作，应该在分区表数据大量改变（如装载了大量数据）后刷新根分区的统计。

（1）设置optimizer_analyze_root_partition参数
  1. 以gpadmin用户登录HAWQ master主机设置环境。
    [gpadmin@hdp3 ~]$ source /usr/local/hawq/greenplum_path.sh
  2. 使用hawq config应用程序设置optimizer_analyze_root_partition参数。
    [gpadmin@hdp3 ~]$ hawq config -c optimizer_analyze_root_partition -v on
  3. 重载HAWQ配置。
    [gpadmin@hdp3 ~]$ hawq stop cluster -u
（2）在系统级启用GPORCA
  1. 以gpadmin用户登录HAWQ master主机设置环境。
    [gpadmin@hdp3 ~]$ source /usr/local/hawq/greenplum_path.sh
  2. 使用hawq config应用程序设置optimizer参数。
    [gpadmin@hdp3 ~]$ hawq config -c optimizer -v on
  3. 重载HAWQ配置。
    [gpadmin@hdp3 ~]$ hawq stop cluster -u
（3）在数据库级别启用GPORCA
        使用ALTER DATABASE命令设置一个数据库的优化器，例如：
db1=# alter database db1 set optimizer = on ;
ALTER DATABASE
（4）在会话级启用GPORCA
        可以使用SET命令在会话级别设置优化器参数，例如：
db1=# set optimizer = on ;
SET
        为特定查询指定GPORCA优化器时，在运行查询前执行该set命令。

3. 使用GPORCA需要考虑的问题
（1）使用GPORCA优化器的前提条件
        为了使用GPORCA优化器执行查询，应该满足以下条件：
  • 表不包含多列分区键。
  • 表不包含多级分区。
  • 不是查询仅存储在master上的表，如系统表。
    db1=# explain select * from pg_attribute;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Seq Scan on pg_attribute  (cost=0.00..62.70 rows=104880 width=103)
 Settings:  default_hash_table_bucket_number=24; optimizer=on
 Optimizer status: legacy query optimizer
(3 rows)
  • 已经收集了分区表的根分区统计信息。
  • 表中的分区数不要太多，如果一个表的分区数超过了20000，应该重新设计表模式。
（2）确定查询使用的优化器
        启用了GPORCA时，可以从EXPLAIN查询计划的输出中查看一个查询是使用了GPORCA还是老的优化器。如果使用的是GPORCA优化器，在查询计划的最后会显示GPORCA的版本，例如：
db1=# explain select * from sales where yr_qtr = 201706;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 ...
 
 Settings:  default_hash_table_bucket_number=24; optimizer=on
 Optimizer status: PQO version 1.684
(9 rows)
        如果查询使用了老的优化器生成的执行计划，输出的最后会显示“legacy query optimizer”。例如：
db1=# explain select 1;
                          QUERY PLAN                          
--------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0)
 Settings:  default_hash_table_bucket_number=24; optimizer=on
 Optimizer status: legacy query optimizer
(3 rows)
        下面的操作只会出现在GPORCA生成的执行计划中，老的优化器不支持这些操作。
  • Assert operator
  • Sequence operator
  • DynamicIndexScan
  • DynamicTableScan
  • Table Scan
（3）生成查询优化上下文
        GPORCA可以生成minidump文件描述给定查询的优化细节，该文件可被用来分析HAWQ的问题。minidump文件位于master的数据目录下，文件名称的格式为：
Minidump_date_time.mdp
        下面看一个生成minidump文件的例子。
  1. 运行一个psql会话，设置optimizer_minidump参数为always。
    [gpadmin@hdp3 ~]$ psql -d db1
psql (8.2.15)
Type "help" for help.

db1=# set optimizer_minidump=always;
SET
  2. 执行一个查询。
    db1=# select * from t;
 a | b | c 
---+---+---
 1 | 1 | 1
 1 | 2 | 2
(2 rows)
  3. 查看生成的minidump文件。
    [gpadmin@hdp3 ~]$ ls -l /data/hawq/master/minidumps/
总用量 12
-rw------- 1 gpadmin gpadmin 8949 4月  11 17:07 Minidump_20170411_170712_72720_2.mdp
[gpadmin@hdp3 ~]$
  4. 运行xmllint将minidump文件格式化，并将格式化后内容输出到一个新文件。
    [gpadmin@hdp3 ~]$ xmllint --format /data/hawq/master/minidumps/Minidump_20170411_170712_72720_2.mdp > /data/hawq/master/minidumps/MyTest.xml
  5. 查看良好格式的minidump文件。
    [gpadmin@hdp3 ~]$ cat /data/hawq/master/minidumps/MyTest.xml
4. GPORCA的限制
        启用GPORCA时，HAWQ中有一些限制。也正是因为GPORCA并不支持所有的HAWQ特性，GPORCA与老优化器才会在HAWQ中并存。
（1）不支持的SQL特性
        GPORCA不支持以下HAWQ特性：
  • PERCENTILE_窗口函数。
    db1=# explain select a, percentile_cont (0.5) within group (order by b desc)
db1-#           from t group by a;
                          QUERY PLAN                                       
--------------------------------------------------------------
 ...
 
 Settings:  default_hash_table_bucket_number=24; optimizer=on
 Optimizer status: legacy query optimizer
(24 rows)
  • 排序归并连接。
  • CUBE和grouping sets分析函数。
    db1=# explain select count(*) from t group by cube(a,b);
                          QUERY PLAN                                       
--------------------------------------------------------------
 ...
 
 Settings:  default_hash_table_bucket_number=24; optimizer=on
 Optimizer status: legacy query optimizer
(27 rows)
（2）性能衰退的情况
        启用GPORCA时，以下是已知的性能衰减情况：
  • 短查询。对于短查询来说，GPORCA为了确定优化的查询执行计划，可能带来额外的开销。
  • ANALYZE。启用GPORCA时，ANALYZE命令生成分区表根分区的统计信息，而老的优化器不收集此统计。
三、查询性能

        HAWQ为查询动态分配资源，数据所在的位置、查询所使用的虚拟段数量、集群的总体健康状况等因素都会影响查询性能。

1. 常用优化手段

（1）动态分区消除。
        HAWQ 有两种分区消除：静态消除和动态消除。静态消除发生在编译期间，在执行计划生成的时候，已经知道哪些分区会被使用。而动态消除发生在运行时，也就是说在运行的时候，才会知道哪些分区会被用到。例如，WHERE字句里面包含一个函数或者子查询用于返回分区键的值。查询过滤条件的值可用于动态分区消除时，查询处理速度将得到提升。该特性由服务器配置参数gp_dynamic_partition_pruning控制，缺省是开启的。
[gpadmin@hdp3 ~]$ hawq config -s gp_dynamic_partition_pruning
GUC		: gp_dynamic_partition_pruning
Value		: on
[gpadmin@hdp3 ~]$
（2）内存优化。
        HAWQ针对查询中的不同操作符分配最佳内存，并且在查询处理的各个阶段动态释放和重新分配内存。
（3）自动终止资源失控的查询。
        当服务器中所有查询占用的内存超过一定阈值，HAWQ可以终止某些查询。HAWQ的资源管理器会计算得到一个为segment分配的虚拟内存限额，再结合可配的系统参数计算阈值。阈值计算公式为：vmem threshold = (资源管理器计算的虚拟内存限额 + hawq_re_memory_overcommit_max) * runaway_detector_activation_percent。
        hawq_re_memory_overcommit_max参数设置每个物理segment可以超过资源管理器动态分配的内存限额的最大值，缺省为8192M。HAWQ使用YARN管理资源时，为了避免内存溢出错误，应该为该参数赋予一个较大值。runaway_detector_activation_percent参数设置触发自动终止查询的虚拟内存限额百分比，缺省值为95，如果设置为100，将禁用虚拟内存检测和自动查询终止。
[gpadmin@hdp3 ~]$ hawq config -s hawq_re_memory_overcommit_max
GUC		: hawq_re_memory_overcommit_max
Value		: 8192
[gpadmin@hdp3 ~]$ hawq config -s runaway_detector_activation_percent
GUC		: runaway_detector_activation_percent
Value		: 95
[gpadmin@hdp3 ~]$
        当一个物理segment使用的虚拟内存数量超过了该阈值，HAWQ就从内存消耗最大的查询开始终止查询，直到虚拟内存的使用低于指定的百分比。假设HAWQ的资源管理器计算得到的一个物理segment的虚拟内存限额为9G，hawq_re_memory_overcommit_max设置为1G，runaway_detector_activation_percent设置为95。那么当虚拟内存使用超过9.5G时，HAWQ开始终止查询。

2. 排查查询性能问题
        一个查询没有达到希望的执行速度时，应该从以下方面检查造成查询缓慢可能的原因。
（1）检查集群健康状况，如是否有DataNode或segment宕机，是否存在磁盘损坏等。
（2）检查表的统计信息，确认是否需要执行分析。
（3）检查查询的执行计划确定瓶颈。对于某些操作如Hash Join，如果没有足够的内存，该操作会使用溢出文件（spill files）。相对于完全在内存中执行的操作，磁盘溢出文件会慢得多。
（4）检查查询计划中的数据本地化统计。
（5）检查资源队列状态。HAWQ的pg_resqueue系统目录表保存资源队列信息。还可以查询pg_resqueue_status视图检查资源队列的运行时状态。
（6）分析资源管理器状态。（参考http://hawq.incubator.apache.org/docs/userguide/2.1.0.0-incubating/resourcemgmt/ResourceManagerStatus.html）

3. 数据本地化统计
        使用EXPLAIN ANALYZE可以获得数据本地化统计，例如：
db1=# explain analyze select * from t;
                                             QUERY PLAN                                         
----------------------------------------------------------------------------------------------------------
 ...

 Data locality statistics:
   data locality ratio: 1.000; virtual segment number: 1; different host number: 1; virtual segment number per host(avg/min/max): (1/1/1); segment size(avg/min/max): (56.000 B/56 B/56 B); segment size with penalty(avg/min/max): (56.000 B/56 B/56 B); continuity(avg/min/max): (1.000/1.000/1.000); DFS metadatacache: 0.138 ms; resource allocation: 1.159 ms; datalocality calculation: 0.252 ms.
 Total runtime: 8.205 ms
(17 rows)
        表1说明数据本地化相关度量值的含义，使用这些信息可以检查潜在的查询性能问题。

统计项

描述

data locality ratio

表示查询总的本地化读取比例。比例越低，从远程节点读取的数据越多。由于远程读取HDFS需要网络IO，可能增加查询的执行时间。对于哈希分布表，一个文件中的所有数据块将由一个segment处理，因此如果HDFS上的数据重新分布，比如做了HDFS Rebalance，那么数据本地化比例将会降低。这种情况下，可以执行CREATE TABLE AS SELECT语句，通过重建表手工执行数据的重新分布。

number of virtual segments

查询使用的虚拟段数量。通常虚拟段数越多，查询执行的越快。如果虚拟段太少，需要检查default_hash_table_bucket_number、hawq_rm_nvseg_perquery_limit或哈希分布表的桶数是否过小。

different host number

表示有多少主机用于运行此查询。当虚拟段数量大于等于HAWQ集群主机总数时，所有主机都应该被使用。对于一个大查询，如果该度量值小于主机数，通常意味着有些主机宕机了。这种情况下，应该执行“select * from gp_segment_configuration”语句检查节点状态。

segment size and segment size with penalty

“segment size”表示一个虚拟段处理的数据量（平均/最小/最大），以字节为单位。“segment size with penalty”表示一个虚拟段处理的包含了远程读取的数据量（平均/最小/最大），以字节为单位，远程读取量计算公式为“net_disk_ratio” * block size。包含远程读取的虚拟段应该比只有本地读取的虚拟段处理更少的数据。“net_disk_ratio”配置参数用于测量远程读取比本地读取慢多少，缺省值为1.01。可依据不同的网络环境调整该参数的值。

continuity

间断地读取HDFS文件会引入额外的查找，减慢查询的表扫描，一个较低的continuity值说明文件在DataNode上的分布并不连续。

DFS metadatacache

表示查询元数据缓存的时间。HDFS块信息被HAWQ的DFS Metadata Cache process进程缓存。如果缓存没有命中，该时间会增加。

resource allocation

表示从资源管理器获取资源所花的时间。

datalocality calculation

表示运行将HDFS块分配给虚拟段的算法和计算数据本地化比例的时间。
表1

4. 虚拟段数量
        执行查询使用的虚拟段数量直接影响查询并行度，从而影响查询性能。
（1）影响虚拟段数量的因素
        分配给查询的虚拟段数量受以下因素可能影响：
  • 查询成本。大查询使用更多的虚拟段。
  • 查询运行时的可用资源情况。如果资源队列中有更多的资源，查询就会使用它。
  • 哈希分布表及其桶数。如果只查询一个哈希分布表，查询的并行度是固定的，等于创建哈希表时分配的桶数。如果查询中既有哈希分布表又有随机分布表，当所有哈希表都具有相同的桶数，并且随机表的大小不大于哈希表大小的1.5倍，分配的虚拟段数等于桶数。否则，分配的虚拟段数依赖于查询成本，此时哈希表的虚拟段分配行为与随机表类似。
  • 查询类型。对于包含外部表或用户定义函数（UDF）的查询，计算其查询成本可能很困难。对于此类查询，分配的虚拟段数量由hawq_rm_nvseg_perquery_limit和hawq_rm_nvseg_perquery_perseg_limit参数，以及定义外部表的ON子句中的位置列表数量所控制。如果查询有是一个结果哈希表（如INSERT into hash_table），虚拟段的数量必须等于结果哈希表的桶数。COPY或ANALYZE等SQL命令将使用不同的策略计算虚拟段数量。
（2）一般规则总结
        如果有足够的可用资源，HAQW使用以下一般规则确定为查询分配的虚拟段数量：
  • select列表中仅包含随机分布表：虚拟段数量依赖于表大小。
  • select列表中仅包含哈希分布表：虚拟段数量依赖于表的桶数。
  • select列表中既包含随机分布表，又哈希分布表：如果所有哈希表都具有相同的桶数，并且随机表的大小不大于哈希表大小的1.5倍，分配的虚拟段数等于桶数。否则，分配的虚拟段数依赖于随机表的大小。
  • 查询中存在用户定义函数：虚拟段数量依赖于hawq_rm_nvseg_perquery_limit和hawq_rm_nvseg_perquery_perseg_limit参数。
  • 查询中存在PXF外部表：虚拟段数量依赖于default_hash_table_bucket_number参数。
  • 查询中存在gpfdist外部表：虚拟段数量不少于location列表中的位置数。
  • CREATE EXTERNAL TABLE命令：虚拟段数量对应命令中ON子句。
  • 哈希分布表与本地文件互拷数据：虚拟段数量依赖于哈希表的桶数。
  • 拷贝随机分布表数据到本地文件：虚拟段数量依赖于表大小。
  • 将本地文件内容拷贝到随机分布表中：虚拟段数量是固定值，如果资源足够，为6。
  • ANALYZE表：分析一个非分区表比等量的分区表使用更多的虚拟段。
  • 哈希分布结果表：虚拟段数量必须等于结果哈希表的桶数。
四、查询剖析
        遇到性能不良的查询时，最常用的调查手段就是查看执行计划。为到达良好性能，HAWQ选择与每个查询相匹配的正确的查询计划。查询计划定义了HAWQ在并行环境中如何运行查询。
        查询优化器根据数据库系统维护的统计信息选择成本最低的查询计划。成本以磁盘I/O作为考量，以查询需要读取的磁盘页数为测量单位。优化器的目标就是制定最小化执行成本的查询计划。
        和其它SQL数据库一样，HAWQ也是用EXPLAIN命令查看一个给定查询的计划。EXPLAIN会显示查询优化器估计出的计划成本。EXPLAIN ANALYZE命令会实际执行查询语句。它除了显示估算的查询成本，还会显示实际执行时间，从这些信息可以分析优化器所做的估算与实际之间的接近程度。
        再次强调在HAWQ中老的优化器与GPORCA是并存的，缺省的查询优化器为GPORCA。HAWQ尽可能使用GPORCA生成执行计划。GPORCA和老优化器的EXPLAIN输出是不同的，例如：
db1=# set optimizer=on;
SET
db1=# explain select * from t;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..431.00 rows=2 width=12)
   ->  Table Scan on t  (cost=0.00..431.00 rows=2 width=12)
 Settings:  default_hash_table_bucket_number=24; optimizer=on
 Optimizer status: PQO version 1.684
(4 rows)

db1=# set optimizer=off;
SET
db1=# explain select * from t;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..1.02 rows=2 width=12)
   ->  Append-only Scan on t  (cost=0.00..1.02 rows=2 width=12)
 Settings:  default_hash_table_bucket_number=24; optimizer=off
 Optimizer status: legacy query optimizer
(4 rows)

（1）读取EXPLAIN的输出
        查询计划的输出是一个由节点构成的树形结构，每个节点表示一个单一操作，例如表扫描、连接、聚合、排序等等。查询计划应该由底向上进行读取，每个节点操作返回的行提供给直接上级节点。最底层的节点通常为一个表扫描操作，连接、聚合、排序等其它操作节点在表扫描节点之上。计划的顶层通常为motion节点，如redistribute、broadcast或gather motions。在查询执行期间，这些操作将在节点间移动数据行。
        计划树中的每个节点对应EXPLAIN输出中的一行，显示基本的节点类型和为该操作估算的执行成本。
  • cost——读取磁盘页的测量单位。1.0表示一次顺序磁盘页读取。前一个值表示获取第一行的成本估算，后一个值表示获取全部行的总成本估算。总成本假定查询返回所有行，但当使用LIMIT时，并不返回全部的行，因此这种情况下的总成本是不对的。需要注意的是，节点成本包含了其子节点的成本，因此顶层节点的的成本就是该计划执行的总成本估算，也就是优化器认为的最小成本。而且成本仅反映了查询优化器考虑的计划执行成本，不包括将结果行传送到客户端的开销。
  • rows——该节点输出的总行数。此行数通常会少于节点需要扫描或处理的行数，反应了对WHERE条件选择性的估算。理想情况下，顶层节点的估算值应该接近查询实际返回的行数。
  • width——该节点输出的所有行的总字节数。
        EXPLAIN输出读取示例。
db1=# explain select * from t where b=1;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..431.00 rows=1 width=12)
   ->  Table Scan on t  (cost=0.00..431.00 rows=1 width=12)
         Filter: b = 1
 Settings:  default_hash_table_bucket_number=24; optimizer=on
 Optimizer status: PQO version 1.684
(5 rows)
        查询计划的EXPLAIN输出只有5行，其中最后一行表示生成该计划的优化器GPORCA，倒数第二行表示哈希桶数和优化器等基本参数的设置。这两行不属于查询计划树。
        现在开始自底向上读取计划。底层是一个表扫描节点，顺序扫描t表。WHERE子句表现为一个过滤条件，表示扫描操作会检查扫描到的每一行是否满足过滤条件，并且只向直接上级节点返回满足条件的行。
        扫描操作的结果传给上级的gather motion操作。在HAWQ中，segment实例向master实例发送数据即为gather motion操作。该操作在并行查询执行计划的slice1分片中完成，并且该分片只在一个segment上执行。正如介绍优化器时所述，查询计划被分成slice，因此segment可以并行执行部分查询计划。
        该计划估算的启动成本（返回首行的成本）为0，总成本为431个磁盘页读取，优化器估算该查询返回1行。这是一个最简单的示例，只有两步操作，实际的EXPLAIN可能复杂得多。

（2）读取EXPLAIN ANALYZE的输出
        与EXPLAIN不同，EXPLAIN ANALYZE命令不但生成执行计划，还会实际执行查询语句。
db1=# select * from t;
 a | b | c 
---+---+---
(0 rows)

db1=# explain insert into t values (1,1,1);
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Insert (slice0; segments: 1)  (rows=1 width=0)
   ->  Redistribute Motion 1:1  (slice1; segments: 1)  (cost=0.00..0.01 rows=1 width=0)
         ->  Result  (cost=0.00..0.01 rows=1 width=0)
 Settings:  default_hash_table_bucket_number=24; optimizer=off
 Optimizer status: legacy query optimizer
(5 rows)

db1=# select * from t;
 a | b | c 
---+---+---
(0 rows)

db1=# explain analyze insert into t values (1,1,1);
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Insert (slice0; segments: 1)  (rows=1 width=0)
   ->  Redistribute Motion 1:1  (slice1; segments: 1)  (cost=0.00..0.01 rows=1 width=0)
         Rows out:  Avg 1.0 rows x 1 workers at destination.  Max/Last(seg0:hdp3/seg0:hdp3) 1/1 rows with 14/14 ms to end
, start offset by 161/161 ms.
         ->  Result  (cost=0.00..0.01 rows=1 width=0)
               Rows out:  Avg 1.0 rows x 1 workers.  Max/Last(seg0:hdp3/seg0:hdp3) 1/1 rows with 0.004/0.004 ms to first 
row, 0.005/0.005 ms to end, start offset by 176/176 ms.

 ...
 
 Total runtime: 210.536 ms
(18 rows)

db1=# select * from t;
 a | b | c 
---+---+---
 1 | 1 | 1
(1 row)
        EXPLAIN ANALYZE显示优化器的估算成本与查询的实际执行成本，因此可以分析估算与实际的接近程度。EXPLAIN ANALYZE的输出还显示如下内容：
  • 查询总的执行时间，单位是毫秒。
  • 查询计划的每个分片使用的内存，以及为整个查询语句预留的内存。
  • 查询分发器的统计信息，包括当前查询使用的执行器数量（总数/缓存数/新连接数），分发时间（总时间/连接建立时间/分发数据时间），及其分发数据、执行器消耗、释放执行器的时间细节（最大/最小/平均）。
  • 如表1所示的数据本地化统计信息。
  • 节点操作涉及的segment（workers）数量，只对返回行的segment计数。
  • 节点操作输出的最多行数和用时最长的segment统计。
  • 一个操作中产生最多行的segment id。
  • 连接操作使用的内存（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.
  • 操作返回首行和返回所有行所用的时间（毫秒），如果两个时间相同，输出中省略返回首行的时间。
        EXPLAIN ANALYZE输出读取示例。为了方便与前面的EXPLAIN做对比，执行同样的查询语句。
db1=# explain analyze select * from t where b=1;
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..431.00 rows=1 width=12)
   Rows out:  Avg 1.0 rows x 1 workers at destination.  Max/Last(seg-1:hdp3/seg-1:hdp3) 1/1 rows with 11/11 ms to end, start offset by 1.054/1.054 ms.
   ->  Table Scan on t  (cost=0.00..431.00 rows=1 width=12)
         Filter: b = 1
         Rows out:  Avg 1.0 rows x 1 workers.  Max/Last(seg0:hdp3/seg0:hdp3) 1/1 rows with 2.892/2.892 ms to first row, 2.989/2.989 ms to end, start offset by 8.579/8.579 ms.
 Slice statistics:
   (slice0)    Executor memory: 163K bytes.
   (slice1)    Executor memory: 279K bytes (seg0:hdp3).
 Statement statistics:
   Memory used: 262144K bytes
 Settings:  default_hash_table_bucket_number=24; optimizer=on
 Optimizer status: PQO version 1.684
 Dispatcher statistics:
   executors used(total/cached/new connection): (1/1/0); dispatcher time(total/connection/dispatch data): (0.342 ms/0.000 ms/0.095 ms).
   dispatch data time(max/min/avg): (0.095 ms/0.095 ms/0.095 ms); consume executor data time(max/min/avg): (0.020 ms/0.020 ms/0.020 ms); free executor time(max/min/avg): (0.000 ms/0.000 ms/0.000 ms).
 Data locality statistics:
   data locality ratio: 1.000; virtual segment number: 1; different host number: 1; virtual segment number per host(avg/min/max): (1/1/1); segment size(avg/min/max): (24.000 B/24 B/24 B); segment size with penalty(avg/min/max): (24.000 B/24 B/24 B); continuity(avg/min/max): (1.000/1.000/1.000); DFS metadatacache: 0.092 ms; resource allocation: 0.911 ms; datalocality calculation: 0.221 ms.
 Total runtime: 13.304 ms
(18 rows)
        与EXPLAIN不同相比，这次的输出长得多，有18行。第11表示生成该计划的优化器GPORCA，第12行表示哈希桶数和优化器等基本参数的设置。这两行与EXPLAIN的输出相同。前5行是执行计划树，比EXPLAIN的输出多出第2、5两行，这两行是节点的实际执行情况，包括返回数据行数、首末行时间、最大最长segment等。Table Scan操作只有一个segment（seg0）返回行，并且只返回1行。Max/Last统计是相同的，因为只有一个segment返回行。找到首行使用的时间为2.892毫秒，返回所有行的时间为2.989毫秒。注意start offset by，它表示的是从分发器开始执行操作到segment返回首行经历的时间为8.579毫秒。查询实际返回行数与估算返回的行数相同。gather motion操作接收1行，并传送到master。gather motion节点的时间统计包含其了子节点Table Scan操作的时间。最后一行显示该查询总的执行时间为13.304毫秒。
        输出中的其它行是各种统计信息，包括分片统计、语句统计、分发器统计、数据本地化统计等。

（3）分析查询计划中的问题
        查询慢时，需要查看执行计划并考虑以下问题：
  • 计划中的某些特定操作是否使用了很长时间？找到最消耗时间的操作并分析原因。例如，哈希表的扫描时间出乎意料的长，可能是由于数据本地化程度低，导致节点间的网络IO花费大量时间。此时重新装载数据可能提高查询速度。
  • 查询优化器估算的行数是否与实际的相近？运行EXPLAIN ANALYZE检查实际与估算的返回行数是否接近。如果相差很多，收集相关表列的统计信息。
  • 是否在计划的早期应用了过滤谓词？在计划早期应用选择过滤使得向上层节点传递的行更少。如果查询计划错误地估计了查询谓词的选择性，收集相关表列的统计信息。也可以尝试改变SQL语句中WHERE子句中列的顺序（查看Filter显示的顺序）。
  • 查询优化器是否选择了最好的表连接顺序？过滤行数越多的表越应该先处理。如果计划没有选择优化的连接顺序，可能需要收集关联列的统计信息，或者设置join_collapse_limit配置参数为1。后者会导致按SQL语句中指定的连接顺序执行。
  • 优化器是否使用了分区消除？确认分区策略和查询谓词中的过滤条件是否匹配。
  • 优化器是否选择了适当的哈希聚合与哈希连接？哈希操作通常比其它的连接或聚合类型快，因为行的比较和排序在内存中完成，而不是读写磁盘。为了让优化器适当地选择哈希操作，必须有足够的可用内存，存储估算的行数。如果可能，运行EXPLAIN ANALYZE显示查询需要的内存和溢出到磁盘的数据量。例如，以下输出显示查询使用了23430K内存，还需要33649K，此时考虑调整内存相关配置优化查询。
    Work_mem used: 23430K bytes avg, 23430K bytes max (seg0). 
Work_mem wanted: 33649K bytes avg, 33649K bytes max (seg0) to lessen 
workfile I/O affecting 2 workers.
        警告：不要在HAWQ中使用PL/pgSQL函数生成动态查询的执行计划，这可能引起服务器崩溃！下面的例子在PostgreSQL 8.4.20中可以正常执行，但在HAWQ2.1.1中数据库直接宕机。
db1=# create or replace function explain_plan_func() returns varchar as $$
declare

 a varchar;
 b int;
 c varchar;

 begin
   a = ‘‘;
   b = 1;
   for c in execute ‘explain select * from t where b=‘ || cast(b as varchar) loop
     a = a || e‘\n‘ || c;
   end loop;
   return a;
 end;
$$
language plpgsql
volatile;
CREATE FUNCTION
db1=# select explain_plan_func();
                 explain_plan_func                  
----------------------------------------------------
 
 Seq Scan on t  (cost=0.00..34.25 rows=10 width=12)
   Filter: (b = 1)
(1 行记录)

用HAWQ轻松取代传统数据仓库（十二） —— 查询优化


联系
我们