> On 11/12/2023, 01:43, "Tom Lane" <t...@sss.pgh.pa.us > <mailto:t...@sss.pgh.pa.us>> wrote:
> I had initially supposed that in a parallel restore we could
> have child workers also commit after every N TOC items, but was
> soon disabused of that idea. After a worker processes a TOC
> item, any dependent items (such as index builds) might get
> dispatched to some other worker, which had better be able to
> see the results of the first worker's step. So at least in
> this implementation, we disable the multi-command-per-COMMIT
> behavior during the parallel part of the restore. Maybe that
> could be improved in future, but it seems like it'd add a
> lot more complexity, and it wouldn't make life any better for
> pg_upgrade (which doesn't use parallel pg_restore, and seems
> unlikely to want to in future).
I was not able to find email thread which details why we are not using
parallel pg_restore for pg_upgrade. IMHO most of the customer will have single
large
database, and not using parallel restore will cause slow pg_upgrade.
I am attaching a patch which enables parallel pg_restore for DATA and POST-DATA
part
of dump. It will push down --jobs value to pg_restore and will restore database
sequentially.
Benchmarks
{5 million LOs 1 large DB}
Patched {v9}
time pg_upgrade --old-bindir ~/15/bin --new-bindir ~/install/bin --old-datadir
~/upgrade/data/pub --new-datadir ~/data/sub --jobs=20
pg_upgrade --old-bindir ~/15/bin --new-bindir ~/install/bin --old-datadir
17.51s user 65.80s system 35% cpu 3:56.64 total
time pg_upgrade --old-bindir ~/15/bin --new-bindir ~/install/bin --old-datadir
~/upgrade/data/pub --new-datadir ~/data/sub -r
pg_upgrade --old-bindir ~/15/bin --new-bindir ~/install/bin --old-datadir
17.51s user 65.85s system 34% cpu 3:58.39 total
HEAD
time pg_upgrade --old-bindir ~/15/bin --new-bindir ~/install/bin --old-datadir
~/upgrade/data/pub --new-datadir ~/data/sub -r --jobs=20
pg_upgrade --old-bindir ~/15/bin --new-bindir ~/install/bin --old-datadir
53.95s user 82.44s system 41% cpu 5:25.23 total
time pg_upgrade --old-bindir ~/15/bin --new-bindir ~/install/bin --old-datadir
~/upgrade/data/pub --new-datadir ~/data/sub -r
pg_upgrade --old-bindir ~/15/bin --new-bindir ~/install/bin --old-datadir
54.94s user 81.26s system 41% cpu 5:24.86 total
Fix with --jobs propagation to pg_restore {on top of v9}
time pg_upgrade --old-bindir ~/15/bin --new-bindir ~/install/bin --old-datadir
~/upgrade/data/pub --new-datadir ~/data/sub -r --jobs=20
pg_upgrade --old-bindir ~/15/bin --new-bindir ~/install/bin --old-datadir
29.12s user 69.85s system 275% cpu 35.930 total
Although parallel restore does have small regression in ideal case of
pg_upgrade --jobs
Multiple DBs {4 DBs each having 2 million LOs}
Fix with --jobs scheduling
time pg_upgrade --old-bindir ~/15/bin --new-bindir ~/install/bin --old-datadir
~/upgrade/data/pub --new-datadir ~/data/sub -r --jobs=4
pg_upgrade --old-bindir ~/15/bin --new-bindir ~/install/bin --old-datadir
31.80s user 109.52s system 120% cpu 1:57.35 total
Patched {v9}
time pg_upgrade --old-bindir ~/15/bin --new-bindir ~/install/bin --old-datadir
~/upgrade/data/pub --new-datadir ~/data/sub -r --jobs=4
pg_upgrade --old-bindir ~/15/bin --new-bindir ~/install/bin --old-datadir
30.88s user 110.05s system 135% cpu 1:43.97 total
Regards
Sachin
v9-005-parallel_pg_restore.patch
Description: v9-005-parallel_pg_restore.patch
