up vote 2 down vote favorite
share [g+] share [fb]

I have a dedicated server with 4gb of ram and a Quad core Xeon cpu, serving a site with 350k pageviews a day using the wordpress script. The problem is, mysql is eating loads of cpu, and on investigation I found that it was handling 250 queries per second. This equates to 60 queries per page load, which cannot be correct. On top of this, most pages are cached using W3 Total Cache. I have several apps accessing the database, but how do I know which one is causing the high query usage?

link|improve this question
note that 250 IOPS is the limit of most HDs, i wonder if that's a coincidence – Javier Dec 7 '10 at 20:12
Hmm well I would have thought most stuff would be cached in memory. And that doesn't explain why theres 250 in the first place. – ddlshack Dec 7 '10 at 20:15
Another thing, the Handler_read_rnd_next value is 2,902.81 M, and the Handler_read_rnd is 771 M. – ddlshack Dec 7 '10 at 20:18
feedback

4 Answers

up vote 1 down vote

MySQL has a statement called "show processlist" to show you the running queries on your MySQL server.

link|improve this answer
That comes up with only 3-5 processes, most of them sleeping, which seems weird when theres 250 per second according with phpmyadmin. – ddlshack Dec 7 '10 at 20:16
feedback
up vote 0 down vote

For mysql issues setup slow queries log and do profiling using mysqltuner.pl and mysql tuning primer. This fixes most mysql problems. Analyze slow queires and check if you can add more indexes.

Don;t forget to use innodb as engine and especially innodb buffer pool size variable.

link|improve this answer
Oh, default wordpress storage engine is MyISAM. Do you suggest changing them to InnoDB? Whats the best one for cpu performance? – ddlshack Dec 7 '10 at 20:32
You should ban MyISAM if you have more than 5 concurrent connections... It's simply not made for this, so yes, switching to InnoDB could be the solution, and don't forget to add indexes on the foreign keys (or recreate the foreign keys, because MyISAM drop them by default..), try to change the default table engine to InnoDB for futures tables creations. – Kedare Dec 7 '10 at 22:50
switch to innodb, is better than myisam. – Paul Dec 8 '10 at 7:15
feedback
up vote 0 down vote

Why not go with something like WP Super Cache which will significantly reduce the amount of queries going to your database server?

link|improve this answer
1  
I said I use W3 Total Cache in my first post. – ddlshack Dec 7 '10 at 22:13
D'oh! Forget I said anything. Sorry about that. – phuzion Dec 15 '10 at 17:11
feedback
up vote 0 down vote

Enable slow query log and set the long_query_time variable. That will log all the queries which take more time than the specified time.

Then use the mysqldumpslow tool to analyse the slow query log.

For more information refer to the mysql documentation.

http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.