AntDB重分布（rebalance）性能对比记录一
AntDB集群环境
postgres=# table pgxc_node;
node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id
-----------+-----------+-----------+--------------+----------------+------------------+-------------
cd1 | C | 39000 | 10.21.20.176 | f | f | -1265012607
cd2 | C | 39000 | 10.21.20.175 | f | f | 455674804
dn4 | D | 39002 | 10.21.20.175 | f | f | 823103418
dn2 | D | 39002 | 10.21.20.176 | f | f | 352366662
dn3 | D | 39001 | 10.21.20.175 | f | f | -700122826
dn1 | D | 39001 | 10.21.20.176 | f | f | -560021589
(6 rows)测试案例描述
- 哈希分布->哈希分布
- 分布键不变，节点变化
- 表无索引
测试数据准备
--10万
create table t_one_million(id int8, value int8) distribute by hash(id);
create table t_one_million_new(id int8, value int8) distribute by hash(id) to node(dn1, dn2, dn3);
insert into t_one_million select r, r*random()*10::int from generate_series(1,100000)r;
--100万
create table t_ten_million(id int8, value int8) distribute by hash(id);
create table t_ten_million_new(id int8, value int8) distribute by hash(id) to node(dn1, dn2, dn3);
insert into t_ten_million select r, r*random()*10::int from generate_series(1,1000000)r;
--1000万
create table t_hundred_million(id int8, value int8) distribute by hash(id);
create table t_hundred_million_new(id int8, value int8) distribute by hash(id) to node(dn1, dn2, dn3);
insert into t_hundred_million select r, r*random()*10::int from generate_series(1,10000000)r;
--1亿
create table t_thousand_million(id int8, value int8) distribute by hash(id);
create table t_thousand_million_new(id int8, value int8) distribute by hash(id) to node(dn1, dn2, dn3);
insert into t_thousand_million select r, r*random()*10::int from generate_series(1,100000000)r;10万条数据rebalance
postgres=# --10万，重分布，节点减少 (dn1,dn2,dn3,dn4)->(dn1,dn2,dn3)
postgres=# select node_name, count(1) from t_one_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+-------
dn1 | 25006
dn2 | 24739
dn3 | 24973
dn4 | 25282
(4 rows)
Time: 290.777 ms
postgres=# alter table t_one_million to node(dn1,dn2,dn3);
ALTER TABLE
Time: 1241.957 ms
postgres=# select node_name, count(1) from t_one_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+-------
dn1 | 33343
dn2 | 33209
dn3 | 33448
(3 rows)
Time: 264.952 ms
postgres=# --10万，重分布，节点变化 (dn1,dn2,dn3) -> (dn1,dn3,dn4)
postgres=# alter table t_one_million to node(dn1,dn3,dn4);
ALTER TABLE
Time: 1132.920 ms
postgres=# select node_name, count(1) from t_one_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+-------
dn1 | 33343
dn3 | 33209
dn4 | 33448
(3 rows)
Time: 302.298 ms
postgres=# --10万，重分布，节点增加 (dn1,dn3,dn4) -> (dn1,dn2,dn3,dn4)
postgres=# alter table t_one_million to node(dn1,dn2,dn3,dn4);
ALTER TABLE
Time: 1275.130 ms
postgres=# select node_name, count(1) from t_one_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+-------
dn1 | 25006
dn2 | 24739
dn3 | 24973
dn4 | 25282
(4 rows)
Time: 299.630 ms10万条数据插入新表（节点4->3）
postgres=# explain (verbose, analyze, plan_id) insert into t_one_million_new select * from t_one_million;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Cluster Gather (cost=1001.00..1568.75 rows=1850 width=16) (actual time=150.624..150.624 rows=0 loops=1)
Plan id: 0
Remote node: 16385,16386,16387,16388
-> Insert on public.t_one_million_new (cost=1.00..13.75 rows=617 width=16) (actual time=0.087..0.087 rows=0 loops=1)
Plan id: 1
Node 16385: (actual time=63.338..63.338 rows=0 loops=1)
Node 16386: (actual time=143.233..143.233 rows=0 loops=1)
Node 16387: (actual time=144.489..144.489 rows=0 loops=1)
Node 16388: (actual time=150.246..150.246 rows=0 loops=1)
-> Cluster Reduce (cost=1.00..13.75 rows=617 width=16) (actual time=0.084..0.084 rows=0 loops=1)
Plan id: 2
Reduce: ('[0:2]={16388,16386,16387}'::oid[])[COALESCE(int4abs((hashint8(t_one_million.id) % 3)), 0)]
Node 16385: (actual time=63.336..63.336 rows=0 loops=1)
Node 16386: (actual time=1.727..67.580 rows=33209 loops=1)
Node 16387: (actual time=1.537..68.800 rows=33448 loops=1)
Node 16388: (actual time=0.050..72.396 rows=33343 loops=1)
-> Seq Scan on public.t_one_million (cost=0.00..7.12 rows=462 width=16) (actual time=0.008..0.008 rows=0 loops=1)
Plan id: 3
Output: t_one_million.id, t_one_million.value
Remote node: 16388,16386,16387,16385
Node 16385: (actual time=0.033..8.568 rows=25282 loops=1)
Node 16386: (actual time=0.045..10.478 rows=24739 loops=1)
Node 16387: (actual time=0.057..9.939 rows=24973 loops=1)
Node 16388: (actual time=0.038..11.604 rows=25006 loops=1)
Planning time: 0.651 ms
Execution time: 266.723 ms
(26 rows)
Time: 457.419 ms
postgres=# select node_name, count(1) from t_one_million_new a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+-------
dn1 | 33343
dn2 | 33209
dn3 | 33448
(3 rows)
Time: 266.118 ms100万条数据rebalance
postgres=# --100万，重分布，节点减少 (dn1,dn2,dn3,dn4)->(dn1,dn2,dn3)
postgres=# select node_name, count(1) from t_ten_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+--------
dn1 | 250043
dn2 | 249051
dn3 | 250521
dn4 | 250385
(4 rows)
Time: 991.656 ms
postgres=# alter table t_ten_million to node(dn1,dn2,dn3);
ALTER TABLE
Time: 7308.235 ms
postgres=# select node_name, count(1) from t_ten_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+--------
dn1 | 333622
dn2 | 332909
dn3 | 333469
(3 rows)
Time: 1071.059 ms
postgres=# --100万，重分布，节点变化 (dn1,dn2,dn3) -> (dn1,dn3,dn4)
postgres=# alter table t_ten_million to node(dn1,dn3,dn4);
ALTER TABLE
Time: 7804.080 ms
postgres=# select node_name, count(1) from t_ten_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+--------
dn1 | 333622
dn3 | 332909
dn4 | 333469
(3 rows)
Time: 2010.396 ms
postgres=# --100万，重分布，节点增加 (dn1,dn3,dn4) -> (dn1,dn2,dn3,dn4)
postgres=# alter table t_ten_million to node(dn1,dn2,dn3,dn4);
ALTER TABLE
Time: 7315.380 ms
postgres=# select node_name, count(1) from t_ten_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+--------
dn1 | 250043
dn2 | 249051
dn3 | 250521
dn4 | 250385
(4 rows)
Time: 1071.580 ms100万条数据插入新表（节点4->3）
postgres=# --100万，插入新表，(dn1,dn2,dn3,dn4)->(dn1,dn2,dn3)
postgres=# explain (verbose, analyze, plan_id) insert into t_ten_million_new select * from t_ten_million;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Cluster Gather (cost=1001.00..307599.69 rows=1000000 width=16) (actual time=1324.788..1324.788 rows=0 loops=1)
Plan id: 0
Remote node: 16385,16386,16387,16388
-> Insert on public.t_ten_million_new (cost=1.00..6599.69 rows=333333 width=16) (actual time=0.117..0.117 rows=0 loops=1)
Plan id: 1
Node 16385: (actual time=433.217..433.217 rows=0 loops=1)
Node 16388: (actual time=1183.333..1183.333 rows=0 loops=1)
Node 16387: (actual time=1243.368..1243.368 rows=0 loops=1)
Node 16386: (actual time=1324.372..1324.372 rows=0 loops=1)
-> Cluster Reduce (cost=1.00..6599.69 rows=333333 width=16) (actual time=0.113..0.113 rows=0 loops=1)
Plan id: 2
Reduce: ('[0:2]={16388,16386,16387}'::oid[])[COALESCE(int4abs((hashint8(t_ten_million.id) % 3)), 0)]
Node 16385: (actual time=433.216..433.216 rows=0 loops=1)
Node 16388: (actual time=0.071..453.257 rows=333622 loops=1)
Node 16387: (actual time=1.504..479.787 rows=333469 loops=1)
Node 16386: (actual time=1.717..475.200 rows=332909 loops=1)
-> Seq Scan on public.t_ten_million (cost=0.00..3852.00 rows=250000 width=16) (actual time=0.009..0.009 rows=0 loops=1)
Plan id: 3
Output: t_ten_million.id, t_ten_million.value
Remote node: 16388,16386,16387,16385
Node 16385: (actual time=0.033..59.441 rows=250385 loops=1)
Node 16388: (actual time=0.054..67.587 rows=250043 loops=1)
Node 16387: (actual time=0.041..69.449 rows=250521 loops=1)
Node 16386: (actual time=0.052..70.767 rows=249051 loops=1)
Planning time: 0.510 ms
Execution time: 1355.531 ms
(26 rows)
Time: 1791.471 ms
postgres=# select node_name, count(1) from t_ten_million_new a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+--------
dn1 | 333622
dn2 | 332909
dn3 | 333469
(3 rows)
Time: 990.051 ms1000万条数据rebalance
postgres=# --1000万，重分布，节点减少 (dn1,dn2,dn3,dn4)->(dn1,dn2,dn3)
postgres=# select node_name, count(1) from t_hundred_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+---------
dn1 | 2501529
dn2 | 2500572
dn3 | 2500608
dn4 | 2497291
(4 rows)
Time: 7489.552 ms
postgres=# alter table t_hundred_million to node(dn1,dn2,dn3);
ALTER TABLE
Time: 67223.163 ms
postgres=# select node_name, count(1) from t_hundred_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+---------
dn1 | 3337145
dn2 | 3330709
dn3 | 3332146
(3 rows)
Time: 9597.601 ms
postgres=# --1000万，重分布，节点变化 (dn1,dn2,dn3) -> (dn1,dn3,dn4)
postgres=# alter table t_hundred_million to node(dn1,dn3,dn4);
ALTER TABLE
Time: 65410.713 ms
postgres=# select node_name, count(1) from t_hundred_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+---------
dn1 | 3337145
dn3 | 3330709
dn4 | 3332146
(3 rows)
Time: 18137.894 ms
postgres=# --1000万，重分布，节点增加 (dn1,dn3,dn4) -> (dn1,dn2,dn3,dn4)
postgres=# alter table t_hundred_million to node(dn1,dn2,dn3,dn4);
ALTER TABLE
Time: 69442.493 ms
postgres=# select node_name, count(1) from t_hundred_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+---------
dn1 | 2501529
dn2 | 2500572
dn3 | 2500608
dn4 | 2497291
(4 rows)
Time: 7256.681 ms1000万条数据插入新表（节点4->3）
postgres=# --1000万，插入新表，(dn1,dn2,dn3,dn4)->(dn1,dn2,dn3)
postgres=# explain (verbose, analyze, plan_id) insert into t_hundred_million_new select * from t_hundred_million;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Cluster Gather (cost=1001.00..3066981.06 rows=10000000 width=16) (actual time=17953.717..17953.717 rows=0 loops=1)
Plan id: 0
Remote node: 16385,16386,16387,16388
-> Insert on public.t_hundred_million_new (cost=1.00..65981.06 rows=3333333 width=16) (actual time=0.090..0.090 rows=0 loops=1)
Plan id: 1
Node 16385: (actual time=8843.154..8843.154 rows=0 loops=1)
Node 16387: (actual time=17729.698..17729.698 rows=0 loops=1)
Node 16386: (actual time=17796.154..17796.154 rows=0 loops=1)
Node 16388: (actual time=17953.132..17953.132 rows=0 loops=1)
-> Cluster Reduce (cost=1.00..65981.06 rows=3333333 width=16) (actual time=0.087..0.087 rows=0 loops=1)
Plan id: 2
Reduce: ('[0:2]={16388,16386,16387}'::oid[])[COALESCE(int4abs((hashint8(t_hundred_million.id) % 3)), 0)]
Node 16385: (actual time=8843.152..8843.152 rows=0 loops=1)
Node 16387: (actual time=1.639..8005.488 rows=3332146 loops=1)
Node 16386: (actual time=1.786..4721.854 rows=3330709 loops=1)
Node 16388: (actual time=0.085..4782.529 rows=3337145 loops=1)
-> Seq Scan on public.t_hundred_million (cost=0.00..38513.75 rows=2500000 width=16) (actual time=0.009..0.009 rows=0 loops=1)
Plan id: 3
Output: t_hundred_million.id, t_hundred_million.value
Remote node: 16388,16386,16387,16385
Node 16385: (actual time=0.069..554.526 rows=2497291 loops=1)
Node 16387: (actual time=0.076..624.383 rows=2500608 loops=1)
Node 16386: (actual time=0.077..663.953 rows=2500572 loops=1)
Node 16388: (actual time=0.071..654.229 rows=2501529 loops=1)
Planning time: 0.504 ms
Execution time: 17984.772 ms
(26 rows)
Time: 18246.613 ms
postgres=# select node_name, count(1) from t_hundred_million_new a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+---------
dn1 | 3337145
dn2 | 3330709
dn3 | 3332146
(3 rows)
Time: 9113.752 ms1亿条数据rebalance
postgres=# --1亿，重分布，节点减少 (dn1,dn2,dn3,dn4)->(dn1,dn2,dn3)
postgres=# select node_name, count(1) from t_thousand_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+----------
dn1 | 25003114
dn2 | 24994822
dn3 | 25004264
dn4 | 24997800
(4 rows)
Time: 85864.177 ms
postgres=# alter table t_thousand_million to node(dn1,dn2,dn3);
ALTER TABLE
Time: 682586.634 ms
postgres=# select node_name, count(1) from t_thousand_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+----------
dn1 | 33340379
dn2 | 33327029
dn3 | 33332592
(3 rows)
Time: 109535.877 ms
postgres=# --1亿，重分布，节点增加 (dn1,dn3,dn4) -> (dn1,dn2,dn3,dn4)
postgres=# alter table t_thousand_million to node(dn1,dn2,dn3,dn4);
ALTER TABLE
Time: 643187.928 ms
postgres=# select node_name, count(1) from t_thousand_million a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+----------
dn1 | 25003114
dn2 | 24994822
dn3 | 25004264
dn4 | 24997800
(4 rows)
Time: 80667.392 ms1亿条数据插入新表
postgres=# explain (verbose, analyze, plan_id) insert into t_thousand_million_new select * from t_thousand_million;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Cluster Gather (cost=1001.00..30660934.44 rows=100000456 width=16) (actual time=197525.915..197525.915 rows=0 loops=1)
Plan id: 0
Remote node: 16385,16386,16387,16388
-> Insert on public.t_thousand_million_new (cost=1.00..659797.64 rows=33333485 width=16) (actual time=0.108..0.108 rows=0 loops=1)
Plan id: 1
Node 16385: (actual time=82753.501..82753.501 rows=0 loops=1)
Node 16386: (actual time=188120.349..188120.349 rows=0 loops=1)
Node 16388: (actual time=193391.484..193391.484 rows=0 loops=1)
Node 16387: (actual time=197524.191..197524.191 rows=0 loops=1)
-> Cluster Reduce (cost=1.00..659797.64 rows=33333485 width=16) (actual time=0.106..0.106 rows=0 loops=1)
Plan id: 2
Reduce: ('[0:2]={16388,16386,16387}'::oid[])[COALESCE(int4abs((hashint8(t_thousand_million.id) % 3)), 0)]
Node 16385: (actual time=82753.499..82753.499 rows=0 loops=1)
Node 16386: (actual time=0.758..43715.150 rows=33327029 loops=1)
Node 16388: (actual time=0.123..46315.190 rows=33340379 loops=1)
Node 16387: (actual time=2.445..92578.101 rows=33332592 loops=1)
-> Seq Scan on public.t_thousand_million (cost=0.00..385136.89 rows=25000114 width=16) (actual time=0.010..0.010 rows=0 loops=1)
Plan id: 3
Output: t_thousand_million.id, t_thousand_million.value
Remote node: 16388,16386,16387,16385
Node 16385: (actual time=0.093..5551.210 rows=24997800 loops=1)
Node 16386: (actual time=0.107..5895.898 rows=24994822 loops=1)
Node 16388: (actual time=0.103..6370.683 rows=25003114 loops=1)
Node 16387: (actual time=0.090..6057.547 rows=25004264 loops=1)
Planning time: 0.524 ms
Execution time: 197551.749 ms
(26 rows)
Time: 197733.173 ms
postgres=# select node_name, count(1) from t_thousand_million_new a, pgxc_node b where a.xc_node_id = b.node_id group by 1 order by 1;
node_name | count
-----------+----------
dn1 | 33340379
dn2 | 33327029
dn3 | 33332592
(3 rows)
Time: 98975.694 msAntDB重分布与插入新表性能对比图
总结：
如上图所示，在无索引的情况下，用插入一张新表的方法模拟重分布，实际用时约占重分布方式的20%比重。这便意味着AntDB(PGXC)的重分布逻辑尚有优化的空间。
1065
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
