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

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!

share|improve this question

1 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)

share|improve this answer
    
Thats not possible, I got already 'ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' , so my websites have some problems at this moment :+.. snif –  user968898 Nov 2 '12 at 19:19
    
Solved, let the process do. Wait and it will shutdown by it self. For me it was after 2.5 hours! Cheers! –  user968898 Nov 2 '12 at 20:37

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.