Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 54D402E00C3 for ; Thu, 21 Feb 2008 20:24:59 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 89756-05 for ; Thu, 21 Feb 2008 20:24:49 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.179]) by postgresql.org (Postfix) with ESMTP id 7E27A2E00C1 for ; Thu, 21 Feb 2008 20:24:55 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so273085waf.5 for ; Thu, 21 Feb 2008 16:24:54 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:mime-version:content-type:content-transfer-encoding:content-disposition; bh=Z1Nm5TnU40dEnK/dBmUYpcMyOAQUp1G3dh3vwdHQBUY=; b=n1bTNHBUVeQhicDRHNkRvI8lpXWjO7yzo3X6PGmQJJLjC3OYvuAYyH0ya34OtAskb3AGdWAhkN0vood3glBe4Wo3P5Yv++SUQ8KlQrkczALGybkylLn9jiC6vvkOkHrAS5hZVNBC55QRgGwAQruLJG2XcqCSPS7gSc8Sdt1Ecyk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:mime-version:content-type:content-transfer-encoding:content-disposition; b=A2cGFKJEYyAsfrSI4wkp088bRiTNcs14heDV6Jlwbedd/gAYjYSVa9zNJ5b0LGB1I+EhCtlhPLANvCCOSpJKcBT0joJZfyBDUC55cPcr30rEY4wmiwbha8NdG56O1hiJJXktQdZC7+C31p0RmDaHTBvkU8yV2tzwTO7QrwtDA34= Received: by 10.114.175.16 with SMTP id x16mr8270059wae.12.1203639894301; Thu, 21 Feb 2008 16:24:54 -0800 (PST) Received: by 10.114.133.6 with HTTP; Thu, 21 Feb 2008 16:24:54 -0800 (PST) Message-ID: Date: Thu, 21 Feb 2008 18:24:54 -0600 From: "Scott Marlowe" To: "Dave Cramer" Subject: config settings, was: 4s query want to run faster Cc: "Mark Kirkwood" , "Adonias Malosso" , "Claus Guttesen" , pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/281 X-Sequence-Number: 28823 On Thu, Feb 21, 2008 at 5:40 PM, Dave Cramer wrote: > > On 21-Feb-08, at 6:16 PM, Scott Marlowe wrote: > > > On Thu, Feb 21, 2008 at 4:59 PM, Mark Kirkwood > > wrote: > >> The other parameter you might want to look at is > >> effective_cache_size - > >> increasing it will encourage index use. On a machine with 16GB the > >> default is probably too small (there are various recommendations > >> about > >> how to set this ISTR either Scott M or Greg Smith had a page > >> somewhere > >> that covered this quite well - guys?). > >> > The default is always too small in my experience. > > What are the rest of the configuration values ? I was thinking that we almost need a matrix of versions and small, typical, large, and too big or whatever for each version, and which hardware configs. max_connections is the one I see abused a lot here. It's a setting that you can set way too high and not notice there's a problem until you go to actually use that many connections and find out your database performance just went south. One should closely monitor connection usage and track it over time, as well as benchmark the behavior of your db under realistic but heavy load. You should know how many connections you can handle in a test setup before things get ugly, and then avoid setting max_connections any higher than about half that if you can do it. Same kind of thinking applies to any resource that has straightline 1:1 increase in resource usage, or a tendency towards that, like work_mem (formerly sort_mem). Dammit, nearly every one really needs it's own mini-howto on how to set it... They all are covered in the runtime config section of the docs.