Re: ERROR: too many dynamic shared memory segments

We also reverted this param :

cmdv3=# show max_parallel_workers_per_gather;
max_parallel_workers_per_gather
---------------------------------
2
(1 row)

It was set to 8.

Il giorno mar 21 gen 2020 alle ore 16:06 Nicola Contu <
nicola(dot)contu(at)gmail(dot)com> ha scritto:

> Hey Thomas,
> after a few months, we started having this issue again.
> So we revert the work_mem parameter to 600MB instead of 2GB.
> But the issue is still there. A query went to segmentation fault, the DB
> went to recovery mode and our app went to read only for a few minutes.
>
> I understand we can increase max_connections so we can have many more
> segments.
>
> My question is : is there a way to understand the number of segments we
> reached?
> Currently we have 220 max_conn so as your formula is 64 + 2* 220 we have
> about 500 shared segments.
> We would like to increase that number to 300 or 400 but would be great to
> understand if there is a way to make sure we will solve the issue as it
> requires a restart of the service.
>
> I know you were also talking about a redesign this part in PostgreSQL. Do
> you know if anything has changed in any of the newer versions after 11.5?
>
> Thanks a lot,
> Nicola
>
>
>
>
>
> Il giorno gio 12 set 2019 alle ore 01:01 Thomas Munro <
> thomas(dot)munro(at)gmail(dot)com> ha scritto:
>
>> On Wed, Sep 11, 2019 at 11:20 PM Nicola Contu <nicola(dot)contu(at)gmail(dot)com>
>> wrote:
>> > If the error persist I will try to revert the work_mem.
>> > Thanks a lot
>>
>> Hi Nicola,
>>
>> It's hard to say exactly what the cause of the problem is in your case
>> and how to avoid it, without knowing what your query plans look like.
>> PostgreSQL allows 64 + 2 * max_connections segments to exist a time,
>> and it needs a number of them that depends on work_mem (in the case of
>> Parallel Hash Join and Parallel Bitmap Index Scan), and also depends
>> on the number of Gather nodes that appear in the plan, which in some
>> unusual cases can result from partitioning.
>>
>> I've seen people reaching this error by running a lot of parallel
>> queries concurrently. If that's the cause, then you can definitely
>> get some relief by turning work_mem down, or by turning
>> max_connections up (even though you don't want to allow more
>> connections -- because it influences the formula for deciding on the
>> DSM segment limit). We should probably adjust some of the internal
>> constants to give us more slots, to avoid that problem, as discussed
>> here:
>>
>>
>> https://www.postgresql.org/message-id/flat/CA%2BhUKGL6H2BpGbiF7Lj6QiTjTGyTLW_vLR%3DSn2tEBeTcYXiMKw%40mail.gmail.com
>>
>> I've also seen people reaching this error by somehow coming up with
>> plans that had a very large number of Gather nodes in them,
>> corresponding to partitions; that's probably a bad plan (it'd
>> presumably be better to terminate parallelism higher up in the plan,
>> but these plans do seem to exist in the wild; I don't recall exactly
>> why). I think we need a bit of a redesign so that if there are
>> multiple Gather nodes, they share the same main DSM segment, instead
>> of blowing through this limit.
>>
>> --
>> Thomas Munro
>> https://enterprisedb.com
>>
>