Re: Clock sweep not caching enough B-Tree leaf pages?

On Mon, Apr 20, 2015 at 11:00 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> Hmm, interesting point. It's possible that we'd still have problems
>> with everything maxing out at 32 on some workloads, but at least it'd
>> be a little harder to max out at 32 than at 5.
>
> Do we have any reproducible test cases to evaluate these assumptions?

The particular point that you are responding to here is easy to
reproduce. Just create any workload that fits in shared_buffers - a
small pgbench database, for example - and access stuff. No usage
counts will go down, but every access will drive usage counts up.
Eventually everything will hit any maximum you care to install, and
actually I don't think it takes very long. You can use pg_buffercache
to see the results.

> I haven't looked at this stuff for a while, but my main issue with
> the clock sweep was finding sweep heavy cases that did not also have
> trouble with the buffer mapping locks (although the facts on the
> ground my have changed since then).

We increased the number of buffer mapping locks to 128, so that
problem should be considerably ameliorated now. But it's not totally
gone, as demonstrated by Andres's experiments with my chash patch.

There was also a patch that eliminated BufFreelistLock in favor of a
spinlock held for much shorter time periods, and then Andres took that
one step further and made it use atomics. That used to be a
*terrible* bottleneck on eviction-heavy workloads and is now much
improved. More work may remain to be done, of course.

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