Question about memory allocations

Stephen Conley <cheetah@tanabi.org>

From: Steve <cheetah@tanabi.org>
To: pgsql-performance@postgresql.org
Date: 2007-04-10T19:28:48Z
Lists: pgsql-performance
Hey there;

I'm trying to tune the memory usage of a new machine that has a -lot- of 
memory in it (32 gigs).  We're upgrading from a machine that had 16 gigs 
of RAM and using a database that's around 130-some gigs on disc.  Our 
largest tables have in the order of close to 10 million rows.

Problem is, the postgres documentation isn't always clear about what 
different memory things are used for and it's definitely not clear about 
what 'useful values' would be for various things.  Further, looking 
online, gets a lot of random stuff and most of the configuration 
information out there is for pre-8.1 versions that don't have all these 
new and strange values :)

This machine exists only for the database.  With that in mind, a few 
questions.


- I've set up a configuration (I'll show important values below), and 
I"m wondering if there's any way I can actually see the distribution of 
memory in the DB and how the memory is being used.

- What is temp_buffers used for exactly?  Does this matter for, say, 
nested queries or anything in specific?  Is there any case where having 
this as a large number actually -helps-?

- Do full_page_writes and wal_buffers settings matter AT ALL for a machine 
where fysnc = off ?

- What does wal_buffers mean and does increasing this value actually help 
anything?

- Any idea if this is a smart configuration for this machine?  It's a 
Redhat Enterprise Linux machine (kernel 2.6.18), 8 dual-core AMD 64bit 
processors, 32 gigs of RAM, 4x 176 (or whatever the exact number is) gig 
SCSI hard drives in a stripe.  Only values I have modified are mentioned, 
everything else left at default:

shared_buffers = 16GB
temp_buffers = 128MB
max_prepared_transactions = 0

# This value is going to probably set off cries of using this as a set
# command instead of a big global value; however there's more big queries
# than small ones and the number of simultaneous users is very small so
# 'for now' this can be set globally big and if it shows improvement
# I'll implement it as set commands later.
#
# Question; does this mean 2 gigs will be immediately allocated to
# every query, or is this just how big the work memory is allowed to
# grow per transaction?
work_mem=2G

maintenance_work_mem = 4GB
max_stack_depth = 16MB

# Vacuum suggested I make this 'over 3600000' on the old machine, so
# I use this value; if it's too big, this is a symptom of another problem,
# I'd be interested to know :)
max_fsm_pages = 5000000

# For a lot of reasons, it doesn't make any sense to use fsync for this
# DB.  Read-only during the day, backed up daily, UPS'd, etc.
fsync = off
full_page_writes = off
wal_buffers = 512MB

# Leaving this low makes the DB complain, but I'm not sure what's 
# reasonable.
checkpoint_segments = 128

random_page_cost = 1.5
cpu_tuple_cost = 0.001
cpu_index_tuple_cost = 0.0005
cpu_operator_cost = 0.00025
effective_cache_size = 8GB

default_statistics_target = 100




Thanks for all your help!

Steve