From: Hannah Huang <hannah.huang.y@gmail.com>
To: mjoigny@neteven.com
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Cannot allocate memory
Date: Tue, 22 Sep 2020 23:10:50 +1000
Message-ID: <6DA04674-987B-490A-AEE6-DB8542905CC4@gmail.com> (raw)
In-Reply-To: <2d20d632-f9ee-9606-f674-18d19b1790c0@neteven.com>
References: <e839bc32-d827-c840-88ae-52acc3a359eb@neteven.com>
<2d20d632-f9ee-9606-f674-18d19b1790c0@neteven.com>
> On 22 Sep 2020, at 6:20 pm, JOIGNY Michael @Neteven <mjoigny@neteven.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=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: hannah.huang.y@gmail.com, mjoigny@neteven.com, pgsql-admin@lists.postgresql.org
Subject: Re: Cannot allocate memory
In-Reply-To: <6DA04674-987B-490A-AEE6-DB8542905CC4@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox