Re: FW: [ADMIN] Shared_buffers and kernel parameters, tuning

Josh Berkus wrote:
> Vincent,
>
> > This server isn't running postgres that long, and we're still trying to
> figure
> > out the best configuration parameters for the highest possible performance.
> > Shared_buffers was one of the first things we looked at. We've tested with
> > shared_buffers at 1024, 8192, 32768 and 131072. So far, performance with
> > shared_buffers set at 32768 was the best we could attain. 8192 and 131072
> came
> > out roughly equal. 1024 was miserable.
>
> Cool!   This is the first report we've had of a successful higher setting for
> shared_buffers.  I'll need to revise the text.  What do people think of:

I have been thinking about shared_buffers, and it seems it is the
age-old issue of working set.

Traditionally Unix doesn't use working set (though a few do).  It just
allocates memory proportionally among all processes, with unreferenced
pages being paged out first.

For PostgreSQL, if your working set is X, if you set your shared buffers
to X, you will get optimal performance (assuming there is no memory
pressure).  If set allocate X/2, you will probably get worse
performance.  If you allocate X*2, you will also probably get slightly
worse performance.

Now, let's suppose you can't allocate X shared buffers, because of
memory pressure.  Suppose you can allocate X/2 shared buffers, and that
will leave X/2 kernel buffers.  It would be better to allocate X/4
shared buffers, and leave X*3/4 kernel buffers.  If you can only
allocate X/5 shared buffers, you might be better with X/10 shared
buffers because you are going to be doing a lot of I/O, and you need
lots of kernel buffers for that.

I think that is what people are seeing when modifying shared buffers:

    X shared buffers is best
    >X shared buffers is too much overhead and starves kernel
    <X might be better by not maximizing shared buffers and have
       more kernel buffers

Add to this that it is very hard to estimate working set.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073