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

help / color / mirror / Atom feed

From: Marc Rechté <marc4@rechte.fr>
To: pgsql-performance@lists.postgresql.org
Subject: OOM killer while pg_restore
Date: Thu, 3 Mar 2022 09:59:03 +0100
Message-ID: <866ba95f-86fb-fa9b-ecee-65281160cff9@rechte.fr> (raw)

Hello,

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.

While importing, one can see the RSS + swap increasing linearly for the 
backend (executing the COPY)

On my machine (quite old PC), it failed after 16 hours, while the disk 
usage was reaching 26 GB and memory usage was 9.1g (RSS+swap)

If we do the same test, suppressing firstly the 5 constraints on the 
table, the restore takes less than 15 minutes !

This was tested on both PG 14.2 and PG 13.6 (linux 64-bit machines).

It there a memory leak or that is normal that a bacend process may 
exhaust the RAM to such an extent ?

Thanks

Regards

view thread (10+ messages)  latest in thread

Message-ID: <866ba95f-86fb-fa9b-ecee-65281160cff9@rechte.fr>
Permalink:  ../866ba95f-86fb-fa9b-ecee-65281160cff9@rechte.fr/
Also on:    postgresql.org/message-id/866ba95f-86fb-fa9b-ecee-65281160cff9@rechte.fr

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: marc4@rechte.fr, pgsql-performance@lists.postgresql.org
  Subject: Re: OOM killer while pg_restore
  In-Reply-To: <866ba95f-86fb-fa9b-ecee-65281160cff9@rechte.fr>

* 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