2

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?

3
  • note that 250 IOPS is the limit of most HDs, i wonder if that's a coincidence
    – Javier
    Dec 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. Dec 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. Dec 7, 2010 at 20:18

4 Answers 4

1

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

1
  • 1
    That 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
0

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.

3
  • 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.
    – Kedare
    Dec 7, 2010 at 22:50
  • switch to innodb, is better than myisam.
    – Paul
    Dec 8, 2010 at 7:15
0

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

2
  • 1
    I said I use W3 Total Cache in my first post. Dec 7, 2010 at 22:13
  • D'oh! Forget I said anything. Sorry about that.
    – phuzion
    Dec 15, 2010 at 17:11
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .