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 1tRnfn-00BlHW-A4 for pgsql-hackers@arkaria.postgresql.org; Sun, 29 Dec 2024 07:25:15 +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 1tRnfl-004qVk-Eb for pgsql-hackers@arkaria.postgresql.org; Sun, 29 Dec 2024 07:25:13 +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 1tRnfl-004qVb-1T for pgsql-hackers@lists.postgresql.org; Sun, 29 Dec 2024 07:25:12 +0000 Received: from mail-ot1-x336.google.com ([2607:f8b0:4864:20::336]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tRnfY-0029g4-ND for pgsql-hackers@lists.postgresql.org; Sun, 29 Dec 2024 07:25:10 +0000 Received: by mail-ot1-x336.google.com with SMTP id 46e09a7af769-71e29a0dfd6so985148a34.1 for ; Sat, 28 Dec 2024 23:25:00 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=upgrade.com; s=google; t=1735457098; x=1736061898; 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=bVWjlx3WRTpaHwiUy26QkfFMPUlCwpglfH03JEKFmzY=; b=bqekMykYUu/eIKUChOA/DSsDySH1xr0g0Ftd8Vxc0QWK0QfuNNpvNcO+SmaYHo3PyB 8dN+/WxBBe5K8LMRFCjjzFYbFVOXFqen01ATNOgDtsEmYw2GaarrczJcOsS6PGmEQ8sr dC/qsZ5IJWkOGLSmsqxpiWGR+3PyELb70r/hpY7rQTcobGK0QCWJOh/s5BTSInocKY1v 1syciUPDxD98CVT0DauzW3LfSDkIKsTI/PTsPSmXHs79LuRzhes5DT7kFzsFgJWmTVIN Od/bF/Gpjj9zQr+u9Lv3NzVwWT/TAmiHc77erUbTZUOvkL+IKWHTZrEWGztqyY+3fKX/ jEFw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1735457098; x=1736061898; 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=bVWjlx3WRTpaHwiUy26QkfFMPUlCwpglfH03JEKFmzY=; b=TQahyIClcIcAxKR6VUwo1wZJy8BFN/8noc3CsubqKS5GOmWD+jfI/Vnf+a61EA5fSY 0UhP0ddNBUxpOdaVvW1SxeIDWTyn6ws9GHTbm9bvHziHfWcVxanpsoPldIFBbIC+ayve m8LKnbSvuCLp3zXR6ZQT/wAaQCW4/HMePDN4f4337vLf1INKGD/X50HAQS2PLlVZCnXw 5oVkpPimyuuBeSeMLSMirfoc5JWxH+a1i9vTMGtkEbFZFtJGT18RZZ+kEm3JYXCTUqzn pVZAJAiE16n9Sj+QLWcI4rbpTKgbp4k1B4gsAdMrDFRNmH7UeK8nXyRedpRSIXt+G2G3 vkWA== X-Forwarded-Encrypted: i=1; AJvYcCXcuWf1aizmMDcV/KmNleodLSAddOrblad6E7wJSYpHibgxoBe2ExDI2G3qd6+n58lwTnDrjN7BPLfW1svh@lists.postgresql.org X-Gm-Message-State: AOJu0YzTx/cUzVgOY1jP9Ns7xnJS3Ifh4IpXeNA0DTw5OykJSH+okPlg aRDoJwu63KnNRSlR2c7YvN8VzuTWrMnPH7LEKaQcAFnvJDApi4LD+vi+mDPzBjU= X-Gm-Gg: ASbGncuQSOLqPyaOyTEnEVoGfTs7Q60OCsiFGxJNPYb1jgGXCStET+Dg/+Su/al2YbC oeVV94V6IXlJOTWYi3bu0v7nOvd27h7fhzYIXEbpiBcHWa3AYSPMjgps1PYAYe0vcYVXEhIkurV hAkkBVMr6j1YlmmXtz/9VfjW/8O18cFyAbwaQ4aZoW0LQYSQphI0KGiIPzeApPVH5Tq6NXzAjqK ZI2r4jp0juVDY+92z4N0L9DWyV+cF+++iEK8+OE1hAkG3F3Vu62DJ689a+cm8xVXO7QA3dWcutp bQc4II46n2jAnEarl8mSmjNSkIqGd50l+Ne7fTrU X-Google-Smtp-Source: AGHT+IHf3pDsZLzesYq9QahTTeP+AII8caWMDdzGvEcFHOzRePccHJjg71Mqfe7YWQmGnxRiFCpQGA== X-Received: by 2002:a05:6830:264d:b0:71e:1849:5ed9 with SMTP id 46e09a7af769-720ff6723bemr6810893a34.2.1735457098590; Sat, 28 Dec 2024 23:24:58 -0800 (PST) Received: from smtpclient.apple (216-188-247-105.dyn.grandenetworks.net. [216.188.247.105]) by smtp.gmail.com with ESMTPSA id 46e09a7af769-71fc97e2649sm5321977a34.38.2024.12.28.23.24.57 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sat, 28 Dec 2024 23:24:58 -0800 (PST) From: Jim Nasby Message-Id: <8B21EC3F-B8A3-4FCE-94B1-0B5AA14BD2C8@upgrade.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_F653330F-D03F-4FCC-B31E-3D7C96863452" 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: Sun, 29 Dec 2024 01:24:47 -0600 In-Reply-To: <20241228102645.228f544d@jeremy-ThinkPad-T430s> Cc: Tomas Vondra , "Anton A. Melnikov" , James Hunter , 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: Jeremy Schneider References: <7912c911af51d5cf28c611190bf3d463b9209343.camel@crunchydata.com> <268e0ac7-8a81-4d65-8b40-b62c4b3f1bf9@postgrespro.ru> <48548d40-634b-4943-a737-3c9d95eacf06@postgrespro.ru> <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> 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=_F653330F-D03F-4FCC-B31E-3D7C96863452 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 On Dec 28, 2024, at 12:26=E2=80=AFPM, Jeremy Schneider = wrote: >=20 > While I don't have a detailed design in mind, I'd like to add a strong > +1 on the general idea that work_mem is hard to effectively use = because > queries can vary so widely in how many nodes might need work memory. >=20 > I'd almost like to have two limits: >=20 > First, a hard per-connection limit which could be set very high - we > can track total memory usage across contexts inside of palloc and = pfree > (and maybe this could also be exposed in pg_stat_activity for easy > visibility into a snapshot of memory use across all backends). If > palloc detects that an allocation would take the total over the hard > limit, then you just fail the palloc with an OOM. This protects > postgres from a memory leak in a single backend OOM'ing the whole > system and restarting the whole database; failing a single connection > is better than failing all of them. >=20 > Second, a soft per-connection "total_connection_work_mem_target" which > could be set lower. The planner can just look at the total number of > nodes that it expects to allocate work memory, divide the target by > this and then set the work_mem for that query. There should be a > reasonable floor (minimum) for work_mem - maybe the value of work_mem > itself becomes this and the new target doesn't do anything besides > increasing runtime work_mem. >=20 > Maybe even could do a "total_instance_work_mem_target" where it's > divided by the number of average active connections or something. 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. And while we could have such a mechanism do something clever = like dynamically lowering every sessions query_mem/work_mem/whatever, = ultimately I think it would also need the ability to deny or delay = sessions from starting new transactions.= --Apple-Mail=_F653330F-D03F-4FCC-B31E-3D7C96863452 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 On Dec 28, = 2024, at 12:26=E2=80=AFPM, Jeremy Schneider = <schneider@ardentperf.com> wrote:

While I don't have a = detailed design in mind, I'd like to add a strong
+1 on the general idea that work_mem is = hard to effectively use because
queries = can vary so widely in how many nodes might need work memory.

I'd almost like to have two = limits:

First, a hard = per-connection limit which could be set very high - we
can track total memory usage across = contexts inside of palloc and pfree
(and = maybe this could also be exposed in pg_stat_activity for easy
visibility into a snapshot of memory use = across all backends). If
palloc = detects that an allocation would take the total over the hard
limit, then you just fail the palloc with = an OOM. This protects
postgres from a memory leak in a single backend OOM'ing the = whole
system and restarting = the whole database; failing a single connection
is better than failing all of = them.

Second, a soft = per-connection "total_connection_work_mem_target" which
could be set lower. The planner can just = look at the total number of
nodes = that it expects to allocate work memory, divide the target by
this and then set the work_mem for that = query.  There should be a
reasonable floor (minimum) for work_mem - maybe the value = of work_mem
itself becomes this and = the new target doesn't do anything besides
increasing runtime work_mem.

Maybe = even could do a "total_instance_work_mem_target" where it's
divided by the number of average active = connections or something.

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. And = while we could have such a mechanism do something clever like = dynamically lowering every sessions query_mem/work_mem/whatever, = ultimately I think it would also need the ability to deny or delay = sessions from starting new transactions.
= --Apple-Mail=_F653330F-D03F-4FCC-B31E-3D7C96863452--