HTAP數據庫 PostgreSQL 場景與性能測試之 17 - (OLTP) 數組相似查詢

標簽

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

場景 - 數組相似查詢 (OLTP)

1、背景

數組是PostgreSQL的一種多值類型，可以存儲多個同類元素。在業務係統設計時，可以使用數組存儲標簽、聚合屬性等。

例如導購業務係統，可以在數組中存儲多個商品ID，根據判斷新提交的導購文章的商品ID是否與已有文章的商品ID相似，實時判定導購文章的合法性（有沒有與已有文章類似的文章）。

《電商內容去重\內容篩選應用(實時識別轉載\盜圖\侵權?) - 文本、圖片集、商品集、數組相似判定的優化和索引技術》

2、設計

1億條記錄，每條記錄包含24個數值組成的數組，數組元素的取值範圍100萬。

實時判定新提交的記錄是否有與已有記錄重複值超過N個元素的記錄。

3、準備測試表

create extension smlar;  
  
create table t_arr_smlar(  
  id int,  
  arr 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;

測試搜索與指定隨機字符串的重疊元素個數超過N個的記錄

create or replace function f_test () returns setof record as $$  
declare  
  varr int[];  
begin  
  set smlar.type = overlap;  
  set smlar.threshold = 20;     -- 超過20個相似，即返回  
  set LOCAL enable_seqscan=off;  
  
  -- 產生一個隨機數組  
  select gen_rand_arr(1000000, 24) into varr;  
  return query select  
    *,  
    smlar( arr, varr)  
  from  
    t_arr_smlar  
  where  
    arr % varr  
  limit 1;  
end;  
$$ language plpgsql strict;

5、準備測試數據

insert into t_arr_smlar select id, gen_rand_arr(1000000,24) from generate_series(1,100000000) t(id);  
  
create index idx_t_arr_smlar on t_arr_smlar using gin (arr _int4_sml_ops);

6、準備測試腳本

vi test.sql  
  
select * from f_test() as t(id int, arr int[], sml real);

7、測試

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

select * from f_test() as t(id int, arr int[], sml real);  
  
postgres=# set smlar.type = overlap;  
postgres=# set smlar.threshold = 20;  
  
postgres=# select  
    *,  
    smlar( arr, '{670277,99869,937294,575449,159952,800157,847689,593052,764962,311135,401858,620507,659772,626246,470638,736153,910818,516379,493533,284204,72296,674361,23,24}')  
  from  
    t_arr_smlar  
  where  
    arr % '{670277,99869,937294,575449,159952,800157,847689,593052,764962,311135,401858,620507,659772,626246,470638,736153,910818,516379,493533,284204,72296,674361,23,24}'  
  limit 100;  
 id |                                                                                   arr                                                                                   | smlar  
----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------  
  1 | {670277,99869,937294,575449,159952,800157,847689,593052,764962,311135,401858,620507,659772,626246,470638,736153,910818,516379,493533,284204,72296,674361,713438,815800} |    22  
(1 row)  
  
Time: 15.288 ms  
  
  
postgres=# explain (analyze,verbose,timing,costs,buffers) select  
    *,  
    smlar( arr, '{670277,99869,937294,575449,159952,800157,847689,593052,764962,311135,401858,620507,659772,626246,470638,736153,910818,516379,493533,284204,72296,674361,23,24}')  
  from  
    t_arr_smlar  
  where  
    arr % '{670277,99869,937294,575449,159952,800157,847689,593052,764962,311135,401858,620507,659772,626246,470638,736153,910818,516379,493533,284204,72296,674361,23,24}'  
  limit 100;  
                                                                                                         QUERY PLAN  
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=980.00..1078.97 rows=100 width=125) (actual time=15.754..15.755 rows=1 loops=1)  
   Output: id, arr, (smlar(arr, '{670277,99869,937294,575449,159952,800157,847689,593052,764962,311135,401858,620507,659772,626246,470638,736153,910818,516379,493533,284204,72296,674361,23,24}'::integer[]))  
   Buffers: shared hit=130  
   ->  Bitmap Heap Scan on public.t_arr_smlar  (cost=980.00..99946.00 rows=100000 width=125) (actual time=15.753..15.753 rows=1 loops=1)  
         Output: id, arr, smlar(arr, '{670277,99869,937294,575449,159952,800157,847689,593052,764962,311135,401858,620507,659772,626246,470638,736153,910818,516379,493533,284204,72296,674361,23,24}'::integer[])  
         Recheck Cond: (t_arr_smlar.arr % '{670277,99869,937294,575449,159952,800157,847689,593052,764962,311135,401858,620507,659772,626246,470638,736153,910818,516379,493533,284204,72296,674361,23,24}'::integer[])  
         Heap Blocks: exact=1  
         Buffers: shared hit=130  
         ->  Bitmap Index Scan on idx_t_arr_smlar  (cost=0.00..955.00 rows=100000 width=0) (actual time=15.724..15.724 rows=1 loops=1)  
               Index Cond: (t_arr_smlar.arr % '{670277,99869,937294,575449,159952,800157,847689,593052,764962,311135,401858,620507,659772,626246,470638,736153,910818,516379,493533,284204,72296,674361,23,24}'::integer[])  
               Buffers: shared hit=129  
 Planning time: 0.112 ms  
 Execution time: 15.827 ms  
(13 rows)  
  
Time: 16.466 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: 572889
latency average = 29.324 ms
latency stddev = 5.015 ms
tps = 1909.385599 (including connections establishing)
tps = 1909.420275 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
        29.324  select * from f_test() as t(id int, arr int[], sml real);