Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kKi4a-0000ul-IB for pgsql-admin@arkaria.postgresql.org; Tue, 22 Sep 2020 13:11:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kKi4Z-0000l0-Fg for pgsql-admin@arkaria.postgresql.org; Tue, 22 Sep 2020 13:11:07 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kKi4Z-0000kt-4w for pgsql-admin@lists.postgresql.org; Tue, 22 Sep 2020 13:11:07 +0000 Received: from mail-pf1-x42c.google.com ([2607:f8b0:4864:20::42c]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kKi4O-0001Sw-Qm for pgsql-admin@lists.postgresql.org; Tue, 22 Sep 2020 13:11:06 +0000 Received: by mail-pf1-x42c.google.com with SMTP id l126so12404174pfd.5 for ; Tue, 22 Sep 2020 06:10:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=JbzEZx3uRH/Eh3wLR+XeeokeTOTNuYrwG0HTlCY2Yng=; b=Fx8M8KTrfaug5g62bWMiTpm7VW6yueegqUBJDbk1nmau0b/0oYWGbkD/lYnSEouWur YZgDjs6NyzIqCklzwi2HSYd0q4omLguhFR/TCucXE0Sy09C9RWtrSn2o59uE10U3sytu ayXroucdTHW4YTbP43WfiK9Z3fBfDmOxLi8CcU1HZ+aMV5AfpFwRvCa1UyszrTVsSytl St6k1fUVxUiH67bi86JLQsj2Dhsff/ZKpQQVCLvbdT/rjw8JD4z8/GLzcTsyEQ6lzCx4 IvCRjHfNVVtz4qYmqkpoq27M2U2yFJMkj/SYVMRLYtUlhSSSwRX23vviVuWY2arM/0L9 DQkA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=JbzEZx3uRH/Eh3wLR+XeeokeTOTNuYrwG0HTlCY2Yng=; b=K+wSB6JhDvxsZSyDORAsQLQWjtGhg0165OUi/3xOtC2KzXqBTTlbLedKIGM1hYK2ho hDIxv1mqt7UbrLPXuLkVs46HgE56GOA3TtjsFEHRRIQylzxZOGotXXHqdmHaGksSzF4F sw7oxIkQ+5OXSXaXbisI7mSJnXgX4iPcQBnn/BMsjkblACMpqdQHMu1bZtkbZEdY7Boq Q3O4sSK96/xJrChBr70JFRhOdOWgj7oPvNofmYgUhoGCn6wjqfTw4hEF29Nl/4c0SJ45 aWXuZ4POSyah/0tXt6Pyj3bKqxwNHb+kRIhIBg1xnrLAaHgyexMQhgyXlfywFUEL83QT J41w== X-Gm-Message-State: AOAM531azN1UZqjOQgD3oBMgjHIz/wzr6yKuNxB4W2gcLu9bfFbJi347 DJ/eCfrxXq117C4FiMWI+PU= X-Google-Smtp-Source: ABdhPJxvYQiLkgDNRvWot54UDiWUjDGBdCOV1MdCTsuVRN0fwUqIhhrKwYAX0KigHxyp95PLn31eeA== X-Received: by 2002:aa7:9f4a:0:b029:142:2501:34f7 with SMTP id h10-20020aa79f4a0000b0290142250134f7mr4024347pfr.80.1600780254630; Tue, 22 Sep 2020 06:10:54 -0700 (PDT) Received: from 192-168-1-114.tpgi.com.au ([203.63.94.51]) by smtp.gmail.com with ESMTPSA id q24sm14925516pfs.206.2020.09.22.06.10.52 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 22 Sep 2020 06:10:54 -0700 (PDT) From: Hannah Huang Message-Id: <6DA04674-987B-490A-AEE6-DB8542905CC4@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_D5EF4214-5AFB-48A9-8D7B-EE6E93257F70" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.1\)) Subject: Re: Cannot allocate memory Date: Tue, 22 Sep 2020 23:10:50 +1000 In-Reply-To: <2d20d632-f9ee-9606-f674-18d19b1790c0@neteven.com> Cc: pgsql-admin@lists.postgresql.org To: mjoigny@neteven.com References: <2d20d632-f9ee-9606-f674-18d19b1790c0@neteven.com> X-Mailer: Apple Mail (2.3608.120.23.2.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_D5EF4214-5AFB-48A9-8D7B-EE6E93257F70 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On 22 Sep 2020, at 6:20 pm, JOIGNY Michael @Neteven = wrote: >=20 > Hi, >=20 > I would like to come back to you about my memory problem on postgres = 12. >=20 > We had the same configuration under postgres 11.8, we disabled JIT = (enabled by default under postgres 12) for segfault problems.=20 >=20 > To illustrate the change in memory behavior, here is a screenshot = before / after migration :=20 >=20 > >=20 >=20 > Do you have any idea what could change the behavior in this way? = another parameter enabled by default under postgres 12 like JIT? >=20 > Regards. >=20 > Le 18/09/2020 =C3=A0 12:03, JOIGNY Michael @Neteven a =C3=A9crit : >> Hi Community,=20 >>=20 >> I'm asking for your lights because i'm having memory problems with = postgres. >>=20 >> Examples of logs : >>=20 >> 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". >>=20 >>=20 >> We use postgresql (primary/standby) with pgbouncer as a pooler, and = repmgr as replication manager. >>=20 >> 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). >>=20 >> Here is my configuration :=20 >>=20 >> system :=20 >>=20 >> Debian : 9.13 >> Memory : 380 Go >> Postgres : 12.4-1.pgdg90+1 >> Pgbouncer : 1.14 >> kernel.shmmax =3D 202591600640 >> kernel.shmall =3D 49460840 >> postgres :=20 >>=20 >> dynamic_shared_memory_type =3D posix # the default is the first = option >> max_connections =3D 2600 # (change requires restart) >> work_mem =3D 96MB # min 64kB >> maintenance_work_mem =3D 8GB # min 1MB >> shared_buffers =3D 64GB # min 128kB >> temp_buffers =3D 32MB # min 800kB >> wal_buffers =3D 16MB # min 32kB, -1 sets based on = shared_buffers >> effective_cache_size =3D 270GB >>=20 >> pgbouncer :=20 >>=20 >> max_client_conn =3D 6000 >> default_pool_size =3D 2590 >> reserve_pool_size =3D 5 >> pool_mode =3D session >>=20 >> Do you think that our parameters are not correct compared to our = configuration? Do you have an idea ? >>=20 >> Best regards. >>=20 >> Michael. >>=20 System settings:=20 After 9.2, PostgreSQL switched to POSIX shared memory. So now it = requires fewer bytes of System V shared memory.=20 You don=E2=80=99t need to configure SHMMAX to that high value which is = ~188GB, same as SHMALL, I would set them back to default value.=20 But, from the second email you sent it seems like an issue related to = the version upgrade (from 11 to 12). So I don=E2=80=99t 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.=20 Thanks, Hannah= --Apple-Mail=_D5EF4214-5AFB-48A9-8D7B-EE6E93257F70 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On 22 Sep 2020, at 6:20 pm, JOIGNY Michael @Neteven <mjoigny@neteven.com>= wrote:

=20 =20

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 =C3=A0 12:03, JOIGNY = Michael @Neteven a =C3=A9crit :

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 =3D 202591600640
kernel.shmall =3D 49460840

postgres :

dynamic_shared_memory_type =3D = posix    # the default is the first option
max_connections =3D 2600        =     # (change requires restart)
work_mem =3D 96MB        =         # min 64kB
maintenance_work_mem =3D 8GB    =     # min 1MB
shared_buffers =3D 64GB        =     # min 128kB
temp_buffers =3D 32MB        =     # min 800kB
wal_buffers =3D 16MB        =     # min 32kB, -1 sets based on shared_buffers
effective_cache_size =3D 270GB

pgbouncer :

max_client_conn =3D 6000
default_pool_size =3D 2590
reserve_pool_size =3D 5
pool_mode =3D 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=E2=80=99t 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=E2=80=99t 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
=

--Apple-Mail=_D5EF4214-5AFB-48A9-8D7B-EE6E93257F70--