Re: BUG #15660: pg_dump memory leaks when dumping LOBs

Hi,

Am Donnerstag, dem 28.02.2019 um 12:30 -0500 schrieb Tom Lane:
> Yeah.  We've heard about this repeatedly since large objects were
> promoted to be full-fledged objects with individual owners and ACL
> info.  It was argued at the time that large objects would be, well,
> large, and therefore there wouldn't be *that* many of them, and so
> it'd be okay for pg_dump to treat them as full-fledged objects with
> their own dump-table-of-contents entries.  Evidently, not everybody
> uses them that way :-(.

To give another data point, we also encountered such a problem at a
customer recently.

They have around 70 tables and a database size of 24 GB, but over 20
million rows in pg_largeobject. The directory dump they are using on an
XFS file system has 14,6 million files in it, leading to a size of the
directory inode of over 450 MB:

|drwx------+ 2 postgres postgres 454M 14. Sep 07:10 xxxxxxxxxtst-20220913

The TOC file is larger than 800 MB and has ca. 14,6 million lines, all
but around 700 of which are BLOB entries.

Dumping with pg_dump -d -j2 leads to frequent out of memory situations
on this node due to the large amount of memory pg_dump needs (and also
because their TSM client apparently also cannot cope with so many files
and takes up 20% of the RAM as well):

|postgres 43844 73.9 8.8 11711000 11680472 ? R 00:27 297:00 /srv/[...]/bin/pg_dump -Fd -b -f [...]/xxxxxxxxxtst-20220913 -j 2 -Z 1 XXXXXXXXXTST
|postgres 43832 0.0 8.8 11711000 11680236 ? S 00:27 0:03 /srv/[...]/bin/pg_dump -Fd -b -f [...]/xxxxxxxxxtst-20220913 -j 2 -Z 1 XXXXXXXXXTST
|postgres 38251 0.3 8.8 11710680 11683100 ? S 00:21 1:22 /srv/[...]/bin/pg_dump -Fd -b -f [...]/xxxxxxxxxtst-20220913 -j 2 -Z 1 XXXXXXXXXTST

> At some point I think we're going to have to revisit that decision in
> pg_dump. Maybe we could merge things so that we have one TOC entry
> for all blobs that share the same owner and ACL? But it comes up
> seldom enough that nobody's gotten motivated to do the work.

Yeah, it would be good if something could be done about this.

> In the short run, the only answer is to run pg_dump on beefier iron,
> or else split up the dump as Haribabu suggests (although TBH I'm
> afraid that might not work either --- if memory serves, pg_dump tends
> to pull in all the per-object info even if it's not going to dump it
> all ...)

In this case, we will try to advise the client to not use large objects
if possible (this is only a test instance so far).

Michael

--
Michael Banck
Teamleiter PostgreSQL-Team
Projektleiter
Tel.: +49 2166 9901-171
E-Mail: michael(dot)banck(at)credativ(dot)de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Geoff Richardson, Peter Lilley

Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz