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:[EMAIL PROTECTED]
>>> Sent: Tue, 01 January 2008 23:31
>>> To: mysql@lists.mysql.com
>>> 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/[EMAIL PROTECTED]
>>>
>>>
>>>
>>> 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/[EMAIL PROTECTED]
>>>
>>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
