Re: Cannot allocate memory

> On 22 Sep 2020, at 6:20 pm, JOIGNY Michael @Neteven <mjoigny(at)neteven(dot)com> wrote:
>
> Hi,
>
> I would like to come back to you about my memory problem on postgres 12.
>
> We had the same configuration under postgres 11.8, we disabled JIT (enabled by default under postgres 12) for segfault problems.
>
> To illustrate the change in memory behavior, here is a screenshot before / after migration :
>
> <memory.png>
>
>
> Do you have any idea what could change the behavior in this way? another parameter enabled by default under postgres 12 like JIT?
>
> Regards.
>
> Le 18/09/2020 à 12:03, JOIGNY Michael @Neteven a écrit :
>> Hi Community,
>>
>> I'm asking for your lights because i'm having memory problems with postgres.
>>
>> Examples of logs :
>>
>> FATAL: could not fork new process for connection: Cannot allocate memory could not fork new process for connection: Cannot allocate memory
>> out of memory DETAIL: Failed on request of size 32800 in memory context "HashBatchContext".
>> out of memory DETAIL Failed on request of size 288 in memory context "CacheMemoryContext".
>>
>>
>> We use postgresql (primary/standby) with pgbouncer as a pooler, and repmgr as replication manager.
>>
>> We have ~ 2000 connections at the same time with ~ 20/30 are active. (we need to set a high number of connexion on postgres, because our app uses a lot of different users, and each user on each app server needs multiple and constant connexions).
>>
>> Here is my configuration :
>>
>> system :
>>
>> Debian : 9.13
>> Memory : 380 Go
>> Postgres : 12.4-1.pgdg90+1
>> Pgbouncer : 1.14
>> kernel.shmmax = 202591600640
>> kernel.shmall = 49460840
>> postgres :
>>
>> dynamic_shared_memory_type = posix # the default is the first option
>> max_connections = 2600 # (change requires restart)
>> work_mem = 96MB # min 64kB
>> maintenance_work_mem = 8GB # min 1MB
>> shared_buffers = 64GB # min 128kB
>> temp_buffers = 32MB # min 800kB
>> wal_buffers = 16MB # min 32kB, -1 sets based on shared_buffers
>> effective_cache_size = 270GB
>>
>> pgbouncer :
>>
>> max_client_conn = 6000
>> default_pool_size = 2590
>> reserve_pool_size = 5
>> pool_mode = session
>>
>> Do you think that our parameters are not correct compared to our configuration? Do you have an idea ?
>>
>> Best regards.
>>
>> Michael.
>>
System settings:
After 9.2, PostgreSQL switched to POSIX shared memory. So now it requires fewer bytes of System V shared memory.

You don’t need to configure SHMMAX to that high value which is ~188GB, same as SHMALL, I would set them back to default value.

But, from the second email you sent it seems like an issue related to the version upgrade (from 11 to 12). So I don’t think you should change OS parameters at this moment. I would suggest you provide more stats on CPU (system, user, wa,ni) and disk IO - before and after.

Thanks, Hannah