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

You might do some more investigating on these to see if there is an  
index you could use to speed these up, 15.8 million records might be a  
full table scan, even if it's not - it's clearly a whole heck of a lot  
of data and that's going to give you a huge performance hit. I'm not  
familiar with the app, but perhaps there's a lot of junk in this  
table? Also, if it's a high volume site, you might run into  
performance issues around locking with a bunch of writes coming in  
during that read if the underlying storage engine is MyISAM. Spend  
some time with explain and the slow queries to see if the indexing  
will help. MySQL has some good background info here
http://dev.mysql.com/doc/refman/5.0/en/explain.html

Second, you can try to allocate more server resources to MySQL by  
reducing the PHP/Apache load (presuming it's all on the same server.)  
If it's not, only the first suggestion below will be of use.

You might look at memcached ( http://www.danga.com/memcached/ ) if  
there are a number of queries that don't change all that often which  
can really reduce load on MySQL - giving it more resources to run  
these more dynamic and more resource intensive queries. Memcache  
stores objects in RAM so they're ready to use right away instead of a  
trip to MySQL - note that a MySQL resultset can't be inserted  
natively, it needs to be converted first - I have a result abstraction  
class I use that behaves like a mysqlResult, but is really an array of  
arrays that hold the MySQL data and let me page through it using  
standard object oriented code like $result->num_rows, $result- 
 >fetch_assoc() etc.

Also, if there are pages that can be flattened, you can save some  
system resources there - basically publishing static versions of the  
templates via script on a schedule to reduce computational load  
generating a page that only changes once a day or once an hour etc.  
This saves everywhere because Apache can serve a static HTML template  
much faster than php or any other language - reducing load on the  
server even further.

While this isn't a PHP list, you can also look at eAccelerator http://eaccelerator.net/ 
  for pre-compiling PHP code saving a bit of load on the server each  
runtime. They claim 1-10x reduction in server load (of course that's  
highly dependent on your code etc) but I have seen on a shared host  
machine I'm on my resource load drop in 1/2. Freeing up those  
resources for other tasks (like MySQL.)

Good luck!

Erik

On Jan 8, 2008, at 5:47 AM, Gunnar R. wrote:

> Thank you Erik!
>
> HDs are OK, a couple of GB free. Not that it's a lot, but I can't  
> imagine
> it being too low for MySQL..
>
> I'm aware memory is a bit low, but RAMBUS chips are hard to come by.  
> They
> don't have them in stock anywhere anymore. Also they are quite  
> expensive.
> It's almost like you could've bought 1/3rd of a new cheap Dell  
> server for
> 2x512MB RAMBUS. But if a new box for a reasonable price wouldn't be  
> any
> faster it's no use anyway...
>
> Concerning slow queries, it seems there's a couple of different  
> queries
> that's being logged.
>
> This is one, taking 66 seconds:
>
> # Query_time: 66  Lock_time: 0  Rows_sent: 0  Rows_examined: 15857680
> SELECT word_id
>                                FROM phpbb_search_wordmatch
>                                GROUP BY word_id
>                                HAVING COUNT(word_id) > 263916;
>
> Usual time for this seems to be from 12 to 66 seconds.
>
> And then there's this, usually taking 10-20 seconds:
>
> # Query_time: 12  Lock_time: 0  Rows_sent: 10  Rows_examined: 395960
> SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as
> id2, p.post_username, p2.post_username AS post_username2,  
> p2.post_time,
> f.forum_name
>                                FROM phpbb_topics t, phpbb_users u,
> phpbb_posts p, phpbb_posts p2, phpbb_users
> u2, phpbb_forums f
>                                WHERE t.topic_poster = u.user_id
>                                AND t.forum_id NOT IN (16, 17)
>                                AND p.post_id = t.topic_first_post_id
>                                AND p2.post_id = t.topic_last_post_id
>                                AND t.forum_id = f.forum_id
>                                AND u2.user_id = p2.poster_id
>
>
>
>
>                                AND t.topic_status <> 1
>                                AND t.topic_status <> 2
>
>                                ORDER BY t.topic_last_post_id DESC
>                                LIMIT 10;
>
>
> In the evenings there seems to be 10-20 slow queries every hour, time
> between them varying from seconds to usually 5-10 minutes.
>
> Cheers,
>
> Gunnar
>
>
> On fre, januar 4, 2008, 05:55, Erik Giberti wrote:
>> 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=giberti@stripped
>>>
>>
>>
>> --
>> 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