From: "JOIGNY Michael @Neteven" <mjoigny@neteven.com>
Subject: Cannot allocate memory
Reply-To: mjoigny@neteven.com
To: pgsql-admin@postgresql.org
Message-ID: <e839bc32-d827-c840-88ae-52acc3a359eb@neteven.com>
Date: Fri, 18 Sep 2020 12:03:50 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------60321B41343FAF25F9D437A4"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------60321B41343FAF25F9D437A4
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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.


--------------60321B41343FAF25F9D437A4
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi Community, <br>
    </p>
    <p>I'm asking for your lights because i'm having memory problems
      with postgres.</p>
    <p>Examples of logs :<br>
    </p>
    <p><i>FATAL: could not fork new process for connection: Cannot
        allocate memory could not fork new process for connection:
        Cannot allocate memory<br>
        out of memory  DETAIL:  Failed on request of size 32800 in
        memory context "HashBatchContext".<br>
        out of memory DETAIL Failed on request of size 288 in memory
        context "CacheMemoryContext".<br>
      </i><br>
      <br>
      We use postgresql (primary/standby) with pgbouncer as a pooler,
      and repmgr as replication manager.</p>
    <p>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).</p>
    <p>Here is my configuration : <br>
    </p>
    <p><u>system : </u><br>
    </p>
    Debian : 9.13<br>
    Memory : 380 Go<br>
    Postgres : 12.4-1.pgdg90+1<br>
    Pgbouncer : 1.14<br>
    kernel.shmmax = 202591600640<br>
    kernel.shmall = 49460840
    <p><u>postgres : <br>
      </u></p>
    <p>dynamic_shared_memory_type = posix    # the default is the first
      option<br>
      max_connections = 2600            # (change requires restart)<br>
      work_mem = 96MB                # min 64kB<br>
      maintenance_work_mem = 8GB        # min 1MB<br>
      shared_buffers = 64GB            # min 128kB<br>
      temp_buffers = 32MB            # min 800kB<br>
      wal_buffers = 16MB            # min 32kB, -1 sets based on
      shared_buffers<br>
      effective_cache_size = 270GB<br>
    </p>
    <p>pgbouncer : <br>
    </p>
    <p>max_client_conn = 6000<br>
      default_pool_size = 2590<br>
      reserve_pool_size = 5<br>
      pool_mode = session<br>
      <br>
      Do you think that our parameters are not correct compared to our
      configuration? Do you have an idea ?<br>
    </p>
    <p>Best regards.<br>
      <br>
      Michael.<br>
    </p>
  </body>
</html>

--------------60321B41343FAF25F9D437A4--