Re: Further pg_upgrade analysis for many tables - Mailing list pgsql-hackers

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@momjian.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@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +