閱讀487 返回首頁    go 阿裏雲 go 技術社區[雲棲]

HTAP數據庫 PostgreSQL 場景與性能測試之 6 - (OLTP) 空間應用 - KNN查詢（搜索附近對象，由近到遠排序輸出）

標簽

PostgreSQL , HTAP , OLTP , OLAP , 場景與性能測試

背景

PostgreSQL是一個曆史悠久的數據庫，曆史可以追溯到1973年，最早由2014計算機圖靈獎得主，關係數據庫的鼻祖Michael_Stonebraker 操刀設計，PostgreSQL具備與Oracle類似的功能、性能、架構以及穩定性。

pic

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流計算、分布式並行計算、時序處理、基因測序、化學分析、圖像分析 等。

pic

在各種應用場景中都可以看到PostgreSQL的應用：

pic

PostgreSQL近年來的發展非常迅勐，從知名數據庫評測網站dbranking的數據庫評分趨勢，可以看到PostgreSQL向上發展的趨勢：

pic

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

場景 - 空間應用 - KNN查詢（搜索附近對象，由近到遠排序輸出） (OLTP)

1、背景

在社交業務、O2O業務、空間應用中，搜索附近的對象是非常常見的需求，例如：

1、微信的搖一搖，

2、探探的搜索附近的異性，

3、導航軟件中搜索附近的加油站、餐館、酒店等。

4、打車軟件，搜索附近的出租車。

5、公安係統，搜索某個多邊形內的對象。

6、團圓係統，搜索某個點附近的所有對象。

我們生活的周圍有非常多的應用都有附近搜索的需求。

2、設計

一張空間表，10億個經緯點，輸入一個隨機點，搜索附近5公裏的人，按近到遠排序輸出前1、100條。

10億個點已經可以包含非常豐富的信息，建築物、用戶、汽車、小區、商場、加油站。。。等。

3、準備測試表

create extension postgis;    
    
create table t_pos(    
  id int primary key,    
  pos geometry    
);

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

create or replace function ff(geometry, float8, int) returns setof record as $$                                                            
declare    
  v_rec record;    
  v_limit int := $3;    
begin    
  set local enable_seqscan=off;   -- 強製索引, 掃描行數夠就退出.    
  for v_rec in     
    select *,     
    st_distancespheroid(pos, $1, 'SPHEROID["WGS84",6378137,298.257223563]') as dist     
    from t_pos     
    order by pos <-> $1    
  loop    
    if v_limit <=0 then    
      -- raise notice '已經取足數據';    
      return;    
    end if;    
    if v_rec.dist > $2 then    
      -- raise notice '滿足條件的點已輸出完畢';    
      return;    
    else    
      -- raise notice 'do someting, v_rec:%', v_rec;    
      return next v_rec;    
    end if;    
    v_limit := v_limit -1;    
  end loop;    
end;    
$$ language plpgsql strict volatile;

5、準備測試數據

insert into t_pos     
select * from (    
  select id,    
  ST_SetSRID(    
              ST_Point( round((random()*(135.085831-73.406586)+73.406586)::numeric,6),        
                        round((random()*(53.880950-3.408477)+3.408477)::numeric,6)        
              ),    
             4326    
            )  as pos    
from generate_series(1,1000000000) t(id)     
) t    
order by st_geohash(pos,15);    
    
create index idx_t_pos_1 on t_pos using gist(pos);

6、準備測試腳本

1、指定任意一個點，由近到遠返回5公裏內的100個點。

vi test1.sql    
    
\set x random(73,135)    
\set y random(3,53)    
select * from ff(st_setsrid(st_makepoint(:x,:y),4326), 5000, 100) as t(id int, pos geometry, dist float8);

2、指定任意一個點，由近到遠返回附近的100個點。

vi test2.sql  
  
\set x random(73,135)    
\set y random(3,53)   
select *, st_distancespheroid(pos, st_setsrid(st_makepoint(:x,:y),4326), 'SPHEROID["WGS84",6378137,298.257223563]') as dist from t_pos order by pos <-> st_setsrid(st_makepoint(:x,:y),4326) limit 100;

3、指定任意一個點，由近到遠返回5公裏內的1個點。

vi test3.sql    
    
\set x random(73,135)    
\set y random(3,53)    
select * from ff(st_setsrid(st_makepoint(:x,:y),4326), 5000, 1) as t(id int, pos geometry, dist float8);

4、指定任意一個點，由近到遠返回附近的1個點。

vi test4.sql  
  
\set x random(73,135)    
\set y random(3,53)   
select *, st_distancespheroid(pos, st_setsrid(st_makepoint(:x,:y),4326), 'SPHEROID["WGS84",6378137,298.257223563]') as dist from t_pos order by pos <-> st_setsrid(st_makepoint(:x,:y),4326) limit 1;

7、測試

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

8、測試結果

1、指定任意一個點，由近到遠返回5公裏內的100個點。

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 112  
number of threads: 112  
duration: 120 s  
number of transactions actually processed: 1716069  
latency average = 7.830 ms  
latency stddev = 5.340 ms  
tps = 14255.242120 (including connections establishing)  
tps = 14258.960645 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.003  \set x random(73,135)    
         0.001  \set y random(3,53)    
         7.828  select * from ff(st_setsrid(st_makepoint(:x,:y),4326), 5000, 100) as t(id int, pos geometry, dist float8);

2、指定任意一個點，由近到遠返回5公裏內的1個點。

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 112  
number of threads: 112  
duration: 120 s  
number of transactions actually processed: 12802519  
latency average = 1.049 ms  
latency stddev = 0.948 ms  
tps = 106443.247555 (including connections establishing)  
tps = 106471.622064 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.002  \set x random(73,135)    
         0.001  \set y random(3,53)    
         1.048  select * from ff(st_setsrid(st_makepoint(:x,:y),4326), 5000, 1) as t(id int, pos geometry, dist float8);

3、指定任意一個點，由近到遠返回附近的100個點。

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 112  
number of threads: 112  
duration: 120 s  
number of transactions actually processed: 4259777  
latency average = 3.154 ms  
latency stddev = 1.730 ms  
tps = 35485.626794 (including connections establishing)  
tps = 35493.479127 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.002  \set x random(73,135)    
         0.001  \set y random(3,53)   
         3.152  select *, st_distancespheroid(pos, st_setsrid(st_makepoint(:x,:y),4326), 'SPHEROID["WGS84",6378137,298.257223563]') as dist from t_pos order by pos <-> st_setsrid(st_makepoint(:x,:y),4326) limit 100;

4、指定任意一個點，由近到遠返回附近的1個點。

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 112  
number of threads: 112  
duration: 120 s  
number of transactions actually processed: 16396606  
latency average = 0.819 ms  
latency stddev = 0.766 ms  
tps = 136561.188639 (including connections establishing)  
tps = 136600.851378 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.002  \set x random(73,135)    
         0.001  \set y random(3,53)   
         0.818  select *, st_distancespheroid(pos, st_setsrid(st_makepoint(:x,:y),4326), 'SPHEROID["WGS84",6378137,298.257223563]') as dist from t_pos order by pos <-> st_setsrid(st_makepoint(:x,:y),4326) limit 1;

TPS

1、指定任意一個點，由近到遠返回5公裏內的100個點。

14258

2、指定任意一個點，由近到遠返回附近的100個點。

35493

3、指定任意一個點，由近到遠返回5公裏內的1個點。

106471

4、指定任意一個點，由近到遠返回附近的1個點。

136600

平均響應時間

1、指定任意一個點，由近到遠返回5公裏內的100個點。

7.830 毫秒

2、指定任意一個點，由近到遠返回附近的100個點。

3.154 毫秒

3、指定任意一個點，由近到遠返回5公裏內的1個點。

1.049 毫秒

4、指定任意一個點，由近到遠返回附近的1個點。

0.819 毫秒

參考

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

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

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

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

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

最後更新：2017-11-12 02:04:36

  上一篇：go  HTAP數據庫 PostgreSQL 場景與性能測試之 7 - (OLTP) 全文檢索 - 含索引實時寫入
  下一篇：go  HTAP數據庫 PostgreSQL 場景與性能測試之 5 - (OLTP) 空間應用 - 空間包含查詢