Re: Effect of changing the value for PARALLEL_TUPLE_QUEUE_SIZE

On Tue, May 30, 2017 at 6:50 AM, Ashutosh Bapat
<ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
> Increasing that number would require increased DSM which may not be
> available. Also, I don't see any analysis as to why 6553600 is chosen?
> Is it optimal? Does that work for all kinds of work loads?

Picky, picky. The point is that Rafia has discovered that a large
increase can sometimes significantly improve performance. I don't
think she's necessarily proposing that (or anything else) as a final
value that we should definitely use, just getting the conversation
started.

I did a little bit of brief experimentation on this same topic a long
time ago and didn't see an improvement from boosting the queue size
beyond 64k but Rafia is testing Gather rather than Gather Merge and,
as I say, my test was very brief. I think it would be a good idea to
try to get a complete picture here. Does this help on any query that
returns many tuples through the Gather? Only the ones that use Gather
Merge? Some queries but not others with no obvious pattern? Only
this query?

Blindly adding a GUC because we found one query that would be faster
with a different value is not the right solution. If we don't even
know why a larger value is needed here and (maybe) not elsewhere, then
how will any user possibly know how to tune the GUC? And do we really
want the user to have to keep adjusting a GUC before each query to get
maximum performance? I think we need to understand the whole picture
here, and then decide what to do. Ideally this would auto-tune, but
we can't write code for that without a more complete picture of the
behavior.

BTW, there are a couple of reasons I originally picked 64k here. One
is that making it smaller was very noticeably terrible in my testing,
while making it bigger didn't help much. The other is that I figured
64k was small enough that nobody would care about the memory
utilization. I'm not sure we can assume the same thing if we make
this bigger. It's probably fine to use a 6.4M tuple queue for each
worker if work_mem is set to something big, but maybe not if work_mem
is set to the default of 4MB.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company