Hi all,

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:

Quote:

"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:

user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 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.

Any ideas?

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

long_query_time=1
slow_query_log=1
slow_query_log_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 long_query_time=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/ ... plain.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.

_________________
PHP Development @ Tomakefast