How to measure mySQL bottlenecks?

Question

What mySQL server variables should we be looking at and what thresholds are significant for the following problem scenarios:

1. CPU bound
2. Disk read bound
3. Disk write bound

And for each scenario, what solutions are recommended to improve them, short of getting better hardware or scaling the database to multiple servers?

Answer 1

This is a complicated area. The "thresholds" that will affect each of your three categories overlap quite a bit.

1. If you are having problems with your operations being CPU bound, then you definitely need to look at: (a) The structure of your database - is it fully normalized. Bad DB structure leads to complex queries which hit the processor. (b) Your indexes - is everything needed for your queries sufficiently indexed. Lack of indexes can hit both the processor and the memory VERY hard. To check indexes, do "EXPLAIN ...your query". Anything row in the resulting explanation that says it isn't using an index, you need to look at closely and if possible, add an index. (c) Use prepared statements wherever possible. These can save the CPU from doing quite a bit of crunching. (d) Use a better compiler with optimizations appropriate for your CPU. This is one for the dedicated types, but it can glean you the odd extra percent here and there.

2. If you are having problems with your operations being read bound (a) Ensure that you are caching where possible. Check the configuration variables for query_cache_limit and query_cache_size. This isn't a magic fix, but raising these can help. (b) As with above, check your indexes. Good indexes reduce the amount of data that needs to be read.

3. If you having problems with your operations being write bound (a) See if you need all the indexes you currently have. Indexes are good, but the trade-off for them improving query time, is that maintaining those indexes can impact the time spent writing the data and keeping them up to date. Normally you want indexes if in doubt, but sometimes you're more interested in rapidly writing to a table than you are in reading from it. (b) Make possible use of INSERT DELAYED to "queue" writes to the database. Note, this is not a magic fix and often inappropriate, but in the right circumstances can be of help. (c) Check for tables that are heavily read from and written to at the same time, e.g. an access list that update's visitor's session data constantly and is read from just as much. It's easy to optimize a table for reading from, and writing to, but not really possible to design a table to be good at both. If you have such a case and it's a bottleneck, consider whether it's possible to split its functions or move any complex operations using that table to a temporary table that you can update as a block periodically.

Note, the only stuff in the above that has a major effect, is good query design / indexing. Beyond that, you want to start considering at better hardware. In particular, you can get a lot of benefit out of a RAID-0 array which doesn't do a lot for writing bound problems, but can do wonders for read-bound problems. And it can be a pretty cheap solution for a big boost.

You also missed two items off your list.

1. Memory bound. If you are hitting memory problems then you must check everything that can be usefully indexed is indexed. You can also look at greater connection pooling if for some reason you're using a lot of discrete connections to your DB.

2. Network bound. If you are hitting network bound problems... well you probably aren't, but if you are, you need another network card or a better network.

Note, that a convenient way to analyze your DB performance is to turn on the log_slow_queries option and set long_query_time to either 0 to get everything, or 0.3 or similar to catch anything that might be holding your database up. You can also turn on log-queries-not-using-indexes to see if anything interesting shows up. Note, this sort of logging can kill a busy live server. Try it on a development box to start.

Hope that's of some help. I'd be interested in anyone's comments on the above.