Re: Further pg_upgrade analysis for many tables

On Sat, Nov 10, 2012 at 05:20:55PM -0500, Bruce Momjian wrote:
> On Fri, Nov 9, 2012 at 04:06:38PM -0800, Jeff Janes wrote:
> > On Thu, Nov 8, 2012 at 7:25 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > >
> > > I did some more research and realized that I was not using --schema-only
> > > like pg_upgrade uses. With that setting, things look like this:
> > >
> > ...
> >
> > For profiling pg_dump in isolation, you should also specify
> > --binary-upgrade. I was surprised that it makes a big difference,
> > slowing it down by about 2 fold.
>
> Yes, I see that now:
>
> pg_dump vs. pg_dump --binary-upgrade
> 9.2 w/ b-u git w/ b-u pg_upgrade
> 1 0.13 0.13 0.11 0.13 11.73
> 1000 4.37 8.18 3.98 8.08 28.79
> 2000 12.98 33.29 12.19 28.11 69.75
> 4000 47.85 140.62 50.14 138.02 289.82
> 8000 210.39 604.95 183.00 517.35 1168.60
> 16000 901.53 2373.79 769.83 1975.94 5022.82
>
> I didn't show the restore numbers yet because I haven't gotten automated
> pg_dump --binary-upgrade restore to work yet, but a normal restore for
> 16k takes 2197.56, so adding that to 1975.94, you get 4173.5, which is
> 83% of 5022.82. That is a big chunk of the total time for pg_upgrade.

What I am seeing here is the same 4x increase for a 2x increase in the
number of tables. Something must be going on there. I have oprofile
set up, so I will try to run oprofile and try to find which functions
are taking up most of the time, though I am confused why Tom didn't see
any obvious causes. I will keep going, and will focus on git head, and
schema-only, non-binary-upgrade mode, for simplicity. I am just not
seeing 9.2 or --binary-upgrade causing any fundamental affects ---
pg_dump --schema-only itself has the same problems, and probably the
same cause.

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

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