Chris Kratz wrote:

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.

If everything is cached in ram, it's pretty easy to be CPU bound. You
very easily could be at this point if your database is only 2.6G and you
don't touch all the tables often.

I do believe that when CPU bound, the best thing to do is get faster CPUs.

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?


On this particular development server, we have:

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

I'm very surprised you are doing RAID 0. You realize that if 1 drive
goes out, your entire array is toast, right? I would recommend doing
either RAID 10 (0+1), or even Raid 5 if you don't do a lot of writes.

Probably most important, though is to look at the individual queries and
see what they are doing.

Postgresql 7.4.5 installed via RPM running on Linux kernel

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
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

Most of these seem okay to me, but random page cost is *way* too low.
This should never be tuned below 2.  I think this says "an index scan of
*all* rows is as cheap as a sequential scan of all rows." and that
should never be true.

What could actually be happening is that you are getting index scans
when a sequential scan would be faster.

I don't know what you would see, but what does "explain analyze select
count(*) from blah;" say. If it is an index scan, you have your machine
mistuned. select count(*) always grabs every row, and this is always
cheaper with a sequential scan.


Attachment: signature.asc
Description: OpenPGP digital signature

Reply via email to