标签
PostgreSQL , imgsmlr , GiST , 图像特征值 , 小波转换 , citus
背景
续
《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)》
