This is the fourth in a series of articles on profiling MySQL. My past three articles have explained how to measure the work a query causes MySQL to do. In this article I introduce a tool I’ve written to do the work for you and produce a compact, readable report of that work, with all the math already done, and the measurements labelled and grouped for ease of comprehension. With this tool you can understand query performance at a glance.
I’m very happy about this tool. Finally, I can run a query and get the kind of measurements I was used to on Microsoft SQL Server. Though the MySQL server isn’t as complex as SQL Server and doesn’t always let you see as much internal information, at least with this tool I can easily use the information I do have.
Introduction
I demonstrated techniques to measure and understand MySQL query performance in my three most recent articles:
- How to profile a query in MySQL
- A case study in profiling queries in MySQL
- MySQL profiling case study, part 2
If you’ve read those articles, this article will make sense to you. If not, it might help to go read them now.
For the last several months I’ve been working on a tool to automate the work I showed you in those articles. Done by hand, it’s a tedious job, and I’d just as soon take execution time as the best indicator of a query’s performance. But with a tool to do it automatically, it’s easy to measure much more data.
MySQL Query Profiler is just such a tool. It’s written in Perl and has minimal dependencies (in fact, it has minimal code, too). It reads queries from a file you give it, executes each one, and measures the results. Then it formats it nicely one of two ways: as human-readable reports with logical grouping and labels, or as tab-separated values to be fed to a spreadsheet, awk or other processing tools.
There are lots of options to control the output. You can get a summary view of the entire batch, view each query’s statistics separately, view only some queries separately, and ask for more or less verbose output. You can turn on and off certain behaviors, such as disabling the query cache or flushing tables.
Documentation and examples
Documentation is linked from the MySQL Query Profiler homepage: the MySQL Query Profiler manual page.
You can also find several real examples there. I created those examples from the code and data structures I’ve discussed in the last several articles:
- The batch input file
- The default output, which is a summary view at verbosity level 1.
- Separate queries: output with separate statistics for each query in the batch.
- More verbose output, verbosity level 3, summary view.
- For processing by other programs, tab-separated values output.
I generated these output files with the following commands:
$ ./mysql-query-profiler batch.sql > mysql-query-profiler-example-1.txt $ ./mysql-query-profiler batch.sql --separate > mysql-query-profiler-example-2.txt $ ./mysql-query-profiler batch.sql --separate -v 3 > mysql-query-profiler-example-3.txt $ ./mysql-query-profiler batch.sql --tab > mysql-query-profiler-example-4.txt
Other profiling tools
Dmitri Mikhailov posted a tool on MySQL Forge to watch queries as they fly by on the wire: A Poor Man’s Query Profiler. This is a different meaning of the word “profile.” This tool analyzes which queries are run on the server. My tool measures the performance characteristics of a query or batch of queries. Both are important needs.
Jeremy Cole of Proven Scaling recently announced a SHOW PROFILE patch to the MySQL source that will allow detailed profiling of query execution. This is a wonderful addition to MySQL’s instrumentation. I don’t know when this will be part of standard MySQL distributions.
I know of no other tools to provide this functionality. If you do, please post a comment.
Acknowledgements
I was heavily inspired by mysqlreport, a great tool for understanding MySQL status at a glance. If I hadn’t seen mysqlreport, I doubt I’d have known how to present query profiling data comprehensibly. Plus, I might not have learned about Perl formats, which are really nifty. Thanks.
Conclusion
This brings to a close my series on profiling queries in MySQL. I hope you’ve found it useful, and I hope my profiling tool is helpful to you. Please let me know how I can improve it for you, or if there’s anything else I can do.
If this was useful to you, you should consider subscribing to my articles via feeds or e-mail.
Technorati Tags:No Tags
Another useful tool to be mentioned is MyProfi that takes mysql query log and outputs the most frequently executed queries removing variable data from them, so one may easily analyze the most popular queries and start optimizing them.
Output Sample:
100 select id, name from user where email={} 94 select password from user where name={} 11 update user set name={}, password={} where id={}Thanks. Downloaded that tool and installed it. Now it won’t recognize my password. Here’s what I am trying at the command prompt:
mysql-query-profiler --user [USER] -d [DATABASE] --pass [CORRECTPASS] --verbosity 3 queries.sqlBut this is what I keep seeing:
DBI connect('database=[DATABASE];host=localhost;port=3306','[DATABASE]',...) failed: Access denied for user '[USER]'@'localhost' (using password: YES) at /usr/bin/mysql-query-profiler line 168Is there any special way I need to give this tool the password? I am specifying it with “–pass” as the perldoc suggests?
Thanks.
I think you should be seeing
connect('database=[DATABASE];host=localhost;port=3306','[USER]',...)Instead of [DATABASE]. If that’s not the trouble, then I’m suspicious of problems with old-style passwords in your MySQL account. Sometimes certain software can connect to an account with an old-style password and other software can’t. There is a section in the MySQL Manual on that. Even though you’d think the error would be something like “Client does not support authentication protocol requested,” I have seen cases where Perl programs fail with what looks like a wrong-password error instead.