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 D770030DC88 for ; Tue, 10 Jun 2003 23:10:50 +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 50071-10 for ; Tue, 10 Jun 2003 20:10:40 -0300 (ADT) Received: from mail2.mediadesign.nl (md2.mediadesign.nl [212.19.205.67]) by svr1.postgresql.org (Postfix) with SMTP id A715130DCDC for ; Tue, 10 Jun 2003 20:10:38 -0300 (ADT) Received: (qmail 23817 invoked by uid 666); 10 Jun 2003 23:10:36 -0000 Date: Wed, 11 Jun 2003 01:10:36 +0200 From: Vincent van Leeuwen To: pgsql-performance@postgresql.org Subject: Re: FW: [ADMIN] Shared_buffers and kernel parameters, tuning Message-ID: <20030610231036.GZ1535@md2.mediadesign.nl> Mail-Followup-To: pgsql-performance@postgresql.org References: <200306101128.00609.josh@agliodbs.com> <200306101908.h5AJ8Mq16858@candle.pha.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200306101908.h5AJ8Mq16858@candle.pha.pa.us> User-Agent: Mutt/1.3.28i X-Archive-Number: 200306/47 X-Sequence-Number: 2131 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? Vincent van Leeuwen Media Design - http://www.mediadesign.nl/