Message-ID: <459AA952.80008@archonet.com>
Date: Tue, 02 Jan 2007 18:49:54 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5.0.8 (X11/20061115)
MIME-Version: 1.0
To: Jeremy Haile <jhaile@fastmail.fm>
Cc: pgsql-performance@postgresql.org
Subject: Re: Config parameters
References: <1167754933.1316.282766279@webmail.messagingengine.com>
	<459A898B.9020509@archonet.com>
	<1167757584.31784.282774689@webmail.messagingengine.com>
In-Reply-To: <1167757584.31784.282774689@webmail.messagingengine.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jeremy Haile wrote:
> What is a decent default setting for work_mem and maintenance_work_mem,
> considering I am regularly querying tables that are tens of millions of
> rows and have 2-4 GB of RAM?

Well, work_mem will depend on your query-load. Queries that do a lot of 
sorting should benefit from increased work_mem. You only have limited 
RAM though, so it's a balancing act between memory used to cache disk 
and per-process sort memory. Note that work_mem is per sort, so you can 
use multiples of that amount in a single query. You can issue a "set" to 
change the value for a session.

How you set maintenance_work_mem will depend on whether you vacuum 
continually (e.g. autovacuum) or at set times.

> Also - what is the best way to determine decent settings for
> temp_buffers and random_page_cost?

With all of these, testing I'm afraid. The only sure thing you can say 
is that random_page_cost should be 1 if all your database fits in RAM.

-- 
   Richard Huxton
   Archonet Ltd