Re: Further pg_upgrade analysis for many tables

On Fri, Nov 9, 2012 at 6:59 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Thu, Nov 8, 2012 at 4:33 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> On Thu, Nov 8, 2012 at 03:46:09PM -0800, Jeff Janes wrote:
>>> On Wed, Nov 7, 2012 at 6:17 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>>> > As a followup to Magnus's report that pg_upgrade was slow for many
>>> > tables, I did some more testing with many tables, e.g.:
>>> >
>>> ...
>>> >
>>> > Any ideas? I am attaching my test script.
>>>
>>> Have you reviewed the thread at:
>>> http://archives.postgresql.org/pgsql-performance/2012-09/msg00003.php
>>> ?
>>>
>>> There is a known N^2 behavior when using pg_dump against pre-9.3 servers.
>>
>> I am actually now dumping git head/9.3, so I assume all the problems we
>> know about should be fixed.
>
> Are sure the server you are dumping out of is head?
>
> Using head's pg_dump, but 9.2.1 server, it takes me 179.11 seconds to
> dump 16,000 tables (schema only) like your example, and it is
> definitely quadratic.
>
>
> But using head's pg_dump do dump tables out of head's server, it only
> took 24.95 seconds, and the quadratic term is not yet important,
> things still look linear.

I also ran a couple of experiments with git head. From 8k to 16k I'm
seeing slightly super-linear scaling (2.25x), from 32k to 64k a
quadratic term has taken over (3.74x).

I ran the experiments on a slightly beefier machine (Intel i5 @ 4GHz,
Intel SSD 320, Linux 3.2, ext4). For 16k, pg_dump took 29s, pg_upgrade
111s. At 64k the times were 150s/1237s. I didn't measure it, but
occasional peek at top suggested that most of the time was spent doing
server side processing of restore.

I also took two profiles (attached). AtEOXact_RelationCache seems to
be the culprit for the quadratic growth.

Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de