Performance Tuning

Hello All,

In contrast to what we hear from most others on this list, we find our
database servers are mostly CPU bound. We are wondering if this is because
we have postgres configured incorrectly in some way, or if we really need
more powerfull processor(s) to gain more performance from postgres.

We continue to tune our individual queries where we can, but it seems we still
are waiting on the db a lot in our app. When we run most queries, top shows
the postmaster running at 90%+ constantly during the duration of the request.
The disks get touched occasionally, but not often. Our database on disk is
around 2.6G and most of the working set remains cached in memory, hence the
few disk accesses. All this seems to point to the need for faster
processors.

Our question is simply this, is it better to invest in a faster processor at
this point, or are there configuration changes to make it faster? I've done
some testing with with 4x SCSI 10k and the performance didn't improve, in
fact it actually was slower the the sata drives marginally. One of our
developers is suggesting we should compile postgres from scratch for this
particular processor, and we may try that. Any other ideas?

-Chris

On this particular development server, we have:

Athlon XP,3000
1.5G Mem
4x Sata drives in Raid 0

Postgresql 7.4.5 installed via RPM running on Linux kernel 2.6.8.1

Items changed in the postgresql.conf:

tcpip_socket = true
max_connections = 32
port = 5432
shared_buffers = 12288 # min 16, at least max_connections*2, 8KB each
sort_mem=16384
vacuum_mem = 32768 # min 1024, size in KB
max_fsm_pages = 60000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 1000 # min 100, ~50 bytes each
effective_cache_size = 115200 # typically 8KB each
random_page_cost = 1 # units are one sequential page fetch cost