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.

It seems to me like there's always 1 task in SQL that runs on my server at work and my personal stuff that gets "stuck" and keeps running forever.

PID USER      PR   NI VIRT  RES  SHR S %CPU  %MEM  TIME+   COMMAND
890 mysql     20   0  326m  54m 7704 S 9999  1.4  13:05.03 mysqld 

Is there a way that I can setup a bot to find stuff like this, shut it down and restart MySQL so I don't need to do it manually ?

update

I should point out that it doesn't always stay at 9999% CPU. Sometimes it shows 1.4 or something like that

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

You shouldn't ever be restarting a database to terminate a query. In MySQL, you can view the running queries with show full processlist; and terminate the process with kill $CONNECTIONNUMBER;.

You could write a script to perform this function.

share|improve this answer
    
I viewed the process list in PHPMyAdmin and that process didn't show up. –  Webnet Oct 21 '10 at 14:18
    
Something is causing MySQL to use the resources. It has to be in there. –  Warner Oct 21 '10 at 14:25
    
Is it possible that it didn't show up on the list for some reason? Cause I'm telling you, it wasn't there. –  Webnet Oct 26 '10 at 13:34
    
If you have high load on your CPU and not idle load, I cannot imagine what would actively cause load on an otherwise properly functioning MySQL server than accessing the data. Maybe if you had stored procedures. –  Warner Oct 26 '10 at 14:16
add comment

Have you run a MySQL tuning script like mysqltuner.pl or tuning-primer.sh to ensure your basic MySQL settings are proper?

You can't just arbitrarily change setting values to what someone has as the configuration is highly dependent upon the hardware resources available, the amount of data being stored, and the queries being run against that data. A MySQL configuration that works great on my server could possibly kill your server.

share|improve this answer
    
Interesting... I've never thought of something like that... though I don't see a PHP version. –  Webnet Oct 26 '10 at 13:33
    
You run either of the scripts that I mentioned from a command line. The tuning-primer.sh is the better of the two. –  KayakJim Oct 26 '10 at 16:16
add comment

I spoke with one of my MySQL DBAs and he had mentioned to me that the key_buffer_size may be set to default which might be the culprit of high CPU usage. Generally raising the default value from 8M to something along the lines of 330M if the memory resources are available should help resolve this problem. This change will require a maintenance window to restart MySQL services.

share|improve this answer
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.