Extremely high mysqld CPU usage with no active queries

Question

I have a VPS running Ubuntu 12.04 LTS with LEMP stack, followed the guide from Linode Library (since I'm using a Linode) to setup, and everything worked fine until now.

I don't know what's wrong, but my CPU usage just goes up since a week ago. Today things getting really bad - I got 74% CPU usage so I went check and found that mysqld taking too much CPU usage (somewhere around 30% ~ 80%)

So I did some Google Search, tried disable InnoDB, restart mysql, reset ntp / system clock (Isn't this bug supposed to happen more than a year ago?!) and reboot my VPS, nothing helped. Even with mysql processlist empty, I still get mysqld CPU usage very high.

I don't know what I missed and have totally no idea, any advice would be appreciated.

Thanks in advance.

Update:

I got these from running "strace mysqld"

write(2, "InnoDB: Unable to lock ./ibdata1"..., 44) = 44
write(2, "InnoDB: Check that you do not al"..., 115) = 115
select(0, NULL, NULL, NULL, {1, 0}^[[A^[[A)     = 0 (Timeout)
fcntl64(3, F_SETLK64, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0}, 0xbfa496f8) = -1 EAGAIN (Resource temporarily unavailable)

hum... I did tried to disable InnoDB and it didn't fix this problem. Any idea?

Update2:

# ps -e | grep mysqld
13099 ?        00:00:20 mysqld

then use "strace -p 13099", the following lines appears repeatedly:

fcntl64(12, F_GETFL)                    = 0x2 (flags O_RDWR)
fcntl64(12, F_SETFL, O_RDWR|O_NONBLOCK) = 0
accept(12, {sa_family=AF_FILE, NULL}, [2]) = 14
fcntl64(12, F_SETFL, O_RDWR)            = 0
getsockname(14, {sa_family=AF_FILE, path="/var/run/mysqld/mysqld.sock"}, [30]) = 0
fcntl64(14, F_SETFL, O_RDONLY)          = 0
fcntl64(14, F_GETFL)                    = 0x2 (flags O_RDWR)
setsockopt(14, SOL_SOCKET, SO_RCVTIMEO, "\36\0\0\0\0\0\0\0", 8) = 0
setsockopt(14, SOL_SOCKET, SO_SNDTIMEO, "<\0\0\0\0\0\0\0", 8) = 0
fcntl64(14, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(14, SOL_IP, IP_TOS, [8], 4)  = -1 EOPNOTSUPP (Operation not supported)
futex(0xb786a584, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0xb786a580, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1
futex(0xb7869998, FUTEX_WAKE_PRIVATE, 1) = 1
poll([{fd=10, events=POLLIN}, {fd=12, events=POLLIN}], 2, -1) = 1 ([{fd=12, revents=POLLIN}])

er... now I totally don't get it x_x help

Solution

From your result of strace, your mysql server was accepting connections. The poll calls mean waiting for a connection and the accept calls mean accepting the incoming connection. So there must be a lot of connections and queries.

You said SHOW PROCESSLIST returned only the process you was querying process list. In fact, this command can only show connections at a particular instant when it is called. Popular PHP sites often do not support presistant MySQL connection. And often a query doesn't take much time but only CPU.

I think you should check all programs/sites which connects to your MySQL server. Maybe you can turn MySQL query logging on for more details.

Comments

[HTTP500]

strace the mysqld process to get clues as to what it's doing.

[Regan]

Find the pid of the mysqld process, and then strace -p <pid>; the message you got acts like you tried to start another instance of mysqld

[Sibin Grasic]

C/P your my.cnf to pastebin. Also, would help us if you gave some system specs

[RadarNyan]

As I said, there's no active queries at all. SHOW PROCESSLIST; just returns nothing except itself.

[HTTP500]

Try running this: "mysqladmin -u root -p -i 1 processlist". That might show you short-lived connections that you're otherwise not seeing with a manual SHOW PROCESSLIST