On Wed, Nov 14, 2012 at 06:11:27AM +0200, Ants Aasma wrote:
> On Wed, Nov 14, 2012 at 2:03 AM, Bruce Momjian <bruce@momjian.us> wrote:
> > At 64k I see pg_upgrade taking 12% of the duration time, if I subtract
> > out the dump/restore times.
>
> My percentage numbers only included CPU time and I used SSD storage.
> For the most part there was no IO wait to speak of, but it's
> completely expected that thousands of link calls are not free.
Agreed. I was looking at wall clock time so I could see the total
impact of everything pg_upgrade does.
> >> 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.
>
> I used perf and Gprof2Dot for this. I will probably do a blog post on
> how to generate these graphs. It's much more useful for me than a
> plain flat profile as I don't know by heart which functions are called
> by which.
Yes, please share that information.
> >> 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.
>
> I agree that parallel restore for schemas is a hard problem. But I
> didn't mean parallelism within the restore, I meant that we could
> start both postmasters and pipe the output from dump directly to
> restore. This way the times for dumping and restoring can overlap.
Wow, that is a very creative idea. The current code doesn't do that,
but this has the potential of doubling pg_upgrade's speed, without
adding a lot of complexity. Here are the challenges of this approach:
* I would need to log the output of pg_dumpall as it is passed to psql
so users can debug problems
* pg_upgrade never runs the old and new clusters at the same time for
fear that it will run out of resources, e.g. shared memory, or if they
are using the same port number. We can make this optional and force
different port numbers.
Let me work up a prototype in the next few days and see how it performs.
Thanks for the great idea.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ It's impossible for everything to be true. +