Re: ERROR: too many dynamic shared memory segments

On Mon, Nov 27, 2017 at 10:54 PM, Jakub Glapa <jakub(dot)glapa(at)gmail(dot)com> wrote:
> The DB enters recovery mode after that.

That's not good. So it actually crashes? Can you please show the
full error messages?

> 2017-11-23 07:20:39 CET::@:[24823]: ERROR: could not attach to dynamic
> shared area

From src/backend/utils/mmgr/dsa.c. The same message is used for
dsa_attach() and for attach_internal(), but in this case it must be
the latter because we use in-place DSA areas for parallel query. This
means that when the worker tried to attach it found that
control->refcnt == 0, meaning 'too late, every other backend including
the leader has already detached'.

> 2017-11-23 07:20:39 CET::@:[24822]: ERROR: could not map dynamic shared
> memory segment

From src/backend/access/transam/parallel.c when dsm_attach returns
null. I think this also means 'too late'.

So those are both workers that have started up and found that the
leader has abandoned the parallel query already, but they discovered
it at different stages. PID 24823 didn't manage to attach to the DSM
segment, while PID 24822 did but found that the other(s) had already
detached from the per-query DSA area inside it.

> 2017-11-23 07:20:40 CET:192.168.xx,xx(33974):u(at)db:[24209]: ERROR: too
> many
> dynamic shared memory segments
>
> The errors happen when the parallel execution is enabled and multiple
> queries are executed simultaneously.
> If I set the max_parallel_workers_per_gather = 0 the error doesn't occur.
> The queries are rather big, each executes over 10sec.
> I see the error when 4 or more queries are started at the same time.
>
> my postgresql.conf:
>
> max_connections = 100

So the two questions we need to figure out are: (1) how do we manage
to use up all 64 + 2 * 100 DSM slots (or somehow corrupt things so it
looks that way) by running only 4 queries, and (2) why would be be
crashing rather than failing and reporting an error? I'm not able to
reproduce the problem from your description running lots of parallel
queries running at the same time. Can you provide reproduction steps?
Does your query plan happen to include a Parallel Bitmap Heap Scan?

--
Thomas Munro
http://www.enterprisedb.com