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

Vincent van Leeuwen wrote:
> On 2003-06-10 15:08:22 -0400, Bruce Momjian wrote:
> > 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.
> >
>
> Makes a lot of sense to me. We're doing a lot of I/O on a small part of that
> 7GB, and the rest is accessed in a more or less random fashion, so 256MB of
> shared buffers sounds about right. I'll play more with this in the future to
> see at what setting it performs best.
>
> Is there any information available in the system tables or statistics
> collector that can help determine X? Could PostgreSQL be easily modified to
> provide more information in this area?

Estimatinge working set is an old problem.  You can look at pgsql_tmp
under each database directory for sort mem, but for shared buffers, I am
not sure how to know the proper size.

Anyone else have an idea?

--
  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