Message-ID: <c5a8939a-5459-4072-a43a-ada2adaf30ba@vondra.me>
Date: Wed, 1 Jan 2025 00:41:05 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Add the ability to limit the amount of memory that can be
 allocated to backends.
To: Jim Nasby <jnasby@upgrade.com>, James Hunter <james.hunter.pg@gmail.com>
Cc: Jeremy Schneider <schneider@ardentperf.com>,
 "Anton A. Melnikov" <a.melnikov@postgrespro.ru>,
 Tomas Vondra <tomas.vondra@enterprisedb.com>,
 Andres Freund <andres@anarazel.de>,
 Andrei Lepikhov <a.lepikhov@postgrespro.ru>,
 Stephen Frost <sfrost@snowman.net>, reid.thompson@crunchydata.com,
 Arne Roland <A.Roland@index.de>,
 "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>,
 vignesh C <vignesh21@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>,
 Ibrar Ahmed <ibrar.ahmad@gmail.com>,
 "stephen.frost" <stephen.frost@crunchydata.com>
References: <7912c911af51d5cf28c611190bf3d463b9209343.camel@crunchydata.com>
 <ZTGoUt+pH8uPucfm@tamriel.snowman.net>
 <c6520d47-e584-4287-833c-82779cc166e0@postgrespro.ru>
 <ZTJ1BfHC99LSzoHw@tamriel.snowman.net>
 <8fcb4406-49f5-4069-b8e9-197a38004ddd@postgrespro.ru>
 <20231024024435.yaqrajcchcliwhjl@awork3.anarazel.de>
 <fbfad1e7-65ba-4202-95df-da9b8177d821@postgrespro.ru>
 <98646b96-6dcf-8d8a-3daf-837f25f8b1e3@enterprisedb.com>
 <a74b5a91-d7c1-4138-86df-371c5e2b2be3@postgrespro.ru>
 <1c5f1856-817d-45e5-8e1a-acd95c6dd335@enterprisedb.com>
 <CAJVSvF6NRsnAqOYSkveiBERU+QyHV1vBrjd0ORFD+kw2N5-rEQ@mail.gmail.com>
 <25e68736-00ff-4346-b432-4cda836743f3@vondra.me>
 <600384a7-09bc-41f8-a38c-2f3d2195054b@postgrespro.ru>
 <4806d917-c019-49c7-9182-1203129cd295@vondra.me>
 <20241228102645.228f544d@jeremy-ThinkPad-T430s>
 <8B21EC3F-B8A3-4FCE-94B1-0B5AA14BD2C8@upgrade.com>
 <CAJVSvF5RQ8TPsk4q5Cz5wfLT72ZAaAKjKo2bYv6PbnJW7P9ZRQ@mail.gmail.com>
 <A0780603-344F-4974-BFA0-7C9FC76B2C7D@upgrade.com>
Content-Language: en-US
From: Tomas Vondra <tomas@vondra.me>
In-Reply-To: <A0780603-344F-4974-BFA0-7C9FC76B2C7D@upgrade.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/c5a8939a-5459-4072-a43a-ada2adaf30ba%40vondra.me>
Precedence: bulk

On 12/31/24 21:46, Jim Nasby wrote:
> On Dec 30, 2024, at 7:05=E2=80=AFPM, James Hunter <james.hunter.pg@gmai=
l.com> wrote:
>>
>> On Sat, Dec 28, 2024 at 11:24=E2=80=AFPM Jim Nasby <jnasby@upgrade.com=
> wrote:
>>>
>>> IMHO none of this will be very sane until we actually have cluster-le=
vel limits. One sudden burst in active connections and you still OOM the =
instance.
>>
>> Fwiw, PG does support "max_connections" GUC, so a backend/connection -=

>> level limit, times "max_connections", yields a cluster-level limit.
>=20
> max_connections is useless here, for two reasons:
>=20
> 1. Changing it requires a restart. That=E2=80=99s at *best* a real PITA=
 in production. [1]
> 2. It still doesn=E2=80=99t solve the actual problem. Unless your workl=
oad *and* your data are extremely homogeneous you can=E2=80=99t simply li=
mit the number of connections and call it a day. A slight change in incom=
ing queries, OR in the data that the queries are looking at and you go fr=
om running fine to meltdown. You don=E2=80=99t even need a plan flip for =
this to happen, just the same plan run at the same rate but now accessing=
 more data than before.
>=20

I really don't follow your argument ...

Yes, changing max_connections requires a restart - so what? AFAIK the
point James was making is that if you multiply max_connections by the
per-backend limit, you get a cluster-wide limit. And presumably the
per-backend limit would be a GUC not requiring a restart.

Yes, high values of max_connections are problematic. I don't see how a
global limit would fundamentally change that. In fact, it could
introduce yet more weird failures because some unrelated backend did
something weird.

FWIW I'm not opposed to having some global memory limit, but as I
explained earlier, I don't see a way to do that sensibly without having
a per-backend limit first. Because if you have a global limit, a single
backend consuming memory could cause all kinds of weird failures in
random other backends.

> Most of what I=E2=80=99ve seen on this thread is discussing ways to *op=
timize* how much memory the set of running backends can consume. Adjustin=
g how you slice the memory pie across backends, or even within a single b=
ackend, is optimization. While that=E2=80=99s a great goal that I do supp=
ort, it will never fully fix the problem. At some point you need to eithe=
r throw your hands in the air and start tossing memory errors, because yo=
u don=E2=80=99t have control over how much work is being thrown at the en=
gine. The only way that the engine can exert control over that would be t=
o hold new transactions from starting when the system is under duress (ie=
, workload management). While workload managers can be quite sophisticate=
d (aka, complex), the nice thing about limiting this scope to work_mem, a=
nd only as a means to prevent complete overload, is that the problem beco=
mes a lot simpler since you=E2=80=99re only looking at one metric and not=
 trying to support any kind of priority system. The only fanciness I thin=
k an MVP would need is a GUC to control how long a transaction can sit wa=
iting before it throws an error. Frankly, that sounds a lot less complex =
and much easier for DBAs to adjust than trying to teach the planner how t=
o apportion out per-node work_mem limits.
>=20
> As I said, I=E2=80=99m not opposed to optimizations, I just think they=E2=
=80=99re very much cart-before-the-horse.
>=20

What optimization? I didn't notice anything like that. I don't see how
"adjusting how you slice the memory pie across backends" counts as an
optimization. I mean, that's exactly what a memory limit is meant to do.

Similarly, there was a proposal to do planning with work_mem, and then
go back and adjust the per-node limits to impose a global limit. That
does not seem like an optimization either ... (more an opposite of it).

> 1: While it=E2=80=99d be a lot of work to make max_connections dynamic =
one thing we could do fairly easily would be to introduce another GUC (ma=
x_backends?) that actually controls the total number of allowed backends =
for everything. The sum of max_backends + autovac workers + background wo=
rkers + whatever else I=E2=80=99m forgetting would have to be less than t=
hat. The idea here is that you=E2=80=99d normally run with max_connection=
s set significantly lower than max_backends. That means that if you need =
to adjust any of these GUCs (other than max_backends) you don=E2=80=99t n=
eed to restart - the new limits would just apply to new connection reques=
ts.

I don't quite understad how max_backends helps with anything except
allowing to change the limit of connections without a restart, or why
would it be needed for introducing a memory limit. To me those seem very
much like two separate features.

regards

--=20
Tomas Vondra