Re: How to run in parallel in Postgres
Lars Aksel Opsahl <[email protected]> Sat, 7 Dec 2019 11:27:56 +0000
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <HE1P189MB0266944A7C861C8711453F7B9D5E0@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM> |
--_000_HE1P189MB0266944A7C861C8711453F7B9D5E0HE1P189MB0266EURP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable > From: Justin Pryzby <[email protected]> > Sent: Saturday, December 7, 2019 2:25 AM > To: Lars Aksel Opsahl <[email protected]> > Cc: [email protected] <[email protected]= resql.org> > Subject: Re: How to run in parallel in Postgres > > On Thu, Dec 05, 2019 at 12:10:42PM +0000, Lars Aksel Opsahl wrote: > > I have a function that prepares data, so the big job can be run it in p= arallel. > > > > Since Postgres now supports parallel I was wondering if it's easy to tr= igger parallel dynamically created SQL calls. > > > > If you look at https://github.com/larsop/find-overlap-and-gap/blob/mast= er/src/test/sql/regress/find_overlap_and_gap.sql you see that > > > > find_overlap_gap_make_run_cmd generates as set of 28 sql calls. > > > > So is it in a simple way possible to use Postgres parallel functionalit= y to call this 28 functions i parallel so I don't have dependent on externa= lly install programs ? > > SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geo= m',4258,'test_data.overlap_gap_input_t1_res',1,28); > SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geo= m',4258,'test_data.overlap_gap_input_t1_res',2,28); > SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geo= m',4258,'test_data.overlap_gap_input_t1_res',3,28); > ... > > I see that find_overlap_gap_single_cell creates tables, so cannot be run = in parallel. > Maybe you could consider rewriting it to return data to its caller instea= d. > You'd also need to mark it as PARALLEL SAFE, of course. > Your other functions involved should be PARALLEL SAFE too. > > Justin Hi Justin The reason why I don't return the results Is that on very bug tables I usua= lly get memory problems if I return all the results to the master function= . So I usually break thing up into small unlogged tables. Then I work on ea= ch table separately or in groups. When all steps are done i merge all the s= mall tables together. I this case we only single step, but usually I work m= any more steps. But I will keep mind that it may work i parallel if I don't create any chil= d tables but returns the result. Thanks. Lars --_000_HE1P189MB0266944A7C861C8711453F7B9D5E0HE1P189MB0266EURP_ 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"> Justin</span> <span style=3D"text-decoration: underline">Pryzby</span> <= pryzby<span style=3D"text-decoration: underline">@telsasoft.com</span>><= /p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> Sent:= Saturday, December 7, 2019 2:25 AM</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> <Lars.Opsahl<span style=3D"text-decoration: underline">@ni= bio.no</span>></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> <span= style=3D"text-decoration: underline"> Cc</span>: <span style=3D"text-decoration: underline">pgsql</span>-performa= nce<span style=3D"text-decoration: underline">@lists.postgresql.org</span> = <pgsql-performance<span style=3D"text-decoration: underline">@lists.post= gresql.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></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 <s= pan style=3D"text-decoration: underline"> Thu</span>, <span style=3D"text-decoration: underline">Dec</span> 05, 2019 = at 12:10:42PM +0000, <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">> > = I have a function that prepares data, so the big job can be run it in paral= lel.</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">> > = Since <span style=3D"text-decoration: underline"> Postgres</span> now supports parallel I was wondering if it's easy to trigg= er parallel dynamically created SQL calls.</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 you look at https://github.com/larsop/find-overlap-and-gap/blob/master/s= rc/test/sql/regress/find_overlap_and_gap.sql<span> </span>you see that</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">> > = find_overlap_gap_make_run_cmd generates as set of 28 <span style=3D"text-decoration: underline">sql</span> calls.</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">> > = So is it in a simple way possible to use <span style=3D"text-decoration: underline">Postgres</span> parallel functio= nality to call this 28 functions i parallel so I don't have dependent on ex= ternally install programs<span> </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">> SELEC= T find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','<span styl= e=3D"text-decoration: underline">geom</span>',4258,'test_data.overlap_gap_i= nput_t1_res',1,28);</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> SELEC= T find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','<span styl= e=3D"text-decoration: underline">geom</span>',4258,'test_data.overlap_gap_i= nput_t1_res',2,28);</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> SELEC= T find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','<span styl= e=3D"text-decoration: underline">geom</span>',4258,'test_data.overlap_gap_i= nput_t1_res',3,28);</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">><span>= </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> I see= that find_overlap_gap_single_cell creates tables, so cannot be run in para= llel.</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> Maybe= you could consider rewriting it to return data to its caller instead.</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> You'd= also need to mark it as PARALLEL SAFE, of course.</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">> Your = other functions involved should be PARALLEL SAFE too.</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"> Justin</span></p> <div> <div class=3D"BodyFragment"><font size=3D"2"><span style=3D"font-size:11pt"= > <div class=3D"PlainText"><br> </div> <div class=3D"PlainText">Hi Justin</div> <div class=3D"PlainText"><br> </div> <div class=3D"PlainText">The reason why I don't return the results Is that = on very bug tables I usually get memory problems if I return all the = results to the master function. So I usually break thing up into small unlo= gged tables. Then I work on each table separately or in groups. When all steps are done i merge all the small tab= les together. I this case we only single step, but usually I work many more= steps.</div> <div class=3D"PlainText"><br> </div> <div class=3D"PlainText">But I will keep mind that it may work i parallel i= f I don't create any child tables but returns the result.</div> <div class=3D"PlainText"><br> </div> <div class=3D"PlainText">Thanks.</div> <div class=3D"PlainText"><br> </div> <div class=3D"PlainText">Lars</div> </span></font></div> </div> </body> </html> --_000_HE1P189MB0266944A7C861C8711453F7B9D5E0HE1P189MB0266EURP_--