Odd MySQL zombie task

Question

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

score 2 · Answer 1 · answered Oct 21 '10 at 15:03

2

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.

answered Oct 21 '10 at 15:03

daemonofchaos

1,211
1
8
10

Interesting... I've never thought of something like that... though I don't see a PHP version. – Ben 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. – daemonofchaos Oct 26 '10 at 16:16

score 1 · Accepted Answer · answered Oct 21 '10 at 14:12

1

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.

answered Oct 21 '10 at 14:12

Warner

23,756
2
59
69

I viewed the process list in PHPMyAdmin and that process didn't show up. – Ben 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. – Ben 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

score 1 · Answer 3 · answered Oct 21 '10 at 14:13

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.

Odd MySQL zombie task

3 Answers3