MIME-Version: 1.0
References: <!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>
 <ZqEND4ZcTDBmcv31@pryzbyj2023> <CAPpHfduUSA7c0YrTxN443SwXXCL41vmXkavYJjN8GS3Wc5Z42w@mail.gmail.com>
 <ZqQIxIsaPWO6coil@pryzbyj2023> <1870579.1722033430@sss.pgh.pa.us>
In-Reply-To: <1870579.1722033430@sss.pgh.pa.us>
From: Alexander Korotkov <aekorotkov@gmail.com>
Date: Sat, 27 Jul 2024 01:55:00 +0300
Message-ID: <CAPpHfdtpr2FYVYqmARZdSp=2zsndgJgmSsHFxFFNTiYRfX8uaA@mail.gmail.com>
Subject: Re: pg_upgrade failing for 200+ million Large Objects
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Justin Pryzby <pryzby@telsasoft.com>, Nathan Bossart <nathandbossart@gmail.com>, 
	Michael Banck <mbanck@gmx.net>, Laurenz Albe <laurenz.albe@cybertec.at>, 
	vignesh C <vignesh21@gmail.com>, "Kumar, Sachin" <ssetiya@amazon.com>, 
	Robins Tharakan <tharakan@gmail.com>, Jan Wieck <jan@wi3ck.info>, Bruce Momjian <bruce@momjian.us>, 
	Andrew Dunstan <andrew@dunslane.net>, Magnus Hagander <magnus@hagander.net>, 
	Peter Eisentraut <peter.eisentraut@enterprisedb.com>, pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAPpHfdtpr2FYVYqmARZdSp%3D2zsndgJgmSsHFxFFNTiYRfX8uaA%40mail.gmail.com>
Precedence: bulk

On Sat, Jul 27, 2024 at 1:37=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Justin Pryzby <pryzby@telsasoft.com> writes:
> > On Fri, Jul 26, 2024 at 10:53:30PM +0300, Alexander Korotkov wrote:
> >> It would be nice to identify such cases and check which memory context=
s are
> >> growing and why.
>
> > I reproduced the problem with this schema:
>
> > SELECT format('CREATE TABLE p(i int, %s) PARTITION BY RANGE(i)', array_=
to_string(a, ', ')) FROM (SELECT array_agg(format('i%s int', i))a FROM gene=
rate_series(1,999)i);
> > SELECT format('CREATE TABLE t%s PARTITION OF p FOR VALUES FROM (%s)TO(%=
s)', i,i,i+1) FROM generate_series(1,999)i;
>
> > This used over 4 GB of RAM.
>
> Interesting.  This doesn't bloat particularly much in a regular
> pg_restore, even with --transaction-size=3D1000; but it does in
> pg_upgrade, as you say.  I found that the bloat was occurring
> during these long sequences of UPDATE commands issued by pg_upgrade:
>
> -- For binary upgrade, recreate inherited column.
> UPDATE pg_catalog.pg_attribute
> SET attislocal =3D false
> WHERE attname =3D 'i'
>   AND attrelid =3D '\"public\".\"t139\"'::pg_catalog.regclass;
>
> -- For binary upgrade, recreate inherited column.
> UPDATE pg_catalog.pg_attribute
> SET attislocal =3D false
> WHERE attname =3D 'i1'
>   AND attrelid =3D '\"public\".\"t139\"'::pg_catalog.regclass;
>
> -- For binary upgrade, recreate inherited column.
> UPDATE pg_catalog.pg_attribute
> SET attislocal =3D false
> WHERE attname =3D 'i2'
>   AND attrelid =3D '\"public\".\"t139\"'::pg_catalog.regclass;
>
> I think the problem is basically that each one of these commands
> causes a relcache inval, for which we can't reclaim space right
> away, so that we end up consuming O(N^2) cache space for an
> N-column inherited table.

I was about to report the same.

> It's fairly easy to fix things so that this example doesn't cause
> that to happen: we just need to issue these updates as one command
> not N commands per table.  See attached.  However, I fear this should
> just be considered a draft, because the other code for binary upgrade
> in the immediate vicinity is just as aggressively stupid and
> unoptimized as this bit, and can probably also be driven to O(N^2)
> behavior with enough CHECK constraints etc.  We've gone out of our way
> to make ALTER TABLE capable of handling many updates to a table's DDL
> in one command, but whoever wrote this code appears not to have read
> that memo, or at least to have believed that performance of pg_upgrade
> isn't of concern.

I was thinking about counting actual number of queries, not TOC
entries for transaction number as a more universal solution.  But that
would require usage of psql_scan() or writing simpler alternative for
this particular purpose.  That looks quite annoying.  What do you
think?


------
Regards,
Alexander Korotkov
Supabase