HTAP數據庫 PostgreSQL 場景與性能測試之 19 - (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的一種多值類型，可以存儲多個同類元素。在業務係統設計時，可以使用數組存儲標簽、聚合屬性等。

例如用戶畫像係統，使用數組存儲用戶的標簽。當需要根據標簽組合圈選一批用戶時，使用數組的包含、相交等手段來篩選選中的記錄。

包含表示包含目標條件中的所有標簽。

相交表示包含目標條件中的任意標簽。

2、設計

1億條記錄，每條記錄包含16個標簽，標簽的取值範圍1萬。另外包含3個屬性字段用於透視。

3、準備測試表

create table t_arr_label(
  id int,
  c1 int,
  c2 int,
  c3 int,
  label int[]
);

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

在若幹範圍內，生成包含若幹個隨機值的數組

create or replace function gen_rand_arr(int,int) returns int[] as $$    
  select array_agg((random()*$1)::int) from generate_series(1,$2);    
$$ language sql strict;

測試搜索包含若幹個元素的記錄，並進行透視，輸出透視結果。

5、準備測試數據

insert into t_arr_label select id, random()*100, random()*10, random()*2, gen_rand_arr(10000, 16) from generate_series(1,100000000) t(id);

create index idx_t_arr_label on t_arr_label using gin (label);

6、準備測試腳本

vi test.sql  
  
\set a1 random(1,10000)
\set a2 random(1,10000)
\set a3 random(1,10000)
select c1,c2,c3,count(*) from t_arr_label where label && (array[:a1, :a2, :a3])::int[] group by grouping sets ((c1),(c2),(c3));

7、測試

單次相似查詢效率，響應時間低於 300 毫秒。(使用綁定變量、並且CACHE命中後，響應時間更低。)

postgres=# select c1,c2,c3,count(*) from t_arr_label where label && '{1,2,3}' group by grouping sets ((c1),(c2),(c3));
 c1  | c2 | c3 | count  
-----+----+----+--------
   7 |    |    |   4704
   5 |    |    |   4825
  75 |    |    |   4717
  41 |    |    |   4812
  97 |    |    |   4784
.....................
  80 |    |    |   4877
  11 |    |    |   4797
   8 |    |    |   4924
  43 |    |    |   4829
     |  6 |    |  47897
     |  7 |    |  48239
     |  0 |    |  23932
     |  9 |    |  48161
     |  5 |    |  47508
     |  4 |    |  48063
     | 10 |    |  23847
     |  3 |    |  47965
     |  1 |    |  47928
     |  2 |    |  48196
     |  8 |    |  47443
     |    |  0 | 119686
     |    |  1 | 238997
     |    |  2 | 120496
(115 rows)

Time: 291.773 ms

postgres=# explain (analyze,verbose,timing,costs,buffers) select c1,c2,c3,count(*) from t_arr_label where label && '{1,2}' group by grouping sets ((c1),(c2),(c3));
                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=208722.69..210622.94 rows=115 width=20) (actual time=254.775..254.794 rows=115 loops=1)
   Output: c1, c2, c3, count(*)
   Hash Key: t_arr_label.c1
   Hash Key: t_arr_label.c2
   Hash Key: t_arr_label.c3
   Buffers: shared hit=26113
   ->  Bitmap Heap Scan on public.t_arr_label  (cost=1601.40..207773.14 rows=189910 width=12) (actual time=35.219..131.170 rows=319591 loops=1)
         Output: id, c1, c2, c3, label
         Recheck Cond: (t_arr_label.label && '{1,2}'::integer[])
         Heap Blocks: exact=26041
         Buffers: shared hit=26113
         ->  Bitmap Index Scan on idx_t_arr_label  (cost=0.00..1553.92 rows=189910 width=0) (actual time=31.495..31.495 rows=319591 loops=1)
               Index Cond: (t_arr_label.label && '{1,2}'::integer[])
               Buffers: shared hit=72
 Planning time: 0.145 ms
 Execution time: 254.869 ms
(16 rows)

壓測

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

8、測試結果

transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 56
number of threads: 56
duration: 300 s
number of transactions actually processed: 34132
latency average = 492.461 ms
latency stddev = 124.294 ms
tps = 113.617614 (including connections establishing)
tps = 113.628805 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         0.005  \set a1 random(1,10000)
         0.001  \set a2 random(1,10000)
         0.000  \set a3 random(1,10000)
       472.754  select c1,c2,c3,count(*) from t_arr_label where label && (array[:a1, :a2, :a3])::int[] group by grouping sets ((c1),(c2),(c3));

TPS： 113

平均響應時間： 492 毫秒

PostgreSQL真正實現了毫秒級圈選和透視分析。

相交比包含慢，是因為包含是包含所有，在BITMAP SCAN時，跳過更多的BLOCK。而相交是OR，OR的條件越多，掃描的BLOCK越多。

參考

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

《數據庫選型之 - 大象十八摸 - 致架構師、開發者》

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

《數據庫界的華山論劍 tpc.org》

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

最後更新：2017-11-12 02:06:12