Re: OOM killer while pg_restore

Em qui., 3 de mar. de 2022 às 09:19, Marc Rechté <marc4(at)rechte(dot)fr> escreveu:

> Em qui., 3 de mar. de 2022 às 05:59, Marc Rechté <marc4(at)rechte(dot)fr>
> escreveu:
> >
> > 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 ?
> >
> > Hi Marc,
> > Can you post the server logs?
> >
> > regards,
> > Ranier Vilela
>
> Will it help ?
>
Show some direction.

> 2022-02-25 12:01:29.306 GMT [1468:24] user=,db=,app=,client= LOG:
> server process (PID 358995) was terminated by signal 9: Killed
> 2022-02-25 12:01:29.306 GMT [1468:25] user=,db=,app=,client= DETAIL:
> Failed process was running: COPY simulations_ecarts_relatifs_saison
> (idpoint, annee, saison, idreferentiel, ecartreltav, ecartreltnav,
> ecartreltxav, ecartreltrav, ecartreltxq90, ecartreltxq10, ecartreltnq10,
> ecartreltnq90, ecartreltxnd, ecartreltnnd, ecartreltnht, ecartreltxhwd,
> ecartreltncwd, ecartreltnfd, ecartreltxfd, ecartrelsd, ecartreltr,
> ecartrelhdd, ecartrelcdd, ecartrelpav, ecartrelpint, ecartrelrr,
> ecartrelpfl90, ecartrelrr1mm, ecartrelpxcwd, ecartrelpn20mm,
> ecartrelpxcdd, ecartrelhusav, ecartreltx35, ecartrelpq90, ecartrelpq99,
> ecartrelrr99, ecartrelffav, ecartrelff3, ecartrelffq98, ecartrelff98)
> FROM stdin;
>
COPY leak?

regards,
Ranier Vilela