pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marc Rechté <marc4@rechte.fr>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: OOM killer while pg_restore
Date: Thu, 03 Mar 2022 10:31:59 -0500
Message-ID: <3161846.1646321519@sss.pgh.pa.us> (raw)
In-Reply-To: <866ba95f-86fb-fa9b-ecee-65281160cff9@rechte.fr>
References: <866ba95f-86fb-fa9b-ecee-65281160cff9@rechte.fr>

=?UTF-8?Q?Marc_Recht=c3=a9?= <marc4@rechte.fr> writes:
> We have a pg_restore which fails due to RAM over-consumption of the 
> corresponding PG backend, which ends-up with OOM killer.
> The table has one PK, one index, and 3 FK constraints, active while 
> restoring.
> The dump contains over 200M rows for that table and is in custom format, 
> which corresponds to 37 GB of total relation size in the original DB.

The FKs would result in queueing row trigger events, which would occupy
some memory.  But those should only need ~12 bytes per FK per row,
which works out to less than 10GB for this number of rows, so it may
be that you've hit something else that we would consider a leak.

Does memory consumption hold steady if you drop the FK constraints?

If not, as others have noted, we'd need more info to investigate
this.  The leak is probably independent of the specific data in
the table, so maybe you could make a small self-contained example
using a script to generate dummy data.

			regards, tom lane

view thread (10+ messages)  latest in thread

Message-ID: <3161846.1646321519@sss.pgh.pa.us>
Permalink:  ../3161846.1646321519@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3161846.1646321519@sss.pgh.pa.us

copy link · copy postgr.es

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, marc4@rechte.fr, pgsql-performance@lists.postgresql.org
  Subject: Re: OOM killer while pg_restore
  In-Reply-To: <3161846.1646321519@sss.pgh.pa.us>

* 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