Re: [RFC, POC] Don't require a NBuffer sized PrivateRefCount array of local buffer pins

On 2014-08-26 22:19:47 -0400, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > The biggest concern previously were some benchmarks. I'm not entirely
> > sure where to get a good testcase for this that's not completely
> > artificial - most simpler testcases don't pin many buffers.
>
> FWIW, I think that's by design; we don't ever want to pin more than one
> buffer per relation+index in use in a given query.

Right.

> You could certainly
> build complicated queries joining many tables in order to push up the
> number of pinned buffers, but whether buffer pin manipulations would be
> the bottleneck in such cases is pretty dubious.

Yea, I actually tried that and I didn't see anything.

> I would say that the issue most deserving of performance testing is your
> sizing of the linear-search array --- it's not obvious that 8 is a good
> size.

It's about the size of a cacheline on all common architectures, that's
how I found it. I don't think it makes a very big difference whether we
make it 4 or 12, but outside of that range I think it'll be unlikely to
be beneficial. The regression tests never go about three or four pins or
so currently, so I think that's a number unlikely to regularly be
crossed in practice.

> Another thing to think about: a way to get to larger numbers of pinned
> buffers without especially-complex queries is to have nested queries,
> such as SQL queries inside plpgsql functions inside outer queries.

What I did was hack together a pgbench script that does a lot of
DECLARE c_01 CURSOR FOR SELECT * FROM pg_attribute WHERE ctid = '(0, 1)';
FETCH NEXT FROM c_01;

I couldn't measure a bigger slowdown (as that has to be executed for
every xact) for the new code than for the old one.

> Does the patch logic take any advantage of the locality-of-reference
> that will occur in such scenarios?

Yes. Whenever a buffer is pinned/unpinned that's not in the array it'll
displace an entry from the array into the hashtable. Even though the
replacement is simplistic/linear I think that should nearly always end
up with the last used buffers in the array.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services