Le 03/03/2022 à 16:31, Tom Lane a écrit :
=?UTF-8?Q?Marc_Recht=c3=a9?= <marc4@xxxxxxxxx> 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
Actually the number of rows is 232735712.
Accordingly the RAM consumption would be x12 x3 = 7.8 GiB.
This is close to the 8,1g I reported earlier (actually it was closer to
7.8 GB, due to GiB vs. GB confusion).
So there is no memory leak.
It took 16 hours on my box to reach that RAM consumption, and then the
COPY failed when checking the first FK (as the referenced table was empty).
I dropped the FK, index, and 3 FK constraints and started over the
pg_restore:
11 minutes to load the table (I did not have time to note RAM consumption)
I then created the PK and index:
24 minutes
For FK, I don't know because the referenced table are empty (but I'll be
able to test next week, if deemed necessary).
16 hours vs. 35 minutes to reach the same state.
This is the data structure:
=================
--
-- Name: simulations_ecarts_relatifs_saison; Type: TABLE; Schema:
donnees2019; Owner: drias; Tablespace:
--
CREATE TABLE simulations_ecarts_relatifs_saison (
idpoint integer NOT NULL,
annee integer NOT NULL,
saison integer NOT NULL,
idreferentiel integer NOT NULL,
ecartreltav real,
ecartreltnav real,
ecartreltxav real,
ecartreltrav real,
ecartreltxq90 real,
ecartreltxq10 real,
ecartreltnq10 real,
ecartreltnq90 real,
ecartreltxnd smallint,
ecartreltnnd smallint,
ecartreltnht smallint,
ecartreltxhwd smallint,
ecartreltncwd smallint,
ecartreltnfd smallint,
ecartreltxfd smallint,
ecartrelsd smallint,
ecartreltr smallint,
ecartrelhdd real,
ecartrelcdd real,
ecartrelpav real,
ecartrelpint real,
ecartrelrr real,
ecartrelpfl90 real,
ecartrelrr1mm real,
ecartrelpxcwd smallint,
ecartrelpn20mm smallint,
ecartrelpxcdd smallint,
ecartrelhusav real,
ecartreltx35 real,
ecartrelpq90 real,
ecartrelpq99 real,
ecartrelrr99 real,
ecartrelffav real,
ecartrelff3 real,
ecartrelffq98 real,
ecartrelff98 real
);
--
-- Name: pk_simulations_ecarts_relatifs_saison_2019; Type: CONSTRAINT;
Schema: donnees2019; Owner: drias; Tablespace:
--
ALTER TABLE ONLY simulations_ecarts_relatifs_saison
ADD CONSTRAINT pk_simulations_ecarts_relatifs_saison_2019 PRIMARY
KEY (idpoint, annee, saison, idreferentiel);
--
-- Name: i_expe_annee_saison_simulations_ecarts_relatifs_saison_2019;
Type: INDEX; Schema: donnees2019; Owner: drias; Tablespace:
--
CREATE INDEX i_expe_annee_saison_simulations_ecarts_relatifs_saison_2019
ON simulations_ecarts_relatifs_saison USING btree (idreferentiel, annee,
saison);
--
-- Name: fk_id_point_ecarts_relatifs_saison_2019; Type: FK CONSTRAINT;
Schema: donnees2019; Owner: drias
--
ALTER TABLE ONLY simulations_ecarts_relatifs_saison
ADD CONSTRAINT fk_id_point_ecarts_relatifs_saison_2019 FOREIGN KEY
(idpoint) REFERENCES grilles.points_grille(id);
--
-- Name: fk_id_referentiel_ecarts_relatifs_saison_2019; Type: FK
CONSTRAINT; Schema: donnees2019; Owner: drias
--
ALTER TABLE ONLY simulations_ecarts_relatifs_saison
ADD CONSTRAINT fk_id_referentiel_ecarts_relatifs_saison_2019
FOREIGN KEY (idreferentiel) REFERENCES
referentiel.referentiel_simulations(id);
--
-- Name: fk_saison_ecarts_relatifs_saison_2019; Type: FK CONSTRAINT;
Schema: donnees2019; Owner: drias
--
ALTER TABLE ONLY simulations_ecarts_relatifs_saison
ADD CONSTRAINT fk_saison_ecarts_relatifs_saison_2019 FOREIGN KEY
(saison) REFERENCES donnees.liste_saison(code_saison);
This is how is init / started the test instance:
=============================
$ initdb -D $MYDIR
$ pg_ctl -D $MYDIR -o "-p 5432 -c unix_socket_directories=. -c
shared_buffers=981MB -c work_mem=20MB -c maintenance_work_mem=98MB" start
