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?
Notihing - it's the developement DB and is dedicated to the development
website - which has a total number of users of '1' ;-)
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.
I'll give this a try.
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
That might have some impact on the production server (which is also
running PG - but the old DB and RT3) however the new DB is only me in
devel, so I think that it will not have much of an effect (I'll still
try it though)
Regards,
Mat
