Configuring and optimizing MySQL servers, from the underlying hardware to the instance settings on up to the application queries, is a multi-faceted and complex process. Here I’d like to assume you’ve got a well-configured physical server (it’s at least functioning and not manifesting any obvious issues) and that you have some decent configuration settings in place. If you’re still facing what you think to be sub-optimal performance, I’m going to describe a workflow you might find useful in your investigations.
Find your targets: the slow query log
First, make sure you have the slow query log turned on and properly configured. Here’s an example /etc/my.cnf section:
# Slow query log settings
# Enable slow log
# Slow query logfile will be LOGDIR/mysql-slow.log.
# DEFAULT IS host_name-slow.log
# Queries taking 5 seconds OR longer will be captured
Add this to your current configuration (or edit as necessary) and restart the MySQL server. The slow query log adds no overhead and is very useful in finding what queries are taking how long on your system.
mysqlsla: slow query log analysis
mysqlsla can parse and analyze all manner of MySQL logs. In this case, let’s use it to analyze our slow query log, assuming the server has been running for a bit to collect an acceptable sample of slow queries. One day to a week should be sufficient, depending on your usage patterns.
Download, made executable, run:
chmod u+x mysqlsla
./mysqlsla --log-type=slow /path/to/slow/log > mysqlsla-report.txt
You minimally need to specify the path to your slow query log and indicate the type of log you are analyzing. Additional options are available for you to specify what in the log to analyze including the specific database and the type of query used. See the documentation.
What you’ll get out is a nicely formatted informative report of the queries found in the slow query log, sorted by what took the longest. You will see how much time was spent in a locked state, how many rows were sent and examined, what users were involved, the query itself, and an abstracted form of the query with particular parameter values generalized:
Looking through this report will allow you to get a sense of what queries are taking up the most of your instance’s time (and are therefore your best points to focus on).
Analyzing target queries with the Query Profiler
I discuss the basics of the MySQL Query Profiler in this post. Let’s assume you’ve found a few queries using mysqlsla as shown above that you want to learn more about and optimize. You could wrap them in a file like so:
# MySQL Query Profile script
SELECT "ignore" AS "START";
# Problem query one:
SELECT foo, bar FROM mydb.mytable;
# Problem query two:
SELECT COUNT(foo) FROM mydb.mytable
GROUP BY bar;
SELECT "ignore" AS "END";
SHOW profile FOR query 1;
SHOW profile FOR query 2;
And run this wrapper script as:
mysql -u root -pXXX < profile.sql > profile-results.txt
The buffer queries give you markers in the output to jump to if you want to filter out the actual data results from your output file (since you’re probably just concerned with the profile data at this point, having a well-defined query). I’ll leave it to the reader to come up with an improved command to remove the query output from the file generated, producing a CSV for each report. (Add to the comments if you make this please )
So to zoom in on the most problematic queries we just need to:
- Enable the slow query log
- Process representative slow query log entries with mysqlsla
- Analyze most troublesome queries with the MySQL Query Profiler