怎么實現數據庫分區表+dblink異步調用并行

134次閱讀

共計 5130 個字符，預計需要花費 13 分鐘才能閱讀完成。

本篇內容介紹了“怎么實現數據庫分區表 +dblink 異步調用并行”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

分區表 + 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

“怎么實現數據庫分區表 +dblink 異步調用并行”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！

正文完