X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 6838230DDBA for ; Wed, 11 Jun 2003 03:52:43 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 01688-08 for ; Wed, 11 Jun 2003 00:52:31 -0300 (ADT) Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251]) by svr1.postgresql.org (Postfix) with ESMTP id 62F3430DE1C for ; Wed, 11 Jun 2003 00:52:28 -0300 (ADT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id h5B3qH327246; Tue, 10 Jun 2003 23:52:17 -0400 (EDT) From: Bruce Momjian Message-Id: <200306110352.h5B3qH327246@candle.pha.pa.us> Subject: Re: FW: [ADMIN] Shared_buffers and kernel parameters, tuning In-Reply-To: <20030610231036.GZ1535@md2.mediadesign.nl> To: Vincent van Leeuwen Date: Tue, 10 Jun 2003 23:52:17 -0400 (EDT) Cc: pgsql-performance@postgresql.org X-Mailer: ELM [version 2.4ME+ PL99 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200306/49 X-Sequence-Number: 2133 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 > > > 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