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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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