HTAP數據庫 PostgreSQL 場景與性能測試之 18 - (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;

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

create or replace function f_test () returns setof record as $$  
declare  
  varr int[];  
begin  
  -- 產生一個隨機數組  (包含任意3個標簽)   
  select gen_rand_arr(10000, 3) into varr;  
  -- 根據標簽篩選數據，並進行透視輸出。
  return query select c1,c2,c3,count(*) from t_arr_label where label @> varr group by grouping sets ((c1),(c2),(c3)); 
end;  
$$ language plpgsql 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  
  
select * from f_test() as t(c1 int, c2 int, c3 int ,cnt int8);

7、測試

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

postgres=# select c1,c2,c3,count(*) from t_arr_label where label @> '{1,2}' group by grouping sets ((c1),(c2),(c3));
 c1  | c2 | c3 | count 
-----+----+----+-------
   1 |    |    |     6
   4 |    |    |     1
   6 |    |    |     2
   8 |    |    |     1
.............
  98 |    |    |     3
  99 |    |    |     1
 100 |    |    |     2
     |    |  0 |    62
     |    |  1 |   149
     |    |  2 |    53
     |  0 |    |     9
     |  1 |    |    22
     |  2 |    |    30
     |  3 |    |    26
     |  4 |    |    22
     |  5 |    |    23
     |  6 |    |    34
     |  7 |    |    22
     |  8 |    |    31
     |  9 |    |    33
     | 10 |    |    12
(102 rows)

Time: 16.050 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                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=111.75..121.83 rows=222 width=20) (actual time=15.402..15.590 rows=102 loops=1)
   Output: c1, c2, c3, count(*)
   Group Key: t_arr_label.c1
   Sort Key: t_arr_label.c3
     Group Key: t_arr_label.c3
   Sort Key: t_arr_label.c2
     Group Key: t_arr_label.c2
   Buffers: shared hit=419
   ->  Sort  (cost=111.75..111.97 rows=90 width=12) (actual time=15.394..15.422 rows=264 loops=1)
         Output: c1, c2, c3
         Sort Key: t_arr_label.c1
         Sort Method: quicksort  Memory: 37kB
         Buffers: shared hit=419
         ->  Bitmap Heap Scan on public.t_arr_label  (cost=17.70..108.82 rows=90 width=12) (actual time=14.711..15.327 rows=264 loops=1)
               Output: c1, c2, c3
               Recheck Cond: (t_arr_label.label @> '{1,2}'::integer[])
               Heap Blocks: exact=264
               Buffers: shared hit=419
               ->  Bitmap Index Scan on idx_t_arr_label  (cost=0.00..17.68 rows=90 width=0) (actual time=14.676..14.676 rows=264 loops=1)
                     Index Cond: (t_arr_label.label @> '{1,2}'::integer[])
                     Buffers: shared hit=155
 Planning time: 0.133 ms
 Execution time: 15.653 ms
(23 rows)

Time: 16.217 ms

壓測

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: 532217
latency average = 31.565 ms
latency stddev = 5.183 ms
tps = 1773.127087 (including connections establishing)
tps = 1773.172254 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
        31.565  select * from f_test() as t(c1 int, c2 int, c3 int ,cnt int8);