How can I throttle MySQL in Linux?

Question

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

Answer 1

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.

Answer 2

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.