Re: random slow query

Hi Sean,

Well, the overall impression is your machine is badly overloaded. Look:

> top - 10:18:58 up 224 days, 15:10, 2 users, load average: 6.27, 7.33, 6
>
The load average of 6.5 means there are six and a half processes
competing for the same CPU (and this system apparently has only one).
This approximately equals to 500% overload.

Recommendation: either add more CPU's or eliminate process competition
by moving them to other boxes.

> Tasks: 239 total, 1 running, 238 sleeping, 0 stopped, 0 zombie
>
This supports what I said above. There are only 92 processes running on
my laptop and I think it is too much. Do you have Apache running on the
same machine?

> Cpu(s): 5.0%us, 0.7%sy, 0.0%ni, 61.5%id, 32.7%wa, 0.0%hi, 0.1%si, 0
>
Waiting time (wa) is rather high, which means processes wait on locks or
for IO, another clue for concurrency issues on this machine.

> Mem: 32962804k total, 32802612k used, 160192k free, 325360k buffers
>
Buffers are about 10% of all the memory which is OK, but I tend to give
buffers some more room.

Recommendation: eliminate unneeded processes, decrease (yes, decrease)
the Postgres cache buffers if they are set too high.

> Swap: 8193140k total, 224916k used, 7968224k free, 30829456k cached
>
200M paged out. It should be zero except of an emergency. 3G of cached
swap is a sign of some crazy paging activity in thepast. Those
unexplainable slowdowns are very likely caused by that.

> Didn't really see the pattern, typical the cpu load is only about 40%
>
40% is too much, really. I start worrying when it is above 10%.

Conclusion:

- the system bears more load than it can handle
- the machine needs an upgrade
- Postges is competing with something (presumably Apache) - separate them.

That should help.

Cheers,
Mike