Linux – Extremely high thesqld CPU usage with no active queries

linuxMySQLubuntu-12.04

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

Best Answer

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.

Related Topic