Re: mosbench revisited

On Wed, Aug 10, 2011 at 9:46 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Robert Haas's message of mié ago 10 21:27:08 -0400 2011:
>> 2011/8/10 Dimitri Fontaine <dfontaine(at)hi-media(dot)com>:
>> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> >> However, it doesn't really do anything to solve this problem.  The
>> >> problem here is not that the size of the relation is changing too
>> >> frequently - indeed, it's not changing at all in this test case.  The
>> >> problem is rather that testing whether or not the size has in fact
>> >> changed is costing too much.
>> >
>> > You were complaining about the cost of the cache maintenance, that in
>> > the current scheme of things would have to be called far too often.
>> > Reducing the relation extension trafic would allow, I guess, to have
>> > something more expensive to reset the cache -- it would not happen much.
>>
>> That's an interesting point.  I confess to having no idea how frequent
>> relation extension is now, or how much overhead we could add before it
>> started to get noticeable.
>
> I have seen several cases on which it (rel extension) is really
> troublesome.  Think insertion on large bytea fields -- the toast table's
> extension lock was heavily contended.  When this was in 8.1 we had quite
> some trouble because of the locking involving some shared buffer pool
> lwlock which is fortunately now partitioned; even without that, it is a
> major contention point.  These were insert-only tables, so pages are
> always full except the last one.

Yeah. I think there's a good argument to be made that we should
extend relations in larger chunks, both for this reason and to avoid
fragmenting the underlying file.

But in this case, the fact that relation extension is such a
heavyweight operation almost works to our advantage. I mean, if we're
already acquiring a heavyweight lock (and they're not called
heavyweight for nothing), making at least one system call which
updates filesystem metadata, and then releasing our heavyweight lock,
the additional overhead of setting a flag in shared memory or somesuch
might well be unnoticeable.

Or it might not - hard to know without testing.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company