X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6140DD1B4FD for ; Tue, 21 Oct 2003 15:57:48 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 93461-08 for ; Tue, 21 Oct 2003 12:57:17 -0300 (ADT) Received: from anchor-post-37.mail.demon.net (anchor-post-37.mail.demon.net [194.217.242.87]) by svr1.postgresql.org (Postfix) with ESMTP id 81BBAD1B563 for ; Tue, 21 Oct 2003 12:57:12 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-37.mail.demon.net with esmtp (Exim 3.35 #1) id 1ABysU-000KOi-0b; Tue, 21 Oct 2003 16:56:58 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id CCB7316D7C; Tue, 21 Oct 2003 16:56:51 +0100 (BST) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 2149316D7B; Tue, 21 Oct 2003 16:56:51 +0100 (BST) From: Richard Huxton To: "Anjan Dave" , Subject: Re: Tuning for mid-size server Date: Tue, 21 Oct 2003 16:56:50 +0100 User-Agent: KMail/1.5 References: <2F2E24372F10744588A27DEECC85FE04B6769C@vt-pe2550-001.vantage.vantage.com> In-Reply-To: <2F2E24372F10744588A27DEECC85FE04B6769C@vt-pe2550-001.vantage.vantage.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200310211656.50374.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/532 X-Sequence-Number: 4280 On Tuesday 21 October 2003 15:28, Anjan Dave wrote: > Hi, > > Pretty soon, a PowerEdge 6650 with 4 x 2Ghz XEONs, and 8GB Memory, with > internal drives on RAID5 will be delivered. Postgres will be from RH8.0. You'll want to upgrade PG to v7.3.4 > I am planning for these values for the postgres configuration - to begin > with: > > Shared_buffers (25% of RAM / 8KB)) = 8589934592 * .25 / 8192 = 262144 > > Sort_mem (4% of RAM / 1KB) = 335544. We'll take about half of that - > 167772 > > Effective_cache_size = 262144 (same as shared_buffers - 25%) My instincts would be to lower the first two substantially, and increase the effective cache once you know load levels. I'd probably start with something like the values below and work up: shared_buffers = 8,000 - 10,000 (PG is happier letting the OS do the cacheing) sort_mem = 4,000 - 8,000 (don't forget this is for each sort) You'll find the annotated postgresql.conf and performance tuning articles useful: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php > In a generic sense, these are recommended values I found in some > documents. The database will be small in size and will gradually grow > over time from few thousands to a few million records, or more. The > activity will be mostly of select statements from a few tables with > joins, orderby, groupby clauses. The web application is based on > Apache/Resin and hotspot JVM 1.4.0. You'll need to figure out how many concurrent users you'll have and how much memory will be required by apache/java. If your database grows radically, you'll probably want to re-tune as it grows. -- Richard Huxton Archonet Ltd