Re: Choosing parallel_degree

On 11/04/2016 17:44, Robert Haas wrote:
> On Mon, Apr 11, 2016 at 11:27 AM, Julien Rouhaud
> <julien(dot)rouhaud(at)dalibo(dot)com> wrote:
>> On 11/04/2016 15:56, tushar wrote:
>>>
>>> I am assuming parallel_degree=0 is as same as not using it , i.e
>>> create table fok2(n int) with (parallel_degree=0); = create table
>>> fok2(n int);
>>>
>>> so in this case it should have accepted the parallel seq .scan.
>>>
>>
>> No, setting it to 0 means to force not using parallel workers (but
>> considering the parallel path IIRC).
>
> I'm not sure what the parenthesized bit means, because you can't use
> parallelism without workers.

Obvious mistake, sorry.

> But I think I should have made the docs
> more clear that 0 = don't parallelize scans of this table while
> committing this. Maybe we should go add a sentence about that.
>

What about

- the setting of <xref linkend="guc-max-parallel-degree">.
+ the setting of <xref linkend="guc-max-parallel-degree">. Setting
this
+ parameter to 0 will disable parallelism for that table.

>> Even if you set a per-table parallel_degree higher than
>> max_parallel_degree, it'll be maxed at max_parallel_degree.
>>
>> Then, the explain shows that the planner assumed it'll launch 9 workers,
>> but only 8 were available (or needed perhaps) at runtime.
>
> We should probably add the number of workers actually obtained to the
> EXPLAIN ANALYZE output. That's been requested before.
>

If it's not too late for 9.6, it would be very great.

>>> postgres=# set max_parallel_degree =2624444;
>>> ERROR: 2624444 is outside the valid range for parameter
>>> "max_parallel_degree" (0 .. 262143)
>>>
>>> postgres=# set max_parallel_degree =262143;
>>> SET
>>> postgres=#
>>>
>>> postgres=# explain analyze verbose select * from abd where n<=1;
>>> ERROR: requested shared memory size overflows size_t
>>>
>>> if we remove the analyze keyword then query running successfully.
>>>
>>> Expected = Is it not better to throw the error at the time of setting
>>> max_parallel_degree, if not supported ?
>>
>> +1
>
> It surprises me that that request overflowed size_t. I guess we
> should look into why that's happening. Did you test this on a 32-bit
> system?
>

I can reproduce the same issue on a 64 bits system. Setting
max_parallel_degree to 32768 or above raise this error:

ERROR: could not resize shared memory segment "/PostgreSQL.44279285" to
18446744072113360072 bytes: Invalid argument

On a 32 bits system, following assert fails:

TRAP: FailedAssertion("!(offset < total_bytes)", File: "shm_toc.c",
Line: 192)

After some gdb, it looks like the overflow comes from

/* Estimate space for tuple queues. */
shm_toc_estimate_chunk(&pcxt->estimator,
PARALLEL_TUPLE_QUEUE_SIZE * pcxt->nworkers);

372 shm_toc_estimate_chunk(&pcxt->estimator,
(gdb) p pcxt->estimator
$2 = {space_for_chunks = 3671712, number_of_keys = 3}
(gdb) n
374 shm_toc_estimate_keys(&pcxt->estimator, 1);
(gdb) p pcxt->estimator
$3 = {space_for_chunks = 18446744071565739680, number_of_keys = 3}

Following change fix the issue:

diff --git a/src/backend/executor/execParallel.c
b/src/backend/executor/execParallel.c
index 572a77b..0a5210e 100644
--- a/src/backend/executor/execParallel.c
+++ b/src/backend/executor/execParallel.c
@@ -370,7 +370,7 @@ ExecInitParallelPlan(PlanState *planstate, EState
*estate, int nworkers)

/* Estimate space for tuple queues. */
shm_toc_estimate_chunk(&pcxt->estimator,
- PARALLEL_TUPLE_QUEUE_SIZE * pcxt->nworkers);
+ (Size) PARALLEL_TUPLE_QUEUE_SIZE *
pcxt->nworkers);
shm_toc_estimate_keys(&pcxt->estimator, 1);

But the query still fails with "ERROR: out of shared memory".

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org