Re: Performance Tuning - Mailing list pgsql-performance

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