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

On Wed, Apr 15, 2015 at 5:26 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> The way our cache works we promote when a buffer is accessed but we only
>> demote when a buffer is flushed. We flush a lot less often than we touch
>> buffers so it's not surprising that the cache ends up full of buffers that
>> are all in the "most recently used" section.
>
> This isn't really correct. We promote when it's accessed, but we
> demote it when the clock sweep hand passes over it, which happens each
> time we consider it for eviction. It does not have to do with
> flushing dirty date to disk, and it does not happen only when the
> buffer is actually evicted.

This is my point though (you're right that "flushed" isn't always the
same as eviction but that's not the important point here). Right now
we only demote when we consider buffers for eviction. But we promote
when we pin buffers. Those two things aren't necessarily happening at
the same rate and in fact are often orders of magnitude different. So
it makes sense that we end up with a lot of buffers promoted all the
way to the most recently used ntile and then have to do n passes
around the clock and have no good information about which buffer to
evict.

What I'm saying is that we should demote a buffer every time we
promote a buffer. So every time we pin a buffer we should advance the
clock a corresponding amount. I know I'm being intentionally vague
about what the corresponding amount is.) The important thing is that
the two should be tied together.

--
greg