From: JOIGNY Michael @Neteven <mjoigny@neteven.com>
To: Hannah Huang <hannah.huang.y@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Cannot allocate memory
Date: Tue, 22 Sep 2020 16:59:15 +0200
Message-ID: <c3a1c4a7-8968-c7ed-2d72-e19f930d32f6@neteven.com> (raw)
In-Reply-To: <6DA04674-987B-490A-AEE6-DB8542905CC4@gmail.com>
References: <e839bc32-d827-c840-88ae-52acc3a359eb@neteven.com>
<2d20d632-f9ee-9606-f674-18d19b1790c0@neteven.com>
<6DA04674-987B-490A-AEE6-DB8542905CC4@gmail.com>
Hi Hannah,
Thanks for your feedback.
Here are the CPU / IO graphs over the same period.
You will find on the graphs the annotation which specifies the moment of
the migration.
cpu
io_requests
io_bytes
io_time
Le 22/09/2020 à 15:10, Hannah Huang a écrit :
>
>
>> On 22 Sep 2020, at 6:20 pm, JOIGNY Michael @Neteven
>> <mjoigny@neteven.com <mailto: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
Attachments:cpu.png (image/png, 225.0K) downloadview imageio_requests.png (image/png, 181.8K) downloadview imageio_bytes.png (image/png, 168.7K) downloadview imageio_time.png (image/png, 150.2K) downloadview image
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: mjoigny@neteven.com, hannah.huang.y@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Cannot allocate memory
In-Reply-To: <c3a1c4a7-8968-c7ed-2d72-e19f930d32f6@neteven.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