Content-Type: multipart/alternative;
 boundary="------------doRhQwVbPvRXZDPJxhNyaTVf"
Message-ID: <866ba95f-86fb-fa9b-ecee-65281160cff9@rechte.fr>
Date: Thu, 3 Mar 2022 09:59:03 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.6.1
Content-Language: fr-FR
To: pgsql-performance@lists.postgresql.org
From: =?UTF-8?Q?Marc_Recht=c3=a9?= <marc4@rechte.fr>
Subject: OOM killer while pg_restore
Archived-At: 
 <https://www.postgresql.org/message-id/866ba95f-86fb-fa9b-ecee-65281160cff9%40rechte.fr>
Precedence: bulk

This is a multi-part message in MIME format.
--------------doRhQwVbPvRXZDPJxhNyaTVf
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------doRhQwVbPvRXZDPJxhNyaTVf
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><font size="4"><font face="monospace">Hello,<br>
          <br>
          We have a pg_restore which fails due to RAM over-consumption
          of the corresponding PG backend, which ends-up with OOM
          killer.<br>
          <br>
          The table has one PK, one index, and 3 FK constraints, active
          while restoring.<br>
          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.<br>
          <br>
          While importing, one can see the RSS + swap increasing
          linearly for the backend (executing the COPY)<br>
          <br>
          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)<br>
          <br>
          If we do the same test, suppressing firstly the 5 constraints
          on the table, the restore takes less than 15 minutes !<br>
          <br>
          This was tested on both PG 14.2 and PG 13.6 (linux 64-bit
          machines).<br>
          <br>
          It there a memory leak or that is normal that a bacend process
          may exhaust the RAM to such an extent ?<br>
          <br>
          Thanks<br>
          <br>
          Regards</font></font><br>
    </p>
  </body>
</html>

--------------doRhQwVbPvRXZDPJxhNyaTVf--