Re: old synchronized scan patch

>>> On Thu, Dec 7, 2006 at 4:57 AM, in message <874ps8km81(dot)fsf(at)enterprisedb(dot)com>,
Gregory Stark <stark(at)enterprisedb(dot)com> wrote:

> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
>>
>> If you focus the backends together by synchronizing them you definitely
>> also then need to solve the problem of false cache reinforcement.
>
> Well the clock sweep algorithm is inherently resistant to this. The
> classical
> algorithm only had a single bit to indicate whether the buffer had been
> touched since hand came around last.
>
> If you have a counter you just need to limit the size of that counter. The
> larger the maximum value of the counter the slower the algorithm will be to
> learn new access patterns. The smaller the counter is the shorter its memory
> will be.
>
> I do vaguely recall discussing having a counter in school but IIRC it was
> just
> a two bit counter which the clock sweep shifted right. So a buffer hit by a
> synchronized would require two sweeps to get flushed instead of one but that
> would be true regardless of how many synchronized scans hit it.
>
> I think the no- counter (ie, single bit of state) is probably exactly what you
> want in this case. If there are synchronized scans running there may be more
> coming along behind you. You do want to keep those buffers around long
> enough
> to be used by them. If there aren't any synchronized scans running then you
> probably want to just keep reusing the same buffers which is what will
> happen
> when the hand comes around the next time if nobody else has looked at.
>
> It may be enough to just cap the use- counter at different values when you
> increment it depending on the type of access. In a sequential scan don't
> increment it beyond 1 (ie, just a single bit of state). In a random I/O
> increment it up to 2. It may also be useful to have different initial use
> values. Or maybe it makes more sense to look at initial values. If you load
> a
> buffer in a sequential scan perhaps its use count could start at 0 whereas
> random i/o could start at 1.

For what little it's worth, the algorithm which tested out best for me in a similar situation in a database I wrote which was used in production environments for decades was to use a 16 bit use counter which was effectively multiplied by 0.75 (with shifts and an add) in a sweep triggered by an I/O count. I know we tried different multiples of the buffer slot count for the trigger point, to find the sweet spot; unfortunately, I don't remember where that was. This seemed to provide pretty good protection against transient activity flushing the buffer.

-Kevin