Re: buffercache/bgwriter

Hi Brad,

yes. that's the question....
in the source code in freelist.c there is something that I don't understand.

This is the first try to get a free page. The second try scans used buffers.
What makes me wonder is the why postgres is checking for <<buf->usage_count
== 0>>
where usage_count is supposed to be NULL initially.

while (StrategyControl->firstFreeBuffer >= 0)
{
buf = &BufferDescriptors[StrategyControl->firstFreeBuffer];
Assert(buf->freeNext != FREENEXT_NOT_IN_LIST);

/* Unconditionally remove buffer from freelist */
StrategyControl->firstFreeBuffer = buf->freeNext;
buf->freeNext = FREENEXT_NOT_IN_LIST;

/*
* If the buffer is pinned or has a nonzero usage_count, we cannot
use
* it; discard it and retry. (This can only happen if VACUUM put a
* valid buffer in the freelist and then someone else used it before
* we got to it. It's probably impossible altogether as of 8.3, but
* we'd better check anyway.)
*/
LockBufHdr(buf);
if (buf->refcount == 0 && buf->usage_count == 0)
{
if (strategy != NULL)
AddBufferToRing(strategy, buf);
return buf;
}
UnlockBufHdr(buf);
}

Best...
Uwe

On 23 March 2011 15:58, Nicholson, Brad (Toronto, ON, CA) <bnicholson(at)hp(dot)com
> wrote:

>
>
> > -----Original Message-----
> > From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-
> > owner(at)postgresql(dot)org] On Behalf Of tv(at)fuzzy(dot)cz
> > Sent: Wednesday, March 23, 2011 10:42 AM
> > To: Uwe Bartels
> > Cc: pgsql-performance(at)postgresql(dot)org
> > Subject: Re: [PERFORM] buffercache/bgwriter
> >
> > > Hi,
> > >
> > > I have very bad bgwriter statistics on a server which runs since many
> > > weeks
> > > and it is still the same after a recent restart.
> > > There are roughly 50% of buffers written by the backend processes and
> > the
> > > rest by checkpoints.
> > > The statistics below are from a server with 140GB RAM, 32GB
> > shared_buffers
> > > and a runtime of one hour.
> > >
> > > As you can see in the pg_buffercache view that there are most buffers
> > > without usagecount - so they are as free or even virgen as they can
> > be.
> > > At the same time I have 53% percent of the dirty buffers written by
> > the
> > > backend process.
> >
> > There are some nice old threads dealing with this - see for example
> >
> > http://postgresql.1045698.n5.nabble.com/Bgwriter-and-pg-stat-bgwriter-
> > buffers-clean-aspects-td2071472.html
> >
> > http://postgresql.1045698.n5.nabble.com/tuning-bgwriter-in-8-4-2-
> > td1926854.html
> >
> > and there even some nice external links to more detailed explanation
> >
> > http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
>
> The interesting question here is - with 3 million unallocated buffers, why
> is the DB evicting buffers (buffers_backend column) instead of allocating
> the unallocated buffers?
>
> Brad.
>