Thread: pg_buffercache's usage count
I'm looking at the usage count column of pg_buffercache's info, and I'm confused. Several buffers that supposed have LRUvalues of 5 belong to non-unique indices which supposedly have never been used. As I understand things, that shouldn'thappen. Am I missing something?
On Feb 23, 2010, at 3:06 PM, Ben Chobot wrote: > I'm looking at the usage count column of pg_buffercache's info, and I'm confused. Several buffers that supposed have LRUvalues of 5 belong to non-unique indices which supposedly have never been used. As I understand things, that shouldn'thappen. Am I missing something? (And maybe more to the point, when does the LRU go down in value?)
Ben Chobot wrote: > On Feb 23, 2010, at 3:06 PM, Ben Chobot wrote: > > >> I'm looking at the usage count column of pg_buffercache's info, and I'm confused. Several buffers that supposed have LRUvalues of 5 belong to non-unique indices which supposedly have never been used. As I understand things, that shouldn'thappen. Am I missing something? >> > > (And maybe more to the point, when does the LRU go down in value?) > Usage counts only go up when a page is "pinned" because some backend requested that particular block for its work via a call to BufferAlloc with the file/block it needs. Usage counts go down every time the clock sweep hand looking to allocate new buffers passes over that page during its constant circular scanning of the buffer cache. The mechanics involved are described in a pretty detailed way by the "Inside the PostgreSQL Buffer Cache" presentation at http://www.westnet.com/~gsmith/content/postgresql/ (and no where else I'm aware of, besides the source code itself). -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
Greg Smith wrote: > Ben Chobot wrote: > >On Feb 23, 2010, at 3:06 PM, Ben Chobot wrote: > > > >>I'm looking at the usage count column of pg_buffercache's info, and I'm confused. Several buffers that supposed haveLRU values of 5 belong to non-unique indices which supposedly have never been used. As I understand things, that shouldn'thappen. Am I missing something? > > > >(And maybe more to the point, when does the LRU go down in value?) > > Usage counts only go up when a page is "pinned" because some backend > requested that particular block for its work via a call to > BufferAlloc with the file/block it needs. BTW the only reason you don't see buffers having a larger "usage" is that the counters are capped at that value. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote: > BTW the only reason you don't see buffers having a larger "usage" is > that the counters are capped at that value. > Right, the usage count is limited to 5 for no reason besides "that seems like a good number". We keep hoping to come across a data set and application with a repeatable benchmark where most of the data ends up at 5, but there's still a lot of buffer cache churn, to allow testing whether a further increase could be valuable. So far nobody has actually found such a set. If I shrunk shared_buffers on Ben's data I think I could create that situation. As is usually the case, I doubt he has another server with 128GB of RAM hanging around just to run that experiment on though, which has always been the reason why I can't simulate this more easily--systems it's prone to happening on aren't cheap. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
On Feb 24, 2010, at 11:09 AM, Greg Smith wrote: > Alvaro Herrera wrote: >> BTW the only reason you don't see buffers having a larger "usage" is >> that the counters are capped at that value. >> > > Right, the usage count is limited to 5 for no reason besides "that seems like a good number". We keep hoping to come acrossa data set and application with a repeatable benchmark where most of the data ends up at 5, but there's still a lotof buffer cache churn, to allow testing whether a further increase could be valuable. So far nobody has actually foundsuch a set. If I shrunk shared_buffers on Ben's data I think I could create that situation. As is usually the case,I doubt he has another server with 128GB of RAM hanging around just to run that experiment on though, which has alwaysbeen the reason why I can't simulate this more easily--systems it's prone to happening on aren't cheap. Well as it happens we *did* just get our third slony node in today, and it could spend some time doing burn-in experimentsif it would be helpful. Unfortunately, I won't be able to drive the same load against it, so I don't know howuseful it would be.