From: Justin Pryzby <pryzby@telsasoft.com>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: 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: Fri, 26 Jul 2024 15:36:20 -0500
Message-ID: <ZqQIxIsaPWO6coil@pryzbyj2023> (raw)
In-Reply-To: <CAPpHfduUSA7c0YrTxN443SwXXCL41vmXkavYJjN8GS3Wc5Z42w@mail.gmail.com>
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>
On Wed, Jul 24, 2024 at 09:17:51AM -0500, Justin Pryzby wrote:
> With partitioning, we have a lot of tables, some of them wide (126
> partitioned tables, 8942 childs, total 1019315 columns).
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 contexts 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 generate_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.
3114201 pryzbyj 20 0 5924520 4.2g 32476 T 0.0 53.8 0:27.35 postgres: pryzbyj postgres [local] UPDATE
The large context is:
2024-07-26 15:22:19.280 CDT [3114201] LOG: level: 1; CacheMemoryContext: 5211209088 total in 50067 blocks; 420688 free (14 chunks); 5210788400 used
Note that there seemed to be no issue when I created 999 tables without
partitioning:
SELECT format('CREATE TABLE t%s(LIKE p)', i,i,i+1) FROM generate_series(1,999)i;
--
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, aekorotkov@gmail.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: <ZqQIxIsaPWO6coil@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