From: Nathan Bossart <nathandbossart@gmail.com>
To: Corey Huinker <corey.huinker@gmail.com>
Cc: Jeff Davis <pgsql@j-davis.com>
Cc: Robert Treat <rob@xzilla.net>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: jian he <jian.universality@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Peter Smith <smithpb2250@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: alvherre@alvh.no-ip.org
Subject: Re: Statistics Import and Export
Date: Sun, 16 Mar 2025 15:33:49 -0500
Message-ID: <Z9c1rbzZegYQTOQE@nathan> (raw)
In-Reply-To: <CADkLM=c+r05srPy9w+-+nbmLEo15dKXYQ03Q_xyK+riJerigLQ@mail.gmail.com>
References: <pbjbvxnq36xo2ooufcnlfqsqnxuvggbqxbx3myjtswokshfjbl@hfihx57z4j4t>
<CA+TgmoYGuEC=E4TbomRyqO8+Uav=90G0dYXw=rA1d5UmRB76Yg@mail.gmail.com>
<bd0513e4b1ea2b2f2d06f02720c6579711cb62a6.camel@j-davis.com>
<CABV9wwO5v8Nu8q+xWexMdL3Z+2xS=fFJMQetBSHy3tR64wNHOA@mail.gmail.com>
<bec4c2feb4ef51c129ee6e1912fffa911a3c7229.camel@j-davis.com>
<CABV9wwP_ajEbLhCzfCO2Sy0180AshYwQFViys=QhyZA7sW2_qw@mail.gmail.com>
<CADkLM=dvG_F2giHEAGJ=DGVGNmujUt0vAaXNSDkDXzbzcGQbxQ@mail.gmail.com>
<CAJSLCQ10++5GLeoRwNMQMKuaVrikEp2X6feBv=kYDtWmsVy-Pw@mail.gmail.com>
<d8a3beed2682fde95936ee779e411b907df1148a.camel@j-davis.com>
<CADkLM=c+r05srPy9w+-+nbmLEo15dKXYQ03Q_xyK+riJerigLQ@mail.gmail.com>
Thanks for working on this, Corey.
On Fri, Mar 14, 2025 at 04:03:16PM -0400, Corey Huinker wrote:
> 0003 -
>
> Storing the restore function calls in the archive entry hogged a lot of
> memory and made people nervous. This introduces a new function pointer that
> generates those restore SQL calls right before they're written to disk,
> thus reducing the memory load from "stats for every object to be dumped" to
> just one object. Thanks to Nathan for diagnosing some weird quirks with
> various formats.
>
> 0004 -
>
> This replaces the query in the prepared statement with one that batches
> them 100 relations at a time, and then maintains that result set until it
> is consumed. It seems to have obvious speedups.
I've been doing a variety of tests with my toy database of 100K relations
[0], and I'm seeing around 20% less memory usage. That's still 20% more
than without stats, but that's still a pretty nice improvement.
I'd propose two small changes to the design:
* I tested a variety of batch sizes, and to my suprise, I saw the best
results with around 64 relations per batch. I imagine the absolute best
batch size will vary greatly depending on the workload. It might also
depend on work_mem and friends.
* The custom format actually does two WriteToc() calls, and since these
patches move the queries to this part of pg_dump, it means we'll run all
the queries twice. The comments around this code suggest that the second
pass isn't strictly necessary and that it is really only useful for
data/parallel restore, so we could probably skip it for no-data dumps.
With those two changes, a pg_upgrade-style dump of my test database goes
from ~21.6 seconds without these patches to ~11.2 seconds with them. For
reference, the same dump without stats takes ~7 seconds on HEAD.
[0] https://postgr.es/m/Z9R9-mFbxukqKmg4%40nathan
--
nathan
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: nathandbossart@gmail.com, corey.huinker@gmail.com, pgsql@j-davis.com, rob@xzilla.net, robertmhaas@gmail.com, andres@anarazel.de, tgl@sss.pgh.pa.us, michael@paquier.xyz, jian.universality@gmail.com, bruce@momjian.us, boekewurm+postgres@gmail.com, magnus@hagander.net, sfrost@snowman.net, ashutosh.bapat.oss@gmail.com, smithpb2250@gmail.com, pgsql-hackers@lists.postgresql.org, alvherre@alvh.no-ip.org
Subject: Re: Statistics Import and Export
In-Reply-To: <Z9c1rbzZegYQTOQE@nathan>
* 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