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?
-Chris
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 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
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.
John =:->
signature.asc
Description: OpenPGP digital signature
