MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Gunnar R. Date:January 8 2008 11:46am
Subject:Re: Performance problem - MySQL at 99.9% CPU
View as plain text  
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=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