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 imagineit 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 anyfaster it's no use anyway...Concerning slow queries, it seems there's a couple of different queriesthat'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 asid2, 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% utilizedtoo... while 91Mb seems like a lot of memory - it's easily consumed bya 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 mysqldHow come the CPUs can have idle time even though mysqld is running at99.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.300registered users, 650.000 posts and about 200.000 visitors/month (12mill "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 thefact that I can't seem to get MySQL to run smoothly. Is this just toobig 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 andWales. 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/mysqlTo unsubscribe: http://lists.mysql.com/mysql? [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]
