Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.300.87.4.3\))
Subject: Re: Add the ability to limit the amount of memory that can be
 allocated to backends.
From: Jim Nasby <jnasby@upgrade.com>
In-Reply-To: <CAJVSvF5RQ8TPsk4q5Cz5wfLT72ZAaAKjKo2bYv6PbnJW7P9ZRQ@mail.gmail.com>
Date: Tue, 31 Dec 2024 14:46:58 -0600
Cc: Jeremy Schneider <schneider@ardentperf.com>,
 Tomas Vondra <tomas@vondra.me>,
 "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>
Content-Transfer-Encoding: quoted-printable
Message-Id: <A0780603-344F-4974-BFA0-7C9FC76B2C7D@upgrade.com>
References: <7912c911af51d5cf28c611190bf3d463b9209343.camel@crunchydata.com>
 <f62b4107-227d-49e7-a9be-104d1ceb03d9@postgrespro.ru>
 <268e0ac7-8a81-4d65-8b40-b62c4b3f1bf9@postgrespro.ru>
 <ZTArWsctGn5fEVPR@tamriel.snowman.net>
 <48548d40-634b-4943-a737-3c9d95eacf06@postgrespro.ru>
 <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>
To: James Hunter <james.hunter.pg@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/A0780603-344F-4974-BFA0-7C9FC76B2C7D%40upgrade.com>
Precedence: bulk

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

max_connections is useless here, for two reasons:

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 =
workload *and* your data are extremely homogeneous you can=E2=80=99t =
simply limit the number of connections and call it a day. A slight =
change in incoming queries, OR in the data that the queries are looking =
at and you go from 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.

Most of what I=E2=80=99ve seen on this thread is discussing ways to =
*optimize* how much memory the set of running backends can consume. =
Adjusting how you slice the memory pie across backends, or even within a =
single backend, is optimization. While that=E2=80=99s a great goal that =
I do support, it will never fully fix the problem. At some point you =
need to either throw your hands in the air and start tossing memory =
errors, because you don=E2=80=99t have control over how much work is =
being thrown at the engine. The only way that the engine can exert =
control over that would be to hold new transactions from starting when =
the system is under duress (ie, workload management). While workload =
managers can be quite sophisticated (aka, complex), the nice thing about =
limiting this scope to work_mem, and only as a means to prevent complete =
overload, is that the problem becomes 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 think an MVP would need is a GUC =
to control how long a transaction can sit waiting 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 to apportion out per-node =
work_mem limits.

As I said, I=E2=80=99m not opposed to optimizations, I just think =
they=E2=80=99re very much cart-before-the-horse.

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 =
(max_backends?) that actually controls the total number of allowed =
backends for everything. The sum of max_backends + autovac workers + =
background workers + whatever else I=E2=80=99m forgetting would have to =
be less than that. The idea here is that you=E2=80=99d normally run with =
max_connections 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 need to restart - the new limits would just apply to =
new connection requests.=