pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Vincent van Leeuwen <pgsql.spam@vinz.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: FW: [ADMIN] Shared_buffers and kernel parameters, tuning
Date: Tue, 10 Jun 2003 23:52:17 -0400 (EDT)
Message-ID: <200306110352.h5B3qH327246@candle.pha.pa.us> (raw)
In-Reply-To: <20030610231036.GZ1535@md2.mediadesign.nl>

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



view thread (13+ messages)

Message-ID: <200306110352.h5B3qH327246@candle.pha.pa.us>
Permalink:  ../200306110352.h5B3qH327246@candle.pha.pa.us/
Also on:    postgresql.org/message-id/200306110352.h5B3qH327246@candle.pha.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: pgman@candle.pha.pa.us, pgsql.spam@vinz.nl
  Subject: Re: FW: [ADMIN] Shared_buffers and kernel parameters, tuning
  In-Reply-To: <200306110352.h5B3qH327246@candle.pha.pa.us>
  References: <20030610231036.GZ1535@md2.mediadesign.nl> <200306110352.h5B3qH327246@candle.pha.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox