Re: Further pg_upgrade analysis for many tables

Patch applied to git head. Thanks Ants Aasma for the analysis that lead
to the patch.

---------------------------------------------------------------------------

On Tue, Nov 13, 2012 at 07:03:51PM -0500, Bruce Momjian wrote:
> On Tue, Nov 13, 2012 at 05:44:54AM +0200, Ants Aasma wrote:
> > On Mon, Nov 12, 2012 at 10:59 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > > You can see a significant speedup with those loops removed. The 16k
> > > case is improved, but still not linear. The 16k dump/restore scale
> > > looks fine, so it must be something in pg_upgrade, or in the kernel.
> >
> > I can confirm the speedup. Profiling results for 9.3 to 9.3 upgrade
> > for 8k and 64k tables are attached. pg_upgrade itself is now taking
> > negligible time.
>
> I generated these timings from the attached test script.
>
> -------------------------- 9.3 ------------------------
> ---- normal ---- -- binary_upgrade -- -- pg_upgrade -
> - dmp - - res - - dmp - - res - git patch
> 1 0.12 0.07 0.13 0.07 11.06 11.02
> 1000 2.20 2.46 3.57 2.82 19.15 18.61
> 2000 4.51 5.01 8.22 5.80 29.12 26.89
> 4000 8.97 10.88 14.76 12.43 45.87 43.08
> 8000 15.30 24.72 30.57 27.10 100.31 79.75
> 16000 36.14 54.88 62.27 61.69 248.03 167.94
> 32000 55.29 162.20 115.16 179.15 695.05 376.84
> 64000 149.86 716.46 265.77 724.32 2323.73 1122.38
>
> You can see the speedup of the patch, particularly for a greater number
> of tables, e.g. 2x faster for 64k tables.
>
> > The 64k profile shows the AtEOXact_RelationCache scaling problem. For
> > the 8k profile nothing really pops out as a clear bottleneck. CPU time
> > distributes 83.1% to postgres, 4.9% to pg_dump, 7.4% to psql and 0.7%
> > to pg_upgrade.
>
> At 64k I see pg_upgrade taking 12% of the duration time, if I subtract
> out the dump/restore times.
>
> I am attaching an updated pg_upgrade patch, which I believe is ready for
> application for 9.3.
>
> > Postgres time itself breaks down with 10% for shutdown checkpoint and
> > 90% for regular running, consisting of 16% parsing, 13% analyze, 20%
> > plan, 30% execute, 11% commit (AtEOXact_RelationCache) and 6% network.
>
> That SVG graph was quite impressive.
>
> > It looks to me that most benefit could be had from introducing more
> > parallelism. Are there any large roadblocks to pipelining the dump and
> > restore to have them happen in parallel?
>
> I talked to Andrew Dustan about parallelization in pg_restore. First,
> we currently use pg_dumpall, which isn't in the custom format required
> for parallel restore, but if we changed to custom format, create table
> isn't done in parallel, only create index/check constraints, and trigger
> creation, etc. Not sure if it worth perusing this just for pg_upgrade.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +