Re: How to run in parallel in Postgres, EXECUTE_PARALLEL
Lars Aksel Opsahl <[email protected]> Sun, 8 Dec 2019 21:59:51 +0000
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <HE1P189MB026617A76AEAF06FDADA20619D590@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM> |
--_000_HE1P189MB026617A76AEAF06FDADA20619D590HE1P189MB0266EURP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable > From: Joe Conway > Sent: Sunday, December 8, 2019 9:04 PM > To: Lars Aksel Opsahl; Laurenz Albe; [email protected]= rg > Subject: Re: How to run in parallel in Postgres, EXECUTE_PARALLEL > > On 12/8/19 1:14 PM, Lars Aksel Opsahl wrote: > > Do you or anybody know if there are any plans for a function call that > > support the calling structure below or something like it and that then > > could finish in 1 second ? (If you are calling a void function, the > > return value should not be any problem.) > > > > DO > > $body$ > > *DECLARE* > > command_string_list text[3]; > > *BEGIN* > > command_string_list[0] =3D 'SELECT pg_sleep(1)'; > > command_string_list[1] =3D 'SELECT pg_sleep(1)'; > > command_string_list[2] =3D 'SELECT pg_sleep(1)'; > > EXECUTE_PARALLEL command_string_list; > > *END* > > $body$; > > > > The only way to this today as I understand it, is to open 3 new > > connections back to the database which you can be done in different way= s. > > Yes, correct. > > > If we had a parallel functions like the one above it's easier to > > make parallel sql without using complex scripts, java, python or other > > system. > > It does require one connection per statement, but with dblink it is not > necessarily all that complex. For example (granted, this could use more > error checking, etc.): > > 8<---------------- > CREATE OR REPLACE FUNCTION > execute_parallel(stmts text[]) > RETURNS text AS > $$ > declare > i int; > retv text; > conn text; > connstr text; > rv int; > db text :=3D current_database(); > begin > for i in 1..array_length(stmts,1) loop > conn :=3D 'conn' || i::text; > connstr :=3D 'dbname=3D' || db; > perform dblink_connect(conn, connstr); > rv :=3D dblink_send_query(conn, stmts[i]); > end loop; > for i in 1..array_length(stmts,1) loop > conn :=3D 'conn' || i::text; > select val into retv > from dblink_get_result(conn) as d(val text); > end loop; > for i in 1..array_length(stmts,1) loop > conn :=3D 'conn' || i::text; > perform dblink_disconnect(conn); > end loop; > return 'OK'; > end; > $$ language plpgsql; > 8<---------------- > > And then: > > 8<---------------- > \timing > DO $$ > declare > stmts text[]; > begin > stmts[1] =3D 'select pg_sleep(1)'; > stmts[2] =3D 'select pg_sleep(1)'; > stmts[3] =3D 'select pg_sleep(1)'; > PERFORM execute_parallel(stmts); > end; > $$ LANGUAGE plpgsql; > DO > Time: 1010.831 ms (00:01.011) > 8<---------------- > > HTH, > > Joe > -- > Crunchy Data - http://crunchydata.com > PostgreSQL Support for Secure Enterprises > Consulting, Training, & Open Source Development Hi Thanks a lot it works like a charm. https://github.com/larsop/find-overlap-= and-gap/tree/use_dblink_for_parallel (The test is failing now because it seems like drop EXTENSION dblink; is no= t cleaning up every thing) As you say we need some error handling. And maybe some retry if not enough= free connections and a parameter for max parallel connections and so on. So far this is best solution I have seen. Thanks. Lars --_000_HE1P189MB026617A76AEAF06FDADA20619D590HE1P189MB0266EURP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <html> <head> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-= 1"> <style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo= ttom:0;} </style> </head> <body dir=3D"ltr"> <div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size= : 12pt; color: rgb(0, 0, 0);"> </div> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> From:= <span style=3D"text-decoration: underline"> Joe</span> <span style=3D"text-decoration: underline">Conway</span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> Sent:= Sunday, December 8, 2019 9:04 PM</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> To: <= span style=3D"text-decoration: underline"> Lars</span> <span style=3D"text-decoration: underline">Aksel</span> <span s= tyle=3D"text-decoration: underline"> Opsahl</span>; <span style=3D"text-decoration: underline">Laurenz</span> <s= pan style=3D"text-decoration: underline"> Albe</span>; <span style=3D"text-decoration: underline">pgsql</span>-perfor= mance<span style=3D"text-decoration: underline">@lists.postgresql.org</span= ></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> Subje= ct: Re: How to run in parallel in <span style=3D"text-decoration: underline">Postgres</span>, EXECUTE_PARALLE= L</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">><span>= </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> On 12= /8/19 1:14 PM, <span style=3D"text-decoration: underline">Lars</span> <span style=3D"text-= decoration: underline"> Aksel</span> <span style=3D"text-decoration: underline">Opsahl</span> wrote= :</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = Do you or anybody know if there are any plans for a function call that</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = support the calling structure below or something like it and that then</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = could finish in 1 second ? (If you are calling a void function, the</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = return value should not be any problem.)</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> ><= span> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = DO</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = $body$</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = *DECLARE*<span> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = command_string_list text[3];</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = *BEGIN*</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = command_string_list[0] =3D 'SELECT pg_sleep(1)';</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = command_string_list[1] =3D 'SELECT pg_sleep(1)';</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = command_string_list[2] =3D 'SELECT pg_sleep(1)';</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = EXECUTE_PARALLEL command_string_list;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = *END*</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = $body$;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> ><= span> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = The only way to this today as I understand it, is to open 3 new</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = connections back to the database which you can be done in different ways.<s= pan> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">><span>= </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> Yes, = correct.</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">><span>= </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = If we had a parallel functions like the one above it's easier to</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = make parallel <span style=3D"text-decoration: underline"> sql</span> without using complex scripts, java, <span style=3D"text-decorat= ion: underline"> python</span> or other</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> > = system.</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">><span>= </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> It do= es require one connection per statement, but with <span style=3D"text-decoration: underline">dblink</span> it is not</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> neces= sarily all that complex. For example (granted, this could use more</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> error= checking, etc.):</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">><span>= </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> 8<= ----------------</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> CREAT= E OR REPLACE FUNCTION</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">><span>= </span>execute_parallel(<span style=3D"text-decoration: underline">s= tmts</span> text[])</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> RETUR= NS text AS</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> $$</p= > <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> decla= re</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span>i <span style=3D"text-decoration: underline">int</span>;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span><span style=3D"text-decoration: underline">retv</span> text;= </p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span><span style=3D"text-decoration: underline">conn</span> text;= </p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span><span style=3D"text-decoration: underline">connstr</span> te= xt;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span><span style=3D"text-decoration: underline">rv</span> <span style=3D"text-decoration: underline">int</span>;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span><span style=3D"text-decoration: underline">db</span> text := =3D current_database();</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> begin= </p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span>for i in 1..array_length(<span style=3D"text-decoration: und= erline">stmts</span>,1) loop</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span> <span style=3D"text-decoration: underline">conn</span> :=3D '<span style=3D= "text-decoration: underline">conn</span>' || i::text;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span> <span style=3D"text-decoration: underline">connstr</span> :=3D '<span style= =3D"text-decoration: underline">dbname</span>=3D' || <span style=3D"text-decoration: underline">db</span>;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span> perform dblink_connect(<span style=3D"text-decoration: underline">conn</spa= n>, <span style=3D"text-decoration: underline"> connstr</span>);</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span> <span style=3D"text-decoration: underline">rv</span> :=3D dblink_send_query= (<span style=3D"text-decoration: underline">conn</span>, <span style=3D"text-decoration: underline">stmts</span>[i]);</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span>end loop;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span>for i in 1..array_length(<span style=3D"text-decoration: und= erline">stmts</span>,1) loop</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span> <span style=3D"text-decoration: underline">conn</span> :=3D '<span style=3D= "text-decoration: underline">conn</span>' || i::text;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span> select <span style=3D"text-decoration: underline">val</span> into <span sty= le=3D"text-decoration: underline"> retv</span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span> from dblink_get_result(<span style=3D"text-decoration: underline">conn</spa= n>) as d(<span style=3D"text-decoration: underline">val</span> text);</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span>end loop;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span>for i in 1..array_length(<span style=3D"text-decoration: und= erline">stmts</span>,1) loop</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span> <span style=3D"text-decoration: underline">conn</span> :=3D '<span style=3D= "text-decoration: underline">conn</span>' || i::text;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span> perform dblink_disconnect(<span style=3D"text-decoration: underline">conn</= span>);</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span>end loop;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span>return 'OK';</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span>end;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> $$ la= nguage <span style=3D"text-decoration: underline"> plpgsql</span>;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> 8<= ----------------</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">><span>= </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> And t= hen:</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">><span>= </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> 8<= ----------------</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> \timi= ng</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> DO $$= </p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span>declare</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span> <span style=3D"text-decoration: underline">stmts</span> text[];</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span>begin</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span> <span style=3D"text-decoration: underline">stmts</span>[1] =3D 'select pg_s= leep(1)';</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span> <span style=3D"text-decoration: underline">stmts</span>[2] =3D 'select pg_s= leep(1)';</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span> <span style=3D"text-decoration: underline">stmts</span>[3] =3D 'select pg_s= leep(1)';</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span>PERFORM execute_parallel(<span style=3D"text-decoration: und= erline">stmts</span>);</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= > </span>end;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> $$ LA= NGUAGE <span style=3D"text-decoration: underline"> plpgsql</span>;</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> DO</p= > <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> Time:= 1010.831 <span style=3D"text-decoration: underline"> ms</span> (00:01.011)</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> 8<= ----------------</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">><span>= </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> HTH,<= /p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">><span>= </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= style=3D"text-decoration: underline"> Joe</span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> --<sp= an> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> Crunc= hy Data - http://crunchydata.com</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> Postg= reSQL Support for Secure Enterprises</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> Consu= lting, Training, & Open Source Development</p> <br> <div class=3D"BodyFragment"><font size=3D"2"><span style=3D"font-size:11pt;= "> <div class=3D"PlainText">Hi</div> <div class=3D"PlainText"><br> </div> <div class=3D"PlainText">Thanks a lot it works like a charm. <a href= =3D"https://github.com/larsop/find-overlap-and-gap/tree/use_dblink_for_para= llel" id=3D"LPNoLP553072">https://github.com/larsop/find-overlap-and-gap/tr= ee/use_dblink_for_parallel</a></div> <div class=3D"PlainText">(The test is failing now because it seems like&nbs= p;<span style=3D"background: var(--white);">drop EXTENSION dblink; </s= pan>is not cleaning up every thing)</div> <div class=3D"PlainText"><span style=3D"background: var(--white);"><br> </span></div> <div class=3D"PlainText"><span style=3D"background: var(--white);">As you s= ay </span><span style=3D"background: var(--white);">we need some error= handling. </span><span style=3D"background: var(--white);">And maybe = some retry if not enough free connections and a parameter for max parallel connections and so on.</span><br> </div> <div class=3D"PlainText"><span style=3D"background: var(--white);"><br> </span></div> <div class=3D"PlainText"><span style=3D"background: var(--white);">So far t= his is best solution I have seen.</span></div> <div class=3D"PlainText"><span style=3D"background: var(--white);"><br> </span></div> <div class=3D"PlainText"><span style=3D"background: var(--white);">Thanks.<= /span></div> <div class=3D"PlainText"><span style=3D"background: var(--white);"><br> </span></div> <div class=3D"PlainText"><span style=3D"background: var(--white);">Lars&nbs= p;</span><br> </div> </span></font></div> </body> </html> --_000_HE1P189MB026617A76AEAF06FDADA20619D590HE1P189MB0266EURP_--