pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephen Frost <sfrost@snowman.net>
To: Joe Conway <mail@joeconway.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Francisco Olarte <folarte@peoplecall.com>
Cc: tom@quist.de
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17793: Query with large number of joins crashes PostgreSQL
Date: Tue, 14 Feb 2023 21:00:32 -0500
Message-ID: <Y+w8wDqJaaAZrB3E@tamriel.snowman.net> (raw)
In-Reply-To: <1975c07f-82a0-3503-e221-211b43878467@joeconway.com>
References: <17793-21fa598adbc21118@postgresql.org>
	<CA+bJJbzUKMzkYjGxbQpaRo_evErh+XsnMUUJC+q+edGCM3un8A@mail.gmail.com>
	<1682145.1676386040@sss.pgh.pa.us>
	<1975c07f-82a0-3503-e221-211b43878467@joeconway.com>

Greetings,

* Joe Conway (mail@joeconway.com) wrote:
> On 2/14/23 09:47, Tom Lane wrote:
> > Francisco Olarte <folarte@peoplecall.com> writes:
> > > On Tue, 14 Feb 2023 at 11:29, PG Bug reporting form
> > > <noreply@postgresql.org> wrote:
> > > > It looks like the OS is killing the process due to running OOM, which is not
> > > > very surprising when looking at the query. Is this expected, or should PG
> > > > have guards in place to prevent this from happening?
> > 
> > > When you run postgres in an environment where someone ( OOM killer )
> > > can K9 it, protection is hard. IIRC OOM can kill you because ANOTHER
> > > process touches memory, among other things.
> > 
> > Yeah.  We have no visibility into what the OOM killer will choose to
> > allow or not allow at any instant.
> > 
> > > ( I do run DBs in machines w/ overcommit disabled, this prevents it
> > > from happening, but it is not Pg who prevents it ).
> > 
> > If overcommit-disabled doesn't seem practical, another idea that's
> > been recommended is to start the postmaster under a "ulimit -v"
> > setting chosen to cause plain ENOMEM failures before the OOM
> > killer kicks in.

Unfortunately, that ends up being on a per-process basis and therefore
isn't as useful as we'd like it to be (ideally, we'd allow all of the
backends to allocate up until we hit some global "only use X amount of
memory" instead).

> Given:
> > It looks like the OS is killing the process due to running OOM ...
> > I could reproduce it using a fresh docker container of the image
> > "postgres:13.10".
> 
> I gather the OP is running Postgres in a container. If so, and if they have
> a memory.limit set (also assuming cgroup v1, otherwise memory.max for cgroup
> v2), disabling overcommit at the host level will not prevent an OOM kill
> when the cgroup exceeds memory.limit

Indeed, we need a way to allow users to specify a limit which PG keeps
track of and prevents PG from hitting the limit.  There's work being
done to both allow users to see how much memory each backend is using as
well as tracking an overall allocation amount in shared memory across
all processes, to allow us to decide to fail an allocation in a given
backend instead of having the kernel decide to run the OOM killer.

Further, once we're managing this, we can also make choices such as
"keep X amount reserved for system processes like autovacuum" and "don't
fail allocations for system processes like autovacuum" or such (just an
example, not saying we necessarily want to do exactly that, but the
point is that it's definitely nicer when we have control instead of
getting hit with a memory allocation failure or worse the OOM killer
deciding to kill -9 PG).

Thanks,

Stephen

Attachments:

  signature.asc (application/pgp-signature, 832B) download

view thread (6+ messages)

Message-ID: <Y+w8wDqJaaAZrB3E@tamriel.snowman.net>
Permalink:  ../Y+w8wDqJaaAZrB3E@tamriel.snowman.net/
Also on:    postgresql.org/message-id/Y+w8wDqJaaAZrB3E@tamriel.snowman.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: sfrost@snowman.net, mail@joeconway.com, tgl@sss.pgh.pa.us, folarte@peoplecall.com, tom@quist.de
  Subject: Re: BUG #17793: Query with large number of joins crashes PostgreSQL
  In-Reply-To: <Y+w8wDqJaaAZrB3E@tamriel.snowman.net>
  References: <17793-21fa598adbc21118@postgresql.org> <CA+bJJbzUKMzkYjGxbQpaRo_evErh+XsnMUUJC+q+edGCM3un8A@mail.gmail.com> <1682145.1676386040@sss.pgh.pa.us> <1975c07f-82a0-3503-e221-211b43878467@joeconway.com> <Y+w8wDqJaaAZrB3E@tamriel.snowman.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox