Giving more resources to UDFs
I am trying to run the EditDistance UDF that came with Vertica as an extension pack UDF on a large data set. The cluster has 3 nodes with 16 cores on each.
While running the query that calculates the EditDistance, I noticed that only 1 or 2 core is being used on each nodes. How do I increase the number of cores/processes to speed up the query?
The query itself is very simple, SELECT A, B, EditDistance(A,B) INTO my_new_table FROM old_table;
Thanks for any recommendations!
While running the query that calculates the EditDistance, I noticed that only 1 or 2 core is being used on each nodes. How do I increase the number of cores/processes to speed up the query?
The query itself is very simple, SELECT A, B, EditDistance(A,B) INTO my_new_table FROM old_table;
Thanks for any recommendations!
0
Comments
lets simplify this. say you don't do an insert, just the select
Select A, B , EditDistance (A,
do you still see that only 1 or 2 cores are being used.
Can you attach the explain plan.
explain Select A, B , EditDistance (A,
Note that using all cores is not "always" faster, but in many cases it can be.
Using Vertica 7.0.1.
Please see below. Was monitoring the query and more cores seemed to kick off after a while, not just 2. Put seems there are periods when not all cores are working, so still would like to see how to improve performance.
Also, am I reading this correctly that only 1 node1 is used for the query?
Thank you,
Tibor
EXPLAIN WITH dist_rsd AS( SELECT DISTINCT rsd3 FROM dev.d_receiptline_new ) SELECT A.rsd3, B.rsd3, EditDistance(A.rsd3, B.rsd3) dist FROM dist_rsd A, dist_rsd B
Access Path:
+-JOIN (CROSS JOIN) [Cost: 165K, Rows: 100M (NO STATISTICS)] (PATH ID: 1)
| Execute on: Query Initiator
| +-- Outer -> SELECT [Cost: 82K, Rows: 10K (NO STATISTICS)] (PATH ID: 2)
| | Execute on: Query Initiator
| | +---> GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 82K, Rows: 10K (NO STATISTICS)] (PATH ID: 3)
| | | Group By: d_receiptline_new.rsd3
| | | Execute on: Query Initiator
| | | +---> STORAGE ACCESS for d_receiptline_new [Cost: 70K, Rows: 18M (NO STATISTICS)] (PATH ID: 4)
| | | | Projection: dev.d_receiptline_new_node0001
| | | | Materialize: d_receiptline_new.rsd3
| | | | Execute on: Query Initiator
| +-- Inner -> SELECT [Cost: 82K, Rows: 10K (NO STATISTICS)] (PATH ID: 5)
| | Execute on: Query Initiator
| | +---> GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 82K, Rows: 10K (NO STATISTICS)] (PATH ID: 6)
| | | Group By: d_receiptline_new.rsd3
| | | Execute on: Query Initiator
| | | +---> STORAGE ACCESS for d_receiptline_new [Cost: 70K, Rows: 18M (NO STATISTICS)] (PATH ID: 7)
| | | | Projection: dev.d_receiptline_new_node0001
| | | | Materialize: d_receiptline_new.rsd3
| | | | Execute on: Query Initiator
In any case,this is adequate for me to similate your situation as descibed by the example below.
IMO, this is what is happening....
1.
You are using all cores to compute the distinct values from the table. (see LOCAL RESEGMENT GROUPS)
| | +---> GROUPBY HASH (LOCAL RESEGMENT GROUPS)
2.
yes, I believe this query is running just on the initiator.
Its hard to tell why without more specific info, but i think it may be due to one of the following (b - most likely)
a. there is a CROSS JOIN involved, the inner would have to be assembled on all nodes anyways, so vertica picked a replicated projections on the initiator
b. there may be only one unsegmented projection and no other appropriately segmented projection is available to do the operation on all nodes
[ There are ways to make it run on all nodes, but that is a different discussion ]
3. look carefully at the plan you will see something like this (i used the + function, but you should see EditDistance()
2[label = "ExprEval: \n A.rsd3\n B.rsd3\n (A.rsd3 + B.rsd3)\nnodeSet: [0] \n[Local Segmented Properties: UNSEGMENTED]\nUnc: A.rsd3[1,1] - Integer(8)\nUnc: B.rsd3[2,1] - Integer(8)\nUnc: (A.rsd3 + B.rsd3)[100,-3] - Integer(8)", color = "green", shape = "box"];
Local Segmented Properties: UNSEGMENTED <==== This
This means the UDx function is running on a single stream (i.e. single core)
I tried to replicated your example as follows, which gave me the exact same plan
skeswani=> create table a( i int, j int);
CREATE TABLE
skeswani=> create projection a_p1 as (select * from a ) unsegmented all nodes;
CREATE PROJECTION
skeswani=> copy a from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|1
>> 1|1
>> 1|2
>> 1|2
>> 1|3
>> \.
skeswani=> with dist_rsd as (select distinct j rsd3 from a) SELECT A.rsd3, B.rsd3, A.rsd3+ B.rsd3 dist FROM dist_rsd A, dist_rsd B ;
rsd3 | rsd3 | dist
------+------+------
1 | 2 | 3
3 | 2 | 5
2 | 2 | 4
1 | 1 | 2
3 | 1 | 4
2 | 1 | 3
1 | 3 | 4
3 | 3 | 6
2 | 3 | 5
skeswani=> explain verbose with dist_rsd as (select distinct j rsd3 from a) SELECT A.rsd3, B.rsd3, A.rsd3+ B.rsd3 dist FROM dist_rsd A, dist_rsd B ;
Essentially
1. the computation of the distinct values is multi-core
2. the UDx , EditDistance is single core, possibly because of the cross join, which generates a single stream.
3. Yes, it running almost entirely on one node
4. With some minor projection modification, i was able to get my synthetic example to run on multiple nodes, however the UDx was still single threaded on each node, possibly because its sits on top of a cross join.
Thanks for your reply, much appreciated! So how do I modify cross join/ projection to force EditDistance multi core? The edit distance is a costly operation, so would like to run it on as many cores as possible.
Thank you for your help,
Tibor
at the risk or giving bad advice without all the facts.
Here is a approach which may work for you.
(modify based on your specific use case)
create table d_receiptline_new ( rsd3 int);
create table d_receiptline_cross_join ( rsd3_1 int, rsd3_2 int, distance int default rsd3_1+rsd3_2 ); -- used the EditDistance (rsd3_1,rsd3_2) instead of the + function
create projection d_receiptline_cross_join_p1 as (select * from d_receiptline_cross_join) segmented by hash (rsd3_1,rsd3_2) all nodes ksafe;
skeswani=> copy d_receiptline_new from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
1
1
2
2
3
\.
insert into d_receiptline_cross_join SELECT A.rsd3, B.rsd3 FROM (select distinct rsd3 from d_receiptline_new) as A, (select distinct rsd3 from d_receiptline_new) B ;
commit;
if you do
explain verbose insert into d_receiptline_cross_join SELECT A.rsd3, B.rsd3 FROM (select distinct rsd3 from d_receiptline_new) as A, (select distinct rsd3 from d_receiptline_new) B ;
3[label = "ExprEval: \n \"*SELECT*\".rsd3\n \"*SELECT*\".rsd3\n (\"*SELECT*\".rsd3 + \"*SELECT*\".rsd3)\n (-9223372036854775808)\nnodeSet: [0, 1, 2, 3, 4, 5, 6, 7] \n[Local Segmented Pr
operties: SEGMENTED]\nUnc: \"*SELECT*\".rsd3[1,1] - Integer(8)\nUnc: \"*SELECT*\".rsd3[1,2] - Integer(8)\nUnc: (\"*SELECT*\".rsd3 + \"*SELECT*\".rsd3)[1,3] - Integer(8)\nUnc: (-9223372036854
775808)[1,4] - Integer(8)", color = "green", shape = "box"];
you will see Local Segmented Properties : SEGMENTED.