Re: Page replacement algorithm in buffer cache

On Tue, Apr 2, 2013 at 1:53 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> That seems pretty unlikely because of A sheer luck of hitting that
> page for the dropout (if your buffer count is N the chances of losing
> it would seem to be 1/N at most) and B highly used pages are much more
> likely to be pinned and thus immune from eviction. But my issue with
> this whole line of analysis is that I've never been able to to turn it
> up in simulated testing. Probably to do it you'd need very very fast
> storage.

Well, if you have shared_buffers=8GB, that's a million buffers. One
in a million events happen pretty frequently on a heavily loaded
server, which, on recent versions of PostgreSQL, can support several
hundred thousand queries per second, each of which accesses multiple
buffers.

I've definitely seen evidence that poor choices of which CLOG buffer
to evict can result in a noticeable system-wide stall while everyone
waits for it to be read back in. I don't have any similar evidence
for shared buffers, but I wouldn't be very surprised if the same
danger exists there, too.

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