I would like to get someone’s recommendations on the best initial settings for a dedicated PostgreSQL server. I do realize that there are a lot of factors that influence how one should configure a database. I am just looking for a good starting point. Ideally I would like the database to reside as much as possible in memory with no disk access. The current database size of my 7.x version of PostgreSQL generates a 6 Gig file when doing a database dump.
Dedicated PostgreSQL 8.2 Server
Redhat Linux 4.x AS 64 bit version (EM64T)
4 Intel Xeon Processors
20 Gig Memory
Current PostgreSQL database is 6 Gig file when doing a database dump
/etc/sysctl.conf file settings:
# 11 Gig
kernel.shmmax = 11811160064
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
postgresql.conf file settings (if not listed then I used the defaults):
max_connections = 300
shared_buffers = 10240MB
work_mem = 10MB
effective_cache_size = 512MB
maintenance_work_mem = 100MB
Any suggestions would be appreciated!
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu