Shulou(Shulou.com)05/31 Report--

This article mainly explains the "similar image search plug-in imgsmlr performance testing and optimization method is what", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "similar image search plug-in imgsmlr performance testing and optimization method is what" it!

Citus (8 nodes, 128shard)

1. Install imgsmlr plug-in software (all nodes)

2. Create extension imgsmlr (all nodes)

3. Generate the function of random img sig (cn, because it only needs to be used for insertion, not push down)

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

4. Create a test table (cn)

Create table t_img (id int primary key, sig signature)

5. Create an index (cn)

Create index idx_t_img_1 on t_img using gist (sig)

6. Create a sharding table (128shard) (cn)

Set citus.shard_count = 128; select create_distributed_table ('tweeimg writing and writing id')

7. Write 450 million random image eigenvalues

Vi test.sql\ set id random (12000000000) 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

Write about 450 million random image eigenvalues

Postgres=# select count (*) from tsp; 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.9360002.836270,7.1862506.264300) 87143098 | (4.801570,7.870150,0.939599,3.666670,1.1023405.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.2442805.768050) 0.887555, 5.990350, 2.991390, 6.186550, 6.464940, 6.187140, 0.150242, 2.123070, 2.932270) (10 rows) query performance

1. Because some types of imgsmlr do not write the corresponding send and recv function interfaces, you need to use TEXT interaction. The CN setting parameters are as follows

Set citus.binary_master_copy_format = off

Did not set up the Times error

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

2. Create a stable function to generate random image eigenvalues, which is convenient for testing. (all nodes)

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

3. Performance

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. Implement the plan

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: 128Tasks 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 thank you for reading The above is the content of "what is the performance test and optimization method of similar image search plug-in imgsmlr". After the study of this article, I believe you have a deeper understanding of what the performance test and optimization method of similar image search plug-in imgsmlr is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.