As we know in the hosting industry there are many people who rely on MySQL but know, and don’t wish to know, next to nothing about MySQL. Yet slow queries are indiscriminate about who they afflict. I think what bothers non-technical users the most about slow queries causing problems in MySQL is a feeling of helplessness. Unlike a car which pretty much anyone can do the regular maintenance, MySQL maintenance isn’t so clear, even less so when the issue is query optimization. This document is meant to be a facilitator between the non-technical MySQL user with slow query problems, and a MySQL expert who can help them. The information here is to help the non-technical user at least get a handle on the situation, and not feel so helpless. From there, a MySQL expert can more easily take over and fix the issue.
Non-technical Guide to Isolating Slow MySQL Queries
MySQL is ubiquitous. As such there are many non-technical users who rely on MySQL but don’t want to become MySQL experts beyond routine maintenance. When query-related performance problems creep in, such users are at a loss because there’s no magic solution for slow queries; each case is unique.
This document is a non-technical guide for isolating slow queries. You don’t 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’ve 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’s really only a guess afterwards whether the changes improved MySQL performance. The easiest way to baseline MySQL performance is with mysqlreport (http://codenode.com/mysql/mysqlreport). Let MySQL run for at least a full day and then run mysqlreport and save the report (it would probably be easier to email yourself the report by doing: mysqlreport -email your@address.com). If MySQL can’t 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 Understanding mysqlreport Reports (http://codenode.com/mysql/mysqlreportdoc)). First is “Read ratio” (line 6). 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 10, “Slow,” which refers to slow queries. 10 seconds is the default time after which a query is considered slow if it takes longer to run. The last column and value on this line (like “%DMS: 0.44”) should generally be less than 0.05. You’ll probably notice problems clearly at 0.30 and above. This is the value we’re trying most to decrease. The last line we need to look at is “Waited,” line 14, 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’s 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 2 lines under the [msyqld] section in /etc/my.cnf:
log-slow-queries
long_query_time = 5
Restart MySQL and wait at least a full day. This will cause MySQL to log all queries which take longer than 5 seconds to run.
4. Isolate Top 3 Slow Queries
MySQL comes with a script called mysqldumpslow which can analyze and sort the slow query log. It has one annoying requirement: Under the [mysqld] section of /etc/my.cnf the following 2 lines must be set:
datadir = /var/lib/mysql
basedir = /usr/
For your server these values may be different. Usually these values are not explicitly set in /etc/my.cnf though, which is why I think this is an annoying requirement. You do not have to restart MySQL after you add these values to /etc/my.cnf. To isolate the top 3 slow queries run the command “mysqldumpslow -s c -t 3”. This is the information you’ll want to show to the MySQL expert. In most cases, if 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 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
Don’t 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 it’s running well, they use it more. And perhaps more people start to use it who didn’t before. As more demand is put on your MySQL server, more optimization will be needed.
Originaly posted at:http://www.webhostingtalk.com
