ERROR: too many dynamic shared memory segments

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

ERROR: too many dynamic shared memory segments

ncontu1
Hello,
We are running postgres 11.5 and in the last two weeks we did :

- upgrade of postgres to 11.5 from 11.4
- increased shared_buffer to 1/3 of the memory
- increased effective_cache_size = 160GB from 120
- increased checkpoint_completion_target = 0.9 from 0.7
- increased checkpoint_timeout = 1h
- increased work_mem = 2GB (this can be set up to 4GB) from 600MB

Since that, in the last two weeks we saw an increment of this error : 

ERROR: too many dynamic shared memory segments 

Is there any relation between these parameters or the pgsql 11.5 version?

Any help can be appreciated.

Thank you,
Nicola
Reply | Threaded
Open this post in threaded view
|

Re: ERROR: too many dynamic shared memory segments

Pavel Stehule
Hi

st 11. 9. 2019 v 9:48 odesílatel Nicola Contu <[hidden email]> napsal:
Hello,
We are running postgres 11.5 and in the last two weeks we did :

- upgrade of postgres to 11.5 from 11.4
- increased shared_buffer to 1/3 of the memory
- increased effective_cache_size = 160GB from 120
- increased checkpoint_completion_target = 0.9 from 0.7
- increased checkpoint_timeout = 1h
- increased work_mem = 2GB (this can be set up to 4GB) from 600MB

Since that, in the last two weeks we saw an increment of this error : 

ERROR: too many dynamic shared memory segments 

Is there any relation between these parameters or the pgsql 11.5 version?

I expect it can be related to increasing work_mem - maybe parallel hash join was used

Regards

Pavel

Any help can be appreciated.

Thank you,
Nicola
Reply | Threaded
Open this post in threaded view
|

Re: ERROR: too many dynamic shared memory segments

ncontu1
In reply to this post by ncontu1
Hello,
We did not see any error in the logs, just that one.
Unfortunately we had problems installing updates in this machine and we are not installing updates since a few months.

Do you think that can be the issue? We are running Centos 7.

I will look into those parameters as well.

Thanks for your feedback


Il giorno mer 11 set 2019 alle ore 09:56 Mickael Franc (mickaelf) <[hidden email]> ha scritto:
Hello,

Maybe a change needed in a kernel parameter such "kernel.shmmax" or "kernel.shmall" to allow by kernel to provide a huge shared-mem.
Did you see any other error in logs ? Have you upgrade your kernel since last two weeks ?

Best,

De : Nicola Contu <[hidden email]>
Envoyé : mercredi 11 septembre 2019 09:47
À : pgsql-generallists.postgresql.org <[hidden email]>
Cc : Alessandro Aste <[hidden email]>
Objet : ERROR: too many dynamic shared memory segments
 
Hello,
We are running postgres 11.5 and in the last two weeks we did :

- upgrade of postgres to 11.5 from 11.4
- increased shared_buffer to 1/3 of the memory
- increased effective_cache_size = 160GB from 120
- increased checkpoint_completion_target = 0.9 from 0.7
- increased checkpoint_timeout = 1h
- increased work_mem = 2GB (this can be set up to 4GB) from 600MB

Since that, in the last two weeks we saw an increment of this error : 

ERROR: too many dynamic shared memory segments 

Is there any relation between these parameters or the pgsql 11.5 version?

Any help can be appreciated.

Thank you,
Nicola
Reply | Threaded
Open this post in threaded view
|

Re: ERROR: too many dynamic shared memory segments

ncontu1
In reply to this post by Pavel Stehule
If the error persist I will try to revert the work_mem.
Thanks a lot

Il giorno mer 11 set 2019 alle ore 10:10 Pavel Stehule <[hidden email]> ha scritto:
Hi

st 11. 9. 2019 v 9:48 odesílatel Nicola Contu <[hidden email]> napsal:
Hello,
We are running postgres 11.5 and in the last two weeks we did :

- upgrade of postgres to 11.5 from 11.4
- increased shared_buffer to 1/3 of the memory
- increased effective_cache_size = 160GB from 120
- increased checkpoint_completion_target = 0.9 from 0.7
- increased checkpoint_timeout = 1h
- increased work_mem = 2GB (this can be set up to 4GB) from 600MB

Since that, in the last two weeks we saw an increment of this error : 

ERROR: too many dynamic shared memory segments 

Is there any relation between these parameters or the pgsql 11.5 version?

I expect it can be related to increasing work_mem - maybe parallel hash join was used

Regards

Pavel

Any help can be appreciated.

Thank you,
Nicola
Reply | Threaded
Open this post in threaded view
|

Re: ERROR: too many dynamic shared memory segments

Thomas Munro-5
On Wed, Sep 11, 2019 at 11:20 PM Nicola Contu <[hidden email]> 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