Re: performance regression in 9.2 when loading lots of small tables

On Tue, Jun 19, 2012 at 2:38 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Jun 19, 2012 at 4:33 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Mon, Jun 18, 2012 at 8:42 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>>> There was a regression introduced in 9.2 that effects the creation and
>>> loading of lots of small tables in a single transaction.
>>>
>>> It affects the loading of a pg_dump file which has a large number of
>>> small tables (10,000 schemas, one table per schema, 10 rows per
>>> table).  I did not test other schema configurations, so these
>>> specifics might not be needed to invoke the problem.
>>>
>>> It causes the loading of a dump with "psql -1 -f " to run at half the
>>> previous speed.  Speed of loading without the -1 is not changed.
>>
>> I tried to figure out why this was happening.  I tried it out on the
>> IBM POWER7 box after building from
>> f5297bdfe4c4a47376c41b96161fb55c2294a0b1 and it ran for about 14
>> minutes.  'time' reports that psql used 38 seconds of user time and 11
>> seconds of system time.  The remaining time was presumably spent
>> waiting for the backend and/or the kernel.
>>
...

>> I did a separate run using perf and it had this to say:
>>
>> Events: 1M cycles
>> +  13.18%         postgres  postgres               [.] FlushRelationBuffers
>> +   9.65%         postgres  postgres               [.] comparetup_index_btree
...
>
> I built REL9_1_STABLE from commit
> 1643031e5fe02d2de9ae6b8f86ef9ffd09fe7d3f and it took 19m45.250s, even
> slower than master.  So something is different in my environment
> versus yours.

Yes, I forgot the perhaps the most important part.
FlushRelationBuffers is a real pig under "-1 -f -" if you are running
on large shared_buffers, and might swamp the other issue.  I run with
shared_buffers=8MB to get around that problem.  And with fsync=off if
you don't have BBU or something.  The unlocked test in
FlushRelationBuffers recently added to HEAD probably is what makes
HEAD faster if you are using large shared_buffers.

With small shared_buffers and fsync off I get 1 minute to load 10,000
tables/schemas in the fast phenotype, or 2 minutes in the slow
phenotype.

It looks like the phenotype actually has come and gone more than once
(for different but related reasons), which is why I couldn't find the
place in the REL9_2_STABLE where it happened very easily with git
bisect.

The fundamental problem seems to be that the slow phenotype takes
twice as many locks on which makes the local hash table twice as big,
which in turn makes the reassignment of resource owners twice as slow.

The original regression seems to be because each table had both an
AccessExclusiveLock and a ShareUpdateExclusiveLock taken on it when it
was checked to see if a toast table was needed.  I now see that that
was reverted in both branches.

But in the 9.2 branch, the slow phenotype was re-introduced in
1575fbcb795fc331f4, although perhaps the details of who is locking
what differs.  I haven't yet sorted that out.

Since the fundamental problem will hopefully be fixed in 9.3, can 9.2
live with this regression in what is probably a rather rare case
(loading huge number of very small tables, and with a small enough
shared_buffers avoid the other limitation)?

Cheers,

Jeff