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 firstname.lastname@example.org).
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
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:
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
. 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."
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.