Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: Frits Hoogland <frits.hoogland@gmail.com>
Mime-Version: 1.0 (1.0)
Subject: Re: Safe vm.overcommit_ratio for Large Multi-Instance PostgreSQL
 Fleet
Date: Wed, 6 Aug 2025 23:14:34 +0200
Message-Id: <3A250F65-F5DE-4E24-ADE7-BEFF4A18A8B9@gmail.com>
References: <f35cdf25-411f-4cbc-a877-85569cf78061@joeconway.com>
Cc: Priya V <mailme0216@gmail.com>, pgsql-performance@lists.postgresql.org
In-Reply-To: <f35cdf25-411f-4cbc-a877-85569cf78061@joeconway.com>
To: Joe Conway <mail@joeconway.com>
Archived-At: 
 <https://www.postgresql.org/message-id/3A250F65-F5DE-4E24-ADE7-BEFF4A18A8B9%40gmail.com>
Precedence: bulk

> As I said, do not disable swap. You don't need a huge amount, but maybe 16=
 GB or so would do it.

Joe, please, can you state a technical reason for saying this?
All you are saying is =E2=80=98don=E2=80=99t do this=E2=80=99.=20

I=E2=80=99ve stated my reasons for why this doesn=E2=80=99t make sense, and y=
ou don=E2=80=99t give any reason.

The article you cite does seem to point to general usage, not database usage=
.

Frits

> Op 6 aug 2025 om 18:33 heeft Joe Conway <mail@joeconway.com> het volgende g=
eschreven:
>=20
> =EF=BB=BF(Both: please trim and reply inline on these lists as I have done=
;
> Frits, please reply all not just to the list -- I never received your
> reply to me)
>=20
>> On 8/6/25 11:51, Priya V wrote:
>> *cat /proc/sys/vm/overcommit_ratio*
>> 50
>> $ *cat /proc/sys/vm/swappiness*
>> 60
>> *Workload*: Multi-tenant PostgreSQL
>> *uname -r*
>> 4.18.0-477.83.1.el8_8.x86_64
>=20
> IMHO you should strongly consider getting on a more recent distro with a n=
ewer kernel.
>=20
>> *free -h*
>> total used free shared buff/cache available
>> Mem: 249Gi 4.3Gi 1.7Gi 22Gi 243Gi 221Gi
>> Swap: 0B 0B 0B
>=20
> As I said, do not disable swap. You don't need a huge amount, but maybe 16=
 GB or so would do it.
>=20
>> if we set overcommit_memory =3D 2, what should we set the overcommit_rati=
on value to ? Can you pls suggest ?
>> Is there a rule of thumb to go with ?
>=20
> There is no rule of thumb that I am aware of. Every workload is different.=
 Start with something like 80 and do your own testing to refine that number.=

>=20
>> *Our goal is to not run into OOM issues, no memory wastage and also not s=
tarve kernel ? *
>=20
> With overcommit_memory =3D 2, swap on (and reasonably sized), and overcomm=
it_ratio to something reasonable (certainly below 100), I think you will hav=
e a difficult time getting an OOM kill even if you try during testing. But y=
ou have to do your own testing for your workloads -- there is no magic butto=
n here.
>=20
> That is, unless you run postgres in a cgroup with memory.limit (cgroup v1)=
 or memory.max (cgroup v2) set. Note, running in containers with memory limi=
ts set e.g. via Kubernetes will do that under the covers. That is a complete=
ly different story.
>=20
>> On Wed, Aug 6, 2025 at 3:47=E2=80=AFAM Frits Hoogland <frits.hoogland@gma=
il.com <mailto:frits.hoogland@gmail.com>> wrote:
>>    Can you name any technical reason why not having swap for a database
>>    is an actual bad idea?
>=20
> Did you read the blog I linked? Do your own experiments.
>=20
> * Swap is what is used when anonymous memory must be reclaimed to allow fo=
r an allocation of anonymous memory.
>=20
> * The Linux kernel will aggressively use all available memory for file buf=
fers, pushing usage against the limits.
>=20
> * Especially in the older 4 series kernels, file buffers often cannot be r=
eclaimed fast enough
>=20
> * With no swap and a large-ish anonymous memory request, it is easy to pus=
h over the limit to cause the OOM killer to strike.
>=20
> * On the other hand, with swap enabled anon memory can be reclaimed giving=
 the kernel more time to deal with file buffer reclamation.
>=20
> At least that is what I have observed.
>=20
> HTH,
>=20
> --
> Joe Conway
> PostgreSQL Contributors Team
> Amazon Web Services: https://aws.amazon.com