标簽
PostgreSQL , HTAP , OLTP , OLAP , 場景與性能測試
https://github.com/digoal/blog/blob/master/201711/20171107_49.md#%E8%83%8C%E6%99%AF 背景
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的各種應用場景以及對應的性能名額。
https://github.com/digoal/blog/blob/master/201711/20171107_49.md#%E7%8E%AF%E5%A2%83 環境
環境部署方法參考：
《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新使用者)》阿裡雲 ECS：
56核，224G，1.5TB*2 SSD雲盤
。
作業系統：
CentOS 7.4 x64
資料庫版本：
PostgreSQL 12
PS：ECS的CPU和IO性能相比實體機會打一定的折扣，可以按下降1倍性能來估算。跑實體主機可以按這裡測試的性能乘以2來估算。
https://github.com/digoal/blog/blob/master/201711/20171107_49.md#%E5%9C%BA%E6%99%AF---%E5%A4%A7%E8%A1%A8join%E7%BB%9F%E8%AE%A1%E6%9F%A5%E8%AF%A2-olap 場景 - 大表JOIN統計查詢 (OLAP)
https://github.com/digoal/blog/blob/master/201711/20171107_49.md#1%E8%83%8C%E6%99%AF 1、背景
多張大表的JOIN，聚合分析。例如有一張使用者表，一張業務日志表表示活躍使用者的行為資料，按天分區。
使用者表1億條記錄，每天1000萬活躍使用者，産生10億行為資料。
根據使用者行為join使用者表，group 使用者表的某些字段，生成使用者畫像。
https://github.com/digoal/blog/blob/master/201711/20171107_49.md#2%E8%AE%BE%E8%AE%A1 2、設計
2張表，1張1億，uid為主鍵。1張10億，訂單号為主鍵，uid關聯第一張表。
https://github.com/digoal/blog/blob/master/201711/20171107_49.md#3%E5%87%86%E5%A4%87%E6%B5%8B%E8%AF%95%E8%A1%A8 3、準備測試表
create unlogged table t_user (uid int8, info text, c1 int, c2 int, c3 int, crt_time timestamp);
create unlogged table t_user_log_20191212(id int8, uid int8, info text, crt_time timestamp);
https://github.com/digoal/blog/blob/master/201711/20171107_49.md#4%E5%87%86%E5%A4%87%E6%B5%8B%E8%AF%95%E5%87%BD%E6%95%B0%E5%8F%AF%E9%80%89 4、準備測試函數(可選)
https://github.com/digoal/blog/blob/master/201711/20171107_49.md#5%E5%87%86%E5%A4%87%E6%B5%8B%E8%AF%95%E6%95%B0%E6%8D%AE 5、準備測試資料
insert into t_user select generate_series(1,100000000), md5(random()::text), random()*10, random()*100, random()*1000, now();
insert into t_user_log_20191212 select generate_series(1,1000000000), random()*10000000, md5(random()::text), now();
空間占用分别為8.8GB, 87GB：
postgres=# \dt+ t_user
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------+-------+----------+---------+-------------
public | t_user | table | postgres | 8880 MB |
(1 row)
postgres=# \dt+ t_user_log_20191212
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------------------+-------+----------+-------+-------------
public | t_user_log_20191212 | table | postgres | 87 GB |
(1 row)
https://github.com/digoal/blog/blob/master/201711/20171107_49.md#6%E5%87%86%E5%A4%87%E6%B5%8B%E8%AF%95%E8%84%9A%E6%9C%AC 6、準備測試腳本
https://github.com/digoal/blog/blob/master/201711/20171107_49.md#7%E6%B5%8B%E8%AF%95 7、測試
alter table t_user set (parallel_workers =64);
alter table t_user_log_20191212 set (parallel_workers =64);
set max_parallel_workers=128;
set max_parallel_workers_per_gather =32;
set min_parallel_table_scan_size =0;
set min_parallel_index_scan_size =0;
set parallel_setup_cost =0;
set parallel_tuple_cost =0;
set jit=on;
set parallel_leader_participation =off;
set enable_sort =off;
set work_mem='64MB';
explain (analyze,verbose,timing,costs,buffers) select c1,count(*) from t_user join t_user_log_20191212 using (uid) group by c1;
select t1.c1,count(*) from t_user t1 join t_user_log_20191212 t2 using (uid) group by c1;
https://github.com/digoal/blog/blob/master/201711/20171107_49.md#8%E6%B5%8B%E8%AF%95%E7%BB%93%E6%9E%9C 8、測試結果
postgres=# explain select c1,count(*) from t_user join t_user_log_20191212 using (uid) group by c1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Finalize HashAggregate (cost=13518803.57..13518803.68 rows=11 width=12)
Group Key: t_user.c1
-> Gather (cost=13518801.70..13518801.81 rows=352 width=12)
Workers Planned: 32
-> Partial HashAggregate (cost=13518801.70..13518801.81 rows=11 width=12)
Group Key: t_user.c1
-> Parallel Hash Join (cost=1221935.52..13362551.69 rows=31250002 width=4)
Hash Cond: (t_user_log_20191212.uid = t_user.uid)
-> Parallel Seq Scan on t_user_log_20191212 (cost=0.00..11676137.02 rows=31250002 width=8)
-> Parallel Hash (cost=1167614.01..1167614.01 rows=3125001 width=12)
-> Parallel Seq Scan on t_user (cost=0.00..1167614.01 rows=3125001 width=12)
JIT:
Functions: 20
Options: Inlining true, Optimization true, Expressions true, Deforming true
(14 rows)
postgres=# select c1,count(*) from t_user join t_user_log_20191212 using (uid) group by c1;
c1 | count
----+-----------
8 | 100163144
7 | 99799644
10 | 50012738
9 | 100095291
1 | 99945550
5 | 99818748
4 | 99862794
2 | 100080723
0 | 50167364
6 | 99907094
3 | 100146857
(11 rows)
Time: 46214.349 ms (00:46.214)
https://github.com/digoal/blog/blob/master/201711/20171107_49.md#tps-xx TPS： xx
https://github.com/digoal/blog/blob/master/201711/20171107_49.md#%E5%93%8D%E5%BA%94%E6%97%B6%E9%97%B4-46-%E7%A7%92 響應時間： 46 秒
不開并行和jit的話，耗時735秒，相差16倍。
https://github.com/digoal/blog/blob/master/201711/20171107_49.md#%E5%8F%82%E8%80%83 參考
《PostgreSQL、Greenplum 應用案例寶典《如來神掌》 - 目錄》 《資料庫選型之 - 大象十八摸 - 緻 架構師、開發者》 《PostgreSQL 使用 pgbench 測試 sysbench 相關case》 《資料庫界的華山論劍 tpc.org》 https://www.postgresql.org/docs/10/static/pgbench.htmlhttps://github.com/digoal/blog/blob/master/201711/20171107_49.md#%E5%85%8D%E8%B4%B9%E9%A2%86%E5%8F%96%E9%98%BF%E9%87%8C%E4%BA%91rds-postgresql%E5%AE%9E%E4%BE%8Becs%E8%99%9A%E6%8B%9F%E6%9C%BA 免費領取阿裡雲RDS PostgreSQL執行個體、ECS虛拟機
