Le jeudi 31 mai 2012 19:11:07, Bruce Momjian a écrit :
> On Wed, May 30, 2012 at 05:55:07PM -0400, Bruce Momjian wrote:
> > > > Seems buffers_alloc is the number of calls to StrategyGetBuffer(),
> > > > which tells how many time we have requested a buffer. Not sure how
> > > > that helps measure buffer pressure.
> > >
> > > Once the linked list is empty, every request for a buffer to read a
> > > new page into must result in the eviction of the previous occupant
> > > from this conceptual freelist buffer (except perhaps for some race
> > > conditions). Isn't that what you wanted? Except that the
> > > buffers_alloc does not get incremented when the StrategyGetBuffer is
> > > satisfied by a ring strategy rather than the default strategy.
> >
> > Well, the ideal case is that I could find out how often data that is
> > near to be discarded is actually needed, hence the "reclaimed" field
> > that is often important for kernel memory presssure reporting on older
> > operating systems. I will post an email soon about my theory of why
> > buffer pressure is an important thing to report to users.
>
> OK, realizing there is no simple way to measure shared buffer pressure,
> let me explain why I want to.
>
> Right now we simplisticly recommend 25% of RAM for shared_buffers, with
> a maximum of 8GB (512MB on Windows). This helps to be sure that there
> are sufficient kernel buffers for high-write operations, and perhaps a
> kernel cache larger than shared buffers.
>
> However, this doesn't help people configure shared buffers larger (e.g.
> 35%) if their working set is larger. Right now, I don't see how a user
> would know this is happening. On the flip side, they might have a
> smaller working set than 25% and spending the overhead of managing 1
> million shared buffers. Again, there is no way to know if that is the
> case.
>
> For example, we have reports that larger shared buffers is sometimes
> better, sometimes not, but there is no feedback we give the user to
> explain why this is happening. My guess is that if their working set is
> larger than 25% of RAM, they benefit, if not, the buffer management
> overhead makes things slower.
>
> I feel we need to allow users to get clearer information on how active
> their shared buffer cache is, perhaps allowing them to shink/grow it as
> appropriate. Asking them to blindly try different shared buffer sizes
> seems suboptimal.
There is also a recent thread about the usagecount used.
Jeff suggested to maybe increase by 2 and decrease by 1. This is really near
another idea I had but I didn't tested yet. Increment/decrement by 1 or 2,
just make even numbers ascending, and odd number descending. So that by just
looking at the usage count you can know what's going with your buffers (more
odd than even or reverse ? building the cache or wasting it or ...). it should
also allows a finer control of eviction.
Increase/decrease shared_buffer while postgresql is up is a nice idea.
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation