Debian hanging on MySQL shutdown

Question

One of my websites had too many MySql connections, I did some performance in the scripting and shutdown some expensive queries to make the website more reachable again.

Because my processlist was full for 100% with queries, I thought it will help the server to restart mysql (to response fast again).

/etc/init.d/mysql stop

No I tried to shutdown mysql and it said it will. But after 10minutes the command seems to be freezed, no more reaction I saw. I quit by ctrl+x and I though to try the mysql stop command again, failure!. I am also not able to start mysql.

If you take a look at the command 'top' it gives me some serious bad mysql CPU results.

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                                                                                                                                       
28429 mysql     20   0  356m 128m 6336 S 88.2  6.4   1095:39 mysqld  

I hope some can help me with this one!

Thanks!

Answer 1

Looks like there may be some bad queries still running on the DBMS - note that setting a timeout / killing the client does not stop any queries started on the DBMS.

Use mysqladmin processlist to identify the queries still running and mysqladmin kill $mysqlprocess to stop them.

This kind of thing is more likely to happen with the MyISAM engine than Innodb (which is a bit slower for a sequential workload but handles concurrent requests much better). If you are currently using MyISAM extensively, then switch to Innodb.

Beyond that, you will still need to do some tuning on your DBMS - MyISAM makes good use of VFS caching - but for Innodb you want to set a large buffer pool and enable o_direct. Then you need to start going through your queries and identifying which indexes to add / which to remove and which statements to re-write.

(BTW bouncing the server should have no impact on the performance)