MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Erik Giberti Date:January 4 2008 4:55am
Subject:Re: Performance problem - MySQL at 99.9% CPU
View as plain text  
Gunnar,

us = user (things like MySQL/PHP/Apache)
sy = system (memory management / swap space / threading / kernel  
processes and so on)
ni = nice (apps running only when nothing else needs the resource)
id = idle (extra cpu cycles being wasted)
wa = wait state (io wait for disk/network/memory)
hi & si - interrupts

Generally acceptable load should be < #processors (so in your case 2  
is okay - machine is performing well - 4 somethings being over  
utilized somewhere)
Also in top 100% = 100% of one processor, so in a dual processor (or  
core) setup, you can actually go to 200%

Your userland apps are taking up 61.3 + 57.0 (118.3% of 200% or 59%  
overall) of system resources.
Your system processes are taking up 66.2% (of 200% or 33% overall)
and it's leaving about 14% (of 200% - so 7% overall) of the system idle.
The remainders are I/O waits etc (your numbers look pretty good there,  
but IO wait can spike and so may be misleading without using other  
tools.

You may be encountering a thrashing problem with the amount of memory  
left or any number of things, but I would look at memory use on this  
box, because your load is pretty high and your performance is  
suffering if it's staying there. Your memory is at about 92% utilized  
too... while 91Mb seems like a lot of memory - it's easily consumed by  
a couple of large queries, sorts and so on which then goes right to  
disk swapping for virtual memory - never good for performance. It  
might also be impacted by IO and you just can't see it in the one  
slice of top we have here. If that number spikes up to 5% and then  
falls back down - it might be time spent going to disk with temp  
tables etc.

Also turn on slow query logging (yes, I know it's another performance  
hit) and see if there is one query that's particularly problematic,  
perhaps optimizing the indexes etc on the table might help with the  
performance.

Also, make sure your HD's aren't full... that will kill performance  
very quickly if the needed disk space isn't there.

Erik


On Jan 3, 2008, at 3:44 PM, Gunnar R. wrote:

> Hello,
>
> Thanks. I read the document, but unfortunately it didn't tell me  
> anything
> new..
>
> One of the things I am a bit confused about is:
>
> top - 22:08:12 up 6 days,  7:23,  1 user,  load average: 4.36, 3.30,  
> 2.84
> Tasks: 134 total,   1 running, 133 sleeping,   0 stopped,   0 zombie
> Cpu0  : 61.3% us, 29.1% sy,  0.0% ni,  7.9% id,  0.7% wa,  0.3% hi,   
> 0.7% si
> Cpu1  : 57.0% us, 37.1% sy,  0.0% ni,  6.0% id,  0.0% wa,  0.0% hi,   
> 0.0% si
> Mem:   1034280k total,   942780k used,    91500k free,    34252k  
> buffers
> Swap:  2031608k total,      104k used,  2031504k free,   278788k  
> cached
>
>  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
> 2410 mysql     15   0  470m 310m 4464 S 99.9 30.8   4200:25 mysqld
>
> How come the CPUs can have idle time even though mysqld is running at
> 99.9%, AND there's a processor queue (4.36)?
>
> Cheers,
>
> Gunnar R.
>
> On ons, januar 2, 2008, 13:07, Andrew Braithwaite wrote:
>> Hi,
>>
>> If you can follow this document:
>>
>> http://www.ufsdump.org/papers/uuasc-june-2006.pdf
>>
>> You should be able to figure out what's happening.
>>
>> Cheers,
>>
>> Andrew
>>
>> -----Original Message-----
>> From: Gunnar R. [mailto:gr@stripped]
>> Sent: Tue, 01 January 2008 23:31
>> To: mysql@stripped
>> Subject: Performance problem - MySQL at 99.9% CPU
>>
>> Hello,
>>
>> I am running a community site mainly based on phpBB. It has about  
>> 9.300
>> registered users, 650.000 posts and about 200.000 visitors/month (12
>> mill
>> "hits"). The SQL database is about 700MB.
>>
>> It's all running on a couple of years old Dell box with two P4 Xeon
>> 1.7Ghz
>> CPUs, 1GB of RAMBUS memory and SCSI disks, with Linux and Apache.
>>
>> The last year the server has been having huge performance problems,  
>> and
>> MySQL (5.0.45) seems to be the problem. It's almost constantly  
>> running
>> at
>> 99.9% CPU ("measured" using 'top').
>>
>> I know the hardware isn't too hot, but either way I am a bit  
>> confused by
>> the
>> fact that I can't seem to get MySQL to run smoothly. Is this just too
>> big a
>> database for this kind of box, or could this be a configuration  
>> issue?
>>
>> I am thinking about buying a new dual core box (with IDE disks?),  
>> but I
>> have
>> to make sure this really is a hardware issue before I spend  
>> thousands of
>> bucks.
>>
>> Any help will be hugely appreciated!
>>
>> Cheers,
>>
>> Gunnar
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=1
>>
>>
>>
>> LOVEFiLM International Limited is a company registered in England and
>> Wales. Registered Number: 04392195. Registered Office: No.9, 6  
>> Portal Way,
>> London W3 6RU, United Kingdom.
>>
>> This e-mail is confidential to the ordinary user of the e-mail  
>> address to
>> which it was addressed. If you have received it in error, please  
>> delete it
>> from your system and notify the sender immediately.
>>
>> This message has been scanned for viruses by BlackSpider  
>> MailControl -
>> www.blackspider.com
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>
>
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>

Thread
Performance problem - MySQL at 99.9% CPUGunnar R.2 Jan
  • Re: Performance problem - MySQL at 99.9% CPUBaron Schwartz2 Jan
  • Re: Performance problem - MySQL at 99.9% CPUAdy Wicaksono2 Jan
    • Re: Performance problem - MySQL at 99.9% CPUGunnar R.3 Jan
  • Re: Performance problem - MySQL at 99.9% CPUPer Jessen2 Jan
    • RE: Performance problem - MySQL at 99.9% CPUEric Frazier2 Jan
  • RE: Performance problem - MySQL at 99.9% CPUAndrew Braithwaite2 Jan
    • RE: Performance problem - MySQL at 99.9% CPUGunnar R.3 Jan
      • Re: Performance problem - MySQL at 99.9% CPUErik Giberti4 Jan
        • Re: Performance problem - MySQL at 99.9% CPUGunnar R.8 Jan
        • Re: Performance problem - MySQL at 99.9% CPUGunnar R.8 Jan
          • Re: Performance problem - MySQL at 99.9% CPUKevin Hunter8 Jan
          • Re: Performance problem - MySQL at 99.9% CPUErik Giberti9 Jan
Re: Performance problem - MySQL at 99.9% CPUKevin Hunter9 Jan