Re: our buffer replacement strategy is kind of lame

On Mon, Jan 2, 2012 at 5:41 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> Does anyone have a better idea for reducing BufFreelistLock
>> contention? Something simple that will work for 9.2?
>
> Get rid of the freelist?  Once shared buffers are full, it's just about
> useless anyway.  But you'd need to think about the test cases that you
> pay attention to, as there might be scenarios where it remains useful.

Agree freelist is mostly useless, but startup and dropping objects requires it.

When its full its just an integer > 0 test, which is cheap and its on
the same cacheline as the nextVictimBuffer anyway, so we have to fetch
it.

The clock sweep is where all the time goes, in its current form.

We have 2 problems

1. StrategySyncStart() requests exclusive lock on BufFreelistLock, so
bgwriter has to fight with backends to find out where it should clean.
As a result it spends lots of time waiting and insufficient time
cleaning.

2. When a backend can't find a free buffer, it spins for a long time
while holding the lock. This makes the buffer strategy O(N) in its
worst case, which slows everything down. Notably, while this is
happening the bgwriter sits doing nothing at all, right at the moment
when it is most needed. The Clock algorithm is an approximation of an
LRU, so is already suboptimal as a "perfect cache". Tweaking to avoid
worst case behaviour makes sense. How much to tweak? Well,...

(1) is fixed by buffreelistlock_reduction.v1.patch

(2) is fixed by freelist_ok.v2.patch

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services