Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
strace the mysqld process to get clues as to what it's doing. –  HTTP500 Oct 28 '13 at 19:37
    
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 –  Regan Oct 29 '13 at 8:35
    
C/P your my.cnf to pastebin. Also, would help us if you gave some system specs –  Sibin Grasic Oct 29 '13 at 21:58
add comment

2 Answers

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.

share|improve this answer
add comment

Can you issue a 'SHOW PROCESSLIST' command? How many connections are there? Every connection takes a thread, maybe the high CPU load is because of the idle connections.

share|improve this answer
    
As I said, there's no active queries at all. SHOW PROCESSLIST; just returns nothing except itself. –  RadarNyan Oct 29 '13 at 9:19
    
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 –  HTTP500 Oct 29 '13 at 19:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.