Re: Further pg_upgrade analysis for many tables

On Wed, Nov  7, 2012 at 09:17:29PM -0500, Bruce Momjian wrote:
> Things look fine through 2k, but at 4k the duration of pg_dump, restore,
> and pg_upgrade (which is mostly a combination of these two) is 4x,
> rather than the 2x as predicted by the growth in the number of tables. 
> To see how bad it is, 16k tables is 1.3 hours, and 32k tables would be
> 5.6 hours by my estimates.
> 
> You can see the majority of pg_upgrade duration is made up of the
> pg_dump and the schema restore, so I can't really speed up pg_upgrade
> without speeding those up, and the 4x increase is in _both_ of those
> operations, not just one.
> 
> Also, for 16k, I had to increase max_locks_per_transaction or the dump
> would fail, which kind of surprised me.
> 
> I tested 9.2 and git head, but they produced identical numbers.  I did
> use synchronous_commit=off.
> 
> Any ideas?  I am attaching my test script.

Thinking this might be related to some server setting, I increased
shared buffers, work_mem, and maintenance_work_mem, but this produced
almost no improvement:

	tables    pg_dump     restore     pg_upgrade
	    1       0.30        0.24       11.73(-)
	 1000       6.46        6.55       28.79(2.45)
	 2000      29.82       20.96       69.75(2.42)
	 4000      95.70      115.88      289.82(4.16)
	 8000     405.38      505.93     1168.60(4.03)
	
	shared_buffers=1GB
	tables    pg_dump     restore     pg_upgrade
	   1        0.26        0.23
	1000        6.22        7.00
	2000       23.92       22.51
	4000       88.44      111.99
	8000      376.20      531.07
	
	shared_buffers=1GB
	work_mem/maintenance_work_mem = 500MB
	1           0.27        0.23
	1000        6.39        8.27
	2000       26.34       20.53
	4000       89.47      104.59
	8000      397.13      486.99

Any ideas what else I should test?  It this O(2n) or O(n^2) behavior?

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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