Re: 2nd Level Buffer Cache

On Fri, Mar 18, 2011 at 8:14 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> rsmogura <rsmogura(at)softperience(dot)eu> wrote:
>
>> Yes, there is some change, and I looked at this more carefully, as
>> my performance results wasn't such as I expected. I found PG uses
>> BufferAccessStrategy to do sequence scans, so my test query took
>> only 32 buffers from pool and didn't overwritten index pool too
>> much. This BAS is really surprising. In any case when I end
>> polishing I will send good patch, with proof.
>
> Yeah, that heuristic makes this less critical, for sure.
>
>> Actually idea of this patch was like this:
>> Some operations requires many buffers, PG uses "clock sweep" to
>> get next free buffer, so it may overwrite index buffer. From point
>> of view of good database design We should use indices, so purging
>> out index from cache will affect performance.
>>
>> As the side effect I saw that this 2nd level keeps pg_* indices
>> in memory too, so I think to include 3rd level cache for some pg_*
>> tables.
>
> Well, the more complex you make it the more overhead there is, which
> makes it harder to come out ahead.  FWIW, in musing about it (as
> recently as this week), my idea was to add another field which would
> factor into the clock sweep calculations.  For indexes, it might be
> "levels above leaf pages".

The high level blocks of frequently used indexes do a pretty good job
of keeping their usage counts high already, and so probably stay in
the buffer pool already. And to the extent they don't, promoting all
indexes (even infrequently used ones, which I think most databases
have) would probably not be the way to encourage the others.

I would be more interested in looking at the sweep algorithm itself.
One thing I noticed in simulating the clock sweep is that the entry of
pages into the buffer with a usage count of 1 might not be very
useful. That give that page 2 sweeps of the clock arm before getting
evicted, so they have an opportunity to get used again. But since all
the blocks they are competing against also do the same thing, that
just means the arm sweeps about twice as fast, so they don't really
get much more of an opportunity. The other thought was that each
buffers gets its usage incremented by 2 or 3 rather than 1 each time
it is found already in the cache.

> Maybe the thing to focus on first is the oft-discussed "benchmark
> farm" (similar to the "build farm"), with a good mix of loads, so
> that the impact of changes can be better tracked for multiple
> workloads on a variety of platforms and configurations.

Yeah, that sounds great. Even just having a centrally organized group
of scripts/programs that have a good mix of loads, without the
automated farm to go with it, would be a help.

Cheers,

Jeff