Mysqld crazyness: using CPU's 80-120% all the time.

First, SHOW FULL PROCESSLIST - maybe you have a ton of bots hammering your website, and the DB traffic is legitimate.

Then…

http://dev.mysql.com/doc/refman/5.1/en/ … y-log.html">http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

longquerytime=1

slowquerylog=1

slowquerylog_file=/somewhere/where/you/can/store/a/huge/file

You can enable them all without restarting mysqld with the appropriate SET GLOBAL commands.

Let the file grow a bunch, turn the thing off, and look to see what queries are problematic. Then put them thru EXPLAIN, and try to optimize.

Been there, done that, dropped average mysql load from 40% to 1% (if I'll ever get the guy who forgot to put indexes in that table… >.<)

Also, if it doesn't log a thing, try longquerytime=0.5 or =0.25.

Can you post a screenshot of the top command?

Also, do you have any caching plugin installed in your WordPress blog? It is considered very bad practice these days to run WordPress without a caching plugin. There are a few good ones out there, includng WP Super Cache and WP Total Cache. Try them.

Here's a capture of what's going on right now:

__top - 09:33:44 up 1 day, 11:15, 1 user, load average: 0.33, 0.32, 0.35

Tasks: 108 total, 3 running, 105 sleeping, 0 stopped, 0 zombie

Cpu(s): 17.2%us, 3.5%sy, 0.0%ni, 79.2%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st

Mem: 1549180k total, 1430404k used, 118776k free, 653612k buffers

Swap: 262136k total, 820k used, 261316k free, 528164k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

1894 mysql 20 0 179m 90m 6052 S 108 6.0 821:10.17 mysqld

10137 www-data 20 0 88472 34m 19m S 9 2.3 0:47.24 php-cgi

9858 www-data 20 0 88976 34m 19m R 9 2.3 0:44.30 php-cgi

10328 www-data 20 0 88420 33m 18m R 7 2.2 0:24.72 php-cgi

10202 www-data 20 0 88556 34m 19m S 7 2.3 0:37.87 php-cgi

10452 www-data 20 0 88992 33m 18m S 7 2.2 0:12.46 php-cgi

1964 www-data 20 0 182m 26m 2700 S 0 1.8 5:19.37 cherokee-worker

1 root 20 0 2680 1460 1200 S 0 0.1 0:00.71 init

2 root 20 0 0 0 0 S 0 0.0 0:00.00 kthreadd

3 root RT 0 0 0 0 S 0 0.0 0:00.08 migration/0

4 root 20 0 0 0 0 S 0 0.0 0:00.18 ksoftirqd/0

5 root RT 0 0 0 0 S 0 0.0 0:00.12 migration/1

6 root 20 0 0 0 0 S 0 0.0 0:00.07 ksoftirqd/1

7 root RT 0 0 0 0 S 0 0.0 0:00.13 migration/2

8 root 20 0 0 0 0 S 0 0.0 0:00.03 ksoftirqd/2

9 root RT 0 0 0 0 S 0 0.0 0:00.11 migration/3

10 root 20 0 0 0 0 S 0 0.0 0:00.00 ksoftirqd/3

11 root 20 0 0 0 0 S 0 0.0 0:00.01 events/0

12 root 20 0 0 0 0 S 0 0.0 0:00.14 events/1

13 root 20 0 0 0 0 S 0 0.0 0:00.02 events/2

14 root 20 0 0 0 0 S 0 0.0 0:00.02 events/3

15 root 20 0 0 0 0 S 0 0.0 0:00.02 khelper

19 root 20 0 0 0 0 S 0 0.0 0:00.00 async/mgr

22 root 20 0 0 0 0 S 0 0.0 0:00.00 xenwatch

23 root 20 0 0 0 0 S 0 0.0 0:00.00 xenbus__

And yes, I've got W3 Total Cache running on the blog, but it seems to make apparently no difference if I enable or disable it on my mysqld load on the CPU.

@rsk: thx for the suggestions. In fact I activated the slow queries, but I don't know exactly what to do with the output. I've seen some queries that take 2-3 secs (didn't leave it activated too long), but I don't lnow what to do to study that. How do I use the EXPLAIN feature you mention? Could you be a little more specific?

I mean, what do I do if I find slow queries? How to correct them? Could you explain how could you drop the average CPU load? That would be really useful for me :)

Thx!

Keep in mind that linode notification thresholds don't mean that you're in trouble. 100% CPU usage is actually only one quarter of the maximum (400% because you've got access to four virtual CPUs). For example, top summarizes it; in that "screenshot", you've got 79.2% idle CPU.

I'd say that you're not giving MySQL very much RAM considering that you're on a 1536 (you're using ~300MB of RAM, the other ~1200MB are free), but while giving it more RAM would improve performance, it wouldn't solve the issue you're currently having.

Is your site 100% wordpress, or is there anything else running against that MySQL server? Any tables that aren't properly indexed? Doing a SELECT against an unindexed table can cause massive CPU load even if there are only a few thousand records.

Open your favourite mysql client (commandline mysql, HeidiSQL, whatever), paste one of the slow queries there, and type EXPLAIN in front of it. Like, "EXPLAIN SELECT column1, col2…". Run. You'll get a report on how's the query gonna be handled.

After that, well, it's a matter of understanding the output and the table, and finding a way (if if one exists) to make the query run faster… good thing to start with is creating indexes on the columns used in the where clause, if they don't exist already. Of course, sometimes it's not that easy, but it's a good start. In other cases, you may need to rewrite the issued query for a more optimal one, or such… but I guess that'd be better asked on SQL-specific or your-software-specific forums.

http://dev.mysql.com/doc/refman/5.1/en/ … ation.html">http://dev.mysql.com/doc/refman/5.1/en/optimization.html

http://dev.mysql.com/doc/refman/5.1/en/ … plain.html">http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

Disclaimer: I am not a DBA, I just sometimes succeed in Making Things Go.

@Guspaz: the VPS runs just a WordPress blog, yes. It has 2.000 posts, over 32k comments, and, as I said, gets between 15k and 20k pageviews a day.

@rsk: thanks for the guidelines, I'll try to get further information about that, and will use the EXPLAIN command to try to detect what the problems are.

It seems the indexing could be a problem, but I thought WP would take care of that while "building" its own database.

I'll take a good look at the links, and I hope I can solve the problems with that help… or at least, get a little further on a possible solution.

Thx again!

mysqld uses less CPU on a 64-bit system. You might want to create a 64-bit app-server and connect to it via the private network.

keep top running a little and see if the io wait changes.

also run hdparm -tT /dev/xvda just to have an idea of how the disk is performing.

Given that your traffic is not that big, if mysql uses too much cpu it may also be because of (maybe temporary) io issues - remember that your Linode is not alone on that disk subsystem :)

also:

• try to optimize tables

• what is the current value for max connections? does your php log show errors?

• which engine are you using for your tables? if innodb, try to switch to innodb plugin and ignore the builtin innodb support

• if you're using innodb, set a good innodb buffer pool size!!

• since you're using total cache, are you using memcached or apc to cache database queries as well?

• are you using wordpress' builtin comments? if yes, switch to Disqus or IntenseDebate; this will prevent your cached pages from being invalidated each time a new comment is posted, thus helping relief mysql

• try mysqltuner.pl to see if it recommends some configuration changes

Go to wp-includes/comment-template.php and find where WordPress gets your comments. WordPress doesn't "LIMIT" comment SELECTs. So if you have 10,000 comments on a post, WordPress loads all of them by default–last I checked. Anyway, if you only need to show 50 comments per page, just add "LIMIT 50" to the end of those three SELECTs in comment-template and I think you'll see a big improvement.