Re: Effect of changing the value for PARALLEL_TUPLE_QUEUE_SIZE

On Tue, May 30, 2017 at 5:28 AM, Rafia Sabih
<rafia(dot)sabih(at)enterprisedb(dot)com> wrote:
> Hello everybody,
>
> Here is a thing I observed in my recent experimentation, on changing
> the value of PARALLEL_TUPLE_QUEUE_SIZE to 6553600, the performance of
> a TPC-H query is improved by more than 50%.

How many tuples are being gathered? This could happen if the workers
are waiting for the leader to make space in the queue after its
filled. By increasing the queue size we might be reducing the waiting
time for worker. In that case, it may be better to check why leader is
not pulling rows faster. How does the performance vary with different
values of PARALLEL_TUPLE_QUEUE_SIZE?

>
> Specifically, with this change, q12 completes in 14 seconds which was
> taking 45 seconds on head. There wasn't any change in the plan
> structure, just the time at gather-merge reduced which gave this
> improvement.
>
> This clearly says that the current value of PARALLEL_TUPLE_QUEUE_SIZE
> is not the best one for all the queries, rather some modification in
> it is very likely to improve performance significantly. One way to do
> is to give this parameters as another GUC just like
> min_parallel_table_scan_size, etc.

GUC may help.

>
> Attached .txt file gives the plan at head and with this patch,
> additionally patch is attached for setting PARALLEL_TUPLE_QUEUE_SIZE
> to 6553600 too.

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?

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company