From: Pierre Ducroquet <p.psql@pinaraf.info>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: Inefficiency in parallel pg_restore with many tables
Date: Mon, 24 Jul 2023 19:27:36 +0200
Message-ID: <1903495.6tgchFWduM@peanuts2> (raw)
In-Reply-To: <3612876.1689443232@sss.pgh.pa.us>
References: <3612876.1689443232@sss.pgh.pa.us>
On Saturday, July 15, 2023 7:47:12 PM CEST Tom Lane wrote:
> I'm not sure how big a deal this is in practice: in most situations
> the individual jobs are larger than they are in this toy example,
> plus the initial non-parallelizable part of the restore is a bigger
> bottleneck anyway with this many tables. Still, we do have one
> real-world complaint, so maybe we should look into improving it.
Hi
For what it's worth, at my current job it's kind of a big deal. I was going to
start looking at the bad performance I got on pg_restore for some databases
with over 50k tables (in 200 namespaces) when I found this thread. The dump
weights in about 2,8GB, the toc.dat file is 230MB, 50 120 tables, 142 069
constraints and 73 669 indexes.
HEAD pg_restore duration: 30 minutes
pg_restore with latest patch from Nathan Bossart: 23 minutes
This is indeed better, but there is still a lot of room for improvements. With
such usecases, I was able to go much faster using the patched pg_restore with
a script that parallelize on each schema instead of relying on the choices
made by pg_restore. It seems the choice of parallelizing only the data loading
is losing nice speedup opportunities with a huge number of objects.
patched pg_restore + parallel restore of schemas: 10 minutes
Anyway, the patch works really fine as is, and I will certainly keep trying
future iterations.
Regards
Pierre
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: p.psql@pinaraf.info, pgsql-hackers@lists.postgresql.org
Subject: Re: Inefficiency in parallel pg_restore with many tables
In-Reply-To: <1903495.6tgchFWduM@peanuts2>
* 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