即便对SELECT等数据库查询语句已经很熟悉了,但HAWQ里的查询有其自己的特点,还是需要研究一下。
一、HAWQ的查询处理流程
理解HAWQ的查询处理过程有助于写出更加优化的查询。与任何其它数据库管理系统类似,HAWQ也有如下的查询执行步骤:
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在查询分区表时做了以下增强:
下面看一个分区表查询的例子。
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,这需要设置以下两个配置参数。
分区表上使用GPORCA时必须用ANALYZE ROOTPARTITION命令收集根分区的统计信息。该命令只收集根分区统计信息,而不收集叶分区。作为一项例行的数据库维护工作,应该在分区表数据大量改变(如装载了大量数据)后刷新根分区的统计。
(1)设置optimizer_analyze_root_partition参数
(2)在系统级启用GPORCA
(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优化器执行查询,应该满足以下条件:
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)
(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生成的执行计划中,老的优化器不支持这些操作。
(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时,以下是已知的性能衰减情况:
三、查询性能
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)影响虚拟段数量的因素
分配给查询的虚拟段数量受以下因素可能影响:
(2)一般规则总结
如果有足够的可用资源,HAQW使用以下一般规则确定为查询分配的虚拟段数量:
四、查询剖析
遇到性能不良的查询时,最常用的调查手段就是查看执行计划。为到达良好性能,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输出中的一行,显示基本的节点类型和为该操作估算的执行成本。
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的输出还显示如下内容:
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)分析查询计划中的问题
查询慢时,需要查看执行计划并考虑以下问题:
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 行记录)