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 1tTSRX-005qLl-6Q for pgsql-hackers@arkaria.postgresql.org; Thu, 02 Jan 2025 21:09:23 +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 1tTSRW-001paZ-6J for pgsql-hackers@arkaria.postgresql.org; Thu, 02 Jan 2025 21:09:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tTSRV-001pYy-JC for pgsql-hackers@lists.postgresql.org; Thu, 02 Jan 2025 21:09:21 +0000 Received: from mail-ot1-x32b.google.com ([2607:f8b0:4864:20::32b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tTSRS-001mKL-11 for pgsql-hackers@lists.postgresql.org; Thu, 02 Jan 2025 21:09:19 +0000 Received: by mail-ot1-x32b.google.com with SMTP id 46e09a7af769-71e327ffbafso1111171a34.2 for ; Thu, 02 Jan 2025 13:09:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=upgrade.com; s=google; t=1735852157; x=1736456957; 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=6b03fhi7A0MlZTHEh92hhopOTywNnpTZ6hAg/9J4Yfc=; b=HrLMNK1c4UyahULViEh/eHNQOUtUXA1//5KZ5IYhZVgjSyluMn2HDaZt7wfUqdx194 tZNs16qKIsYIz6D88JFGF4smxzyG8Foo8Z/K/vO9NEeRgddqEsq3euJRGCCL8mLYlBxL +LnbM+ZG81NHTJqkR/AUbudEVyELWRQFojSokv/aS/4ReESKKXhg/PYVJG24i29jCe/R ahMAQxYNuId/jjDoagjRetT574RwLG+/TL7YJpamO1HkSQ8mZvUcxwa3UhYdbDvTtdYG CNYMvAXbjYZAlQSXTZDN/W+zOsYE2/LdGe9/T6qEn2+EP0uDAdB7zx+PnBPVzbSZwSRp sDPg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1735852157; x=1736456957; 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=6b03fhi7A0MlZTHEh92hhopOTywNnpTZ6hAg/9J4Yfc=; b=HUEh/o8NhB/Bml9b42iJP3PNeMm4Qh7iHuEWWFfJNPpMHs/Q5rgIb18gE5l4k1PmVN 5F+YBWjPEyrOi46R9qOLfcl2hkzVasBbg0sMVC0rt8xRUdZHogWV7p8A3ylD/cfJ7aec JSy1hcqjKTY8B8IvlLgrU/HhG/Smqg2mPjY8LMdv8AGms63FRQuv9T53pqxcDiu5e1EE ITxLIvyeU++E+3uPWzkx+vrr9hPTq7o+DNy9Us7vwBxmFEaM3L+a+SN2Cxd70WOF598Y 4BkN478pduILjzlwlam45qm9K/307xYYZol7OXIczsqYj2Sly5XuiLT9H8B43iEGCvtn 8ygA== X-Forwarded-Encrypted: i=1; AJvYcCU3Ju2dDpTOD/kLtXmn85JIGNhwzR9vlSRWKsajb7rbC/hs6TUWPmlkET5i4HAACkmsQvcKJpchUWu6KHNL@lists.postgresql.org X-Gm-Message-State: AOJu0YwuyfM0JoB4aIMwFpDqCmi/Oyj30wFoag7H7pRpAmTq9i2oKjBb /AIhxkPp76fNCrny/kRbkxHjNJsEDf8CLq3kJlshpC4v+lPq60nx1afTIRDAmEU= X-Gm-Gg: ASbGncsVb06VT3qpz9Kmg3ENnN0mKxtOPeQXbxm3syPbpXiZ0S3jSA52Iv3zcRcyAbb cLFsyL/aODDbAHh62LeR5OQEwFjW6nHo4lxvA1jkuyiXnE8WUSLAxSKzR6dbRYhINBvaiK/4xc4 HcGthjun0rEqhKOlM23faAUG4fMVEzm8BuMv1PkqOa4oGakFnto403WxueN6N492Bkk9hoHPsSP pQUAXJYNv9UFUAz1uR+8R0mLsAZJ2eTo9eiSKsadAl2DttDA+aOmbVxT1sOm53FnpBwWNnRrczw bnxQ9Pk4mDAU393GR7vup1bKynQ/qmtIe8I= X-Google-Smtp-Source: AGHT+IG/hJYqMviM2bzmtWsR1I0ba3wlHODsnPPUmuKSfpoHmMYu75j4IucGs+OCgHRh0YRaGmxQPw== X-Received: by 2002:a05:6870:9108:b0:29e:27b6:be9b with SMTP id 586e51a60fabf-2a7fb0b7639mr9357101fac.4.1735852157522; Thu, 02 Jan 2025 13:09:17 -0800 (PST) Received: from smtpclient.apple (syn-070-113-014-216.res.spectrum.com. [70.113.14.216]) by smtp.gmail.com with ESMTPSA id 006d021491bc7-5f4db36fe7csm7288716eaf.1.2025.01.02.13.09.15 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 02 Jan 2025 13:09:17 -0800 (PST) From: Jim Nasby Message-Id: <967BCFAB-D931-46DE-933B-5BD32064E49D@upgrade.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_C4FF69E2-424A-4042-8DE5-691437F54393" 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. Date: Thu, 2 Jan 2025 15:09:04 -0600 In-Reply-To: Cc: James Hunter , Jeremy Schneider , "Anton A. Melnikov" , Tomas Vondra , Andres Freund , Andrei Lepikhov , Stephen Frost , reid.thompson@crunchydata.com, Arne Roland , "pgsql-hackers@lists.postgresql.org" , vignesh C , Justin Pryzby , Ibrar Ahmed , "stephen.frost" To: Tomas Vondra References: <7912c911af51d5cf28c611190bf3d463b9209343.camel@crunchydata.com> <8fcb4406-49f5-4069-b8e9-197a38004ddd@postgrespro.ru> <20231024024435.yaqrajcchcliwhjl@awork3.anarazel.de> <98646b96-6dcf-8d8a-3daf-837f25f8b1e3@enterprisedb.com> <1c5f1856-817d-45e5-8e1a-acd95c6dd335@enterprisedb.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> X-Mailer: Apple Mail (2.3826.300.87.4.3) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_C4FF69E2-424A-4042-8DE5-691437F54393 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Dec 31, 2024, at 5:41=E2=80=AFPM, Tomas Vondra = wrote: >=20 > On 12/31/24 21:46, Jim Nasby wrote: >> On Dec 30, 2024, at 7:05=E2=80=AFPM, James Hunter = wrote: >>>=20 >>> On Sat, Dec 28, 2024 at 11:24=E2=80=AFPM Jim Nasby = 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. >>=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 = 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. >>=20 >=20 > I really don't follow your argument ... >=20 > 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. >=20 > 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. That=E2=80=99s basically my argument for having workload management. If = a system becomes loaded enough for the global limit to start kicking in = it=E2=80=99s likely that query response time is increasing, which means = you will soon have more and more active backends trying to run queries. = That=E2=80=99s just going to make the situation even worse. You=E2=80=99d = either have to start trying to =E2=80=9Ctake memory away=E2=80=9D from = already running backends or backends that are just starting would have = such a low limit as to cause them to spill very quickly, creating = further load on the system. > 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. I agree, but I=E2=80=99m also not sure how much a per-backend limit = would actually help on its own, especially in OLTP environments. >> 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. >>=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 >=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. >=20 > 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). It=E2=80=99s optimization in that you=E2=80=99re trying to increase how = many active backends you can have before getting memory errors. It=E2=80=99= s an alternative to throwing more memory at the problem or limiting the = rate of incoming workload. >> 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. >=20 > 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. It=E2=80=99s related to this because the number of active backends is = directly related to memory consumption. Yet because max_connections = requires a restart it=E2=80=99s very hard to actually manage how many = active backends you have. Your only option is a single-point connection = pool, but that introduces its own problems. That said, I do think a workload manager would be more effective than = trying to limit total connections.= --Apple-Mail=_C4FF69E2-424A-4042-8DE5-691437F54393 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
On Dec 31, = 2024, at 5:41=E2=80=AFPM, Tomas Vondra <tomas@vondra.me> = wrote:

On 12/31/24 21:46, Jim = Nasby wrote:
On Dec 30, 2024, at 7:05=E2=80=AFPM, James = Hunter <james.hunter.pg@gmail.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-level = 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.

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.


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.

That=E2=80=99s basically = my argument for having workload management. If a system becomes loaded = enough for the global limit to start kicking in it=E2=80=99s likely that = query response time is increasing, which means you will soon have more = and more active backends trying to run queries. That=E2=80=99s just = going to make the situation even worse. You=E2=80=99d either have to = start trying to =E2=80=9Ctake memory away=E2=80=9D from already running = backends or backends that are just starting would have such a low limit = as to cause them to spill very quickly, creating further load on the = system.

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.

I agree, = but I=E2=80=99m also not sure how much a per-backend limit would = actually help on its own, especially in OLTP = environments.

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.


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

It=E2=80=99s = optimization in that you=E2=80=99re trying to increase how many active = backends you can have before getting memory errors. It=E2=80=99s an = alternative to throwing more memory at the problem or limiting the rate = of incoming workload.

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.

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.

It=E2=80=99s related to this = because the number of active backends is directly related to memory = consumption. Yet because max_connections requires a restart it=E2=80=99s = very hard to actually manage how many active backends you have. Your = only option is a single-point connection pool, but that introduces its = own problems.

That said, I do think a workload = manager would be more effective than trying to limit total = connections.
= --Apple-Mail=_C4FF69E2-424A-4042-8DE5-691437F54393--