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=1GBtables pg_dump restore pg_upgrade 1 0.26
0.231000 6.22 7.002000 23.92 22.514000 88.44 111.998000 376.20
531.07shared_buffers=1GBwork_mem/maintenance_work_mem= 500MB1 0.27 0.231000 6.39
8.272000 26.34 20.534000 89.47 104.598000 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. +
