5 Greenplum表的管理实践-2 表分布策略
文章目录
本文主要从表的分布策略,Greenplum是一个大规模并行处理数据库,它由一个master和多个segment组成,其数据按照设定的分布策略分布于各个segment上。数据表的单个行会被分配到一个或多个segment上,但是有这么多的segment,它到底会被分到哪个或哪些segment上呢?分布策略会告诉我们。
5.1 表分布策略
在 Greenplum 5 中,有2种分布策略:
- 哈希分布
- 随机分布
在 Greenplum 6 中,添加了另一个策略:
- 哈希分布
- 随机分布
- 复制分布
哈希分布
要使用这一策略,需要在创建表使用 “DISTRIBUTED BY(column,[…])” 子句。
散列算法使分布键将每一行分配给特定 segment。相同值的键将始终散列到同一个 segment。选择唯一的分布键(例如Primary Key)将确保较均匀的数据分布。哈希分布是表的默认分布策略。
如果创建表时未提供 DISTRIBUTED 子句,则将 PRIMARY KEY(如果表真的有的话)或表的第一个合格列用作分布键。什么类型的列是合格列呢?几何类型或用户自定义数据类型的列不能用作 Greenplum 分布键列。如果表中没有合格的列,则退化为随机分布策略。
但是,如果未提供 DISTRIBUTED 子句,Greenplum 最后会选择哪种分布策略还会受其它因素的影响,例如:GUC gp_create_table_random_default_distribution 和当时使用的优化器(optimizer)也将影响最终决定。因此,请千万不要忘记在 CREATE TABLE 时添加 DISTRIBUTED BY 子句
。
Note: 如果创建表时没有指定DISTRIBUTED BY,Greenplum数据库服务器配置参数
gp_create_table_random_default_distribution
[gpadmin@mdw ~]$ gpconfig --show gp_create_table_random_default_distribution
Values on all segments are consistent
GUC : gp_create_table_random_default_distribution
Master value: off
Segment value: off
[gpadmin@mdw ~]$
随机分布
要使用这一策略,需要在创建表使用 “DISTRIBUTED RANDOMLY” 子句。
随机分布会将数据行按到来顺序依次循环发送到各个 segment 上。与哈希分布策略不同,具有相同值的数据行不一定位于同一个 segment 上。虽然随机分布确保了数据的平均分布,但只要有可能,应该尽量选择哈希分布策略,哈希分布的性能更加优良。
复制分布
这种分布策略是 GPDB 6 的新增特性。要使用这一策略,需要在创建表使用 “DISTRIBUTED REPLICATED” 子句。
Greenplum 数据库将每行数据分配到每个 segment上。这种分布策略下,表数据将均匀分布,因为每个 segment 都存储着同样的数据行。当您需要在 segment 上执行用户自定义的函数且这些函数需要访问表中的所有行时,就需要用到复制分布策略。或者当有大表与小表 join,把足够小的表指定为 replicated 也可能提升性能。
请注意,有一个例外:catalog 表没有分布策略。
关于3项策略的摘要:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IhPKnWSd-1588921554857)(D:\SYBASE&informix&DB2\Greenplum\4 学习笔记\数据库管理\5 Greenplum表的管理实践-2.assets\3baead233c6f42a4bdc7f48d93c272cc.jpg)]
控制表的分布策略。
更多有关该参数的信息,请见Greenplum数据库参考指南的“服务器配置参数”部分。
在决定表分布策略时,请考虑以下几点。
- 均匀数据分布 — 为了最好的性能,所有的Segment应该包含等量的数据。如果数据不平衡或者倾斜,具有更多数据的Segment就必须做更多工作来执行它那一部分的查询处理。 请选择对于每一个记录都唯一的分布键,例如主键。
- 本地和分布式操作 — 本地操作比分布式操作更快。在Segment层面上,如果与连接、排序或者聚集操作相关的工作在本地完成,查询处理是最快的。在系统层面完成的工作要求在Segment之间分布元组,其效率会低些。当表共享一个共同的分布键时,在它们共享的分布键列上的连接或者排序工作会在本地完成。对于随机分布策略来说,本地连接操作就行不通了。
- 均匀查询处理 — 为了最好的性能,所有的Segment应该处理等量的查询负载。如果一个表的数据分布策略与查询谓词匹配不好,查询负载可能会倾斜。例如,假定一个销售事务表按照客户ID列(分布键)分布。如果查询中的谓词引用了一个单一的客户ID,该查询处理工作会被集中在一个Segment上。
选择法则:
1)尽量选择经常需要 JOIN 的列
当关联键和分布键均一致时,可以在 Segment 中完成 JOIN,不需要重分布或者广播小表。
当关联键和分布键不一致时,则需要重分布不一致的表或者广播小表,带来额外的开销。
2)尽量选择分布均匀的列或者多列
若选择的分布列值分布不均匀,则可能导致数据倾斜。某些 Segment 非常大(压力大),某些 Segment 非常小。根据木桶原理,时间消耗会卡在数据多的节点上。
3)尽量选择高并发查询的条件列(指该查询条件产生的中间结果集小的,如果中间结果集很大,那就让所有节点都来参与运算更好,因此不选)
如果数据经常被高并发的键值或离散查询,可以将查询条件的列作为分布列,这样不需要连接到所有的 Segment 去查,可以大大提高并发能力。
4)不要轻易使用随机分布
复制表分布策略(DISTRIBUTED REPLICATED)应该在小表上使用。 将大表数据复制到每个节点上无论在存储还是维护上都是有代价的。 复制表最基本的用例是:
- 删除用户定义的函数可以对节点执行的操作的限制
- 频繁使用的表不需要广播到所有节点可以提高查询性能。
声明分布键
CREATE TABLE命令的可选子句DISTRIBUTED BY, DISTRIBUTED RANDOMLY和DISTRIBUTED REPLICATED决定了表的分布策略。默认的哈希分布策略使用PRIMARY KEY(如果有的话)或表的第一列作为分布键。几何信息列或用户自定义数据类型的列是不能作为Greenplum数据库分布列的。 如果找不到合适的哈希分布的列,Greenplum数据库就选择随机分布策略。
复制表没有分布列,因为每行都分布在Greenplum数据库所有节点上。
为了保证哈希分布数据的均匀分布,最好选一个唯一键作为分布列。如果找不到,则选择DISTRIBUTED RANDOMLY。例如:
=> CREATE TABLE products
(name varchar(40),
prod_id integer,
supplier_id integer)
DISTRIBUTED BY (prod_id);
=> CREATE TABLE random_stuff
(things text,
doodads text,
etc text)
DISTRIBUTED RANDOMLY;
Important: 主键总是表的分布列。如果没有主键,但是有唯一索引存在,则选择它为分布键。
自定义分布键哈希函数
用于哈希分布策略的哈希函数由列的数据类型的哈希运算符类定义。由于默认的Greenplum数据库使用数据类型的默认哈希运算符类,因此用于哈希连接和哈希聚合的运算符类相同,适用于大多数用例。 但是,您可以在DISTRIBUTED BY子句中声明非默认的哈希运算符类。
使用自定义哈希运算符类可以用于支持与默认相等运算符(=)不同的运算符上的共存连接。
自定义哈希操作符类用例
此示例为整数数据类型创建自定义哈希运算符类,该类用于提高查询性能。运算符类比较整数的绝对值。
创建一个函数和一个等于运算符,如果两个整数的绝对值相等,则返回true。
CREATE FUNCTION abseq(int, int) RETURNS BOOL AS
$$
begin return abs($1) = abs($2); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE OPERATOR |=| (
PROCEDURE = abseq,
LEFTARG = int,
RIGHTARG = int,
COMMUTATOR = |=|,
hashes, merges);
现在,创建一个使用运算符的哈希函数和运算符类。
CREATE FUNCTION abshashfunc(int) RETURNS int AS
$$
begin return hashint(abs($1)); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE OPERATOR CLASS abs_int_hash_ops FOR TYPE int4
USING hash AS
OPERATOR 1 |=|,
FUNCTION 1 abshashfunc(int);
并且,为它们创建小于和大于运算符和B树运算符类。我们的查询不需要它们,但是Greenplum数据库的Postgres查询优化器必须依赖它们做连接的co-location。
CREATE FUNCTION abslt(int, int) RETURNS BOOL AS
$$
begin return abs($1) < abs($2); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE OPERATOR |<| (
PROCEDURE = abslt,
LEFTARG = int,
RIGHTARG = int);
CREATE FUNCTION absgt(int, int) RETURNS BOOL AS
$$
begin return abs($1) > abs($2); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE OPERATOR |>| (
PROCEDURE = absgt,
LEFTARG = int,
RIGHTARG = int);
CREATE FUNCTION abscmp(int, int) RETURNS int AS
$$
begin return btint4cmp(abs($1),abs($2)); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE OPERATOR CLASS abs_int_btree_ops FOR TYPE int4
USING btree AS
OPERATOR 1 |<|,
OPERATOR 3 |=|,
OPERATOR 5 |>|,
FUNCTION 1 abscmp(int, int);
现在,您可以在表中使用自定义哈希运算符类。
CREATE TABLE atab (a int) DISTRIBUTED BY (a abs_int_hash_ops);
CREATE TABLE btab (b int) DISTRIBUTED BY (b abs_int_hash_ops);
INSERT INTO atab VALUES (-1), (0), (1);
INSERT INTO btab VALUES (-1), (0), (1), (2);
执行使用自定义相等运算符
|=|
的连接的查询 可以利用co-location。 使用默认的整数opclass,此查询将需要Redistribute Motion节点,但使用自定义opclass,可以实现更高效的计划。
EXPLAIN (COSTS OFF) SELECT a, b FROM atab, btab WHERE a = b;
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice3; segments: 3)
-> Hash Join
Hash Cond: (btab.b = atab.a)
-> Redistribute Motion 3:3 (slice1; segments: 3)
Hash Key: btab.b
-> Seq Scan on btab
-> Hash
-> Redistribute Motion 3:3 (slice2; segments: 3)
Hash Key: atab.a
-> Seq Scan on atab
Optimizer: Postgres query optimizer
(11 rows)
测试hash分布
1 创建hash分布表
CREATE TABLE testdis (id int primary key, name varchar(20)) DISTRIBUTED BY (id);
archdata=# \dt testdis
List of relations
Schema | Name | Type | Owner | Storage
--------+---------+-------+---------+---------
public | testdis | table | gpadmin | heap
(1 row)
archdata=# \d testdis
Table "public.testdis"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer | not null
name | character varying(20) |
Indexes:
"testdis_pkey" PRIMARY KEY, btree (id)
Distributed by: (id)
2 生成数据
CREATE OR REPLACE FUNCTION creatData()
RETURNS boolean AS
$BODY$
DECLARE
i int;
BEGIN
i :=1;
FOR i IN 1..10000 LOOP
INSERT INTO testdis(id,name) VALUES(i,'CEIC');
END LOOP;
IF i > 0 THEN
RETURN true;
ELSE
RETURN false;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
archdata=# CREATE OR REPLACE FUNCTION creatData()
archdata-# RETURNS boolean AS
archdata-# $BODY$
archdata$# DECLARE
archdata$# i int;
archdata$# BEGIN
archdata$# i :=1;
archdata$# FOR i IN 1..10000 LOOP
archdata$# INSERT INTO testdis(id,name) VALUES(i,'CEIC');
archdata$# END LOOP;
archdata$#
archdata$# IF i > 0 THEN
archdata$# RETURN true;
archdata$# ELSE
archdata$# RETURN false;
archdata$# END IF;
archdata$# END;
archdata$# $BODY$
archdata-# LANGUAGE 'plpgsql' VOLATILE;
CREATE FUNCTION
archdata=# ALTER FUNCTION creatData() OWNER TO gpadmin;
ALTER FUNCTION
archdata=# SELECT * FROM creatData();
3 检查数据分布情况
SELECT gp_segment_id, count(*) FROM testdis GROUP BY 1;
archdata=# SELECT gp_segment_id, count(*) FROM testdis GROUP BY 1;
gp_segment_id | count
---------------+-------
0 | 1654
2 | 1665
3 | 1661
5 | 1683
1 | 1655
4 | 1682
(6 rows)
archdata=# select * FROM testdis where gp_segment_id=0;
id | name
-------+------
1 | CEIC
13 | CEIC
15 | CEIC
17 | CEIC
29 | CEIC
31 | CEIC
33 | CEIC
35 | CEIC
37 | CEIC
49 | CEIC
select * from testdis where gp_segment_id=0 limit 10 offset 11;
archdata=# select * from testdis where gp_segment_id=0 limit 10 offset 11;
id | name
-----+------
63 | CEIC
69 | CEIC
71 | CEIC
83 | CEIC
85 | CEIC
103 | CEIC
105 | CEIC
117 | CEIC
119 | CEIC
121 | CEIC
(10 rows)
^
archdata=# SELECT gp_segment_id, id,name FROM testdis order by id;
gp_segment_id | id | name
---------------+-------+------
0 | 1 | CEIC --证明segment_id和ID的对应方式hash方式
3 | 2 | CEIC
4 | 3 | CEIC
1 | 4 | CEIC
4 | 5 | CEIC
1 | 6 | CEIC
4 | 7 | CEIC
5 | 8 | CEIC
2 | 9 | CEIC
5 | 10 | CEIC
2 | 11 | CEIC
5 | 12 | CEIC
0 | 13 | CEIC
3 | 14 | CEIC
0 | 15 | CEIC
3 | 16 | CEIC
0 | 17 | CEIC
1 | 18 | CEIC
archdata=# SELECT gp_segment_id, id,name FROM testdis order by id limit 20 offset 20;
gp_segment_id | id | name
---------------+----+------
4 | 21 | CEIC
1 | 22 | CEIC
2 | 23 | CEIC
5 | 24 | CEIC
2 | 25 | CEIC
5 | 26 | CEIC
2 | 27 | CEIC
3 | 28 | CEIC
0 | 29 | CEIC
3 | 30 | CEIC
0 | 31 | CEIC
5 | 32 | CEIC
0 | 33 | CEIC
测试随机random分布
1 创建表
CREATE TABLE testdis_ramdomly (id int primary key, name varchar(20)) DISTRIBUTED RANDOMLY;
archdata=# CREATE TABLE testdis_ramdomly (id int primary key, name varchar(20)) DISTRIBUTED RANDOMLY;
ERROR: PRIMARY KEY and DISTRIBUTED RANDOMLY are incompatible
说明有主键的表是无法创建随机分布的
2 生成数据
insert into testdis_ramdomly as select * from testdis;
archdata=# insert into testdis_ramdomly select * from testdis;
INSERT 0 10000
3 检查数据分布情况
archdata=# SELECT gp_segment_id, count(*) FROM testdis_ramdomly GROUP BY 1 order by 1;
gp_segment_id | count
---------------+-------
0 | 1684
1 | 1687 --也是比较平均的
2 | 1677
3 | 1646
4 | 1641
5 | 1665
(6 rows)
archdata=#
archdata=# SELECT gp_segment_id, id,name FROM testdis_ramdomly order by id;
gp_segment_id | id | name
---------------+-------+------
2 | 1 | CEIC
0 | 2 | CEIC
2 | 3 | CEIC
5 | 4 | CEIC
3 | 5 | CEIC
0 | 6 | CEIC
2 | 7 | CEIC
3 | 8 | CEIC
4 | 9 | CEIC
4 | 10 | CEIC
3 | 11 | CEIC
1 | 12 | CEIC
1 | 13 | CEIC
3 | 14 | CEIC
改变表的分布策略
创建一张表,在没有primary key 或者 unique key 的情况下,GreenPlum默认会把第一个column作为分布键
archdata=# create table tab01(id int,name varchar(20));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
archdata=#
CREATE TABLE
archdata=# \d+ tab01
Table "public.tab01"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------+----------+-------------
id | integer | | plain |
name | character varying(20) | | extended |
Has OIDs: no
Distributed by: (id)
使用以下语句修改分布键
archdata=# alter table tab01 set distributed by(name);
ALTER TABLE
archdata=# \d+ tab01
Table "public.tab01"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------+----------+-------------
id | integer | | plain |
name | character varying(20) | | extended |
Has OIDs: no
Distributed by: (name)
在不确定哪个column为分布键的情况下可以使用randomly策略