Re: Further pg_upgrade analysis for many tables

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. +