X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1B3C3D1DAA8 for ; Tue, 13 Jan 2004 17:14:20 +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 86424-07 for ; Tue, 13 Jan 2004 13:13:48 -0400 (AST) Received: from anchor-post-37.mail.demon.net (anchor-post-36.mail.demon.net [194.217.242.86]) by svr1.postgresql.org (Postfix) with ESMTP id C1776D1DB3F for ; Tue, 13 Jan 2004 13:13:47 -0400 (AST) 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 1AgS6s-000IGD-0b; Tue, 13 Jan 2004 17:13:46 +0000 Received: by mainbox.archonet.com (Postfix, from userid 529) id BABE81717D; Tue, 13 Jan 2004 17:13:45 +0000 (GMT) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 4B88817177; Tue, 13 Jan 2004 17:13:43 +0000 (GMT) From: Richard Huxton To: David Hill , pgsql-performance@postgresql.org Subject: Re: freebsd 5.2 and max_connections Date: Tue, 13 Jan 2004 17:13:42 +0000 User-Agent: KMail/1.5 References: <20040113160413.GB1554@phobia.ms> In-Reply-To: <20040113160413.GB1554@phobia.ms> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200401131713.42682.dev@archonet.com> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/133 X-Sequence-Number: 5308 On Tuesday 13 January 2004 16:04, David Hill wrote: > Hello - > I am using postgresql to hold aliases, users, and relay_domains for postfix > and courier to do lookups from. I am not storing mail in sql. > > I need postgresql to have fast read performance, so i setup index's on the > tables. Also, the queries are basically "select blah from table where > domain='domain.com'";, so i dont need to be able to support large results. > > I will have a lot of mail servers connecting to this postgresql db, so i > need to support a lot of connections... but dont need to support large > results. Firstly - if you don't know about the tuning guidelines/annotated config file, you should go here: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php Hmm - small result sets accessed directly via indexed fields, so sort_mem probably isn't important to you. Make sure your effective cache setting is accurate though, so PG can estimate whether it'll need to access the disks. Not sure if clustering one or more tables will help - I'm guessing not. What might help is to increase the statistics gathered on important columns. That should give the planner a more accurate estimate of value distribution and shouldn't cost you too much to keep accurate, since I'm guessing a low rate of updating. You might want to play with the random page cost (?or is it random access cost?) but more RAM for a bigger disk cache is probably the simplest tweak. -- Richard Huxton Archonet Ltd