Re: How to set parallel_tuple_cost

--0000000000004a1d13059a2cd666
Content-Type: text/plain; charset="UTF-8"

On Fri, Dec 20, 2019 at 1:58 PM Tom Lane <[email protected]> wrote:

> Jeff Janes <[email protected]> writes:
> > The docs for parallel_tuple_cost are quite terse, as the reference
> section
> > of the docs usually are:
> > "Sets the planner's estimate of the cost of transferring one tuple from a
> > parallel worker process to another process. The default is 0.1."
>
> > If you were take the doc description literally, then the default value
> > seems much too high, as it doesn't take 10x the (default) cpu_tuple_cost
> to
> > transfer a tuple up from a parallel worker.
>
> Really?  If anything, I'd have thought it might be worse than 10x.
> Cross-process communication isn't cheap, at least not according to
> my instincts.
>

I was a bit surprised.  I set it up so that there was a fine-tunable filter
which can be applied in the parallel workers, and then only the surviving
tuples get passed up to the leader.  The use of a parallel seq scan didn't
become slower than the non-parallel version until over 95% of the tuples
were surviving the filter.  If I wanted to make the estimated cost
cross-over point match the measured time cross-over point, I had to mark
the parallel_tuple_cost down to about 0.011.  This was an 8 CPU machine, an
AWS m5.4xlarge, with max_parallel_workers_per_gather=7.  (On my crummy
2-CPU Windows 10 laptop running ubuntu via VirtualBox, the cross-over point
was closer to 40% of the tuples surviving, and the parallel_tuple_cost to
match cross-over point would be about 0.016, but I don't have enough RAM to
make a large enough all-in-shared-buffer table to really get a good
assessments).

My method was to make shared_buffers be a large fraction of RAM (55GB, out
of 64GB), then make a table slightly smaller than that and forced it into
shared_buffers with pg_prewarm.  I set seq_page_cost = random_age_cost = 0,
to accurately reflect the fact that no IO is occuring.

create table para_seq as select floor(random()*10000)::int as id, random()
as x, md5(random()::text)||md5(random()::text) t from
generate_series(1,8000000*55);
vacuum ANALYZE para_seq ;
select pg_prewarm('para_seq');

explain (analyze, buffers, settings, timing off) select * from para_seq
where id<9500;

Where you can change the 9500 to tune the selectivity of the filter.  Is
this the correct way to try to isolate just the overhead of transferring of
a tuple away from other considerations so it can be measured?

I don't think the fact that EXPLAIN ANALYZE throws away the result set
without reading it should change anything.  Reading it should add the same
fixed overhead to both parallel and non-parallel, so would dilute out
percentage difference without change absolute differences.

I tried it with wider tuples as well, but not so wide they would activate
TOAST, and didn't really see a difference in the conclusion.


> > On the other hand, you
> > probably don't want a query which consumes 8x the CPU resources just to
> > finish only 5% faster (on an otherwise idle server with 8 CPUs).  Maybe
> > this Amdahl factor is what inspired the high default value?
>
> I think the large value of parallel_setup_cost is what's meant to
> discourage that scenario.
>

I think that can only account for overhead like forking and setting up
memory segments.  The overhead of moving around tuples (more than
single-threaded execution already moves them around) would need to scale
with the number of tuples moved around.

Cheers,

Jeff

--0000000000004a1d13059a2cd666
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Dec 20, 2019 at 1:58 PM Tom Lane =
&lt;<a href=3D"mailto:[email protected]">[email protected]</a>&gt; wrote:<b=
r></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">Jeff Janes &lt;<a href=3D"mailto:[email protected]" target=3D=
"_blank">[email protected]</a>&gt; writes:<br>
&gt; The docs for parallel_tuple_cost are quite terse, as the reference sec=
tion<br>
&gt; of the docs usually are:<br>
&gt; &quot;Sets the planner&#39;s estimate of the cost of transferring one =
tuple from a<br>
&gt; parallel worker process to another process. The default is 0.1.&quot;<=
br>
<br>
&gt; If you were take the doc description literally, then the default value=
<br>
&gt; seems much too high, as it doesn&#39;t take 10x the (default) cpu_tupl=
e_cost to<br>
&gt; transfer a tuple up from a parallel worker.<br>
<br>
Really?=C2=A0 If anything, I&#39;d have thought it might be worse than 10x.=
<br>
Cross-process communication isn&#39;t cheap, at least not according to<br>
my instincts.<br></blockquote><div><br></div><div>I was a bit surprised.=C2=
=A0 I set it up so that there was a fine-tunable filter which can be applie=
d in the parallel workers, and then only the surviving tuples get passed up=
 to the leader.=C2=A0 The use of a parallel seq scan didn&#39;t become slow=
er than the non-parallel version until over 95% of the tuples were survivin=
g the filter.=C2=A0 If I wanted to make the estimated cost cross-over point=
 match the measured time cross-over point, I had to mark the parallel_tuple=
_cost down to about 0.011.=C2=A0 This was an 8 CPU machine, an AWS m5.4xlar=
ge, with max_parallel_workers_per_gather=3D7.=C2=A0 (On my crummy 2-CPU Win=
dows 10 laptop running ubuntu via VirtualBox, the cross-over point was clos=
er to 40% of the tuples surviving, and the parallel_tuple_cost to match cro=
ss-over point would be about 0.016, but I don&#39;t have enough RAM to make=
 a large enough all-in-shared-buffer table to really get a good assessments=
).</div><div><br></div><div>My method was to make shared_buffers be a large=
 fraction of RAM (55GB, out of 64GB), then make a table slightly smaller th=
an that and forced it into shared_buffers with pg_prewarm.=C2=A0 I set seq_=
page_cost =3D random_age_cost =3D 0, to accurately reflect the fact that no=
 IO is occuring.</div><div><br></div><div>create table para_seq as select f=
loor(random()*10000)::int as id, random() as x, md5(random()::text)||md5(ra=
ndom()::text) t from generate_series(1,8000000*55);<br>vacuum ANALYZE para_=
seq ;<br>select pg_prewarm(&#39;para_seq&#39;);<br></div><div><br></div><di=
v>explain (analyze, buffers, settings, timing off) select * from para_seq w=
here id&lt;9500;<br></div><div>=C2=A0</div><div>Where you can change the 95=
00 to tune the selectivity of the filter.=C2=A0 Is this the correct way to =
try to isolate just the overhead of transferring of a tuple away from other=
 considerations so it can be measured?</div><div><br></div><div>I don&#39;t=
 think the fact that EXPLAIN ANALYZE throws away the result set without rea=
ding it should change anything.=C2=A0 Reading it should add the same fixed =
overhead to both parallel and non-parallel, so would dilute out percentage =
difference without change absolute differences.</div><div><br></div><div>I =
tried it with wider tuples as well, but not so wide they would activate TOA=
ST, and didn&#39;t really see a difference in the conclusion.</div><div><br=
></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;=
border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
&gt; On the other hand, you<br>
&gt; probably don&#39;t want a query which consumes 8x the CPU resources ju=
st to<br>
&gt; finish only 5% faster (on an otherwise idle server with 8 CPUs).=C2=A0=
 Maybe<br>
&gt; this Amdahl factor is what inspired the high default value?<br>
<br>
I think the large value of parallel_setup_cost is what&#39;s meant to<br>
discourage that scenario.<br></blockquote><div><br></div><div>I think that =
can only account for overhead like forking and setting up memory segments.=
=C2=A0 The overhead of moving around tuples (more than single-threaded exec=
ution already moves them around) would need to scale with the number of tup=
les moved around.</div><div>=C2=A0</div><div>Cheers,</div><div><br></div><d=
iv>Jeff</div></div></div>

--0000000000004a1d13059a2cd666--