Configuration for a new server.

Scott,

I don't know if you received my private email, but just in case you did not I am posting the infomration here.

I have a new set of servers coming in - Dual Xeon E5620's, 96GB RAM, 18 spindles (1 RAID1 for OS - SATA, 12 disk RAID10 for data - SAS, RAID-1 for logs - SAS, 2 hot spares SAS). They are replacing a single Dual Xeon E5406 with 16GB RAM and 2x RAID1 - one for OS/Data, one for Logs.

Current server is using 3840MB of shared buffers.

It will be running FreeBSD 8.1 x64, PG 9.0.2, running streaming replication to a like server.

I have read the performance tuning book written by Greg Smith, and am using it as a guide to configure it for performance.

The main questions which I have are the following:

Is the 25% RAM for shared memory still a good number to go with for this size server?

There are approximately 50 tables which get updated with almost 100% records updated every 5 minutes - what is a good number of autovacuum processes to have on these? The current server I am replacing only has 3 of them but I think I may gain a benefit from having more.

Currently I have what I believe to be an aggressive bgwriter setting as follows:

bgwriter_delay = 200ms # 10-10000ms between rounds

bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round

bgwriter_lru_multiplier = 10 # 0-10.0 multipler on buffers scanned/round

Does this look right?

I have the following settings:

work_mem = 64MB # min 64kB

maintenance_work_mem = 128MB # min 1MB

And, of course, some of the most critical ones - the WAL settings. Right now, in order to give the best performance to the end users due to the size of the current box, I have a very unoptimal setting in my opinion

fsync = off # turns forced synchronization on or off

#synchronous_commit = on # immediate fsync at commit

#wal_sync_method = fsync # the default is the first option

# supported by the operating system:

# open_datasync

# fdatasync

# fsync

# fsync_writethrough

# open_sync

full_page_writes = on # recover from partial page writes

wal_buffers = 16MB

#wal_buffers = 1024KB # min 32kB

# (change requires restart)

# wal_writer_delay = 100ms # 1-10000 milliseconds

#commit_delay = 0 # range 0-100000, in microseconds

#commit_siblings = 5 # range 1-1000

# - Checkpoints -

#checkpoint_segments = 128 # in logfile segments, min 1, 16MB each

checkpoint_segments = 1024

checkpoint_timeout = 60min # range 30s-1h

#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0

checkpoint_completion_target = 0.1

checkpoint_warning = 45min # 0 disables

These are values which I arrived to by playing with them to make sure that the end user performance did not suffer. The checkpoints are taking about 8 minutes to complete, but between checkpoints the disk i/o on the data partition is very minimal - when I had lower segments running a 15 minute timeout with a .9 completion target, the platform was fairly slow vis-à-vis the end user.

The above configuration is using PG 8.4.

Thanks in advance for any insight.