HTAP數據庫 PostgreSQL 場景與性能測試之 2 - (OLTP) 多表JOIN

標簽

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來估算。

場景 - 多表JOIN (OLTP)

1、背景

數據屬性分散設計，查詢時，通過多表JOIN提取各個屬性表的數據。

2、設計

10張表，每張表1000萬數據量，一對一，join。

3、準備測試表

create table t1(  
  id int primary key,  
  info text default 'tessssssssssssssssssssssssssssssssssssst',   
  state int default 0,   
  crt_time timestamp default now(),   
  mod_time timestamp default now()  
);  
  
create table t2 (like t1 including all);  
create table t3 (like t1 including all);  
create table t4 (like t1 including all);  
create table t5 (like t1 including all);  
create table t6 (like t1 including all);  
create table t7 (like t1 including all);  
create table t8 (like t1 including all);  
create table t9 (like t1 including all);  
create table t10 (like t1 including all);

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

5、準備測試數據

insert into t1 select generate_series(1,10000000);  
insert into t2 select * from t1;  
insert into t3 select * from t1;  
insert into t4 select * from t1;  
insert into t5 select * from t1;  
insert into t6 select * from t1;  
insert into t7 select * from t1;  
insert into t8 select * from t1;  
insert into t9 select * from t1;  
insert into t10 select * from t1;  
alter role all set join_collapse_limit=1;

6、準備測試腳本

vi test.sql  
  
\set id random(1,10000000)  
select * from t1 join t2 using (id) join t3 using (id) join t4 using (id) join t5 using (id) join t6 using (id) join t7 using (id) join t8 using (id) join t9 using (id) join t10 using (id) where t1.id=:id;

7、測試

CONNECTS=112  
TIMES=300  
export PGHOST=$PGDATA  
export PGPORT=1999  
export PGUSER=postgres  
export PGPASSWORD=postgres  
export PGDATABASE=postgres  
  
pgbench -M prepared -n -r -P 5 -f ./test.sql -c $CONNECTS -j $CONNECTS -T $TIMES

alter role all reset join_collapse_limit;

8、測試結果

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 112  
number of threads: 112  
duration: 300 s  
number of transactions actually processed: 31033358  
latency average = 1.083 ms  
latency stddev = 1.063 ms  
tps = 103408.162713 (including connections establishing)  
tps = 103433.470434 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.001  \set id random(1,10000000)  
         1.082  select * from t1 join t2 using (id) join t3 using (id) join t4 using (id) join t5 using (id) join t6 using (id) join t7 using (id) join t8 using (id) join t9 using (id) join t10 using (id) where t1.id=:id;