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?
-
note that 250 IOPS is the limit of most HDs, i wonder if that's a coincidence– JavierDec 7, 2010 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.– Cameron MartinDec 7, 2010 at 20:15
-
Another thing, the Handler_read_rnd_next value is 2,902.81 M, and the Handler_read_rnd is 771 M.– Cameron MartinDec 7, 2010 at 20:18
4 Answers
MySQL has a statement called "show processlist" to show you the running queries on your MySQL server.
-
1That comes up with only 3-5 processes, most of them sleeping, which seems weird when theres 250 per second according with phpmyadmin. Dec 7, 2010 at 20:16
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.
-
Oh, default wordpress storage engine is MyISAM. Do you suggest changing them to InnoDB? Whats the best one for cpu performance? Dec 7, 2010 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.– KedareDec 7, 2010 at 22:50
-
Why not go with something like WP Super Cache which will significantly reduce the amount of queries going to your database server?
-
1
-
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.