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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Vincent van Leeuwen <pgsql(dot)spam(at)vinz(dot)nl>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: FW: [ADMIN] Shared_buffers and kernel parameters, tuning
Date: 2003-06-11 03:52:17
Message-ID: 200306110352.h5B3qH327246@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message pginfo 2003-06-11 15:35:10 left join performance problem
Previous Message Vincent van Leeuwen 2003-06-10 23:25:38 Re: FW: [ADMIN] Shared_buffers and kernel parameters, tuning