1

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%?

2 Answers 2

3

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.

1
  • 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, 2013 at 14:49
1

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.

2
  • 1
    actually, the lowest nice priority is +19, not +20. Feb 23, 2011 at 0:35
  • @Julien I am so used to having renice aliased to renice -n that I forgot that ;)
    – pehrs
    Feb 23, 2011 at 8:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .