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.

Sometimes mysqld goes up to 95% cpu using top. This grinds my entire server to a halt. Is there some way of throttling cpu usage say to a maximum of 25%?

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

First, you need to enable slow query logging so you can detect the root cause of the problem.

Next, you need to profile your apps so you understand where resources are spent.

Finally, performance tune your mysql instance(es) to work best with your hardware and the app requirements.

In my experience, what kills mysql is disk access. The only way to "throttle" mysql is to properly shard your data so that disk access is kept to a minimum. If you have a table with 100 million rows, even with indexing, you'll experience join problems. So, shard that data out if possible. You can also use replication to "load balance" reads across multiple hosts.

There's any number of "shoot from the hip" solutions to solve performance problems. Without specificity of the root cause, it's unlikely any will help.

share|improve this answer
    
I have a (VERY cheap) server provider who shut down my server if I use more than a specific amount of CPU time. I know that I have enough CPU available in the day to complete all my queries but I can't change my code to throttle the sending of them - so I want to throttle the execution of them, slowing down the MySQL server. Is this possible? Can I configure Linux to limit CPU for example? –  mjaggard Jun 13 '13 at 14:49
add comment

Randy has a very good answer, but I would like to add a more generic point.

CPU that is not used is wasted. A properly configured server does not grind go a halt if one service claims all CPU, as your operating system is built to handle this. So, what you can do is that you can lower the priority of the mysqld process (renice +19) to ensure your other process have a higher priority and get the CPU time they need.

Of course, this does not take care of the underlying problem, nor will it handle resource competition (high IO load or Swapping can make a server impossible to work with). But if it is a pure CPU issue changing the priority can help.

share|improve this answer
1  
actually, the lowest nice priority is +19, not +20. –  Julien Vehent Feb 23 '11 at 0:35
    
@Julien I am so used to having renice aliased to renice -n that I forgot that ;) –  pehrs Feb 23 '11 at 8:28
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.