Re: PostgreSQL performance issues

On 30-Aug-06, at 7:35 AM, Willo van der Merwe wrote:

> Luke Lonergan wrote:
>>> Currently the load looks like this:
>>> Cpu0  : 96.8% us,  1.9% sy,  0.0% ni,  0.3% id,  0.0% wa,  0.0%
>>> hi,  1.0% si
>>> Cpu1  : 97.8% us,  1.6% sy,  0.0% ni,  0.3% id,  0.0% wa,  0.0%
>>> hi,  0.3% si
>>> Cpu2  : 96.8% us,  2.6% sy,  0.0% ni,  0.3% id,  0.0% wa,  0.0%
>>> hi,  0.3% si
>>> Cpu3  : 96.2% us,  3.2% sy,  0.0% ni,  0.3% id,  0.0% wa,  0.0%
>>> hi,  0.3% si
>>>
>>
>> All four CPUs are hammered busy - check "top" and look for runaway
>> processes.
>>
>> - Luke
>>
>>
>>
> Yes, the first 463 process are all postgres. In the meanwhile I've
> done:
> Dropped max_connections from 500 to 250 and
> Upped shared_buffers = 50000

With 4G of memory you can push shared buffers to double that.
effective_cache should be 3/4 of available memory.

Can you also check vmstat 1 for high context switches during this
query, high being over 100k

Dave
>
> Without any apparent effect.
>
> ---------------------------(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
>