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個計算節點。

<a href="https://github.com/digoal/blog/blob/master/201707/20170714_01_zip_001.tar.bz2">explain result</a>

<a href="https://github.com/digoal/blog/blob/master/201707/20170714_01_zip_002.tar.bz2">explain result</a>

對比兩者的explain，dp的計算資源利用非常到位。（得益于jit、列存儲、向量計算和它的分布式優化器）

1. pg 10 優化器還有優化的空間。

query 2, 20 沒有拿到最優的執行計劃，調整開關後性能更佳。

query 2

query 20

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

dp

query 18

5. pg 10對相關性好的某些列，使用了brin索引，比如orders，lineitem表的日期字段，未使用分區表。dp使用了時間按天分區。

詳見

<a href="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.pg10</a>

<a href="https://github.com/digoal/gp_tpch/blob/master/dss/tpch-load.sql.column">https://github.com/digoal/gp_tpch/blob/master/dss/tpch-load.sql.column</a>