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

On Wed, Apr 15, 2015 at 12:15 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> IIUC, this will allow us to increase usage count only when the buffer
> is touched by clocksweep to decrement the usage count.

Yes.

> I think such a solution will be good for the cases when many evictions
> needs to be performed to satisfy the workload, OTOH when there are
> not too many evictions that needs to be done, in such a case some of
> the buffers that are accessed much more will have equal probability to
> get evicted as compare to buffers which are less accessed.

Possibly, but I think it's even worse under the current algorithm.
Under this proposal, if we go for a long time without any buffer
evictions, every buffer usage's count will top out at 2 more than
wherever it was after the last clock sweep. In the worst case, every
buffer (or most of them) could end up with the same usage count. But
under the status quo, they'll all go to 5, which is an even bigger
loss of information, and which will make the first eviction much more
expensive than if they are all pegged at 2 or 3.

There could be ways to improve things further, such as by slowly
advancing the clock sweep even when no eviction is required. But
that's a bit tricky to do, too. You'd like (perhaps) to advance it
one step for every buffer allocation, but you don't want a centralized
counter, or unnecessary contention on the clock sweep when no eviction
is necessary in the first place. There's probably some way to make it
work, though, if we put our mind to it. I'm inclined to try the
simpler approach first and see what it buys.

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