Hi Matthew,
I know exactly what you experience.
We had a 4-way DC Opteron and Pg 7.4 too.
You should monitor context switches.
First suggest upgrade to 8.2.5 because the scale up is much better with 8.2.
You need to limit the number of concurrent queries to less than 8 (8
cores) if you need to stay with Pg 7.4.
The memory setting is looking good to me. I would increase sort_mem and
effective_cache_size, but this would solve your problem.
Best regards
Sven.
Matthew Lunnon schrieb:
> Hi,
>
> I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running
> postgres 7.4.3. This has been recompiled on the server for 64 stored
> procedure parameters, (I assume this makes postgres 64 bit but are not
> sure). When the server gets under load from database connections
> executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to
> limit at about 30-35% usage with no iowait reported. If I run a simple
> select at this time it takes 5 seconds, the same query runs in 300
> millis when the server is not under load so it seems that the database
> is not performing well even though there is plenty of spare CPU. There
> does not appear to be large amounts of disk IO and my database is about
> 5.5G so this should fit comfortably in RAM.
>
> changes to postgresql.sql:
>
> max_connections = 500
> shared_buffers = 96000
> sort_mem = 10240
> effective_cache_size = 1000000
>
> Does anyone have any ideas what my bottle neck might be and what I can
> do about it?
>
> Thanks for any help.
>
> Matthew.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
--
Sven Geisler <sgeisler@xxxxxxxxxx> Tel +49.30.921017.81 Fax .50
Senior Developer, AEC/communications GmbH & Co. KG Berlin, Germany
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
