Non-technical Guide to Isolating Slow MySQL Queries

MySQL is ubiquitous. As such there are many non-technical users who rely on MySQL but do not want to become MySQL experts beyond routine maintenance. When query-related performance problems creep in, such users are at a loss because there is no magic solution for slow queries; each case is unique.

This document is a non-technical guide for isolating slow queries. You do not have to be a MySQL expert, or know how to analyze queries, to isolate (i.e. identify) which queries are causing the most problems on your server. Once you have isolated these queries, you can consult with a MySQL expert to fix them.

First Step: Baseline

Always establish the current baseline of MySQL performance before any changes are made. Otherwise it is really only a guess afterwards whether the changes improved MySQL performance. The easiest way to baseline MySQL performance is with mysqlreport. Let MySQL run for at least a full day and then run mysqlreport with the --all command line option and save the report (it would probably be easier to email yourself the report by doing: mysqlreport --all --email your@address.com). If MySQL cannot last a day without killing the server, try at least to bear with it; certain performance factors are skewed when not averaged over enough time.

2. Assess Baseline

The report that mysqlreport writes can contain a lot of information, but for our purpose here there are only three things we need to look at. (A mysqlreport report is fully discussed in Guide To Understanding mysqlreport).

First is "Read ratio" (line 6 or 7). This should never be over 0.01. If it is, MySQL needs to be configured to use more RAM. Make sure your server can handle MySQL using more RAM though. If your system is out of RAM overall, and MySQL is too, if MySQL tries to use more it will cause the system to swap (use hard drive space as RAM) which usually makes the problem worse.

The next value is on line 16, "Slow," which refers to slow queries. 10 seconds is the default time after which a query is considered slow if it takes longer to execute. The last column and value on this line (like "%DMS:   0.44") should generally be less than 0.05. You will probably notice problems clearly at 0.30 and above. This is the value that we are trying most to decrease.

The last line we need to look at is "Waited," line 48, specifically the last column and value (like "%Total:   0.27"). This value refers to table locks which had to wait to acquire a lock. Locking is how MySQL shares data among so many simultaneous users. This value should be less than 10%. If it is not the cause is usually slow queries.

It is not necessary to understand the nature of these values at this point, but they give us an idea how well or not MySQL is really running. If the values are high, then the MySQL expert you consult may have an easy job. If the values are low but MySQL is clearly running slowly, your MySQL expert should still be able to determine exactly why this is.

3. Log Slow Queries and Wait

By default MySQL does not log slow queries and the slow query time is 10 seconds. This needs to be changed by adding these lines under the [msyqld] section in /etc/my.cnf:

log-slow-queries
long_query_time = 1

Restart MySQL and wait at least a full day. This will cause MySQL to log all queries which take longer than 1 second to execute.

The slow queries log file is called slow_queries.log (by default) and will be located in your MySQL data directory. If you do not know you MySQL data directory, log into MySQL and execute "SHOW VARIABLES LIKE 'datadir';". That will show you the data directory that MySQL is currently using. A typical data directory for Linux systems is /var/lib/mysql/. Therefore, the slow queries log is /var/lib/mysql/slow_queries.log.

4. Isolate Top 10 Slow Queries

The easiest way to isolate the top 10 slowest queries in the slow queries log is to use mysqlsla. Run mysqlsla on your slow queries log and save the output to a file. For example: "mysqlsla --log-type slow /var/lib/mysql/slow_queries.log > ~/top_10_slow_queries". That command will create a file in your home directory called top_10_slow_queries. This file is what you will want to show to the MySQL expert. In most cases, if even the top 3 slow queries can be fixed, overall MySQL and server performance will increase dramatically. From here the proverbial ball is in the MySQL expert's court.

5. Post-fix Proof

Presuming that your MySQL expert was able to fix the top slow queries, the final step is to actually prove this is the case and not just coincidence. Restart MySQL and wait as long as MySQL had ran in the first step (at least a day ideally). Then baseline MySQL performance again with mysqlreport. Compare the first report with this second report, specifically the three values we looked at in step two (Read ratio, Slow, and Waited). There should be a noticeable decrease in these three values. If not, consult further with the MySQL expert; they should be able to tell you why this case is not a "simple fix."

Forewarning

Do not be alarmed if your MySQL server requires this process again in a few months. Whereas performance issues can be a compounding problem, so can performance increases. I frequently see cases where MySQL performance is fixed and a few months later breaks again. This is not a fault of MySQL, but a side-effect of growth. It seems when users of your database realize that it is running well, they use it more. And perhaps more people start to use it who did not before. As more demand is put on your MySQL server, more optimization will be needed.