MIME-Version: 1.0
References: <CAC5iy61r=0hQ=jnFtr4r1uZuw1i=ZF32EiS53AhpEFBhwkOHbA@mail.gmail.com>
 <37308daa-3860-455f-b9f0-567a982c4554@joeconway.com>
In-Reply-To: <37308daa-3860-455f-b9f0-567a982c4554@joeconway.com>
From: Siraj G <tosiraj.g@gmail.com>
Date: Thu, 17 Oct 2024 10:42:46 +0530
Message-ID: <CAC5iy63vNdm4KY6V+MMyD5oJN7ZSm0LBSBqirNcp1ckShbSv=g@mail.gmail.com>
Subject: Re: Help in dealing with OOM
To: Joe Conway <mail@joeconway.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000592be20624a53d4a"
Archived-At: <https://www.postgresql.org/message-id/CAC5iy63vNdm4KY6V%2BMMyD5oJN7ZSm0LBSBqirNcp1ckShbSv%3Dg%40mail.gmail.com>
Precedence: bulk

--000000000000592be20624a53d4a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Thanks Joe, I will set these kernel parameters.

I also would like to highlight that the issue happened on SECONDARY. While
the PRIMARY has less memory and computation in comparison to SECONDARY, not
sure if there is anything wrong in the PgSQL.

PRIMARY: 48vCPUs & 48GB memory
SECONDARY: 64vCPUs & 64GB memory

I noticed a few things which do not sound tidy:
1. Total number of DBs are: 1860  (DB environment serves a product that has
tenants - around 1100 tenants which means these many DBs are active)
     : Is there any metric for optimal performance on the number of DBs we
should have per instance? I would assume NO (and it should be purely based
on the overall operations), but just a question out of curiosity.
2. max_connections is set to 10000.
I tried to reduce it to 4000 but was unable to do so (I tried this after
reducing the max_connections in PRIMARY to 4000). This is the error:
FATAL:  hot standby is not possible because max_connections =3D 4000 is a
lower setting than on the master server (its value was 10000)

If I am clubbing multiple things, sorry for the clutter.

Regards
Siraj

On Tue, Oct 15, 2024 at 12:39=E2=80=AFAM Joe Conway <mail@joeconway.com> wr=
ote:

> On 10/14/24 14:37, Siraj G wrote:
> > This is from the OS log (/var/log/kern.log):
> >
> > oom-
> >
> kill:constraint=3DCONSTRAINT_NONE,nodemask=3D(null),cpuset=3D/,mems_allow=
ed=3D0,global_oom,task_memcg=3D/system.sli
>      ce/system-postgresql.slice/postgresql@12-main.service
> ,task=3Dpostgres,pid=3D2334587,uid=3D114
> >    494 Oct 14 09:58:10 gce-k12-prod-as1-erp-pg-secondary kernel:
> > [6905020.514569] Out of memory: Killed process 2334587 (postgres) total=
-
> > vm:26349584kB, anon-rss:3464kB, file-rss:0kB, shmem-rs
> >   s:21813032kB, UID:114 pgtables:49024kB oom_score_adj:0
>
>
> 1. Do you happen to have swap disabled? If so, don't do that.
>
> 2. Does the postgres cgroup have memory.limit (cgroup v1) or memory.max
>     (cgroup v2) set?
>
> 3. If #2 answer is no, have you followed the documented guidance here
>     (in particular vm.overcommit_memory=3D2):
>
>
>
> https://www.postgresql.org/docs/12/kernel-resources.html#LINUX-MEMORY-OVE=
RCOMMIT
>
>
> --
> Joe Conway
> PostgreSQL Contributors Team
> RDS Open Source Databases
> Amazon Web Services: https://aws.amazon.com
>

--000000000000592be20624a53d4a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Thanks Joe, I will set these kernel param=
eters.<div><br></div><div>I also would like to highlight that the issue hap=
pened on SECONDARY. While the PRIMARY has less memory and computation in co=
mparison to SECONDARY, not sure if there is anything wrong in the PgSQL.</d=
iv><div><br></div><div>PRIMARY: 48vCPUs &amp; 48GB memory<br></div><div>SEC=
ONDARY: 64vCPUs &amp; 64GB memory</div><div><br></div><div>I noticed a few =
things which do not sound tidy:</div><div>1. Total number of DBs are: 1860=
=C2=A0 (DB environment serves a product that has tenants - around 1100 tena=
nts which means these many DBs are active)<br>=C2=A0 =C2=A0 =C2=A0: Is ther=
e any metric for optimal performance on the number of DBs we should have pe=
r instance? I would assume NO (and it should be purely based on the overall=
 operations), but just a question out of curiosity.</div><div>2. max_connec=
tions is set to 10000.=C2=A0</div><div>I tried to reduce it to 4000 but was=
 unable to do so (I tried this after reducing the max_connections in PRIMAR=
Y to 4000). This is the error:</div><div>FATAL: =C2=A0hot standby is not po=
ssible because max_connections =3D 4000 is a lower setting than on the mast=
er server (its value was 10000)<br></div><div><br></div><div>If I am clubbi=
ng multiple things, sorry for the clutter.</div><div><br></div><div>Regards=
</div><div>Siraj</div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr"=
 class=3D"gmail_attr">On Tue, Oct 15, 2024 at 12:39=E2=80=AFAM Joe Conway &=
lt;<a href=3D"mailto:mail@joeconway.com">mail@joeconway.com</a>&gt; wrote:<=
br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex">On 10/14/24 14:3=
7, Siraj G wrote:<br>
&gt; This is from the OS log (/var/log/kern.log):<br>
&gt; <br>
&gt; oom- <br>
&gt; kill:constraint=3DCONSTRAINT_NONE,nodemask=3D(null),cpuset=3D/,mems_al=
lowed=3D0,global_oom,task_memcg=3D/system.sli =C2=A0 =C2=A0 =C2=A0ce/system=
-postgresql.slice/postgresql@12-main.service,task=3Dpostgres,pid=3D2334587,=
uid=3D114<br>
&gt;=C2=A0 =C2=A0 494 Oct 14 09:58:10 gce-k12-prod-as1-erp-pg-secondary ker=
nel: <br>
&gt; [6905020.514569] Out of memory: Killed process 2334587 (postgres) tota=
l- <br>
&gt; vm:26349584kB, anon-rss:3464kB, file-rss:0kB, shmem-rs=C2=A0 =C2=A0 =
=C2=A0<br>
&gt;=C2=A0 =C2=A0s:21813032kB, UID:114 pgtables:49024kB oom_score_adj:0<br>
<br>
<br>
1. Do you happen to have swap disabled? If so, don&#39;t do that.<br>
<br>
2. Does the postgres cgroup have memory.limit (cgroup v1) or memory.max<br>
=C2=A0 =C2=A0 (cgroup v2) set?<br>
<br>
3. If #2 answer is no, have you followed the documented guidance here<br>
=C2=A0 =C2=A0 (in particular vm.overcommit_memory=3D2):<br>
<br>
<br>
<a href=3D"https://www.postgresql.org/docs/12/kernel-resources.html#LINUX-M=
EMORY-OVERCOMMIT" rel=3D"noreferrer" target=3D"_blank">https://www.postgres=
ql.org/docs/12/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT</a><br>
<br>
<br>
-- <br>
Joe Conway<br>
PostgreSQL Contributors Team<br>
RDS Open Source Databases<br>
Amazon Web Services: <a href=3D"https://aws.amazon.com" rel=3D"noreferrer" =
target=3D"_blank">https://aws.amazon.com</a><br>
</blockquote></div></div>

--000000000000592be20624a53d4a--