Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ukIMP-000dbl-8b for pgsql-performance@arkaria.postgresql.org; Fri, 08 Aug 2025 08:21:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ukIMO-00AVRS-0G for pgsql-performance@arkaria.postgresql.org; Fri, 08 Aug 2025 08:21:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ukIMN-00AVRH-JS for pgsql-performance@lists.postgresql.org; Fri, 08 Aug 2025 08:21:55 +0000 Received: from mail-ej1-x634.google.com ([2a00:1450:4864:20::634]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ukIML-001PP8-0R for pgsql-performance@lists.postgresql.org; Fri, 08 Aug 2025 08:21:55 +0000 Received: by mail-ej1-x634.google.com with SMTP id a640c23a62f3a-af925cbd73aso363726966b.1 for ; Fri, 08 Aug 2025 01:21:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1754641312; x=1755246112; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=RAFeuwsyTagmm86fvbktIQvFZQYiQacCjAuwpZyMgus=; b=PHY+JmpqRt3+1RuV4OiSIXYkn0awhn3PjdPbbGCzj0nFJ9LFctEmrE67VvP2FlrejW xWD71zE7r7mVtbRUGm0tNCfwDJ9yJ1+6OBmlpyCnLC86ZctlnmcQXeCjPi+637ScCk+n 3O8XW4MuMXuhgGXv4tOLf3PL/rCgMWNHKWZiDnzSOu4nAPbUFTjsI0/DdsUFlTdFxenA +AJmqR2tACpdJR8YErvBdhYRiQZHMjgYboG6BF2tCxfMqLTI86/i3rNaz4UaYgWfEFxC 5mT7+fXnCaL7g1gSk4c6QvgqeYkcsXSnHOSeOXj2HZ9H05f+nPxHuqGt0TEqvUCppt4x bAag== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1754641312; x=1755246112; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=RAFeuwsyTagmm86fvbktIQvFZQYiQacCjAuwpZyMgus=; b=a1swo8/HlmqxWsf/YJIRzIiqvMR6aj+GOGK4VeNdSP9nNuimbRKXka/kf9RXw7TpWF 5foizUxT1xFQBvLY7JtHTfS98QZOyiszaQtXCpm0eTjS+Vrz0RvGDzUut/PEErHbIWbp u43u0AFLFLSezMdWdlZh0asQdXw14pCt5iQx9ca2Wk0Y9qjjSfX3m+tY9mmm6M5/79i/ whsPEF2PBL+QeksAOcOLGJnOAAt/bkt+QeE5Oi7PVwGXS3ZKs/hZ3/oHypXkuPyagcED vfCDgt1C8GcANLwBSWf3Bj0oO1tUZ0wsXMuQoOpxwHn9yGegAkhaCtdSJ6ZHaZ4QWZqF wuGg== X-Forwarded-Encrypted: i=1; AJvYcCUfPEYDjtYOb3XUSu+dj9UYlhMTOTx3ZMfksHLWVC+cr0/j9Kmqlpeti3iLGExPi1xL0E5wVZ5mgbE2vrHpPBiAfw==@lists.postgresql.org X-Gm-Message-State: AOJu0YwWcLPdtCd6myD6F3gAnE/VahBHIhCJHO6UMVwmUXGixS5VZvJV OVxbMAklnlorVEFlbp3y/u8phVPpF5GmRm0Vtwm0ES3/UW4aEoWHK5wZyEd53g== X-Gm-Gg: ASbGnctb67c+6k4fXvEMVMrXnoILgW5CMVz+qfKFrgzOOqVPhZmcT79fKTJQD9bZkXX l8S0vZCmmYHmTy0g19ToaHqp8Ro1ae9uPIEMrQhTS5jgKDvUVYPUmJd5XJ85Wan5ZvzC2Nceo35 JST4JdeCFbDMxzRFyySQiFxve7klxyA2OwnZMscIotSRJlbqkHpJjKRKI3QZCsx4Uh8k1ZUL+7r p9kcDpqtHv7j2gjZysgccBn4DK9O1uEaZ5oJ/Bhc6Xnh+zlvY/s8A0wccjG0LIwYgVIGMox6IJI +xB7y0e7aTOZz9OxuCTrYQHvyztUm+k00yKnekQ1IqI464sGE1RrSrgjpGl++LEBOoABKORTbkB 80YZSeSOA3L1mETWspyaNuZbwbztufD/8MWVHZBqc92S6y1lunmlYdTw0kg9H9u07PQom946W+T yHJT0gx5mL5Hh0vC7b4Hn8xMOkq1PLAVA8bwlI8cy6hmvwdhj27d8tP5H44g== X-Google-Smtp-Source: AGHT+IHHj7Fst++B2sE6L3HmJddCV7y7ne77FFL9/ZDXFdGqRIe8/l+ur/eMFEPXalC2v70+RjLtvw== X-Received: by 2002:a17:906:4788:b0:ae9:8dc8:511c with SMTP id a640c23a62f3a-af9c641a806mr167102466b.13.1754641311690; Fri, 08 Aug 2025 01:21:51 -0700 (PDT) Received: from smtpclient.apple (2001-1c04-0681-7700-dc57-9531-aaa6-f343.cable.dynamic.v6.ziggo.nl. [2001:1c04:681:7700:dc57:9531:aaa6:f343]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-af91a1e6cecsm1459816766b.70.2025.08.08.01.21.51 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 08 Aug 2025 01:21:51 -0700 (PDT) From: Frits Hoogland Message-Id: <1A770E71-8F3C-4D92-816A-44C63AC1AFA7@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_37976106-1500-4952-B3F6-A239ACB0DC01" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\)) Subject: Re: Safe vm.overcommit_ratio for Large Multi-Instance PostgreSQL Fleet Date: Fri, 8 Aug 2025 10:21:40 +0200 In-Reply-To: <7fd9f434-e0d9-414b-b396-841ad3c00040@joeconway.com> Cc: Priya V , pgsql-performance@lists.postgresql.org To: Joe Conway References: <3A250F65-F5DE-4E24-ADE7-BEFF4A18A8B9@gmail.com> <7fd9f434-e0d9-414b-b396-841ad3c00040@joeconway.com> X-Mailer: Apple Mail (2.3826.700.81) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_37976106-1500-4952-B3F6-A239ACB0DC01 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Joe, I am trying to help, and make people think about things correctly. The linux kernel is actually constantly changing, sometimes subtle and = sometimes less subtle, and there is a general lack of very clear = statistics indicating the more nuanced memory operations, and the = documentation about it. And: there are a lot of myths about memory management, which either are = myths because it's a situation that was once true but given the changes = of the kernel code is not true anymore, but also sometimes just a myth. The best technical description of recent memory management that I could = find is: = https://lpc.events/event/11/contributions/896/attachments/793/1493/slides-= r2.pdf >>> Op 6 aug 2025 om 18:33 heeft Joe Conway > het volgende geschreven: >=20 >>> * Swap is what is used when anonymous memory must be reclaimed to >>> allow for an allocation of anonymous memory. Correct. Swapped out pages are anonymous memory pages exclusively. It's the result of memory reclaim for anonymous pages, which cannot be = discarded like (non dirty and non-pinned) file pages, which don't need = saving the page content. >>> * The Linux kernel will aggressively use all available memory for >>> file buffers, pushing usage against the limits. It's an explicit design of the linux kernel to not reclaim file pages = when they are unpinned/not used anymore, leaving them as a cached page. (anonymous pages are freed explciitly when released by the ower and put = on the free list) There is no aggresive push, file pages are left after use, so there is = no pushing usage against the limits. It's the swapper ('page daemon') that eventually (based on a zone limit = called 'memory low', which is vm.min_free_kbytes *2), based on LRU, = frees file pages, and when free memory gets to vm.min_free_kbytes*1 = (called 'pages min') forces tasks to free memory theirselves (called = 'direct reclaim'). >>> * Especially in the older 4 series kernels, file buffers often >>> cannot be reclaimed fast enough I am not sure what is described here, and whether this is about the = swapper or direct reclaim. There is no need to do this 'fast enough', see the above slide deck. This probably is aimed at the swapper not reclaiming 'fast enough', = however, that is not how this works: if memory requests makes free = memory go to 'pages min', a task will perform 'direct reclaim'. >>> * 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. I am afraid that this is not a correct representation of the actual = mechanism, again: look at the slide deck and explanations above. The swapper frees memory, which is used by a task requesting pages at = page fault, for which it doesn't matter if that is anonymous memory or = file memory..=20 If memory gets down to pages min, the swapper did not reclaim memory = fast enough, and a task will perform direct reclaim. The decision on what memory type to reclaim in case of direct reclaim is = file memory or anonymous memory. If there is no swap, the option to use anonymous memory is not = available, because anonymous pages cannot be discarded like non-dirty, = unpinned file pages can, they have to be preserved. If swappiness is set to 0, but swap is available, some documentation = suggests it will never use anonymous memory, however I found this not to = be true, linux might still choose anonymous memory to reclaim. = Obviously, the lower swappiness, the lesser reclaim will choose = anonymous memory pages. What you seem to suggest, is that with no swap, and thus the option to = use anonymous pages for reclaim the reclaim mechanism is dependent on = the speed of (file) reclaim, possibly from the swapper. I hope it's = clear this is not true. Obviously, when there is swap, the total amount of pages that become = potentially available for reclaim becomes higher, because the size of = swap anonymous pages can be reclaimed. But then if that amount is set to a low amount (as suggested: You don't = need a huge amount'), the actual increase in pages availability for = reclaim is negligible, and thus the benefit that it provides for not = running out of memory. >>> * On the other hand, with swap enabled anon memory can be >>> reclaimed giving the kernel more time to deal with file buffer >>> reclamation. See the explanation with the previous comments. Time is not a component = in reclaim for failure to find pages for a task that page faults for = memory addition, because a task will do direct reclaim if it exhausts = free memory provided by the swapper. >>> At least that is what I have observed. The kernel code for direct reclaim shows that when direct reclaim has = finished scanning memory pages (either only file pages with no swap, of = in the case of having swap, the file and anonymous pages), and wasn't = able to satisfy the request for the pages it needs, it will trigger the = kernel Out of memory thread, because it has run out of available pages = it needs. Again, like I mentioned in the beginning, there are lots and lots of = nuances and mechanisms in play, this is a reasonable basic explanation = of the mechanism based on the above slide deck and reading the kernel = code. One thing that can very easily be misleading is that memory is not a = general, system wide, pool, but instead separated by zones. This might = lead to situation where there still is memory available for reclaim = system wide, but not in the zone the process is scanning, and thus might = seem to run out of memory triggering the OOM killer when there still is = memory, which can be very confusing if you're not aware of these = details. I do have read, experimented, searched, tested and diagnosed a lot of = issues. And this is what have come up with, which does fit the kernel = code, and documentation that I trust. Based on these mechanisms, and especially for database systems, removing = swap is a way to take away a mechanism that has no benefit for database = systems on modern, high memory, systems. That does not mean it's not beneficial in other cases. If memory usage = is very dynamic, memory is more constrained, and the operation is less = latency sensitive, it might be a good idea to have an overflow, with all = the downsides that it brings. Frits Hoogland > On 7 Aug 2025, at 03:12, Joe Conway wrote: >=20 > On 8/6/25 17:14, Frits Hoogland wrote: >>> As I said, do not disable swap. You don't need a huge amount, but >>> maybe 16 GB or so would do it. >=20 >> 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. >> I=E2=80=99ve stated my reasons for why this doesn=E2=80=99t make = sense, and you don=E2=80=99t give any reason. >=20 > What do you call the below? >=20 >>> Op 6 aug 2025 om 18:33 heeft Joe Conway het = volgende geschreven: >=20 >>> * 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. >=20 > If you don't think that is adequate technical reason, feel free to = ignore my advice. >=20 > --=20 > Joe Conway > PostgreSQL Contributors Team > Amazon Web Services: https://aws.amazon.com --Apple-Mail=_37976106-1500-4952-B3F6-A239ACB0DC01 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Joe, I am = trying to help, and make people think about things = correctly.

The linux kernel is actually constantly = changing, sometimes subtle and sometimes less subtle, and there is a = general lack of very clear statistics indicating the more nuanced memory = operations, and the documentation about it.
And: there are a = lot of myths about memory management, which either are myths because = it's a situation that was once true but given the changes of the kernel = code is not true anymore, but also sometimes just a = myth.

The best technical description of recent = memory management that I could find is: https://lpc.events/event/11/contributions/896/attachments/= 793/1493/slides-r2.pdf

Op 6 = aug 2025 om 18:33 heeft Joe Conway <mail@joeconway.com> het = volgende geschreven:

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

Correct. = Swapped out pages are anonymous memory pages exclusively.
It's = the result of memory reclaim for anonymous pages, which cannot be = discarded like (non dirty and non-pinned) file pages, which don't need = saving the page content.

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

It's= an explicit design of the linux kernel to not reclaim file pages when = they are unpinned/not used anymore, leaving them as a cached = page.
(anonymous pages are freed explciitly when released by = the ower and put on the free list)
There is no aggresive push, = file pages are left after use, so there is no pushing usage against the = limits.
It's the swapper ('page daemon') that eventually = (based on a zone limit called 'memory low', which is vm.min_free_kbytes = *2), based on LRU, frees file pages, and when free memory gets to = vm.min_free_kbytes*1 (called 'pages min') forces tasks to free memory = theirselves (called 'direct reclaim').

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

I = am not sure what is described here, and whether this is about the = swapper or direct reclaim.
There is no need to do this 'fast = enough', see the above slide deck.
This probably is aimed at = the swapper not reclaiming 'fast enough', however, that is not how this = works: if memory requests makes free memory go to 'pages min', a task = will perform 'direct reclaim'.

* 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.

I = am afraid that this is not a correct representation of the actual = mechanism, again: look at the slide deck and explanations = above.
The swapper frees memory, which is used by a task = requesting pages at page fault, for which it doesn't matter if that is = anonymous memory or file memory.. 
If memory gets down to = pages min, the swapper did not reclaim memory fast enough, and a task = will perform direct reclaim.

The decision on = what memory type to reclaim in case of direct reclaim is file memory or = anonymous memory.
If there is no swap, the option to use = anonymous memory is not available, because anonymous pages cannot be = discarded like non-dirty, unpinned file pages can, they have to be = preserved.
If swappiness is set to 0, but swap is available, = some documentation suggests it will never use anonymous memory, however = I found this not to be true, linux might still choose anonymous memory = to reclaim. Obviously, the lower swappiness, the lesser reclaim will = choose anonymous memory pages.

What you seem to = suggest, is that with no swap, and thus the option to use anonymous = pages for reclaim the reclaim mechanism is dependent on the speed of = (file) reclaim, possibly from the swapper. I hope it's clear this is not = true.

Obviously, when there is swap, the total = amount of pages that become potentially available for reclaim becomes = higher, because the size of swap anonymous pages can be = reclaimed.
But then if that amount is set to a low amount (as = suggested: You don't need a huge amount'), the actual increase in pages = availability for reclaim is negligible, and thus the benefit that it = provides for not running out of memory.

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

=
See the explanation with the previous comments. Time is not a = component in reclaim for failure to find pages for a task that page = faults for memory addition, because a task will do direct reclaim if it = exhausts free memory provided by the swapper.

At = least that is what I have = observed.

The = kernel code for direct reclaim shows that when direct reclaim has = finished scanning memory pages (either only file pages with no swap, of = in the case of having swap, the file and anonymous pages), and wasn't = able to satisfy the request for the pages it needs, it will trigger the = kernel Out of memory thread, because it has run out of available pages = it needs.

Again, like I mentioned in the = beginning, there are lots and lots of nuances and mechanisms in play, = this is a reasonable basic explanation of the mechanism based on the = above slide deck and reading the kernel code.
One thing that = can very easily be misleading is that memory is not a general, system = wide, pool, but instead separated by zones. This might lead to situation = where there still is memory available for reclaim system wide, but not = in the zone the process is scanning, and thus might seem to run out of = memory triggering the OOM killer when there still is memory, which can = be very confusing if you're not aware of these = details.

I do have read, experimented, = searched, tested and diagnosed a lot of issues. And this is what have = come up with, which does fit the kernel code, and documentation that I = trust.

Based on these mechanisms, and = especially for database systems, removing swap is a way to take away a = mechanism that has no benefit for database systems on modern, high = memory, systems.
That does not mean it's not beneficial in = other cases. If memory usage is very dynamic, memory is more = constrained, and the operation is less latency sensitive, it might be a = good idea to have an overflow, with all the downsides that it = brings.


Frits Hoogland

On 7 Aug 2025, at 03:12, Joe = Conway <mail@joeconway.com> wrote:

On 8/6/25 17:14, Frits = Hoogland wrote:
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.
I=E2=80=99ve stated my reasons for why this doesn=E2=80=99= t make sense, and you don=E2=80=99t give any = reason.

What do you call the = below?

Op 6 = aug 2025 om 18:33 heeft Joe Conway <mail@joeconway.com> het = volgende geschreven:

* 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.

If you don't think that is = adequate technical reason, feel free to ignore my advice.

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

= --Apple-Mail=_37976106-1500-4952-B3F6-A239ACB0DC01--