Deepgreen与Greenplum TPC-H性能测试对比（使用德哥脚本）

Deepgreen数据库基于开源MPP数据库Greenplum而来，那么他的优越性几何？今天给大家分享数据仓库测试TPC-H的结果让大家加以比较：

本次对比需要注意的几点：

测试参照德哥2015年发的【Greenplum的TPC-H】测试，只做了压缩类型的简单修改
由于测试机器性能问题，可能无法最大化展示二者性能（greenplum部分测试timeout）

一、测试环境：

服务器         IP              节点
Master          192.168.100.107 1 Master
Segment1        192.168.100.108 2 instance
Segment2        192.168.100.109 2 instance
软件版本：
Greenplum 4.3.12
Deepgreen 16.16

二、TPC-H测试结果

Greenplum：

2017-05-19 13:59:16 [1495173556] : preparing TPC-H database
2017-05-19 13:59:16 [1495173556] :   loading data
2017-05-19 13:59:39 [1495173579] :   creating primary keys
2017-05-19 13:59:39 [1495173579] :   creating foreign keys
2017-05-19 13:59:39 [1495173579] :   creating indexes
2017-05-19 13:59:57 [1495173597] :   analyzing
2017-05-19 14:00:10 [1495173610] : running TPC-H benchmark
2017-05-19 14:00:10 [1495173610] : running queries defined in TPC-H benchmark
2017-05-19 14:00:10 [1495173610] :   running query 1
2017-05-19 14:00:21 [1495173621] :     query 1 finished OK (6 seconds)
2017-05-19 14:00:21 [1495173621] :   running query 2
2017-05-19 14:08:37 [1495174117] :     query 2 finished OK (250 seconds)
2017-05-19 14:08:37 [1495174117] :   running query 3
2017-05-19 14:53:15 [1495176795] :     query 3 finished OK (1363 seconds)
2017-05-19 14:53:15 [1495176795] :   running query 4
2017-05-19 14:53:17 [1495176797] :     query 4 finished OK (1 seconds)
2017-05-19 14:53:17 [1495176797] :   running query 5
2017-05-19 14:53:18 [1495176798] :     query 5 finished OK (1 seconds)
2017-05-19 14:53:18 [1495176798] :   running query 6
2017-05-19 14:53:19 [1495176799] :     query 6 finished OK (1 seconds)
2017-05-19 14:53:19 [1495176799] :   running query 7
2017-05-19 15:28:32 [1495178912] :     query 7 finished OK (1057 seconds)
2017-05-19 15:28:32 [1495178912] :   running query 8
2017-05-19 15:54:09 [1495180449] :     query 8 finished OK (777 seconds)
2017-05-19 15:54:09 [1495180449] :   running query 9
2017-05-19 21:52:26 [1495201946] :     killing query 9 (timeout)
2017-05-19 21:52:36 [1495201956] :   running query 10
2017-05-19 21:52:37 [1495201957] :     query 10 finished OK (1 seconds)
2017-05-19 21:52:37 [1495201957] :   running query 11
2017-05-19 21:55:26 [1495202126] :     query 11 finished OK (85 seconds)
2017-05-19 21:55:26 [1495202126] :   running query 12
2017-05-19 21:55:27 [1495202127] :     query 12 finished OK (1 seconds)
2017-05-19 21:55:27 [1495202127] :   running query 13
2017-05-20 00:45:29 [1495212329] :     killing query 13 (timeout)
2017-05-20 00:45:39 [1495212339] :   running query 14
2017-05-20 00:45:40 [1495212340] :     query 14 finished OK (1 seconds)
2017-05-20 00:45:40 [1495212340] :   running query 15
2017-05-20 00:45:42 [1495212342] :     query 15 finished OK (1 seconds)
2017-05-20 00:45:42 [1495212342] :   running query 16
2017-05-20 00:48:30 [1495212510] :     query 16 finished OK (84 seconds)
2017-05-20 00:48:30 [1495212510] :   running query 17
2017-05-20 00:48:46 [1495212526] :     query 17 finished OK (8 seconds)
2017-05-20 00:48:46 [1495212526] :   running query 18
2017-05-20 02:06:47 [1495217207] :     killing query 18 (timeout)
2017-05-20 02:06:58 [1495217218] :   running query 19
2017-05-20 07:11:50 [1495235510] :     killing query 19 (timeout)
2017-05-20 07:12:00 [1495235520] :   running query 20
2017-05-20 07:12:02 [1495235522] :     query 20 finished OK (1 seconds)
2017-05-20 07:12:02 [1495235522] :   running query 21
2017-05-20 09:57:36 [1495245456] :     killing query 21 (timeout)
2017-05-20 09:57:46 [1495245466] :   running query 22
2017-05-20 10:12:01 [1495246321] :     query 22 finished OK (428 seconds)
2017-05-20 10:12:01 [1495246321] : finished TPC-H benchmark

Deepgreen：

[dgadmin@linux1 results]$ cat bench.log
2017-05-19 11:44:56 [1495165496] : preparing TPC-H database
2017-05-19 11:44:56 [1495165496] :   loading data
2017-05-19 11:45:14 [1495165514] :   creating primary keys
2017-05-19 11:45:14 [1495165514] :   creating foreign keys
2017-05-19 11:45:14 [1495165514] :   creating indexes
2017-05-19 11:45:29 [1495165529] :   analyzing
2017-05-19 11:45:32 [1495165532] : running TPC-H benchmark
2017-05-19 11:45:32 [1495165532] : running queries defined in TPC-H benchmark
2017-05-19 11:45:32 [1495165532] :   running query 1
2017-05-19 11:45:40 [1495165540] :     query 1 finished OK (4 seconds)
2017-05-19 11:45:40 [1495165540] :   running query 2
2017-05-19 11:52:23 [1495165943] :     query 2 finished OK (199 seconds)
2017-05-19 11:52:23 [1495165943] :   running query 3
2017-05-19 11:52:27 [1495165947] :     query 3 finished OK (3 seconds)
2017-05-19 11:52:27 [1495165947] :   running query 4
2017-05-19 11:52:28 [1495165948] :     query 4 finished OK (1 seconds)
2017-05-19 11:52:28 [1495165948] :   running query 5
2017-05-19 11:52:29 [1495165949] :     query 5 finished OK (1 seconds)
2017-05-19 11:52:29 [1495165949] :   running query 6
2017-05-19 11:52:30 [1495165950] :     query 6 finished OK (1 seconds)
2017-05-19 11:52:30 [1495165950] :   running query 7
2017-05-19 11:52:34 [1495165954] :     query 7 finished OK (3 seconds)
2017-05-19 11:52:34 [1495165954] :   running query 8
2017-05-19 11:52:36 [1495165956] :     query 8 finished OK (1 seconds)
2017-05-19 11:52:36 [1495165956] :   running query 9
2017-05-19 12:26:01 [1495167961] :     query 9 finished OK (1002 seconds)
2017-05-19 12:26:01 [1495167961] :   running query 10
2017-05-19 12:26:02 [1495167962] :     query 10 finished OK (1 seconds)
2017-05-19 12:26:02 [1495167962] :   running query 11
2017-05-19 12:26:05 [1495167965] :     query 11 finished OK (3 seconds)
2017-05-19 12:26:05 [1495167965] :   running query 12
2017-05-19 12:26:07 [1495167967] :     query 12 finished OK (1 seconds)
2017-05-19 12:26:07 [1495167967] :   running query 13
2017-05-19 12:45:19 [1495169119] :     query 13 finished OK (588 seconds)
2017-05-19 12:45:19 [1495169119] :   running query 14
2017-05-19 12:45:20 [1495169120] :     query 14 finished OK (1 seconds)
2017-05-19 12:45:20 [1495169120] :   running query 15
2017-05-19 12:45:21 [1495169121] :     query 15 finished OK (1 seconds)
2017-05-19 12:45:21 [1495169121] :   running query 16
2017-05-19 12:45:25 [1495169125] :     query 16 finished OK (3 seconds)
2017-05-19 12:45:25 [1495169125] :   running query 17
2017-05-19 12:45:30 [1495169130] :     query 17 finished OK (3 seconds)
2017-05-19 12:45:30 [1495169130] :   running query 18
2017-05-19 12:45:32 [1495169132] :     query 18 finished OK (1 seconds)
2017-05-19 12:45:32 [1495169132] :   running query 19
2017-05-19 12:45:34 [1495169134] :     query 19 finished OK (1 seconds)
2017-05-19 12:45:34 [1495169134] :   running query 20
2017-05-19 12:45:35 [1495169135] :     query 20 finished OK (1 seconds)
2017-05-19 12:45:35 [1495169135] :   running query 21
2017-05-19 12:45:36 [1495169136] :     query 21 finished OK (1 seconds)
2017-05-19 12:45:36 [1495169136] :   running query 22
2017-05-19 12:57:30 [1495169850] :     query 22 finished OK (357 seconds)
2017-05-19 12:57:30 [1495169850] : finished TPC-H benchmark

三、结果对比

总的来说，Deepgreen在整个测试中整体优于Greenplum。在22轮测试中，有8轮持平，其余14轮Deepgreen均优于Greenplum。其中Q3、Q7、Q8、Q11、Q16查询比Greenplum快指数倍；Greenplum的Q9、Q13、Q18、Q19、Q21 timeout，而Deepgreen没有；Q4、Q5、Q6、Q10、Q12、Q14、Q15、Q20二者持平。