HTAP資料庫PostgreSQL場景與效能測試之23-(OLAP)平行計算

德哥發表於2017-11-14
資料庫SQL

標籤

PostgreSQL , HTAP , OLTP , OLAP , 場景與效能測試

背景

PostgreSQL是一個歷史悠久的資料庫，歷史可以追溯到1973年，最早由2014計算機圖靈獎得主，關聯式資料庫的鼻祖Michael_Stonebraker 操刀設計，PostgreSQL具備與Oracle類似的功能、效能、架構以及穩定性。

pic

PostgreSQL社群的貢獻者眾多，來自全球各個行業，歷經數年，PostgreSQL 每年釋出一個大版本，以持久的生命力和穩定性著稱。

2017年10月，PostgreSQL 推出10 版本，攜帶諸多驚天特性，目標是勝任OLAP和OLTP的HTAP混合場景的需求：

《最受開發者歡迎的HTAP資料庫PostgreSQL 10特性》

1、多核並行增強

2、fdw 聚合下推

3、邏輯訂閱

4、分割槽

5、金融級多副本

6、json、jsonb全文檢索

7、還有外掛化形式存在的特性，如 向量計算、JIT、SQL圖計算、SQL流計算、分散式平行計算、時序處理、基因測序、化學分析、影像分析 等。

pic

在各種應用場景中都可以看到PostgreSQL的應用：

pic

PostgreSQL近年來的發展非常迅猛，從知名資料庫評測網站dbranking的資料庫評分趨勢，可以看到PostgreSQL向上發展的趨勢：

pic

從每年PostgreSQL中國召開的社群會議，也能看到同樣的趨勢，參與的公司越來越多，分享的公司越來越多，分享的主題越來越豐富，橫跨了 傳統企業、網際網路、醫療、金融、國企、物流、電商、社交、車聯網、共享XX、雲、遊戲、公共交通、航空、鐵路、軍工、培訓、諮詢服務等 行業。

接下來的一系列文章，將給大家介紹PostgreSQL的各種應用場景以及對應的效能指標。

環境

環境部署方法參考：

《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(適合新使用者)》

阿里雲 ECS：56核，224G，1.5TB*2 SSD雲盤。

作業系統：CentOS 7.4 x64

資料庫版本：PostgreSQL 10

PS：ECS的CPU和IO效能相比物理機會打一定的折扣，可以按下降1倍效能來估算。跑物理主機可以按這裡測試的效能乘以2來估算。

場景 – 平行計算 (OLAP)

1、背景

PostgreSQL 從9.6開始支援平行計算，使得OLTP和OLAP可以在一個例項中實現。

1、並行排序

2、並行全表掃描

3、並行JOIN

4、並行聚合

5、並行filter

2、設計

1億資料，包括整型，字串，浮點，時間。

1、並行排序

2、並行全表掃描

3、並行JOIN

4、並行聚合

5、並行filter

3、準備測試表

create table t1 (id int, c1 float4, c2 text, c3 timestamp, c4 int);  
create table t2 (id int, c1 float4, c2 text, c3 timestamp, c4 int);

4、準備測試函式(可選)

5、準備測試資料

insert into t1 select id, random()*1000, md5(random()::text), clock_timestamp(), random()*100 from generate_series(1,100000000) t(id);  
insert into t2 select id, random()*1000, md5(random()::text), clock_timestamp(), random()*1000 from generate_series(1,100000000) t(id);

6、準備測試指令碼

set parallel_setup_cost =0;  
set parallel_tuple_cost =0;  
set max_parallel_workers_per_gather =32;  
alter table t1 set (parallel_workers =32);  
alter table t2 set (parallel_workers =32);

1、並行排序，1億記錄排序。

select * from t1 order by id desc limit 1;

2、並行全表掃描，1億記錄全表掃描，求id=1的記錄。

select count(*) from t1 where id=1;

3、並行JOIN，1億記錄 A JOIN B 1億記錄，按A表的輸入條件過濾，按B表聚合。

select count(b.c4) from t2 a join t1 b on (a.id=b.id and a.c4=1);

4、並行聚合，1億記錄，分組聚合。

select max(c1) from t1 where c4>90 group by c4;

5、並行filter，並行的字串、浮點、時間、整型過濾。

select count(*) from t1 where c2=`abc` or c1<`10` or c3<`2017-01-01` or c4>123;

7、測試

1、並行排序，1億記錄排序。 2.6 秒。

postgres=# explain select * from t1 order by id desc limit 1;  
                                        QUERY PLAN  
------------------------------------------------------------------------------------------  
 Limit  (cost=1568818.77..1568818.80 rows=1 width=53)  
   ->  Gather Merge  (cost=1568818.77..4348829.22 rows=100000032 width=53)  
         Workers Planned: 32  
         ->  Sort  (cost=1568817.94..1576630.44 rows=3125001 width=53)  
               Sort Key: id DESC  
               ->  Parallel Seq Scan on t1  (cost=0.00..1167614.01 rows=3125001 width=53)  
(6 rows)  
  
postgres=# select id from t1 order by id desc limit 1;  
    id  
-----------  
 100000000  
(1 row)  
Time: 2600.160 ms (00:02.600)

2、並行全表掃描，1億記錄全表掃描，求id=1的記錄。 0.88 秒。

postgres=# explain select count(*) from t1 where id=1;  
                                 QUERY PLAN  
-----------------------------------------------------------------------------  
 Aggregate  (cost=1175426.51..1175426.52 rows=1 width=8)  
   ->  Gather  (cost=0.00..1175426.51 rows=1 width=0)  
         Workers Planned: 32  
         ->  Parallel Seq Scan on t1  (cost=0.00..1175426.51 rows=1 width=0)  
               Filter: (id = 1)  
(5 rows)  
  
postgres=# select count(*) from t1 where id=1;  
 count  
-------  
     1  
(1 row)  
Time: 882.059 ms

3、並行JOIN，1億記錄 A JOIN B 1億記錄，按A表的輸入條件過濾，按B表聚合。 17 秒。

postgres=# explain select count(b.c4) from t2 a join t1 b on (a.id=b.id and a.c4=1);  
                                              QUERY PLAN  
-------------------------------------------------------------------------------------------------------  
 Finalize Aggregate  (cost=4330512.77..4330512.78 rows=1 width=8)  
   ->  Gather  (cost=4330512.72..4330512.73 rows=16 width=8)  
         Workers Planned: 16  
         ->  Partial Aggregate  (cost=4330512.72..4330512.73 rows=1 width=8)  
               ->  Merge Join  (cost=4298704.54..4330497.64 rows=6034 width=4)  
                     Merge Cond: (b.id = a.id)  
                     ->  Sort  (cost=1904346.28..1919971.29 rows=6250002 width=8)  
                           Sort Key: b.id  
                           ->  Parallel Seq Scan on t1 b  (cost=0.00..1198864.02 rows=6250002 width=8)  
                     ->  Sort  (cost=2394358.25..2394599.63 rows=96550 width=4)  
                           Sort Key: a.id  
                           ->  Seq Scan on t2 a  (cost=0.00..2386364.40 rows=96550 width=4)  
                                 Filter: (c4 = 1)  
(13 rows)  
  
postgres=# select count(b.c4) from t2 a join t1 b on (a.id=b.id and a.c4=1);  
 count  
-------  
 99854  
(1 row)  
  
Time: 17333.843 ms (00:17.334)

4、並行聚合，1億記錄，分組聚合。 0.9 秒。

postgres=# explain select max(c1) from t1 where c4>90 group by c4;  
                                          QUERY PLAN  
----------------------------------------------------------------------------------------------  
 Finalize GroupAggregate  (cost=1177103.43..1177128.68 rows=101 width=8)  
   Group Key: c4  
   ->  Sort  (cost=1177103.43..1177111.51 rows=3232 width=8)  
         Sort Key: c4  
         ->  Gather  (cost=1176914.03..1176915.04 rows=3232 width=8)  
               Workers Planned: 32  
               ->  Partial HashAggregate  (cost=1176914.03..1176915.04 rows=101 width=8)  
                     Group Key: c4  
                     ->  Parallel Seq Scan on t1  (cost=0.00..1175426.51 rows=297503 width=8)  
                           Filter: (c4 > 90)  
(10 rows)  
  
postgres=# select max(c1) from t1 where c4>90 group by c4;  
   max  
---------  
    1000  
 999.999  
 999.999  
 999.999  
 999.999  
 999.996  
    1000  
    1000  
    1000  
 999.999  
(10 rows)  
  
Time: 945.695 ms

5、並行filter，並行的字串、浮點、時間、整型過濾。 1 秒。

postgres=# explain select count(*) from t1 where c2=`abc` or c1<`10` or c3<`2017-01-01` or c4>123;  
                                                                    QUERY PLAN  
--------------------------------------------------------------------------------------------------------------------------------------------------  
 Finalize Aggregate  (cost=1198947.36..1198947.38 rows=1 width=8)  
   ->  Gather  (cost=1198947.27..1198947.28 rows=32 width=8)  
         Workers Planned: 32  
         ->  Partial Aggregate  (cost=1198947.27..1198947.28 rows=1 width=8)  
               ->  Parallel Seq Scan on t1  (cost=0.00..1198864.02 rows=33302 width=0)  
                     Filter: ((c2 = `abc`::text) OR (c1 < `10`::real) OR (c3 < `2017-01-01 00:00:00`::timestamp without time zone) OR (c4 > 123))  
(6 rows)  
  
postgres=# select count(*) from t1 where c2=`abc` or c1<`10` or c3<`2017-01-01` or c4>123;  
 count  
--------  
 999179  
(1 row)  
  
Time: 1015.627 ms (00:01.016)

TPS

平均響應時間

1、並行排序，1億記錄排序。 2.6 秒。

2、並行全表掃描，1億記錄全表掃描，求id=1的記錄。 0.88 秒。

3、並行JOIN，1億記錄 A JOIN B 1億記錄，按A表的輸入條件過濾，按B表聚合。 17 秒。

4、並行聚合，1億記錄，分組聚合。 0.9 秒。

5、並行filter，並行的字串、浮點、時間、整型過濾。 1 秒。

參考

《PostgreSQL、Greenplum 應用案例寶典《如來神掌》 – 目錄》

《資料庫選型之 – 大象十八摸 – 致 架構師、開發者》

《PostgreSQL 使用 pgbench 測試 sysbench 相關case》

《資料庫界的華山論劍 tpc.org》

https://www.postgresql.org/docs/10/static/pgbench.html


相關文章