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.

Recently, I purged the cache files for my website and now MySQL is eating up CPU up to 100%. It slowly grows from 10% to 100% within 5min and so the server crashes. Fortunately, I turned stopped the service via #service mysql stop but each time I turn it on, it starts eating the CPU.

Here's the error log:

#/usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/dev.system.de.pid
101112 18:22:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
101112 18:22:03 [Warning] option 'innodb-additional-mem-pool-size': signed value 204800 adjusted to 524288
101112 18:22:03 [Warning] option 'innodb-log-buffer-size': signed value 204800 adjusted to 262144
101112 18:22:03 [Note] Plugin 'ndbcluster' is disabled.
101112 18:22:03  InnoDB: Started; log sequence number 0 63198013
101112 18:22:03 [Note] Event Scheduler: Loaded 0 events
101112 18:22:03 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.50'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

Status information:

Current dir: /var/lib/mysql/
Running threads: 0  Stack size: 196608
Current locks:
lock: 0x89df8a0:

lock: 0x89d6798:

lock: 0x89cca68:

lock: 0x89c1500:

lock: 0x89b9818:

lock: 0x89adff8:

lock: 0x89a9918:

lock: 0x899b3e0:


Key caches:
default
Buffer_size:      33554432
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:             3
not flushed:             0
w_requests:              0
writes:                  0
r_requests:              6
reads:                   3


handler status:
read_key:            0
read_next:           3
read_rnd             0
read_first:          3
write:               0
delete               0
update:              0

Table status:
Opened tables:         15
Open tables:            8
Open files:            16
Open streams:           0

Alarm status:
Active alarms:   0
Max used alarms: 0
Next alarm time: 0

Begin safemalloc memory dump:

End safemalloc memory dump.

Memory status:
Non-mmapped space allocated from system: 1286144
Number of free chunks:                   19
Number of fastbin blocks:                0
Number of mmapped regions:               7
Space in mmapped regions:                108834816
Maximum total allocated space:           0
Space available in freed fastbin blocks: 0
Total allocated space:                   1242928
Total free space:                        43216
Top-most, releasable space:              25280
Estimated memory (with thread stack):    110120960

Events status:
LLA = Last Locked At  LUA = Last Unlocked At
WOC = Waiting On Condition  DL = Data Locked

Event scheduler status:
State      : INITIALIZED
Thread id  : 0
LLA        : n/a:0
LUA        : n/a:0
WOC        : NO
Workers    : 0
Executed   : 0
Data locked: NO

Event queue status:
Element count   : 0
Data locked     : NO
Attempting lock : NO
LLA             : init_queue:132
LUA             : init_queue:142
WOC             : NO
Next activation : never
101112 18:26:07 [Note] /usr/libexec/mysqld: Normal shutdown

101112 18:26:08 [Note] Event Scheduler: Purging the queue. 0 events
101112 18:26:08 [ERROR] /usr/libexec/mysqld: Sort aborted
101112 18:26:10  InnoDB: Starting shutdown...
101112 18:26:13  InnoDB: Shutdown completed; log sequence number 0 63198023
101112 18:26:13 [Note] /usr/libexec/mysqld: Shutdown complete

Sorry, but I don't know how to read this. Could anyone help?

share|improve this question
    
CPU usage can't crash mysql. Did you check the memory usage as well? –  J-16 SDiZ Nov 13 '10 at 2:45
    
Actually, both CPU Usage and Memory usage were at 100% from stats I was checking out at Plesk –  Gio Borje Nov 13 '10 at 2:49

1 Answer 1

up vote 1 down vote accepted

In my.cnf add the following lines under [mysqld]:
long_query_time = 5
log-slow-queries

Then restart MySQL and look for any queries showing up in there (any that take over 5 seconds to run). It's also possible that MySQL isn't actually the problem, it's Apache hammering it.

share|improve this answer
    
When I added that under [mysqld], MySQL failed to start. –  Gio Borje Nov 13 '10 at 2:52
    
Sorry! I forgot to put new lines in, the code was correct but if you put it all on one line it will fail. –  James Lawrie Nov 13 '10 at 2:54
    
Ahh. Well, it turns out my website is running on a loop to regenerate the deleted cache files except the cache files won't regenerate because they don't have access to the folder after the deletion. Thanks for the help! I was really panicking thinking that someone is DDoSing my server. –  Gio Borje Nov 13 '10 at 3:00

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.