Re: How to run in parallel in Postgres
Ondrej Ivanič <[email protected]> Sat, 7 Dec 2019 12:23:15 +1100
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <CAM6mie+EibAnj47FeA6-S7YLGQ0mLGfRroS87qYKyWDMb8mFyg@mail.gmail.com> |
--000000000000603d1c059913029b
Content-Type: text/plain; charset="UTF-8"
Hi Lars,
I have two suggestions:
- `xargs` almost always present and it can run in parallel (-P) but script
needs to be changed:
for((i=1;i<=28;i++)); do echo "SELECT
find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',${I},28);";
done | xargs -n1 -P 10 psql ...
- `UNION ALL` might trigger parallel execution (you need to mess with the
cost of the function and perhaps other settings):
SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',
4258,'test_data.overlap_gap_input_t1_res',1,28) UNION ALL
SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',
4258,'test_data.overlap_gap_input_t1_res',2,28)
...
Cheers,
On Thu, 5 Dec 2019 at 23:11, Lars Aksel Opsahl <[email protected]> wrote:
> Hi
>
> I have a function that prepares data, so the big job can be run it in
> parallel.
>
> Today I have solved this by using "Gnu parallel" like this.
> psql testdb -c"\! psql -t -q -o /tmp/run_cmd.sql testdb -c\"SELECT
> find_overlap_gap_make_run_cmd('sl_lop.overlap_gap_input_t1','geom',4258,'sl_lop.overlap_gap_input_t1_res',50);\";
> parallel -j 4 psql testdb -c :::: /tmp/run_cmd.sql" 2>> /tmp/analyze.log;
>
> The problem here is that I depend on external code which may not be
> installed.
>
> Since Postgres now supports parallel I was wondering if it's easy to
> trigger parallel dynamically created SQL calls.
>
> If you look at
> https://github.com/larsop/find-overlap-and-gap/blob/master/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 functionality
> to call this 28 functions i parallel so I don't have dependent
> on externally install programs ?
>
>
> When this 28 sql calls are done, the find_overlap_gap_make_run_cmd may continue
> to the next step of work. So the function that triggers parallel calls wait
> for them complete and then may start on the next step of work.
>
>
> Thanks .
>
>
> Lars
>
>
>
>
>
>
--
Ondrej
--000000000000603d1c059913029b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr">Hi Lars,<div><br></div><div>I have two suggestions:</div><=
div><br></div><div>- `xargs` almost always present and it can run in parall=
el (-P) but script needs to be changed:</div><div>for((i=3D1;i<=3D28;i++=
)); do echo "SELECT find_overlap_gap_single_cell('test_data.overla=
p_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1=
_res',${I},28);"; done | xargs -n1 -P 10 psql ...=C2=A0</div><div>=
<br></div><div>- `UNION ALL` might trigger parallel execution (you need to =
mess with the cost of the function and perhaps other settings):</div><div><=
span class=3D"gmail-pl-k" style=3D"box-sizing:border-box;color:rgb(215,58,7=
3);font-family:SFMono-Regular,Consolas,"Liberation Mono",Menlo,mo=
nospace;font-size:12px;white-space:pre">SELECT</span><span style=3D"color:r=
gb(36,41,46);font-family:SFMono-Regular,Consolas,"Liberation Mono"=
;,Menlo,monospace;font-size:12px;white-space:pre"> find_overlap_gap_single_=
cell(</span><span class=3D"gmail-pl-s" style=3D"box-sizing:border-box;color=
:rgb(3,47,98);font-family:SFMono-Regular,Consolas,"Liberation Mono&quo=
t;,Menlo,monospace;font-size:12px;white-space:pre"><span class=3D"gmail-pl-=
pds" style=3D"box-sizing:border-box">'</span>test_data.overlap_gap_inpu=
t_t1<span class=3D"gmail-pl-pds" style=3D"box-sizing:border-box">'</spa=
n></span><span style=3D"color:rgb(36,41,46);font-family:SFMono-Regular,Cons=
olas,"Liberation Mono",Menlo,monospace;font-size:12px;white-space=
:pre">,</span><span class=3D"gmail-pl-s" style=3D"box-sizing:border-box;col=
or:rgb(3,47,98);font-family:SFMono-Regular,Consolas,"Liberation Mono&q=
uot;,Menlo,monospace;font-size:12px;white-space:pre"><span class=3D"gmail-p=
l-pds" style=3D"box-sizing:border-box">'</span>geom<span class=3D"gmail=
-pl-pds" style=3D"box-sizing:border-box">'</span></span><span style=3D"=
color:rgb(36,41,46);font-family:SFMono-Regular,Consolas,"Liberation Mo=
no",Menlo,monospace;font-size:12px;white-space:pre">,</span><span clas=
s=3D"gmail-pl-c1" style=3D"box-sizing:border-box;color:rgb(0,92,197);font-f=
amily:SFMono-Regular,Consolas,"Liberation Mono",Menlo,monospace;f=
ont-size:12px;white-space:pre">4258</span><span style=3D"color:rgb(36,41,46=
);font-family:SFMono-Regular,Consolas,"Liberation Mono",Menlo,mon=
ospace;font-size:12px;white-space:pre">,</span><span class=3D"gmail-pl-s" s=
tyle=3D"box-sizing:border-box;color:rgb(3,47,98);font-family:SFMono-Regular=
,Consolas,"Liberation Mono",Menlo,monospace;font-size:12px;white-=
space:pre"><span class=3D"gmail-pl-pds" style=3D"box-sizing:border-box">=
9;</span>test_data.overlap_gap_input_t1_res<span class=3D"gmail-pl-pds" sty=
le=3D"box-sizing:border-box">'</span></span><span style=3D"color:rgb(36=
,41,46);font-family:SFMono-Regular,Consolas,"Liberation Mono",Men=
lo,monospace;font-size:12px;white-space:pre">,</span><span class=3D"gmail-p=
l-c1" style=3D"box-sizing:border-box;color:rgb(0,92,197);font-family:SFMono=
-Regular,Consolas,"Liberation Mono",Menlo,monospace;font-size:12p=
x;white-space:pre">1</span><span style=3D"color:rgb(36,41,46);font-family:S=
FMono-Regular,Consolas,"Liberation Mono",Menlo,monospace;font-siz=
e:12px;white-space:pre">,</span><span class=3D"gmail-pl-c1" style=3D"box-si=
zing:border-box;color:rgb(0,92,197);font-family:SFMono-Regular,Consolas,&qu=
ot;Liberation Mono",Menlo,monospace;font-size:12px;white-space:pre">28=
</span><span style=3D"color:rgb(36,41,46);font-family:SFMono-Regular,Consol=
as,"Liberation Mono",Menlo,monospace;font-size:12px;white-space:p=
re">)
</span>UNION ALL</div><div><span class=3D"gmail-pl-k" style=3D"box-sizing:b=
order-box;color:rgb(215,58,73);font-family:SFMono-Regular,Consolas,"Li=
beration Mono",Menlo,monospace;font-size:12px;white-space:pre">SELECT<=
/span><span style=3D"color:rgb(36,41,46);font-family:SFMono-Regular,Consola=
s,"Liberation Mono",Menlo,monospace;font-size:12px;white-space:pr=
e"> find_overlap_gap_single_cell(</span><span class=3D"gmail-pl-s" style=3D=
"box-sizing:border-box;color:rgb(3,47,98);font-family:SFMono-Regular,Consol=
as,"Liberation Mono",Menlo,monospace;font-size:12px;white-space:p=
re"><span class=3D"gmail-pl-pds" style=3D"box-sizing:border-box">'</spa=
n>test_data.overlap_gap_input_t1<span class=3D"gmail-pl-pds" style=3D"box-s=
izing:border-box">'</span></span><span style=3D"color:rgb(36,41,46);fon=
t-family:SFMono-Regular,Consolas,"Liberation Mono",Menlo,monospac=
e;font-size:12px;white-space:pre">,</span><span class=3D"gmail-pl-s" style=
=3D"box-sizing:border-box;color:rgb(3,47,98);font-family:SFMono-Regular,Con=
solas,"Liberation Mono",Menlo,monospace;font-size:12px;white-spac=
e:pre"><span class=3D"gmail-pl-pds" style=3D"box-sizing:border-box">'</=
span>geom<span class=3D"gmail-pl-pds" style=3D"box-sizing:border-box">'=
</span></span><span style=3D"color:rgb(36,41,46);font-family:SFMono-Regular=
,Consolas,"Liberation Mono",Menlo,monospace;font-size:12px;white-=
space:pre">,</span><span class=3D"gmail-pl-c1" style=3D"box-sizing:border-b=
ox;color:rgb(0,92,197);font-family:SFMono-Regular,Consolas,"Liberation=
Mono",Menlo,monospace;font-size:12px;white-space:pre">4258</span><spa=
n style=3D"color:rgb(36,41,46);font-family:SFMono-Regular,Consolas,"Li=
beration Mono",Menlo,monospace;font-size:12px;white-space:pre">,</span=
><span class=3D"gmail-pl-s" style=3D"box-sizing:border-box;color:rgb(3,47,9=
8);font-family:SFMono-Regular,Consolas,"Liberation Mono",Menlo,mo=
nospace;font-size:12px;white-space:pre"><span class=3D"gmail-pl-pds" style=
=3D"box-sizing:border-box">'</span>test_data.overlap_gap_input_t1_res<s=
pan class=3D"gmail-pl-pds" style=3D"box-sizing:border-box">'</span></sp=
an><span style=3D"color:rgb(36,41,46);font-family:SFMono-Regular,Consolas,&=
quot;Liberation Mono",Menlo,monospace;font-size:12px;white-space:pre">=
,</span><span style=3D"font-family:SFMono-Regular,Consolas,"Liberation=
Mono",Menlo,monospace;font-size:12px;white-space:pre"><font color=3D"=
#005cc5">2</font></span><span style=3D"color:rgb(36,41,46);font-family:SFMo=
no-Regular,Consolas,"Liberation Mono",Menlo,monospace;font-size:1=
2px;white-space:pre">,</span><span class=3D"gmail-pl-c1" style=3D"box-sizin=
g:border-box;color:rgb(0,92,197);font-family:SFMono-Regular,Consolas,"=
Liberation Mono",Menlo,monospace;font-size:12px;white-space:pre">28</s=
pan><span style=3D"color:rgb(36,41,46);font-family:SFMono-Regular,Consolas,=
"Liberation Mono",Menlo,monospace;font-size:12px;white-space:pre"=
>)</span><br></div><div><font color=3D"#24292e" face=3D"SFMono-Regular, Con=
solas, Liberation Mono, Menlo, monospace"><span style=3D"font-size:12px;whi=
te-space:pre">...</span></font></div><div><font color=3D"#24292e" face=3D"S=
FMono-Regular, Consolas, Liberation Mono, Menlo, monospace"><span style=3D"=
font-size:12px;white-space:pre"><br></span></font></div><div><font color=3D=
"#24292e" face=3D"SFMono-Regular, Consolas, Liberation Mono, Menlo, monospa=
ce"><span style=3D"font-size:12px;white-space:pre">Cheers,</span></font></d=
iv></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_att=
r">On Thu, 5 Dec 2019 at 23:11, Lars Aksel Opsahl <<a href=3D"mailto:Lar=
[email protected]">[email protected]</a>> wrote:<br></div><blockquote=
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px so=
lid rgb(204,204,204);padding-left:1ex">
<div dir=3D"ltr">
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
Hi</div>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
I have a function that prepares data, so the big job can be run it in paral=
lel.=C2=A0</div>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
Today I have solved this by using "Gnu parallel" like this.</div>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
<span style=3D"font-family:Menlo;font-size:12px;font-style:normal;font-vari=
ant-caps:normal;color:rgb(0,0,0);text-decoration:underline">psql</span><spa=
n style=3D"font-family:Menlo;font-size:12px;font-style:normal;font-variant-=
caps:normal;color:rgb(0,0,0)">
</span><span style=3D"font-family:Menlo;font-size:12px;font-style:normal;fo=
nt-variant-caps:normal;color:rgb(0,0,0);text-decoration:underline">testdb</=
span><span style=3D"font-family:Menlo;font-size:12px;font-style:normal;font=
-variant-caps:normal;color:rgb(0,0,0)">
-c"\! </span><span style=3D"font-family:Menlo;font-size:12px;font-sty=
le:normal;font-variant-caps:normal;color:rgb(0,0,0);text-decoration:underli=
ne">psql</span><span style=3D"font-family:Menlo;font-size:12px;font-style:n=
ormal;font-variant-caps:normal;color:rgb(0,0,0)">
-t -q -o /</span><span style=3D"font-family:Menlo;font-size:12px;font-styl=
e:normal;font-variant-caps:normal;color:rgb(0,0,0);text-decoration:underlin=
e">tmp</span><span style=3D"font-family:Menlo;font-size:12px;font-style:nor=
mal;font-variant-caps:normal;color:rgb(0,0,0)">/run_cmd.sql
</span><span style=3D"font-family:Menlo;font-size:12px;font-style:normal;fo=
nt-variant-caps:normal;color:rgb(0,0,0);text-decoration:underline">testdb</=
span><span style=3D"font-family:Menlo;font-size:12px;font-style:normal;font=
-variant-caps:normal;color:rgb(0,0,0)">
-c\"SELECT </span><span style=3D"font-family:Menlo;font-size:12px;fon=
t-style:normal;font-variant-caps:normal;color:rgb(0,0,0)">find_overlap_gap_=
make_run_cmd('sl_lop.overlap_gap_input_t1','</span><span style=
=3D"font-family:Menlo;font-size:12px;font-style:normal;font-variant-caps:no=
rmal;color:rgb(0,0,0);text-decoration:underline">geom</span><span style=3D"=
font-family:Menlo;font-size:12px;font-style:normal;font-variant-caps:normal=
;color:rgb(0,0,0)">',4258,'sl_lop.overlap_gap_input_t1_res',50)=
;\";
parallel -j 4</span><span style=3D"font-family:Menlo;font-size:12px;font-s=
tyle:normal;font-variant-caps:normal;color:rgb(0,0,0)">=C2=A0
</span><span style=3D"font-family:Menlo;font-size:12px;font-style:normal;fo=
nt-variant-caps:normal;color:rgb(0,0,0);text-decoration:underline">psql</sp=
an><span style=3D"font-family:Menlo;font-size:12px;font-style:normal;font-v=
ariant-caps:normal;color:rgb(0,0,0)">
</span><span style=3D"font-family:Menlo;font-size:12px;font-style:normal;fo=
nt-variant-caps:normal;color:rgb(0,0,0);text-decoration:underline">testdb</=
span><span style=3D"font-family:Menlo;font-size:12px;font-style:normal;font=
-variant-caps:normal;color:rgb(0,0,0)">
-c :::: /</span><span style=3D"font-family:Menlo;font-size:12px;font-style=
:normal;font-variant-caps:normal;color:rgb(0,0,0);text-decoration:underline=
">tmp</span><span style=3D"font-family:Menlo;font-size:12px;font-style:norm=
al;font-variant-caps:normal;color:rgb(0,0,0)">/run_cmd.sql"
2>> /</span><span style=3D"font-family:Menlo;font-size:12px;font-sty=
le:normal;font-variant-caps:normal;color:rgb(0,0,0);text-decoration:underli=
ne">tmp</span><span style=3D"font-family:Menlo;font-size:12px;font-style:no=
rmal;font-variant-caps:normal;color:rgb(0,0,0)">/analyze.log;</span><br>
</div>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
<span style=3D"color:rgb(0,0,0);font-family:Calibri,Arial,Helvetica,sans-se=
rif;font-size:12pt"><br>
</span></div>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
<span style=3D"color:rgb(0,0,0);font-family:Calibri,Arial,Helvetica,sans-se=
rif;font-size:12pt">The problem here is that I depend on external code whic=
h may not be installed.=C2=A0</span><br>
</div>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
Since Postgres now supports parallel I was wondering if it's easy to tr=
igger parallel dynamically created SQL calls.</div>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
<br>
</div>
<div><font face=3D"Calibri, Arial, Helvetica, sans-serif" size=3D"3" style=
=3D"color:rgb(0,0,0)">If you look at=C2=A0</font><a href=3D"https://github.=
com/larsop/find-overlap-and-gap/blob/master/src/test/sql/regress/find_overl=
ap_and_gap.sql" id=3D"gmail-m_903330833331552120LPNoLP731509" style=3D"colo=
r:rgb(0,0,0);font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt"=
target=3D"_blank">https://github.com/larsop/find-overlap-and-gap/blob/mast=
er/src/test/sql/regress/find_overlap_and_gap.sql</a><font face=3D"Calibri, =
Arial, Helvetica, sans-serif" size=3D"3" style=3D"color:rgb(0,0,0)">=C2=A0<=
/font><font face=3D"Calibri, Arial, Helvetica, sans-serif" size=3D"3" style=
=3D"color:rgb(0,0,0)">
you see that </font>
<p style=3D"color:rgb(0,0,0);font-family:Menlo;font-size:12px;margin:0px;fo=
nt-style:normal;font-variant-caps:normal;font-weight:normal;font-stretch:no=
rmal;line-height:normal">
find_overlap_gap_make_run_cmd=C2=A0<span style=3D"color:rgb(0,0,0);font-fam=
ily:Calibri,Arial,Helvetica,sans-serif;font-size:12pt">generates as set of =
28 sql calls.=C2=A0</span></p>
<p style=3D"color:rgb(0,0,0);font-family:Menlo;font-size:12px;margin:0px;fo=
nt-style:normal;font-variant-caps:normal;font-weight:normal;font-stretch:no=
rmal;line-height:normal">
<span style=3D"color:rgb(0,0,0);font-family:Calibri,Arial,Helvetica,sans-se=
rif;font-size:12pt"><br>
</span></p>
<p style=3D"margin:0px;font-stretch:normal;line-height:normal"><font color=
=3D"#000000" face=3D"Calibri, Arial, Helvetica, sans-serif" size=3D"3">So i=
s it in a simple way possible to use Postgres parallel functionality to cal=
l this 28 functions i parallel so I=C2=A0</font><font color=3D"#000000" fac=
e=3D"Calibri, Arial, Helvetica, sans-serif"><span>don't</span></font><f=
ont color=3D"#000000" face=3D"Calibri, Arial, Helvetica, sans-serif" size=
=3D"3">=C2=A0have=C2=A0dependent
on=C2=A0externally install programs =C2=A0?</font></p>
<p style=3D"color:rgb(0,0,0);font-family:Menlo;font-size:12px;margin:0px;fo=
nt-style:normal;font-variant-caps:normal;font-weight:normal;font-stretch:no=
rmal;line-height:normal">
<span style=3D"color:rgb(0,0,0);font-family:Calibri,Arial,Helvetica,sans-se=
rif;font-size:12pt"><br>
</span></p>
<p style=3D"color:rgb(0,0,0);margin:0px;font-style:normal;font-variant-caps=
:normal;font-weight:normal;font-stretch:normal;line-height:normal">
<font face=3D"Calibri, Arial, Helvetica, sans-serif">When this 28 sql calls=
are done, the=C2=A0<span style=3D"font-family:Menlo;font-size:12px;backgro=
und-color:rgb(255,255,255);display:inline">find_overlap_gap_make_run_cmd ma=
y</span></font><span style=3D"color:rgb(0,0,0);font-family:Calibri,Arial,He=
lvetica,sans-serif;font-size:12pt">=C2=A0continue
to the next step of work. So the function that triggers parallel calls wai=
t for them complete and then may=C2=A0start on the next step of work.</span=
></p>
<p style=3D"color:rgb(0,0,0);margin:0px;font-style:normal;font-variant-caps=
:normal;font-weight:normal;font-stretch:normal;line-height:normal">
<span style=3D"color:rgb(0,0,0);font-family:Calibri,Arial,Helvetica,sans-se=
rif;font-size:12pt"><br>
</span></p>
<p style=3D"color:rgb(0,0,0);margin:0px;font-style:normal;font-variant-caps=
:normal;font-weight:normal;font-stretch:normal;line-height:normal">
<span style=3D"color:rgb(0,0,0);font-family:Calibri,Arial,Helvetica,sans-se=
rif;font-size:12pt">Thanks .</span></p>
<p style=3D"color:rgb(0,0,0);font-family:Menlo;font-size:12px;margin:0px;fo=
nt-style:normal;font-variant-caps:normal;font-weight:normal;font-stretch:no=
rmal;line-height:normal">
<span style=3D"color:rgb(0,0,0);font-family:Calibri,Arial,Helvetica,sans-se=
rif;font-size:12pt"><br>
</span></p>
<p style=3D"color:rgb(0,0,0);font-family:Menlo;font-size:12px;margin:0px;fo=
nt-style:normal;font-variant-caps:normal;font-weight:normal;font-stretch:no=
rmal;line-height:normal">
<span style=3D"color:rgb(0,0,0);font-family:Calibri,Arial,Helvetica,sans-se=
rif;font-size:12pt">Lars</span></p>
</div>
<br>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
=C2=A0</div>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
<br>
</div>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt=
;color:rgb(0,0,0)">
=C2=A0 =C2=A0</div>
</div>
</blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"=
class=3D"gmail_signature">Ondrej</div>
--000000000000603d1c059913029b--