mysqld hogging CPU like crazy

Running a CentOS-5.0 image on a Linode 540 on the Atlanta DC (atlanta43). mysqld is hogging CPU like crazy. Consuming anywhere from 99% to 250% (or more) CPU has now become the norm for mysqld. I have tried all mysql optimisation tricks I could find. No relief. I've also cut down the limit on processes Apache can spawn to serve requests. Persistent connections is enabled. I am simply stumped.

A while back the Linode crashed when the kernel invoked the oom-killer. The oom-killer, trying to reclaim memory, took down the entire system. Not completely sure whether it has happened again since that time, but I'm ripping my way through logs.

Clues, suggestions? I'd really appreciate any I can get. Thanks!

Ayaz Ahmed Khan

13 Replies

@ayaz:

Clues, suggestions? I'd really appreciate any I can get. Thanks!

Is mysql going crazy or are your sites just being hit hard?

You enabled and checked the slow query log, also enabled the queries with no index's log?

Just takes 1 query to slow the whole boat down….

Also you looked into and properly using the mysql cache?

@josephb:

Is mysql going crazy or are your sites just being hit hard?

The website gets traffic, but I wouldn't call it heavy. On average, it receives around 43K hits per day. There is nothing else running. The system serves a standard WordPress installation.

I don't know why but mysqld shouldn't be claiming that much CPU that much of the time. Have I bumped into a limitation of the Linode/Xen?

Ayaz Ahmed Khan

@ayaz:

The system serves a standard WordPress installation.

Are you running the WP-Super-Cache plugin? If not, you certainly should be. It's one of the few "standard" plugins I require people run if they're using WP on my server.

@Wizzard:

You enabled and checked the slow query log, also enabled the queries with no index's log?

Just takes 1 query to slow the whole boat down….

Also you looked into and properly using the mysql cache?

Thanks. Yup, all that you mention is enabled and running, except the 'no index log' part. I'll follow up with that.

Ayaz Ahmed Khan

@anderiv:

Are you running the WP-Super-Cache plugin? If not, you certainly should be. It's one of the few "standard" plugins I require people run if they're using WP on my server.

Thanks, Anderiv. Not the WP-Super-Cache plugin, but WP-Cache is plugged in. Also, the static content from WP has been moved such that it is now being served by another host altogether (not part of the Linode). This was done desperately in the hopes that the CPU usage may stablise. But, no dice.

Ayaz Ahmed Khan

@ayaz:

The website gets traffic, but I wouldn't call it heavy. On average, it receives around 43K hits per day. There is nothing else running. The system serves a standard WordPress installation.

One of the sites I host has 150k uniques and 2million hits a month, my Linode 540 doesn't even blink.

I'd be enabling some mysql caching, and query logging to see where things are going wrong.

@ayaz:

Have I bumped into a limitation of the Linode/Xen?

Ayaz Ahmed Khan
Don't think so. I run a site on the same server, atlanta43 and heavily use a table that has up to 1'500,000 uniques. I will probably get around 4k hits per day. The 540 linode, same as said by josephb, doesn't "even blink". You are missing something. This is surely not a mysql or Linode/Xen bug nor a specific problem with atlanta43.

Id be interested to see the my.cnf and apache/http configs for those of you that said you get 10k+ hits a day.. Just for my own corisoty, to see how yours is optimised vs mine :)

search the mysql bug database.

i remember reading about this problem a while ago where a buggy startup script would make mysqld spin the CPU at 100%.

i don't have a link right now, will update this thread if i find it.

same here, using Debian Etch :(

Edit: fixed - It was because of crazy mysql query in WordPress plugin.

![](" /> Fixed at 20h :oops: ..what can make one DISTINCT :)~~~~

I'm having a slow mysql issue too!

I'm re-importing a 250MB mysqldump file and my problem is Disk I/O is slowing me down. It seems to go in short bursts and then waits for a bit and then does some more.

According to my stats I'm at about 2.0K for Disk IO.

Is that a high figure for a linode?

Is there any way to speed this up or is this normal?

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct