Message-ID: <d9aeb423-b8da-4868-badb-9f361436ce22@vondra.me>
Date: Mon, 6 Jan 2025 22:54:26 +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>
Cc: James Hunter <james.hunter.pg@gmail.com>,
 Jeremy Schneider <schneider@ardentperf.com>,
 "Anton A. Melnikov" <a.melnikov@postgrespro.ru>,
 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>
 <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>
 <c5a8939a-5459-4072-a43a-ada2adaf30ba@vondra.me>
 <967BCFAB-D931-46DE-933B-5BD32064E49D@upgrade.com>
 <6be20166-734d-432f-a2d2-40f405517cd1@vondra.me>
 <7EF54040-C95E-425C-A3FA-A5A0BD60D122@upgrade.com>
Content-Language: en-US
From: Tomas Vondra <tomas@vondra.me>
In-Reply-To: <7EF54040-C95E-425C-A3FA-A5A0BD60D122@upgrade.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/d9aeb423-b8da-4868-badb-9f361436ce22%40vondra.me>
Precedence: bulk

On 1/6/25 22:07, Jim Nasby wrote:
> On Jan 2, 2025, at 9:21 PM, Tomas Vondra <tomas@vondra.me> wrote:
>>
>>> That said, I do think a workload manager would be more effective than
>>> trying to limit total connections.
>>
>> The "workload management" concept is so abstract I find it very
>> difficult to discuss without much more detail about how would it
>> actually work / be implemented.
>>
>> I believe implementing some rudimentary "global" memory accounting would
>> not be *that* hard (possibly along the lines of the patches early in
>> this thread), and adding some sort of dynamic connection limit would not
>> be much harder I think. But then comes the hard part of actually doing
>> the "workload management" part, which seems pretty comparable to what a
>> QoS / scheduler needs to do. With all the weird corner cases.
> 
> I’ve been saying “workload management” for lack of a better term, but my
> initial suggestion upthread was to simply stop allowing new transactions
> to start if global work_mem consumption exceeded some threshold. That’s
> simplistic enough that I wouldn’t really consider it “workload
> management”. Maybe “deferred execution” would be a better name. The only
> other thing it’d need is a timeout on how long a new transaction could
> sit in limbo.
> 

How would that not be a huge DoS vector? If a low-priority task can
allocate a chunk of memory, and block/delay execution of everything
else, that seems potentially disastrous.

I believe there is a reason why workload management systems tend to be
fairly complex systems, both to implement and manage, requiring stuff
like resource pools, workload classes, etc.

> I agree that no matter what everything being proposed would rely on
> having metrics on actual work_mem consumption. That would definitely be
> a good feature on its own. I’m thinking adding “work_mem_bytes” and
> “work_mem_operations” to pg_stat_activity (where “work_mem_operations”
> would tell you how many different things were using work_mem in the backend.
> 
> Incidentally, something related to this that I’ve seen is backend memory
> consumption slowly growing over time. Unbounded growth of relcache and
> friends was presumably the biggest contributor. There’s an argument to
> be made for a view dedicated to tracking per-backend memory stats, with
> additional info about things contributing to idle memory consumption.

True, but I don't quite see how would a global memory limit help with
any of that. In fact, relcache seems exactly like the thing to limit at
the backend level.

regards

-- 
Tomas Vondra