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@momjian.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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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