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 40C1C30DE4F for ; Tue, 10 Jun 2003 18:28:52 +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 27136-02 for ; Tue, 10 Jun 2003 15:28:41 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by svr1.postgresql.org (Postfix) with ESMTP id 5771F30E63F for ; Tue, 10 Jun 2003 15:28:39 -0300 (ADT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3108523; Tue, 10 Jun 2003 11:28:45 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Vincent van Leeuwen , pgsql-performance@postgresql.org Subject: Re: FW: [ADMIN] Shared_buffers and kernel parameters, tuning Date: Tue, 10 Jun 2003 11:28:00 -0700 User-Agent: KMail/1.4.3 References: <200306100846.21875.josh@agliodbs.com> <20030610181247.GX1535@md2.mediadesign.nl> In-Reply-To: <20030610181247.GX1535@md2.mediadesign.nl> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200306101128.00609.josh@agliodbs.com> X-Archive-Number: 200306/36 X-Sequence-Number: 2120 Vincent, > This server isn't running postgres that long, and we're still trying to= =20 figure > out the best configuration parameters for the highest possible performanc= e. > Shared_buffers was one of the first things we looked at. We've tested with > shared_buffers at 1024, 8192, 32768 and 131072. So far, performance with > shared_buffers set at 32768 was the best we could attain. 8192 and 131072= =20 came > out roughly equal. 1024 was miserable. Cool! This is the first report we've had of a successful higher setting f= or=20 shared_buffers. I'll need to revise the text. What do people think of: SHARED_BUFFERS Sets the size of Postgres' memory buffer where queries are held before being fed into the Kernel buffer of the host system. It's very important to remember that this is only a holding area, and not the total memory availab= le for the server. As such, resist the urge to set this number to a large portion of your RAM, as this will actually degrade performance on many OSes. Members of the pgsql-performance mailing list have mostly found useful valu= es in the range of 1000-6000, depending on available RAM, database size, and number of concurrent queries. This can go up slightly for servers with a great deal of RAM; the useful maximum on Linux seems to be 6% to 10% of available RAM, with performance degrading at higher settings. Information on other OSes is not yet posted. On multi-purpose servers, of course, the setting should be lowered.=20 > Also, there was a very strong relation between the shared_buffers setting= =20 and > the amount of cpu time spent in kernelland. Currently, the server spends > roughly 20% of it's time in kernelspace (according to vmstat). When > shared_buffers was 8192, this went up to about 30%. This makes perfect sense ... less shared_buffers =3D more kernel_buffers, a= nd=20 vice-versa. > Currently, we have the following settings: > shared_buffers =3D 32768 > max_fsm_relations =3D 100 You might wanna increase this; current recommended is 300 just to make sure= =20 that you have one for every table. > Just to be absolutely sure: all *_cost parameters only influence the chos= en > plan, right? There is absolutely nothing else influenced which doesn't sh= ow=20 up > in an EXPLAIN ANALYZE, right? Yes, AFAIK. --=20 -Josh Berkus Aglio Database Solutions San Francisco