Analyze Database Slow Query Log and Optimize Expensive Queries | LoadStorm

If you’re serious about web performance optimization, then you must run an efficient database. One necessary skill is an ability to analyze a database slow query log and optimize the most expensive, slowest results. While one relatively slow query may not be the end of the world, many slow queries will add up quickly and frustrate users.

Setting Up a Slow Query Log

Running a slow query log is not a default setting in MySQL, so the first thing you’ll need to do is set one up. Open the server’s configuration file and enter “log-slow-queries” as well as an output file. On the next line, enter “long_query_time=x”. The value x represents a certain number of seconds, and any query taking longer than that number will appear in the output log.

Although needs will vary, a value of 1 is generally recognized as a good starting point. For heavily used servers, a value of 1 may result in a slow query log which is too large, creating additional performance problems.

In addition, you may want add a third line which reads “log-queries-not-using-indexes”. (Evidently the creators of MySQL did not realize that the plural of “index” is actually “indices.”) This will instruct MySQL to log any query which does not use an index, whether or not the query time exceeds the value listed on the second line. If you only want to log queries not using indices, you can set the value on the second line very high.

Analyze Your Slow Query Log

First, evaluate the overall size of your output file. If the log is unmanageable, you should increase the value of x so that you are logging fewer queries. This will help you isolate the biggest problem queries so that you can optimize them first. If you fix the biggest problem areas and still have time (especially if your database is still running unacceptably slow), you can systematically reduce the value of x and optimize the resulting queries until you have achieved an acceptable result.

Second, consider whether particular queries need to be optimized. A query that is run once or twice per day and averaging 10 seconds may not need to be optimized (or perhaps can’t be made significantly faster). On the other hand, a query run thousands of time per day and averaging 2 seconds should be optimized to the greatest extent possible.

Optimizing the Results

One of the most important components of web performance optimization is to speed up the database access because it is one of the most common problem areas. The whole point of the slow query log is to identify slow queries and optimize them for faster overall performance. Of course, optimizing a query requires a basic understanding of MySQL which I won’t address here, and if you asked 10 different coders to optimize a database or even a particular query, you would probably get 10 approaches, each slightly different.

However, one surefire technique for optimizing slow queries is to look for the tables in your database and index them appropriately. This will allow your database to quickly identify requested data without scanning an entire table. But be aware, this approach is an art, not a science. “Over-indexing” will cause your database to do extra work by requiring it to build indices for even small tables.

When the average coder is unsatisfied with database performance in the web application, a gut reaction might be to upgrade the hardware. Perhaps a hardware upgrade is necessary, especially if traffic to the database is increasing and expected to continue to increase. But an experienced coder will first set up and analyze a slow query log looking for any improvement possible in the code or configuration.

This article was written by Brad Sauer as a guest blogger for LoadStorm. He is an expert in the Databases category at www.yoexpert.com.