Message-ID: <4C753D98.1050705@nabancard.com>
Date: Wed, 25 Aug 2010 11:58:16 -0400
From: Bob Branch <bbranch@nabancard.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.11) Gecko/20100713 Thunderbird/3.0.6
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: New servers, need suggestions for sensible tuning settings
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hello!

We've just installed a couple new servers that will be our new database 
servers.  The basic hardware stats of each are:

2 x Xeon E5620 (2.4GHz quad-core)
32GB DDR3 1333 RAM
6 x 600GB 15krpm SAS drives - RAID 10
Perc 6/i RAID controller with battery backup

I've lurked on this list for a short while and gotten a small (tiny, I'm 
sure) set of ideas about parameters that have commonly been mentioned 
when performance tuning questions come up, but haven't had the freedom 
to do much tuning to our existing setups.  Now that we've got new 
servers (and thus my tuning won't potentially impact production 
performance until we roll these out), I've begun researching how I 
should be tuning these to keep the speeds up in an environment where 
we're regularly dealing with queries handling multiple tables with 20 
million or more rows, and tables that grow by half a million records per 
day.

I've found a great many resources suggesting settings to tune and even 
some that suggest ways to determine the values to set for them, but I'm 
having trouble tracking down anything CURRENT in terms of what options 
to set and what values to give them.  The most comprehensive lists of 
suggestions (as provided by a few, though not a great many minutes 
perusing Google results) list options that don't seem to exist anymore 
(eg- sort_mem) in the version of 8.4 that Ubuntu currently has for 10.04 
server (8.4.4).

Is there a reference I can look at that will give me a description of 
how to determine sensible values for settings like shared_buffers, 
effective_cache_size, etc. that I see discussed on this list and 
elsewhere?  If there isn't a really good reference, would any of you 
mind suggesting sensible starting points for these options or methods 
for determining what those would be, as well as other options I might 
benefit from changing the values of?

I'm limited a bit in my tuning by the disks available - I can keep the 
WAL on a separate partition in the array, but I wasn't able to allocate 
an entire disk or separate array to it due to the amount of the 
avaliable space I'll need for the databases these servers will house. 
I've helped it a bit by using ext2 on the partition the WAL is on (the 
databases themselves are on ext4), but I'm not sure what else I can do 
in terms of tuning the WAL without re-thinking the storage setup.

Thanks in advance for any help you might be able to provide.

Bob Branch