Re: PostgreSQL performance issues

That's exactly what I'm experiencing.

Everything was fine until yesterday, when we noticed a considerable site
slow-down. Graphs showed the server suddenly spiking to a load of 67. At
first I thought somebody executed a ran-away query, so I restarted
postgres, but after it came back up, it climbed back up to this load.

In the meanwhile I've applied some table level optimizations and the
postgres.conf optimizatrions ... nothing

Here's the vmstat output, since reboot last night

[root(at)srv1 ~]# vmstat -a
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free inact active si so bi bo in cs us sy
id wa
27 0 0 595312 248100 2962764 0 0 8 31 105 7 63
2 35 0
[root(at)srv1 ~]# vmstat -d
disk- ------------reads------------ ------------writes-----------
-----IO------
total merged sectors ms total merged sectors ms
cur sec
ram0 0 0 0 0 0 0 0 0
0 0
ram1 0 0 0 0 0 0 0 0
0 0
ram2 0 0 0 0 0 0 0 0
0 0
ram3 0 0 0 0 0 0 0 0
0 0
ram4 0 0 0 0 0 0 0 0
0 0
ram5 0 0 0 0 0 0 0 0
0 0
ram6 0 0 0 0 0 0 0 0
0 0
ram7 0 0 0 0 0 0 0 0
0 0
ram8 0 0 0 0 0 0 0 0
0 0
ram9 0 0 0 0 0 0 0 0
0 0
ram10 0 0 0 0 0 0 0 0
0 0
ram11 0 0 0 0 0 0 0 0
0 0
ram12 0 0 0 0 0 0 0 0
0 0
ram13 0 0 0 0 0 0 0 0
0 0
ram14 0 0 0 0 0 0 0 0
0 0
ram15 0 0 0 0 0 0 0 0
0 0
sda 197959 38959 4129737 952923 777438 1315162 16839981
39809324 0 2791
fd0 0 0 0 0 0 0 0 0
0 0
md0 0 0 0 0 0 0 0 0
0 0

Luke Lonergan wrote:
> Interesting - in this quick snapshot there is no I/O happening at all.
> What happens when you track the activity for a longer period of time?
>
> How about just capturing vmstat during a period when the queries are
> slow?
>
> Has the load average been this high forever or are you experiencing a
> growth in workload? 463 processes all doing CPU work will take 100x as
> long as one query on a 4 CPU box, have you worked through how long you
> should expect the queries to take?
>
> - Luke
>
>
>> -----Original Message-----
>> From: Willo van der Merwe [mailto:willo(at)studentvillage(dot)co(dot)za]
>> Sent: Wednesday, August 30, 2006 4:35 AM
>> To: Luke Lonergan
>> Cc: Merlin Moncure; pgsql-performance(at)postgresql(dot)org
>> Subject: Re: [PERFORM] PostgreSQL performance issues
>>
>> 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
>>
>> Without any apparent effect.
>>
>>
>>
>
>
>