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.
