Message-Id: <1167765598.16831.282794437@webmail.messagingengine.com>
From: "Jeremy Haile" <jhaile@fastmail.fm>
To: pgsql-performance@postgresql.org
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="ISO-8859-1"
MIME-Version: 1.0
References: <1167754933.1316.282766279@webmail.messagingengine.com>
	<459A898B.9020509@archonet.com>
	<1167757584.31784.282774689@webmail.messagingengine.com>
	<459AA952.80008@archonet.com>
Subject: Re: Config parameters
In-Reply-To: <459AA952.80008@archonet.com>
Date: Tue, 02 Jan 2007 14:19:58 -0500

Thanks for the information!

Are there any rule-of-thumb starting points for these values that you
use when setting up servers?  I'd at least like a starting point for
testing different values.  

For example, I'm sure setting a default work_mem of 100MB is usually
overkill - but is 5MB usually a reasonable number?  20MB?  My system
does not have a huge number of concurrent users, but they are hitting
large tables.  I'm not sure what numbers people usually use here
successfully.

For maintenance_work_mem, I turned off autovacuum to save on
performance, but run a vacuum analyze once an hour.  My current database
characteristics are heavy insert (bulk inserts every 5 minutes) and
medium amount of selects on large, heavily indexed tables.

For temp_buffers - any rule of thumb starting point?  What's the best
way to evaluate if this number is adjusted correctly?

For random_page_cost - is the default of 4 pretty good for most drives? 
Do you usually bump it up to 3 on modern servers?  I've usually done
internal RAID setups, but the database I'm currently working on is
hitting a SAN over fiber.

I realize that these values can vary a lot based on a variety of factors
- but I'd love some more advice on what good rule-of-thumb starting
points are for experimentation and how to evaluate whether the values
are set correctly. (in the case of temp_buffers and work_mem especially)


On Tue, 02 Jan 2007 18:49:54 +0000, "Richard Huxton" <dev@archonet.com>
said:
> 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