Re: Effect of changing the value for PARALLEL_TUPLE_QUEUE_SIZE

On Thu, Jun 1, 2017 at 6:41 PM, Rafia Sabih
<rafia(dot)sabih(at)enterprisedb(dot)com> wrote:
> On Tue, May 30, 2017 at 4:57 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> 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?
>>
> I did further exploration trying other values of
> PARALLEL_TUPLE_QUEUE_SIZE and trying different queries and here are my
> findings,
> - on even setting PARALLEL_TUPLE_QUEUE_SIZE to 655360, there isn't
> much improvement in q12 itself.
> - there is no other TPC-H query which is showing significant
> improvement on 6553600 itself. There is a small improvement in q3
> which is also using gather-merge.
> - as per perf analysis of q12 on head and patch, the %age of
> ExecGatherMerge is 18% with patch and 98% on head, and similar with
> gather_merge_readnext and gather_merge_writenext.
>
> As per my understanding it looks like this increase in tuple queue
> size is helping only gather-merge. Particularly, in the case where it
> is enough stalling by master in gather-merge because it is maintaining
> the sort-order. Like in q12 the index is unclustered and gather-merge
> is just above parallel index scan, thus, it is likely that to maintain
> the order the workers have to wait long for the in-sequence tuple is
> attained by the master. Something like this might be happening, master
> takes one tuple from worker 1, then next say 10 tuples from worker 2
> and so on, and then finally returning to worker1, so, one worker 1 has
> done enough that filled it's queue it sits idle. Hence, on increasing
> the tuple queue size helps in workers to keep on working for longer
> and this is improving the performance.
>

Your reasoning sounds sensible to me. I think the other way to attack
this problem is that we can maintain some local queue in each of the
workers when the shared memory queue becomes full. Basically, we can
extend your "Faster processing at Gather node" patch [1] such that
instead of fixed sized local queue, we can extend it when the shm
queue become full. I think that way we can handle both the problems
(worker won't stall if shm queues are full and workers can do batched
writes in shm queue to avoid the shm queue communication overhead) in
a similar way.

[1] - https://www.postgresql.org/message-id/CAOGQiiMwhOd5-iKZnizn%2BEdzZmB0bc3xa6rKXQgvhbnQ29zCJg%40mail.gmail.com

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com