MIME-Version: 1.0
References: 
 <CAFsZ43xFxjSiONwRccXBQXZrPRd+Lh7XAkSVEG1ai165xPcoDA@mail.gmail.com>
 <3633df47-5693-4871-9e12-bfbb68dd3313@joeconway.com>
 <5520EFB7-EDD1-42C1-8141-63EED777E026@gmail.com>
In-Reply-To: <5520EFB7-EDD1-42C1-8141-63EED777E026@gmail.com>
From: Priya V <mailme0216@gmail.com>
Date: Wed, 6 Aug 2025 10:51:29 -0500
Message-ID: 
 <CAFsZ43xF00OHNg6d8iD0zd0OCpWe8+r9p4A+HAupo0jxOdYgpQ@mail.gmail.com>
Subject: Re: Safe vm.overcommit_ratio for Large Multi-Instance PostgreSQL
 Fleet
To: Frits Hoogland <frits.hoogland@gmail.com>
Cc: Joe Conway <mail@joeconway.com>, pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000000f477d063bb45136"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFsZ43xF00OHNg6d8iD0zd0OCpWe8%2Br9p4A%2BHAupo0jxOdYgpQ%40mail.gmail.com>
Precedence: bulk

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

Hi Frits, Joe,

Thank you both for you insights

*Current situation:*

*cat /proc/sys/vm/overcommit_memory*
0

*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

*free -h*
total used free shared buff/cache available
Mem: 249Gi 4.3Gi 1.7Gi 22Gi 243Gi 221Gi
Swap: 0B 0B 0B

if we set overcommit_memory =3D 2, what should we set the overcommit_ration
value to ? Can you pls suggest ?
Is there a rule of thumb to go with ?

*Our goal is to not run into OOM issues, no memory wastage and also not
starve kernel ? *

Thanks!


On Wed, Aug 6, 2025 at 3:47=E2=80=AFAM Frits Hoogland <frits.hoogland@gmail=
.com>
wrote:

> Joe,
>
> Can you name any technical reason why not having swap for a database is a=
n
> actual bad idea?
>
> Memory always is limited. Swap was invented to overcome a situation where
> the (incidental) memory usage of paged in memory was could (regularly) ge=
t
> higher than physical memory would allow, and thus have the (clear)
> workaround of having swap to 'cushion' the memory shortage issue by
> allowing a "second level" memory storage on disk.
> Still, this does not making memory unlimited. Swap extends the physical
> memory available with the amount of swap. There still is a situation wher=
e
> you can run out of memory when swap is added, simply by paging in more
> memory than physical memory and swap.
>
> Today, most systems are not memory constrained anymore, or: it is possibl=
e
> to get a server with enough physical memory to hold your common needed
> total memory need.
> And given the latency sensitive nature of databases in general, which
> includes postgres, for any serious deployment you should get a server wit=
h
> enough memory to host your workload, and configure postgres not to overlo=
ad
> the memory.
>
> If you do oversubscribe on (physical) memory, you will get pain somewhere=
,
> there is no way around that.
> The article in defense of swap in essence is saying that if you happen to
> oversubscribe on memory, sharing the pain between anonymous and file is
> better.
> I would say you are already in a bad place if that happens, which is
> especially bad for databases, and databases should allow you to make memo=
ry
> usage predictable.
>
> However, what I found is that with 4+ kernels (4.18 to be precise; rhel
> 8), the kernel can try to favour file pages in certain situations making
> anonymous memory getting paged out even if swappiness is set to 1 or 0, a=
nd
> if there is a wealth of inactive file memory. It seems to have to do with
> workingset protection(?) mechanisms, but given the lack of clear statisti=
cs
> I can't be sure about that. What it does lead to in my situations is a
> constant rate of swapping in and out in certain situations, whilst there =
is
> no technical reason for linux to do so because there is enough available
> memory.
>
> My point of view has been that vm.overcommit_memory set to 2 was the way
> to go, because that allows linux to limit based on a set limit on
> allocation time, which guarantees way to make the database never run out =
of
> memory.
> it does guarantees linux to never run out of memory, absolutely.
> However, this limit is hard, and is applied for the process at both
> usermode and system mode (kernel level), and thus can enforce not providi=
ng
> memory at times where it's not safe to do so, and thus corrupt execution.=
 I
> have to be honest, I have not seen this myself, but trustworthy sources
> have reported this repeatedly, which I am inclined to believe. This means
> postgres execution can corrupt/terminate in unlucky situations, which is
> impacts availability.
>
>
> *Frits Hoogland*
>
>
>
>
> On 5 Aug 2025, at 20:52, Joe Conway <mail@joeconway.com> wrote:
>
> On 8/5/25 13:01, Priya V wrote:
>
> *Environment:*
>    *PostgreSQL Versions:* Mix of 13.13 and 15.12 (upgrades in progress
>    to be at 15.12 currently both are actively in use)
>
>
> PostgreSQL 13 end of life after November 13, 2025
>
>    *OS / Kernel:* RHEL 7 & RHEL 8 variants, kernels in the 4.14=E2=80=934=
.18 range
>
>
> RHEL 7 has been EOL for quite a while now. Note that you have to watch ou=
t
> for collation issues/corrupted indexes after OS upgrades due to collation=
s
> changing with newer glibc versions.
>
>    *Swap:* Currently none
>
>
> bad idea
>
>    *Workload:* Highly mixed =E2=80=94 OLTP-style internal apps with
>    unpredictable query patterns and connection counts
>    *Goal:* Uniform, safe memory settings across the fleet to avoid
>    kernel or database instability
>
>
> We=E2=80=99re considering:
>    *|vm.overcommit_memory =3D 2|* for strict accounting
>
>
> yes
>
>    Increasing |vm.overcommit_ratio| from 50 =E2=86=92 80 or 90 to better
>    reflect actual PostgreSQL usage (e.g., |work_mem| reservations that
>    aren=E2=80=99t fully used)
>
>
> work_mem does not reserve memory -- it is a maximum that might be used in
> memory for a particular operation
>
> *Our questions for those running large PostgreSQL fleets:*
> 1.
>    What |overcommit_ratio| do you find safe for PostgreSQL without
>    causing kernel memory crunches?
>
>
> Read this:
>
> https://www.cybertec-postgresql.com/en/what-you-should-know-about-linux-m=
emory-overcommit-in-postgresql/
>
> 2.
>    Do you prefer |overcommit_memory =3D 1| or |=3D 2| for production stab=
ility?
>
>
> Use overcommit_memory =3D 2 for production stability
>
> 3.
>    How much swap (if any) do you keep in large-memory servers where
>    PostgreSQL is the primary workload? Is having swap configured a good
>    idea or not ?
>
>
> You don't necessary need a large amount of swap, but you definitely shoul=
d
> not disable it.
>
> Some background on that:
> https://chrisdown.name/2018/01/02/in-defence-of-swap.html
>
> 4.
>    Any real-world cases where kernel accounting was too strict or too
>    loose for PostgreSQL?
>
>
> In my experience the biggest issues are when postgres is running in a
> memory constrained cgroup. If you want to constrain memory with cgroups,
> use cgroup v2 (not 1) and use memory.high to constrain it, not memory.max=
.
>
> 5. What settings to go with if we are not planning on using swap ?
>
>
> IMHO do not disable swap on Linux, at least not on production, ever.
>
> We=E2=80=99d like to avoid both extremes:
>    Too low a ratio =E2=86=92 PostgreSQL backends failing allocations even=
 with
>    free RAM
>
>
> Have you actually seen this or are you theorizing?
>
>    Too high a ratio =E2=86=92 OOM killer terminating PostgreSQL under loa=
d spikes
>
>
> If overcommit_memory =3D 2, overcommit_ratio is reasonable (less than 100=
,
> maybe 80 or so as you suggested), and swap is not disabled, and you are n=
ot
> running in a memory constrained cgroup, I would be very surprised if you
> will ever get hit by the OOM killer. And if you do, things are so bad the
> database was probably dying anyway.
>
> HTH,
>
> --
> Joe Conway
> PostgreSQL Contributors Team
> Amazon Web Services: https://aws.amazon.com
>
>
>
>

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

<div dir=3D"ltr"><div>Hi Frits, Joe,=C2=A0</div><div><br></div><div>Thank y=
ou both for you insights=C2=A0</div><div><br></div><div><b>Current situatio=
n:</b></div><div><br></div><div><b>cat /proc/sys/vm/overcommit_memory</b><b=
r>0<br><br><b>cat /proc/sys/vm/overcommit_ratio</b><br>50<br><br>$ <b>cat /=
proc/sys/vm/swappiness</b><br>60</div><div><br></div><div><strong>Workload<=
/strong>: Multi-tenant PostgreSQL</div><div><br></div><div><b>uname -r</b><=
br>4.18.0-477.83.1.el8_8.x86_64<br></div><div><br></div><div><b>free -h</b>=
<br> total used free shared buff/cache available<br>Mem: 249Gi 4.3Gi 1.7Gi =
22Gi 243Gi 221Gi<br>Swap: 0B 0B 0B</div><div><br></div><div>if we set overc=
ommit_memory =3D 2, what should we set the overcommit_ration value to ? Can=
 you pls suggest ?=C2=A0</div><div>Is there a rule of thumb to go with ?</d=
iv><div><br></div><div><b>Our goal is to not run into OOM issues, no memory=
 wastage and also not starve kernel ?=C2=A0</b></div><div><br></div><div>Th=
anks!=C2=A0</div><div><br></div><div><br></div><div><br></div><div><br></di=
v></div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"lt=
r" class=3D"gmail_attr">On Wed, Aug 6, 2025 at 3:47=E2=80=AFAM Frits Hoogla=
nd &lt;<a href=3D"mailto:frits.hoogland@gmail.com">frits.hoogland@gmail.com=
</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:=
0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=
<div>Joe,=C2=A0<div><br></div><div>Can you name any technical reason why no=
t having swap for a database is an actual bad idea?</div><div><br></div><di=
v>Memory always is limited. Swap was invented to overcome a situation where=
 the (incidental) memory usage of paged in memory was could (regularly) get=
 higher than physical memory would allow, and thus have the (clear) workaro=
und of having swap to &#39;cushion&#39; the memory shortage issue by allowi=
ng a &quot;second level&quot; memory storage on disk.</div><div>Still, this=
 does not making memory unlimited. Swap extends the physical memory availab=
le with the amount of swap. There still is a situation where you can run ou=
t of memory when swap is added, simply by paging in more memory than physic=
al memory and swap.</div><div><br></div><div>Today, most systems are not me=
mory constrained anymore, or: it is possible to get a server with enough ph=
ysical memory to hold your common needed total memory need.=C2=A0</div><div=
>And given the latency sensitive nature of databases in general, which incl=
udes postgres, for any serious deployment you should get a server with enou=
gh memory to host your workload, and configure postgres not to overload the=
 memory.</div><div><br></div><div>If you do oversubscribe on (physical) mem=
ory, you will get pain somewhere, there is no way around that.</div><div>Th=
e article in defense of swap in essence is saying that if you happen to ove=
rsubscribe on memory, sharing the pain between anonymous and file is better=
.</div><div>I would say you are already in a bad place if that happens, whi=
ch is especially bad for databases, and databases should allow you to make =
memory usage predictable.</div><div><br></div><div>However, what I found is=
 that with 4+ kernels (4.18 to be precise; rhel 8), the kernel can try to f=
avour file pages in certain situations making anonymous memory getting page=
d out even if swappiness is set to 1 or 0, and if there is a wealth of inac=
tive file memory. It seems to have to do with workingset protection(?) mech=
anisms, but given the lack of clear statistics I can&#39;t be sure about th=
at. What it does lead to in my situations is a constant rate of swapping in=
 and out in certain situations, whilst there is no technical reason for lin=
ux to do so because there is enough available memory.</div><div><br></div><=
div>My point of view has been that vm.overcommit_memory set to 2 was the wa=
y to go, because that allows linux to limit based on a set limit on allocat=
ion time, which guarantees way to make the database never run out of memory=
.</div><div>it does guarantees linux to never run out of memory, absolutely=
.</div><div>However, this limit is hard, and is applied for the process at =
both usermode and system mode (kernel level), and thus can enforce not prov=
iding memory at times where it&#39;s not safe to do so, and thus corrupt ex=
ecution. I have to be honest, I have not seen this myself, but trustworthy =
sources have reported this repeatedly, which I am inclined to believe. This=
 means postgres execution can corrupt/terminate in unlucky situations, whic=
h is impacts availability.</div><div><br></div><div>=C2=A0</div><div><div>
<div dir=3D"auto" style=3D"color:rgb(0,0,0);letter-spacing:normal;text-alig=
n:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing=
:0px;text-decoration:none"><div><b>Frits Hoogland</b></div><div><br></div><=
/div><br><br>

</div>
<div><br><blockquote type=3D"cite"><div>On 5 Aug 2025, at 20:52, Joe Conway=
 &lt;<a href=3D"mailto:mail@joeconway.com" target=3D"_blank">mail@joeconway=
.com</a>&gt; wrote:</div><br><div><div>On 8/5/25 13:01, Priya V wrote:<br><=
blockquote type=3D"cite">*Environment:*<br> =C2=A0=C2=A0=C2=A0*PostgreSQL V=
ersions:* Mix of 13.13 and 15.12 (upgrades in progress<br> =C2=A0=C2=A0=C2=
=A0to be at 15.12 currently both are actively in use)<br></blockquote><br>P=
ostgreSQL 13 end of life after November 13, 2025<br><br><blockquote type=3D=
"cite"> =C2=A0=C2=A0=C2=A0*OS / Kernel:* RHEL 7 &amp; RHEL 8 variants, kern=
els in the 4.14=E2=80=934.18 range<br></blockquote><br>RHEL 7 has been EOL =
for quite a while now. Note that you have to watch out for collation issues=
/corrupted indexes after OS upgrades due to collations changing with newer =
glibc versions.<br><br><blockquote type=3D"cite"> =C2=A0=C2=A0=C2=A0*Swap:*=
 Currently none<br></blockquote><br>bad idea<br><br><blockquote type=3D"cit=
e"> =C2=A0=C2=A0=C2=A0*Workload:* Highly mixed =E2=80=94 OLTP-style interna=
l apps with<br> =C2=A0=C2=A0=C2=A0unpredictable query patterns and connecti=
on counts<br> =C2=A0=C2=A0=C2=A0*Goal:* Uniform, safe memory settings acros=
s the fleet to avoid<br> =C2=A0=C2=A0=C2=A0kernel or database instability<b=
r></blockquote><br><blockquote type=3D"cite">We=E2=80=99re considering:<br>=
 =C2=A0=C2=A0=C2=A0*|vm.overcommit_memory =3D 2|* for strict accounting<br>=
</blockquote><br>yes<br><br><blockquote type=3D"cite"> =C2=A0=C2=A0=C2=A0In=
creasing |vm.overcommit_ratio| from 50 =E2=86=92 80 or 90 to better<br> =C2=
=A0=C2=A0=C2=A0reflect actual PostgreSQL usage (e.g., |work_mem| reservatio=
ns that<br> =C2=A0=C2=A0=C2=A0aren=E2=80=99t fully used)<br></blockquote><b=
r>work_mem does not reserve memory -- it is a maximum that might be used in=
 memory for a particular operation<br><br><blockquote type=3D"cite">*Our qu=
estions for those running large PostgreSQL fleets:*<br> 1.<br> =C2=A0=C2=A0=
=C2=A0What |overcommit_ratio| do you find safe for PostgreSQL without<br> =
=C2=A0=C2=A0=C2=A0causing kernel memory crunches?<br></blockquote><br>Read =
this:<br><a href=3D"https://www.cybertec-postgresql.com/en/what-you-should-=
know-about-linux-memory-overcommit-in-postgresql/" target=3D"_blank">https:=
//www.cybertec-postgresql.com/en/what-you-should-know-about-linux-memory-ov=
ercommit-in-postgresql/</a><br><br><blockquote type=3D"cite"> 2.<br> =C2=A0=
=C2=A0=C2=A0Do you prefer |overcommit_memory =3D 1| or |=3D 2| for producti=
on stability?<br></blockquote><br>Use overcommit_memory =3D 2 for productio=
n stability<br><br><blockquote type=3D"cite"> 3.<br> =C2=A0=C2=A0=C2=A0How =
much swap (if any) do you keep in large-memory servers where<br> =C2=A0=C2=
=A0=C2=A0PostgreSQL is the primary workload? Is having swap configured a go=
od<br> =C2=A0=C2=A0=C2=A0idea or not ?<br></blockquote><br>You don&#39;t ne=
cessary need a large amount of swap, but you definitely should not disable =
it.<br><br>Some background on that:<br><a href=3D"https://chrisdown.name/20=
18/01/02/in-defence-of-swap.html" target=3D"_blank">https://chrisdown.name/=
2018/01/02/in-defence-of-swap.html</a><br><br><blockquote type=3D"cite"> 4.=
<br> =C2=A0=C2=A0=C2=A0Any real-world cases where kernel accounting was too=
 strict or too<br> =C2=A0=C2=A0=C2=A0loose for PostgreSQL?<br></blockquote>=
<br>In my experience the biggest issues are when postgres is running in a m=
emory constrained cgroup. If you want to constrain memory with cgroups, use=
 cgroup v2 (not 1) and use memory.high to constrain it, not memory.max.<br>=
<br><blockquote type=3D"cite"> 5. What settings to go with if we are not pl=
anning on using swap ?<br></blockquote><br>IMHO do not disable swap on Linu=
x, at least not on production, ever.<br><br><blockquote type=3D"cite">We=E2=
=80=99d like to avoid both extremes:<br> =C2=A0=C2=A0=C2=A0Too low a ratio =
=E2=86=92 PostgreSQL backends failing allocations even with<br> =C2=A0=C2=
=A0=C2=A0free RAM<br></blockquote><br>Have you actually seen this or are yo=
u theorizing?<br><br><blockquote type=3D"cite"> =C2=A0=C2=A0=C2=A0Too high =
a ratio =E2=86=92 OOM killer terminating PostgreSQL under load spikes<br></=
blockquote><br>If overcommit_memory =3D 2, overcommit_ratio is reasonable (=
less than 100, maybe 80 or so as you suggested), and swap is not disabled, =
and you are not running in a memory constrained cgroup, I would be very sur=
prised if you will ever get hit by the OOM killer. And if you do, things ar=
e so bad the database was probably dying anyway.<br><br>HTH,<br><br>-- <br>=
Joe Conway<br>PostgreSQL Contributors Team<br>Amazon Web Services: <a href=
=3D"https://aws.amazon.com" target=3D"_blank">https://aws.amazon.com</a><br=
><br><br></div></div></blockquote></div><br></div></div></blockquote></div>

--0000000000000f477d063bb45136--