From: Justin Pryzby <pryzby@telsasoft.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Nathan Bossart <nathandbossart@gmail.com>
Cc: Michael Banck <mbanck@gmx.net>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: vignesh C <vignesh21@gmail.com>
Cc: Kumar, Sachin <ssetiya@amazon.com>
Cc: Robins Tharakan <tharakan@gmail.com>
Cc: Jan Wieck <jan@wi3ck.info>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: pg_upgrade failing for 200+ million Large Objects
Date: Wed, 24 Jul 2024 09:17:51 -0500
Message-ID: <ZqEND4ZcTDBmcv31@pryzbyj2023> (raw)
In-Reply-To: <1217588.1711999706@sss.pgh.pa.us>
References: <CALDaNm08DLqkr6LQfB=AJ-wEOGtASW+rYGsD6Q56-bYBYoHFdA@mail.gmail.com>
<842242.1706287466@sss.pgh.pa.us>
<!2751089.1710544721@sss.pgh.pa.us>
<4a3ebf7d81bfc6dd4d545e5b27d6e8f6c32d8937.camel@cybertec.at>
<3023817.1710629175@sss.pgh.pa.us>
<6603e4e0.500a0220.a557f.4f39@mx.google.com>
<3304322.1711551245@sss.pgh.pa.us>
<20240327150826.GB3994937@nathanxps13>
<20240401191930.GA2302032@nathanxps13>
<1217588.1711999706@sss.pgh.pa.us>
On Mon, Apr 01, 2024 at 03:28:26PM -0400, Tom Lane wrote:
> Nathan Bossart <nathandbossart@gmail.com> writes:
> > The one design point that worries me a little is the non-configurability of
> > --transaction-size in pg_upgrade. I think it's fine to default it to 1,000
> > or something, but given how often I've had to fiddle with
> > max_locks_per_transaction, I'm wondering if we might regret hard-coding it.
>
> Well, we could add a command-line switch to pg_upgrade, but I'm
> unconvinced that it'd be worth the trouble. I think a very large
> fraction of users invoke pg_upgrade by means of packager-supplied
> scripts that are unlikely to provide a way to pass through such
> a switch. I'm inclined to say let's leave it as-is until we get
> some actual field requests for a switch.
I've been importing our schemas and doing upgrade testing, and was
surprised when a postgres backend was killed for OOM during pg_upgrade:
Killed process 989302 (postgres) total-vm:5495648kB, anon-rss:5153292kB, ...
Upgrading from v16 => v16 doesn't use nearly as much RAM.
While tracking down the responsible commit, I reproduced the problem
using a subset of tables; at 959b38d770, the backend process used
~650 MB RAM, and at its parent commit used at most ~120 MB.
959b38d770b Invent --transaction-size option for pg_restore.
By changing RESTORE_TRANSACTION_SIZE to 100, backend RAM use goes to
180 MB during pg_upgrade, which is reasonable.
With partitioning, we have a lot of tables, some of them wide (126
partitioned tables, 8942 childs, total 1019315 columns). I didn't track
if certain parts of our schema contribute most to the high backend mem
use, just that it's now 5x (while testing a subset) to 50x higher.
We'd surely prefer that the transaction size be configurable.
--
Justin
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-hackers@postgresql.org
Cc: pryzby@telsasoft.com, tgl@sss.pgh.pa.us, nathandbossart@gmail.com, mbanck@gmx.net, laurenz.albe@cybertec.at, vignesh21@gmail.com, ssetiya@amazon.com, tharakan@gmail.com, jan@wi3ck.info, bruce@momjian.us, andrew@dunslane.net, magnus@hagander.net, peter.eisentraut@enterprisedb.com
Subject: Re: pg_upgrade failing for 200+ million Large Objects
In-Reply-To: <ZqEND4ZcTDBmcv31@pryzbyj2023>
* 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