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

小結

對比兩者的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)

 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)

 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