Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试与优化 3 - citus 8机128shard (4亿图像)

3 minute read

背景

续

《PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试 1 - 单机单表 (4亿图像)》

《PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试 2 - 单机分区表 (dblink 异步调用并行) (4亿图像)》

《PostgreSQL sharding : citus 系列1 - 多机部署（含OLTP(TPC-B)测试）》

citus (8节点, 128 shard)

1、安装imgsmlr插件软件 (所有节点)

2、create extension imgsmlr (所有节点)

3、生成随机img sig的函数 (cn, 因为只需要用于插入, 不需要下推)

CREATE OR REPLACE FUNCTION public.gen_rand_img_sig(integer)  
 RETURNS signature  
 LANGUAGE sql  
 STRICT  
AS $function$  
  select ('('||rtrim(ltrim(array(select (random()*$1)::float4 from generate_series(1,16))::text,'{'),'}')||')')::signature;  
$function$;

4、创建测试表 (cn)

create table t_img (id int primary key, sig signature);

5、创建索引 (cn)

create index idx_t_img_1 on t_img using gist (sig);

6、创建分片表(128 shard) (cn)

set citus.shard_count = 128;  
select create_distributed_table('t_img','id');

7、写入4.5亿随机图像特征值

vi test.sql  
  
\set id random(1,2000000000)  
insert into t_img values (:id, gen_rand_img_sig(10)) on conflict(id) do nothing;  

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 128 -j 128 -t 10000000

写入约4.5亿随机图像特征值

postgres=# select count(*) from t_img;  
   count     
-----------  
 446953185  
(1 row)  

postgres=# select * from t_img limit 10;  
    id     |                                                                               sig                                                                                  
-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------  
  47902935 | (5.861920, 1.062770, 8.318020, 2.205840, 0.202951, 6.956610, 1.413190, 2.898480, 8.961630, 6.377800, 1.110450, 6.684520, 2.286290, 7.850760, 1.832650, 0.074348)  
 174656795 | (2.165030, 0.183753, 9.913950, 9.208260, 5.165660, 6.603510, 2.008380, 8.117910, 2.358590, 5.466330, 9.139280, 8.893700, 4.664190, 9.361670, 9.016990, 2.271000)  
  96186891 | (9.605980, 4.395920, 4.336720, 3.174360, 8.706960, 0.155107, 9.408940, 4.531100, 2.783530, 5.681780, 9.792380, 6.428320, 2.983760, 9.733290, 7.635160, 7.035780)  
  55061667 | (7.567960, 5.874530, 5.222040, 5.638520, 3.488960, 8.770750, 7.054610, 7.239630, 9.202280, 9.465020, 4.079080, 5.729770, 0.475227, 8.434800, 6.873730, 5.140080)  
  64659434 | (4.860650, 3.984440, 3.009900, 5.116680, 6.489150, 4.224800, 0.609752, 8.731120, 6.577390, 8.542540, 9.096120, 8.976700, 8.936000, 2.836270, 7.186250, 6.264300)  
  87143098 | (4.801570, 7.870150, 0.939599, 3.666670, 1.102340, 5.819580, 6.511330, 6.430760, 0.584531, 3.024190, 6.255460, 8.823820, 5.076960, 0.181344, 8.137380, 1.230360)  
 109245945 | (7.541850, 7.201460, 6.858400, 2.605210, 1.283090, 7.525200, 4.213240, 8.413760, 9.707390, 1.916970, 1.719320, 1.255280, 9.006780, 4.851420, 2.168250, 5.997360)  
   4979218 | (8.463000, 4.051410, 9.057320, 1.367980, 3.344340, 7.032640, 8.583770, 1.873090, 5.524810, 0.187254, 5.783270, 6.141040, 2.479410, 6.406450, 9.371700, 0.050690)  
  72846137 | (7.018560, 4.039150, 9.114800, 2.911170, 5.531180, 8.557330, 6.739050, 0.103649, 3.691390, 7.584640, 8.184180, 0.599390, 9.037130, 4.090610, 4.369770, 6.480000)  
  36813995 | (4.643480, 8.704640, 1.073880, 2.665530, 3.298300, 9.244280, 5.768050, 0.887555, 5.990350, 2.991390, 6.186550, 6.464940, 6.187140, 0.150242, 2.123070, 2.932270)  
(10 rows)

查询性能

1、由于imgsmlr的一些类型没有写对应的send, recv函数接口，所以需要使用TEXT交互。CN设置参数如下

set citus.binary_master_copy_format =off;

未设置时报错

WARNING:  42883: no binary output function available for type signature  
LOCATION:  ReportResultError, remote_commands.c:302

2、创建生成随机图像特征值stable函数，便于测试。（所有节点）

create or replace function get_rand_img_sig(int) returns signature as $$  
  select ('('||rtrim(ltrim(array(select (random()*$1)::float4 from generate_series(1,16))::text,'{'),'}')||')')::signature;  
$$ language sql strict stable;

3、性能

postgres=# select * from t_img order by sig <-> get_rand_img_sig(10) limit 1;  
    id     |                                                                               sig                                                                                  
-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 565459043 | (1.790420, 9.463960, 7.089370, 5.888980, 0.974693, 2.148580, 6.153310, 9.098670, 2.815750, 7.625620, 7.598990, 7.141670, 7.189410, 4.630740, 3.673030, 7.820140)  
(1 row)  
  
Time: 612.839 ms

4、执行计划

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t_img order by sig <-> get_rand_img_sig(10) limit 1;  
                                                                                         QUERY PLAN                                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=0.00..0.00 rows=0 width=0) (actual time=823.235..823.237 rows=1 loops=1)  
   Output: remote_scan.id, remote_scan.sig, remote_scan.worker_column_3  
   ->  Sort  (cost=0.00..0.00 rows=0 width=0) (actual time=823.233..823.233 rows=1 loops=1)  
         Output: remote_scan.id, remote_scan.sig, remote_scan.worker_column_3  
         Sort Key: remote_scan.worker_column_3  
         Sort Method: top-N heapsort  Memory: 25kB  
         ->  Custom Scan (Citus Real-Time)  (cost=0.00..0.00 rows=0 width=0) (actual time=823.185..823.200 rows=128 loops=1)  
               Output: remote_scan.id, remote_scan.sig, remote_scan.worker_column_3  
               Task Count: 128  
               Tasks Shown: One of 128  
               ->  Task  
                     Node: host=172.24.211.224 port=1921 dbname=postgres  
                     ->  Limit  (cost=0.67..0.97 rows=1 width=72) (actual time=151.011..151.012 rows=1 loops=1)  
                           Output: id, sig, ((sig <-> get_rand_img_sig(10)))  
                           Buffers: shared hit=5769  
                           ->  Index Scan using idx_t_img_1_106940 on public.t_img_106940 t_img  (cost=0.67..1052191.36 rows=3488100 width=72) (actual time=151.008..151.009 rows=1 loops=1)  
                                 Output: id, sig, (sig <-> get_rand_img_sig(10))  
                                 Order By: (t_img.sig <-> get_rand_img_sig(10))  
                                 Buffers: shared hit=5769  
                         Planning time: 1.021 ms  
                         Execution time: 156.785 ms  
 Planning time: 2.364 ms  
 Execution time: 823.577 ms  
(23 rows)  

postgres=# select * from t_img order by sig <-> get_rand_img_sig(10) limit 1;  
    id    |                                                                               sig                                                                                  
----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 30290963 | (4.656000, 7.143380, 7.738080, 1.971150, 4.294430, 4.397560, 7.121350, 8.629690, 2.768710, 2.715320, 0.358493, 0.486682, 5.985860, 8.319860, 2.560290, 3.384480)  
(1 row)  
  
Time: 612.783 ms  
postgres=# select * from t_img order by sig <-> get_rand_img_sig(10) limit 1;  
     id     |                                                                               sig                                                                                  
------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------  
 1632633492 | (6.969460, 5.835990, 0.629481, 7.621580, 0.171138, 2.586950, 1.483150, 5.526530, 3.835270, 2.275350, 3.470760, 4.934100, 0.442193, 1.843810, 0.561291, 0.647721)  
(1 row)  
  
Time: 610.960 ms

小结

使用citus，可以方便的扩展计算能力，提高PG的读写性能。

shard数目，建议参考worker节点的实际CPU核数量，建议不要超过核数，根据并发度的情况，考虑SHARD总数。

例如有8个WORKER节点，每个WORKER节点部署一个PG实例，每个WORKER节点32核。那么建议的shard数不要超过256（8*32）。如果并发很高，可以把shard数调低一点。

set citus.shard_count = 128;  
select create_distributed_table('t_img','id');

参考

https://github.com/postgrespro/imgsmlr

《PostgreSQL 相似搜索插件介绍大汇总 (rum,pg_trgm,smlar,imgsmlr,pg_similarity) (rum,gin,gist)》

《PostgreSQL sharding : citus 系列1 - 多机部署（含OLTP(TPC-B)测试）》

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...