Re: Further pg_upgrade analysis for many tables

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.

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.

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.

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?

Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de