Re: random slow query

Hi Mike,

Thanks for the details. Yes, besides another mysql server running on
the same server, there is also an homegrown application that frequent
read/write the file system.

The postgres shared cache is at 4G, is that too big?

Thanks

Sean

On Tue, Jun 30, 2009 at 1:23 PM, Mike Ivanov<mikei(at)activestate(dot)com> wrote:
> 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
>
>