Mysqld crazyness: using CPU's 80-120% all the time.
I've got a Linode 1536 and a single installation of WordPress 3.0.1. My Linode is running Ubuntu LTS 10.04.1, and I've installed Cherokee as web server, and Mysql 5.1.41 is running also there.
My blog is not a huge one: it gets around 15-20k pageviews/day (around 500k per month), but although in the beginning the linode (it was a 1024 one) was running fine, in the past weeks I've been receiven messages like this all the time:
> _"Your Linode, linode57172, has exceeded the notification threshold (90) for CPU Usage by averaging 104.7% for the last 2 hours."
I'm trying to investigate the causes, but I found none:
There's no spike in pageviews or unique visitors.
I've not installed any new plugins or new versions of the "LAMP" stack.
And the responsible for this high CPU usage is no other that mysqld. When I execute the 'top' command I get a %CPU that most of the time goes between 80 and 120. That's really ackward for me, and I'm trying to find the reasons.
I've read the forums carefully and I've googled for mysqld and CPU consumption problems, but I don't find any answer. Here's some of the most relevant data (to my knowledge) of my /etc/mysql/my.cnf:
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
key_buffer = 16M
maxallowedpacket = 16M
thread_stack = 192K
threadcachesize = 8
querycachetype = 1
querycachelimit = 1M
querycachesize = 64M__
I've been trying to 'play' with those parameters, but I haven't found if there's a magical combo, or if I am doing something really wrong… or if I even have to look elsewhere.
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.
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.
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
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.
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.
Disclaimer: I am not a DBA, I just sometimes succeed in Making Things Go.
@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.
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
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