PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试与优化 2 - 单机分区表 (dblink 异步调用并行) (4亿图像)
背景
续
《PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试 1 - 单机单表 (4亿图像)》
使用分区表+dblink异步接口并行调用。（内核层面直接支持imgsmlr gist index scan并行更好）
分区表 + dblink 异步调用 并行
1、创建分区表
create table t_img (id int primary key, sig signature) partition by hash (id);
2、创建64个分区
do language plpgsql $$
declare
i int;
begin
for i in 0..63
loop
execute format('create table t_img%s partition of t_img for values WITH (MODULUS 64, REMAINDER %s)', i, i);
end loop;
end;
$$;
3、创建图像特征值字段索引
create index idx_t_img_1 on t_img using gist(sig);
4、写入4亿随机图像特征值
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 64 -j 64 -t 10000000
dblink 异步调用封装
1、创建dblink插件
create extension if not exists dblink;
2、创建一个建立连接函数，不报错
create or replace function conn(
name, -- dblink名字
text -- 连接串,URL
) returns void as $$
declare
begin
perform dblink_connect($1, $2);
return;
exception when others then
return;
end;
$$ language plpgsql strict;
3、编写一个函数，输入参数为分区数，图像特征值。开启64个并行同时搜索每个分区，返回一条最相似的图像记录。
create or replace function parallel_img_search(
v_mod int, -- 分区数
v_sig signature, -- 图像特征值
conn text default format('hostaddr=%s port=%s user=%s dbname=%s application_name=', '127.0.0.1', current_setting('port'), current_user, current_database()) -- dblink连接
)
returns setof record as
$$
declare
app_prefix text := 'abc';
sql text;
ts1 timestamp;
begin
for i in 0..v_mod loop
perform conn(app_prefix||i, conn||app_prefix||i);
perform id,sig from dblink_get_result(app_prefix||i, false) as t(id int, sig signature);
sql := format('select * from t_img%s order by sig <-> %L limit 1', i, v_sig);
perform dblink_send_query(app_prefix||i, sql);
end loop;
ts1 := clock_timestamp();
for i in 0..v_mod loop
return query select id,sig from dblink_get_result(app_prefix||i, false) as t(id int, sig signature);
end loop;
raise notice '%', clock_timestamp()-ts1;
return;
end;
$$ language plpgsql strict;
4、创建一个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;
例子
postgres=# select get_rand_img_sig(10);
get_rand_img_sig
------------------------------------------------------------------------------------------------------------------------------------------------------------------
(3.970030, 2.340900, 0.946223, 5.951010, 6.560340, 7.922950, 6.646290, 0.430310, 7.690120, 5.799870, 1.337850, 1.319830, 3.178170, 6.439380, 0.925341, 2.215810)
(1 row)
Time: 0.345 ms
5、写入约2.98亿图像特征值。
postgres=# select count(*) from t_img;
count
-----------
297915819
(1 row)
使用dblink异步调用并行查询64个分区
使用dblink异步调用接口，查询所有分区，耗时：394毫秒
postgres=# select * from parallel_img_search(63, '(3.970030, 2.340900, 0.946223, 5.951010, 6.560340, 7.922950, 6.646290, 0.430310, 7.690120, 5.799870, 1.337850, 1.319830, 3.178170, 6.439380, 0.925341, 2.215810)'::signature) as t (id int, sig signature) order by sig <-> '(3.970030, 2.340900, 0.946223, 5.951010, 6.560340, 7.922950, 6.646290, 0.430310, 7.690120, 5.799870, 1.337850, 1.319830, 3.178170, 6.439380, 0.925341, 2.215810)'::signature limit 1;
NOTICE: 00:00:00.394257
id | sig
------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------
1918283556 | (3.122560, 2.748080, 1.133250, 5.426950, 6.626340, 6.876810, 7.959190, 0.798523, 8.638600, 5.075110, 1.366100, 0.899454, 2.980070, 4.580630, 0.986704, 1.582110)
(1 row)
Time: 741.161 ms
直接查询单个分区耗时：238毫秒
postgres=# explain (analyze,verbose,timing,costs,buffers) select sig from t_img48 order by sig <-> '(3.970030, 2.340900, 0.946223, 5.951010, 6.560340, 7.922950, 6.646290, 0.430310, 7.690120, 5.799870, 1.337850, 1.319830, 3.178170, 6.439380, 0.925341, 2.215810)' limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.36..0.37 rows=1 width=72) (actual time=231.287..231.288 rows=1 loops=1)
Output: id, sig, ((sig <-> '(3.970030, 2.340900, 0.946223, 5.951010, 6.560340, 7.922950, 6.646290, 0.430310, 7.690120, 5.799870, 1.337850, 1.319830, 3.178170, 6.439380, 0.925341, 2.215810)'::signature))
Buffers: shared hit=11881
-> Index Scan using t_img48_sig_idx on public.t_img48 (cost=0.36..41619.32 rows=4466603 width=72) (actual time=231.285..231.285 rows=1 loops=1)
Output: id, sig, (sig <-> '(3.970030, 2.340900, 0.946223, 5.951010, 6.560340, 7.922950, 6.646290, 0.430310, 7.690120, 5.799870, 1.337850, 1.319830, 3.178170, 6.439380, 0.925341, 2.215810)'::signature)
Order By: (t_img48.sig <-> '(3.970030, 2.340900, 0.946223, 5.951010, 6.560340, 7.922950, 6.646290, 0.430310, 7.690120, 5.799870, 1.337850, 1.319830, 3.178170, 6.439380, 0.925341, 2.215810)'::signature)
Buffers: shared hit=11881
Planning Time: 0.060 ms
Execution Time: 237.818 ms
(9 rows)
Time: 238.242 ms
相比于第一篇文档：单表4.39亿图像，以图搜图耗时4.2秒。使用dblink异步接口(64并行,2.98亿)，以图搜图耗时394毫秒，有较大性能提升。
小结
使用dblink异步调用，并没有达到238毫秒，而是394毫秒。
使用dblink异步调用后，每秒处理的索引数据约15 GB。
postgres=# select pg_size_pretty(11881*64*8192::numeric/0.394);
pg_size_pretty
----------------
15 GB
(1 row)
Time: 0.258 ms
瓶颈可能到了内存COPY上面。
下一篇我们看一下使用citus 多机的情况 。
参考
https://github.com/postgrespro/imgsmlr
《PostgreSQL 相似搜索插件介绍大汇总 (rum,pg_trgm,smlar,imgsmlr,pg_similarity) (rum,gin,gist)》
《PostgreSQL dblink异步调用实践,跑并行多任务 - 例如开N个并行后台任务创建索引, 开N个后台任务跑若干SQL》