On 30-Aug-06, at 10:10 AM, Vivek Khera wrote:
On Aug 30, 2006, at 5:29 AM, Matthew Sullivan wrote:
The hardware is a Compaq 6400r with 4G of EDO RAM, 4x500MHz Xeons
and a Compaq RAID 3200 in RAID 5 configuration running across 3
spindles (34G total space).
The OS is FreeBSD 5.4-RELEASE-p14
The PG Version is 8.1.3
What else does this box do?
I think you should try these settings, which I use on 4GB dual
Opteron boxes running FreeBSD 6.x dedicated to Postgres only. Your
effective_cache_size seems overly optimistic for freebsd. cranking
up the shared buffers seems to be one of the best bangs for the
buck under pg 8.1. I recently doubled them and nearly tripled my
performance on a massive write-mostly (insert/update) load. Unless
your disk system is *really* slow, random_page_cost should be
reduced from the default 4.
Actually unless you have a ram disk you should probably leave
random_page_cost at 4, shared buffers should be 2x what you have
here, maintenance work mem is pretty high
effective cache should be much larger 3/4 of 4G or about 360000
Setting work _mem this high should be done with caution. From the
manual "Note that for a complex query, several sort or hash
operations might be running in parallel; each one will be allowed to
use as much memory as this value specifies before it starts to put
data into temporary files. Also, several running sessions could be
doing such operations concurrently. So the total memory used could be
many times the value of work_mem"
As you can see, I change *very* little from the default config.
shared_buffers = 70000 # min 16 or
max_connections*2, 8KB each
work_mem = 262144 # min 64, size in KB
maintenance_work_mem = 524288 # min 1024, size in KB
checkpoint_segments = 256
checkpoint_timeout = 900
effective_cache_size = 27462 # `sysctl -n
vfs.hibufspace` / 8192 (BLKSZ)
random_page_cost = 2
if you're feeling adventurous try these to reduce the checkpoint
impact on the system:
bgwriter_lru_percent = 2.0
bgwriter_lru_maxpages = 40
bgwriter_all_percent = 0.666
bgwriter_all_maxpages = 40
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. MailerMailer, LLC Rockville, MD
http://www.MailerMailer.com/ +1-301-869-4449 x806
