开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

TPC-H测试 - PostgreSQL 10 vs Deepgreen(Greenplum)

简介:
+关注继续查看

标签

PostgreSQL , Deepgreen , Greenplum


背景

通常一家企业除了有在线业务处理的数据库、也会有数据分析型的数据库。

很长一段时间以来,数据库产品也是分场景设计的,例如MySQL就是非常典型的TP型数据库。Greenplum则是非常典型的AP型数据库。

Oracle介于两者之间,具备典型TP型数据库的能力,同时还具备小型的数据分析的能力(例如通过并行查询提升计算能力,通过分析型SQL语法支持分析功能,通过大量的分析函数、窗口函数支持计算逻辑)。当数据量特别庞大时,还是典型的AP更加擅长(例如Greenplum)。

目前除了商业数据库具备了TP+小型AP的能力,开源数据库也在往这块发展。

开源数据库的TP+AP也分为两个阵营:一个是中间件阵营,一个是数据库阵营。

1、中间件阵营,中间件不存数据,仅仅作为SQL解析、用户交互、计算。

中间件也分为两种模式:

一种是纯中间件,通过通用数据库接口连接数据库。这种往往数据传输效率不高。

一种是用引擎的API,直接读写数据文件。

中间件模式的好处是易于横向扩展,毕竟中间件本身是无状态的。

缺点也很明显,毕竟不是完全掌控数据库存储,下推做得再好,总有下推不了的,下推不了的时候就得将数据收上来计算,存在搬运数据的可能。

2、数据库引擎阵营,沿袭了Oracle的作风。

优势显而易见,因为是数据库内核本身实现的,所以效率非常高。

缺点是实现难度较高,所以开源界目前也只有PostgreSQL在往这个方向发展。

我们可以看到PostgreSQL最近几年发布的特性,大量数据计算能力大幅提升:

1、多核并行,单条SQL可以利用多个CPU并行计算。处理大查询非常高效。(通常是线性提升,例如32个并行,提升32倍。)

2、向量计算,使用CPU的向量计算指令,减少函数回调,大幅提升大量数据处理的性能。(通常提升10倍左右。)

需安装插件。(VOPS)

3、JIT,动态编译,在处理大量的条件过滤或表达式时,性能提升非常的明显。(通常提升3~5倍)。

4、列存储,更容易和JIT,向量计算结合,同时在处理按列统计时,效果非常好。

需安装插件。(imcs, cstore)

5、算子复用,一些聚合操作,中间步骤复用算子,减少运算量。效果提升也比较明显。

6、GPU,利用GPU的计算能力,例如在十多个大表的JOIN时,效果提升20倍以上。

需安装插件。(pg_strom)

7、FPGA,利用FPGA的计算能力,效果与GPU类似。

需安装插件。

8、MPP插件,例如Citus插件,可以把PG数据库变成分布式数据库。

需安装插件。(citus)

9、流式计算,将计算分摊到每分每秒,解决集中式计算的运力需求。就好像春运一样,需要大量运力,而流计算不需要大量运力,因为把春运抹平了。

需安装插件。(pipelinedb)

10、时序插件,对应时序处理很有效。

需安装插件。(timescale)

11、R、Python组件,用户可以编写R或Python的计算逻辑,在数据库中直接运行用户编写的代码,将数据和计算整合在一起,提升效率。

安装语言插件(plpython, plr)。

12、MADLIB,机器学习库。通过函数接口进行调用,也是进军OLAP的信号。

需安装插件。(madlib)

这么多的信号在表明,PostgreSQL是一个HTAP数据库,用户即能用PG处理OLTP业务,同时也能处理OLAP业务场景。

资源调度方面,目前还没有内置resource queue的功能,但是PG是进程模式,即使是使用CGROUP来管理也是可行的。

说了这么多,PG 10的原生(不安装任何插件)的TPC-H性能如何?

我以Deepgreen为参照,对比PG 10的tpc-h性能。

Deepgreen是一个基于PostgreSQL的MPP数据库,应该是目前性能最好的OLAP商业产品。拿DP来比并不是不自量力,而是找到差距,看到可以提升的点。

测试使用32C,512G,SSD环境。PG10开32个并行度。Deepgreen使用列存储,开48个计算节点。

PostgreSQL 10 TPC-H

explain result

Deepgreen TPC-H

explain result

小结

pic

对比两者的EXPLAIN,DP的计算资源利用非常到位。(得益于JIT、列存储、向量计算和它的分布式优化器)

1. PG 10 优化器还有优化的空间。

query 2, 20 没有拿到最优的执行计划,调整开关后性能更佳。

query 2

set enable_mergejoin=off;  
set enable_nestloop=off;  
2s  

query 20

set enable_hashjoin=off;  
82s  

2. 不可否认,相比其他OLTP数据库,PG 10的AP能力已经领先很多年了。

3. 在同等硬件条件下,PG 10软件层面还有优化空间。

由于没有使用PG 10的向量计算、列存储插件,PG 10的效率还有很大的提升空间。

4. query 17, query 18和DP的差距非常明显,即使将执行计划都调成一样的。DP经过几轮重分布,48个计算节点,小数据量的内部HASH JOIN。

PG 10 的并行HASH JOIN还有很大的优化空间,(其实PG 10比PG 9.6已经有了较大的优化,比如HASH TABLE不需要每个worker一份COPY了)。

query 17

PG 10

 Limit  (cost=30996689.95..30996689.97 rows=1 width=8)  
   ->  Aggregate  (cost=30996689.95..30996689.97 rows=1 width=8)  
         ->  Hash Join  (cost=17681423.82..30996217.92 rows=188813 width=8)  
               Hash Cond: (part.p_partkey = lineitem_1.l_partkey)  
               Join Filter: (lineitem.l_quantity < (('0.2'::double precision * avg(lineitem_1.l_quantity))))  
               ->  Gather  (cost=729877.62..14035697.89 rows=593311 width=32)  
                     Workers Planned: 32  
                     ->  Hash Join  (cost=729877.62..14035697.89 rows=18541 width=32)  
                           Hash Cond: (lineitem.l_partkey = part.p_partkey)  
                           ->  Parallel Seq Scan on lineitem  (cost=0.00..13235317.90 rows=18751190 width=24)  
                           ->  Hash  (cost=729630.42..729630.42 rows=19776 width=8)  
                                 ->  Seq Scan on part  (cost=0.00..729630.42 rows=19776 width=8)  
                                       Filter: ((p_brand = 'Brand#34'::bpchar) AND (p_container = 'WRAP PACK'::bpchar))  
               ->  Hash  (cost=16712866.90..16712866.90 rows=19094344 width=16)  
                     ->  HashAggregate  (cost=16235508.30..16521923.46 rows=19094344 width=16)  
                           Group Key: lineitem_1.l_partkey  
                           ->  Gather  (cost=0.00..13235317.90 rows=600038080 width=16)  
                                 Workers Planned: 32  
                                 ->  Parallel Seq Scan on lineitem lineitem_1  (cost=0.00..13235317.90 rows=18751190 width=16)  

DP

 Limit  (cost=623298.39..623298.41 rows=1 width=8)  
   ->  Aggregate  (cost=623298.39..623298.41 rows=1 width=8)  
         ->  Gather Motion 48:1  (slice3; segments: 48)  (cost=623297.88..623298.38 rows=1 width=8)  
               ->  Aggregate  (cost=623297.88..623297.89 rows=1 width=8)  
                     ->  Hash Join  (cost=509798.96..623278.29 rows=164 width=8)  
                           Hash Cond: postgres.lineitem.l_partkey = part.p_partkey  
                           Join Filter: postgres.lineitem.l_quantity < part_agg.avg_quantity  
                           ->  Append  (cost=0.00..101520.00 rows=99001 width=24)  
                                 ->  Append-only Columnar Scan on lineitem_1_prt_1 lineitem  (cost=0.00..163.56 rows=160 width=24)  
......  
                                 ->  Append-only Columnar Scan on lineitem_1_prt_96 lineitem  (cost=0.00..0.00 rows=1 width=24)  
                           ->  Hash  (cost=509789.01..509789.01 rows=17 width=24)  
                                 ->  Broadcast Motion 48:48  (slice2; segments: 48)  (cost=125352.63..509789.01 rows=17 width=24)  
                                       ->  Hash Join  (cost=125352.63..509780.88 rows=1 width=24)  
                                             Hash Cond: part.p_partkey = part_agg.agg_partkey  
                                             ->  Append-only Columnar Scan on part  (cost=0.00..384181.00 rows=2059 width=8)  
                                                   Filter: p_brand = 'Brand#34'::bpchar AND p_container = 'WRAP PACK'::bpchar  
                                             ->  Hash  (cost=125340.12..125340.12 rows=21 width=16)  
                                                   ->  HashAggregate  (cost=125315.10..125330.11 rows=21 width=16)  
                                                         Group By: postgres.lineitem.l_partkey  
                                                         ->  Redistribute Motion 48:48  (slice1; segments: 48)  (cost=125280.06..125300.08 rows=21 width=40)  
                                                               Hash Key: postgres.lineitem.l_partkey  
                                                               ->  HashAggregate  (cost=125280.06..125280.06 rows=21 width=40)  
                                                                     Group By: postgres.lineitem.l_partkey  
                                                                     ->  Append  (cost=0.00..101520.00 rows=99001 width=16)  
                                                                           ->  Append-only Columnar Scan on lineitem_1_prt_1 lineitem  (cost=0.00..163.56 rows=160 width=16)  
......  
                                                                           ->  Append-only Columnar Scan on lineitem_1_prt_96 lineitem  (cost=0.00..0.00 rows=1 width=16)  
 Settings:  optimizer=off  
 Optimizer status: legacy query optimizer  

query 18

PG 10

 Limit  (cost=119441562.94..119441565.69 rows=100 width=55)  
   ->  GroupAggregate  (cost=119441562.94..130457672.11 rows=400585788 width=55)  
         Group Key: orders.o_totalprice, orders.o_orderdate, customer.c_name, customer.c_custkey, orders.o_orderkey  
         ->  Sort  (cost=119441562.94..120443027.41 rows=400585788 width=55)  
               Sort Key: orders.o_totalprice DESC, orders.o_orderdate, customer.c_name, customer.c_custkey, orders.o_orderkey  
               ->  Hash Join  (cost=27436526.10..53792051.41 rows=400585788 width=55)  
                     Hash Cond: (orders.o_orderkey = lineitem_1.l_orderkey)  
                     ->  Gather  (cost=7946450.25..26726494.83 rows=600038080 width=63)  
                           Workers Planned: 32  
                           ->  Hash Join  (cost=7946450.25..26726494.83 rows=18751190 width=63)  
                                 Hash Cond: (orders.o_custkey = customer.c_custkey)  
                                 ->  Hash Join  (cost=7228662.12..25422732.02 rows=18751190 width=44)  
                                       Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)  
                                       ->  Parallel Seq Scan on lineitem  (cost=0.00..13235317.90 rows=18751190 width=16)  
                                       ->  Hash  (cost=4328257.72..4328257.72 rows=150000672 width=28)  
                                             ->  Seq Scan on orders  (cost=0.00..4328257.72 rows=150000672 width=28)  
                                 ->  Hash  (cost=530291.39..530291.39 rows=14999739 width=27)  
                                       ->  Seq Scan on customer  (cost=0.00..530291.39 rows=14999739 width=27)  
                     ->  Hash  (cost=18238319.10..18238319.10 rows=100140540 width=8)  
                           ->  HashAggregate  (cost=16235508.30..17236913.70 rows=100140540 width=8)  
                                 Group Key: lineitem_1.l_orderkey  
                                 Filter: (sum(lineitem_1.l_quantity) > '314'::double precision)  
                                 ->  Gather  (cost=0.00..13235317.90 rows=600038080 width=16)  
                                       Workers Planned: 32  
                                       ->  Parallel Seq Scan on lineitem lineitem_1  (cost=0.00..13235317.90 rows=18751190 width=16)  

DP

 Limit  (cost=1373517167.24..1373517172.24 rows=100 width=104)  
   ->  Gather Motion 48:1  (slice3; segments: 48)  (cost=1373517167.24..1373517172.24 rows=100 width=104)  
         Merge Key: postgres.orders.o_totalprice, postgres.orders.o_orderdate, customer.c_name, customer.c_custkey, postgres.orders.o_orderkey  
         ->  Limit  (cost=1373517167.24..1373517170.24 rows=3 width=104)  
               ->  GroupAggregate  (cost=1373517167.24..1511389736.43 rows=95744840 width=104)  
                     Group By: postgres.orders.o_totalprice, postgres.orders.o_orderdate, customer.c_name, customer.c_custkey, postgres.orders.o_orderkey  
                     ->  Sort  (cost=1373517167.24..1385006548.00 rows=95744840 width=55)  
                           Sort Key: postgres.orders.o_totalprice, postgres.orders.o_orderdate, customer.c_name, customer.c_custkey, postgres.orders.o_orderkey  
                           ->  Hash Join  (cost=486078.65..58117733.98 rows=95744840 width=55)  
                                 Hash Cond: "IN_subquery".l_orderkey = postgres.orders.o_orderkey  
                                 ->  Hash Join  (cost=125317.60..298177.18 rows=99100 width=24)  
                                       Hash Cond: postgres.lineitem.l_orderkey = "IN_subquery".l_orderkey  
                                       ->  Append  (cost=0.00..101520.00 rows=99001 width=16)  
                                             ->  Append-only Columnar Scan on lineitem_1_prt_1 lineitem  (cost=0.00..163.56 rows=160 width=16)  
......  
                                             ->  Append-only Columnar Scan on lineitem_1_prt_96 lineitem  (cost=0.00..0.00 rows=1 width=16)  
                                       ->  Hash  (cost=125305.09..125305.09 rows=21 width=8)  
                                             ->  HashAggregate  (cost=125280.06..125295.08 rows=21 width=8)  
                                                   Filter: sum(postgres.lineitem.l_quantity) > 314::double precision  
                                                   Group By: postgres.lineitem.l_orderkey  
                                                   ->  Append  (cost=0.00..101520.00 rows=99001 width=16)  
                                                         ->  Append-only Columnar Scan on lineitem_1_prt_1 lineitem  (cost=0.00..163.56 rows=160 width=16)  
......  
                                                         ->  Append-only Columnar Scan on lineitem_1_prt_96 lineitem  (cost=0.00..0.00 rows=1 width=16)  
                                 ->  Hash  (cost=348684.17..348684.17 rows=20129 width=47)  
                                       ->  Redistribute Motion 48:48  (slice2; segments: 48)  (cost=52166.26..348684.17 rows=20129 width=47)  
                                             Hash Key: postgres.orders.o_orderkey  
                                             ->  Hash Join  (cost=52166.26..329361.15 rows=20129 width=47)  
                                                   Hash Cond: customer.c_custkey = postgres.orders.o_custkey  
                                                   ->  Append-only Columnar Scan on customer  (cost=0.00..227618.00 rows=312500 width=27)  
                                                   ->  Hash  (cost=40089.37..40089.37 rows=20129 width=28)  
                                                         ->  Redistribute Motion 48:48  (slice1; segments: 48)  (cost=0.00..40089.37 rows=20129 width=28)  
                                                               Hash Key: postgres.orders.o_custkey  
                                                               ->  Append  (cost=0.00..20766.35 rows=20129 width=28)  
                                                                     ->  Append-only Columnar Scan on orders_1_prt_1 orders  (cost=0.00..267.41 rows=260 width=28)  
......  
                                                                     ->  Append-only Columnar Scan on orders_1_prt_96 orders  (cost=0.00..0.00 rows=1 width=28)  
 Settings:  optimizer=off  
 Optimizer status: legacy query optimizer  

5. PG 10对相关性好的某些列,使用了BRIN索引,比如ORDERS,LINEITEM表的日期字段,未使用分区表。DP使用了时间按天分区。

详见

https://github.com/digoal/gp_tpch/blob/master/dss/tpch-load.sql.pg10

https://github.com/digoal/gp_tpch/blob/master/dss/tpch-load.sql.column

参考

1. https://github.com/digoal/gp_tpch

2. 《Parallel Query In PostgreSQL》

3. https://sites.google.com/site/robertmhaas/presentations

4. 《Hash Joins Past, Present, Future》

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SpringIOC源码解析(5)—— 通过document对象解析出BeanDefinition实例
location BeanDefinitionReader Resource BeanDefinitionRegistry Resourceloader DefaultListableBeanFactory
27 0
greenplum 4.1 版本及老版本如何使用读写外部表+管道实现gptransfer同步功能
greenplum 4.1 版本及老版本如何使用读写外部表+管道实现gptransfer同步功能
693 0
Deepgreen & Greenplum 高可用(二) - Master故障转移
上一篇文章中提到了Segment节点的故障转移,其中主要涉及Mirror添加、故障自动切换、故障修复后balanced到原集群状态,以及一些建议。具体请移步传送门-->《Deepgreen & Greenplum 高可用(一) - Segment节点故障转移 》。
4308 0
Deepgreen与Greenplum TPC-H性能测试对比(使用VitesseData脚本)
前两天发了一篇基于[德哥测试脚本]的测试对比文章《Deepgreen与Greenplum TPC-H性能测试对比(使用德哥脚本)》,由于测试数据量少,两个数据库有几轮测试都是1秒持平,但是大多数测试Deepgreen均优于Greenplum,有的甚至快至百倍,感兴趣的朋友可以再回头看看。
7270 0
VMware安装MacOS虚拟机 "\\.\Global\vmx86": 系统找不到指定的文件
版权声明:本文为 testcs_dn(微wx笑) 原创文章,非商用自由转载-保持署名-注明出处,谢谢。 https://blog.csdn.net/testcs_dn/article/details/51345912 使用VMware安装MacOS虚拟机,启动客户机时提示: "\\.\Global\vmx86": 系统找不到指定的文件。
582 0
+关注
德哥
公益是一辈子的事, I&#39;m digoal, just do it.
2153
文章
245
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
相关文档: 云数据库 OceanBase 版 可信账本数据库 云原生关系型数据库 PolarDB PostgreSQL引擎
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载