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.

Due to some bad scripting on my part, MySQL sometimes goes haywire - taking up a lot of CPU power (load average of more then 20 - I only have 8 cores)! When I restart it is all good again.

So my question is: is there a way to restart MySQL when it takes up more then 400% CPU?

Preferably I would restart only the associated account(s) which cause the problem.

Server: Linux with whm

share|improve this question
    
Is this really a good idea? Doesn't it run the risk of leaving your DB in an inconsistent state? –  pjc50 May 18 '12 at 13:08
5  
Why don't you just fix the script? –  user606723 May 18 '12 at 15:14
    
@user606723 Could be actively writing scripts. I know something like this would be useful when in testing you run SELECT * FROM mega_table WHERE (complicated logic) without realizing it and bring the server to its knees for a long time –  TheLQ May 19 '12 at 17:51
    
Yes, the ability to kill spids is useful.. but in long term we should fix the issue. –  user606723 May 19 '12 at 21:34
add comment

3 Answers

up vote 16 down vote accepted

Restarting mysql in that case is a bad practice. It results in thrashing your buffers and if your database is large enough - it will take some time to warm up.

I suppose you have some heavy queries that consumes too much cpu.

Use mysql> show full processlist to identify long-running queries and try to optimize them: create indices, re-write queries to run faster and do less sorting.

For immediate results you can kill this queries using:

mysql> kill QUERYIDNUMBER

To directly answer your questions - you can safely restart it with service mysql restart, but this will take long time. Restarting will block all new connections to the database, but it will wait until all running queries finish. You may kill heavy queries to speed up.

Also if you need to speed-up mysql restarting there's a good trick to do it.

First, run mysql> set global innodb_max_dirty_pages_pct = 0; then wait until all (or almost all) dirty buffer pages flush to disk, see mysqladmin ext -i10 | grep dirty

When you have output like this:

| Innodb_buffer_pool_pages_dirty | 12 |

You can restart your mysql server and it will be much faster:

service mysql restart

share|improve this answer
add comment

I can only repeat that it really is a bad idea trying to solve that problem by restarting mysql. You should try to identify and optimize the queries which cause your server to overload.

Two hints which can help you:

  1. mysqltuner.pl will analyze your current configuration and show you where some of the obvious performance killers are.
  2. The slow query log will log all the queries lasting longer than a configurable time. You can then analyze the individual queries using the EXPLAIN SQL command.
share|improve this answer
    
when LA hits that much - pretty all queries will be in slowlog. –  DukeLion May 18 '12 at 14:27
    
That is surely true, but it doesn't necessarily need to be a single bad query which causes the load to go through the roof. The slow query log (while the systems runs normally) will help identifying large tables and tables with badly chosen indexes and thus help to improve the overall performance of the system. –  Oliver May 18 '12 at 14:40
    
Sure, slowlog is of much use when you have suboptimally running mysql. But in case of high LA processlist (especially in percona fork) is much more convenient in finding bad queries. –  DukeLion May 18 '12 at 14:43
    
Yeah that's true. –  Oliver May 18 '12 at 14:51
add comment

monit will do that for you: http://en.wikipedia.org/wiki/Monit

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.