Message-ID: <f35cdf25-411f-4cbc-a877-85569cf78061@joeconway.com>
Date: Wed, 6 Aug 2025 12:33:49 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Safe vm.overcommit_ratio for Large Multi-Instance PostgreSQL
 Fleet
To: Priya V <mailme0216@gmail.com>, Frits Hoogland <frits.hoogland@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
References: 
 <CAFsZ43xFxjSiONwRccXBQXZrPRd+Lh7XAkSVEG1ai165xPcoDA@mail.gmail.com>
 <3633df47-5693-4871-9e12-bfbb68dd3313@joeconway.com>
 <5520EFB7-EDD1-42C1-8141-63EED777E026@gmail.com>
 <CAFsZ43xF00OHNg6d8iD0zd0OCpWe8+r9p4A+HAupo0jxOdYgpQ@mail.gmail.com>
Content-Language: en-US
From: Joe Conway <mail@joeconway.com>
Autocrypt: addr=mail@joeconway.com; keydata=
 xsFNBEpXMCsBEADDnXUQzjlyi/cX02Gtdy2CLcroE5CsC7DJKdOBDbfgn0kfiIYoV5JniG4l
 VyzZUodY8yUAagqLYolh0UkBzs9N+qkm7erde4ypw3jzVQ37BuzIvk3nMUbuDZDgxWqX+nVS
 sKc+BQ5BpzgCHg48leoRO2ohjvYnUhgH3j2rFZCzaj6qQ7mv+XoxOJmUlVQtG06Jwkk7Vu14
 7U9nMMM6hyUKzVnmCphnlcMNo26UyVU70MwFfFJgcI0c5fpp8byN56eD6VJVnufO5WAuEhzE
 qcrSJR2FAlmM90GBY+6vP29twLDCHuSFvrnujNCx/BvCC/a3/gPvyAFp4JtMm9eXAmq3m/Kw
 94nTJXVdcbQeQQDp3KIG7MmWS4lnGvPn8v0CjgNaLvZXFLo1FgmUVsyEq1Lww4iRLa6sbpXJ
 ESx15UEue1k1YZM9C+4F/o3aeKNsAienjw2EXFzcaxIg/C4P493VMi3Qa8ycVxR5iYhUbYdo
 DFIUQhbFNsYfrtW/qZAELT3FCYFpZYG01e9Hj+cBrXXgyDDkQ5Lq4mlvmkRvuxn61V6Au4HA
 0sJiCox5pM1FvzT+aI8HY1BYaiB9Pl4fhpKgmhhlSuglk9v39S4jmlUIb45iLAUVpeNM6Qjm
 69pf5da9sm4aGFa7YlDSKf/WcU7z9ITZxsilOi2n7YJiwG7kTQARAQABzSRKb3NlcGggRSBD
 b253YXkgPG1haWxAam9lY29ud2F5LmNvbT7CwXoEEwEIACQCGwMCHgECF4AFCwkIBwMFFQoJ
 CAsFFgIDAQAFAlWTVvUCGQEACgkQMyt+aLaZQ0oPCQ/9HyRewMyvAIJRmoXoLAr8AoFLId6R
 qBJnNX0Lll0RLZui65aQ0+exwX7aH7TxWR16B2gWX3OmLfGT8XITOoG+zt9zsEpLvNkHchkF
 T/jyAcbuRj5WX9hamZgMbjXAJeCdlhW+fRA9Upb0w4dgBjqK5OgsqMikASL7t2vogHl9H08j
 vSoQLW+8wTnSBXBeBTBwB7xLIin5WVivzFHUCrnD2UsjeBIW3fmGdpTAjSxRzG+UPYVwXQ8F
 FLt7DpEytvLWapmZWMRdj0WZ/Q3SOO/Ed0yFqbzuwKaWcFrQBNeS2Sig+FefBNS98f9Hx7ku
 H3DW34qX/zSSdDh0jLs7X3PkIgF6BZR2TxaCwHPP9ERDiDaUInC9U7We1iZE1DjW8rLMEVJB
 hY0ClrrF67pnUKTbcU+uajpPn+2Jl74T0Set/XxpHZ4cezcJuqg31R8vHZgd5cf1WKP0D0pc
 qiuS02BBFkNCs1jQ+raTWcDuE6F1mUO2nvjUBN9r4y5DUbCNSqLKeAe/aA6JaSDkBpoXKdNS
 +c4rbzbktWkfUW8EhVlCGzNpy4ezEoVsqV2Ex7fNoxsE2vnSylLT9hycAmYf8ryMvniRZqnD
 T4JgLenIcQlkhB896T7wApOXfD8OJj1/XFxAfPi6vdlsr81uoxuB4euLp8IyduwLORRUogO9
 zmAXG5jOwU0ESlcyJwEQAOkTBb9yDhJbMUgvhM11rZwT5tm4Y9TqtEHn0Zy3t9g7bdFFpMva
 v/KENd3oAtLFpMDf+H3AggFk4ftUwJwiVgJ88ilvCynJUGXiuYIaexY4DLgn4xpnuiEpYEFV
 dWnlw7dWVTc62exfqIz9bSWRzwfBCY9ruYGEb4RDPDSNSAVyI7sxHzef2asiYxIcxrTrw5Vu
 gWNlPZcV5/EJ6PUvATjBF2TBkXV7KOciQng2tsQGrGMkY5mduNqwpuh6zfPcVF8LeObe96wv
 5ZhPRpO79nef7hnK2lJogp3JIo558Jlbz9WHtQEMZR85+bUhtI825QyNAFz3Jrn7NMgvDikc
 2OrWo7YMgMC5hDSWVFqA6/EQCNnDWGABWgeYHZFpnPwsvUWIYdhSilUuj/Tuzvz9ZmucFNbQ
 bauDQw6VQ38ofGnoYDZFJsGncprB8dBi4tDrIQ+1RlIh6C2Z/eMipqJOT26+spluTjouvnKT
 0S5yOgyX0PjbsysgwQdCGNJLHOjhHbSpSmOLaduV3CQo/0+DHT/TBjYfIXjTWouY9TkGxG4e
 NrxU0u2xAy5bMqOPmsFdjLTWlQUlF/fTMhB54XwI3FHWgnSnXZzStDTmTebLNdT/ftgliAzA
 81uMj49j0exv731/v+7udLA1bV8gnZ01zQCASDpWiRQR3fgwcugSUqgRABEBAAHCwV8EGAEI
 AAkFAkpXMicCGwwACgkQMyt+aLaZQ0pwAQ//bjcWnZg/jjRQ9gbZUGMqniItZYRglBMKIqt4
 Fia379JmHwTvavnFkJ8XMZ56UB0FIrgS+sUkRH6cPRQR+7Qi392LD021DXgSsz9CwFHjFyBG
 HwLEOTRcfYQbtJy0shHDJB4aQTOX3ERDH1PsvJNuevmQMzS0DWFav9+xMz9rKP4N+HffoBIZ
 E0C1xIE43nD4eLsbycte9sVIrmlNuUti3qUxJAQw8HwfJ6ZbBInHxquApR16uD1u99o6Xlnd
 FrDlY22tRmHCM0bR81GfGNdcU3Uo+rG/R/k4qa7s9/dgKvMbyH3fHhp/ceKag80Xo8IFurRl
 0ZJP3sHJ2QDHCVLat7jRZ+43hi1WlIhFbrgn6IyI0i7XR/W8JjrC5MsKq4TUwGH077sU/kcH
 YebVJZRbUUst2hAGHDFVBcG12qoKf+ltL9qXJc1y7BGeCoUW6QjOpljpq6ZL4FQUsM0RSRjs
 5egE3szPcIf5SyPK6WDOApoAq6M7BBFMGDZwEylYMtr0YekA1u86UA9D2xwLHEbBBp/uiby1
 c9JbPJ1Pn8zJP8WZNeRw4Q9TtqVK09+oLirMUSpIDd6KdZ1VgRxOK2re7tjDvkVuYsSrsiJ+
 1iJNEnp9iK0ok0DlJpSCe6KhkxpaTdeoWMXdKuJWec0NIqoAd54ZgBPnr+UPxTixgPq/p6Q=
In-Reply-To: 
 <CAFsZ43xF00OHNg6d8iD0zd0OCpWe8+r9p4A+HAupo0jxOdYgpQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/f35cdf25-411f-4cbc-a877-85569cf78061%40joeconway.com>
Precedence: bulk

(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)

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

IMHO you should strongly consider getting on a more recent distro with a 
newer kernel.

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

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

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

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.

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

With overcommit_memory = 2, swap on (and reasonably sized), and 
overcommit_ratio to something reasonable (certainly below 100), I think 
you will have a difficult time getting an OOM kill even if you try 
during testing. But you have to do your own testing for your workloads 
-- there is no magic button here.

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 limits set e.g. via Kubernetes will do that under the covers. 
That is a completely different story.

> On Wed, Aug 6, 2025 at 3:47 AM Frits Hoogland <frits.hoogland@gmail.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?

Did you read the blog I linked? Do your own experiments.

* Swap is what is used when anonymous memory must be reclaimed to allow 
for an allocation of anonymous memory.

* The Linux kernel will aggressively use all available memory for file 
buffers, pushing usage against the limits.

* Especially in the older 4 series kernels, file buffers often cannot be 
reclaimed fast enough

* With no swap and a large-ish anonymous memory request, it is easy to 
push over the limit to cause the OOM killer to strike.

* On the other hand, with swap enabled anon memory can be reclaimed 
giving the kernel more time to deal with file buffer reclamation.

At least that is what I have observed.

HTH,

-- 
Joe Conway
PostgreSQL Contributors Team
Amazon Web Services: https://aws.amazon.com