927
HTAP數據庫 PostgreSQL 場景與性能測試之 23 - (OLAP) 並行計算
標簽
PostgreSQL , HTAP , OLTP , OLAP , 場景與性能測試
背景
PostgreSQL是一個曆史悠久的數據庫，曆史可以追溯到1973年，最早由2014計算機圖靈獎得主，關係數據庫的鼻祖Michael_Stonebraker 操刀設計，PostgreSQL具備與Oracle類似的功能、性能、架構以及穩定性。
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流計算、分布式並行計算、時序處理、基因測序、化學分析、圖像分析 等。
在各種應用場景中都可以看到PostgreSQL的應用：
PostgreSQL近年來的發展非常迅勐，從知名數據庫評測網站dbranking的數據庫評分趨勢，可以看到PostgreSQL向上發展的趨勢：
從每年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》
https://www.postgresql.org/docs/10/static/pgbench.html
最後更新：2017-11-14 14:04:22