Profiling Server Performance - High Performance MySQL - page 80

The MySQL Enterprise Monitor's Query Analyzer
One of the tools you should consider using is the MySQL Enterprise Monitor. It's part
of a commercial MySQL support subscription from Oracle. It can capture queries to
your server, either from the application's MySQL connection libraries or from a proxy
(although we're not fans of using the proxy). It has a very nice graphical user interface
that shows a profile of queries on the server and makes it easy to zoom into a specific
time period, such as during a suspicious spike in a graph of status counters. You can
also see information such as the queries' EXPLAIN plans, making it a very useful trou-
bleshooting and diagnosis tool.
Profiling MySQL Queries
There are two broad approaches to profiling queries, which address two of the ques-
tions we mentioned in this chapter's introduction. You can profile a whole server, in
terms of which queries contribute the most to its load. (If you've started at the top with
application-level profiling, you might already know which queries need attention.)
Then, once you've targeted specific queries for optimization, you can drill down to
profiling them individually, measuring which subtasks contribute the most to their
response times.
Profiling a Server's Workload
The server-wide approach is worthwhile because it can help you to audit a server for
inefficient queries. Identifying and fixing these “bad” queries can help you improve the
application's performance overall, as well as target specific trouble spots. You can re-
duce the overall load on the server, thus making all queries faster by reducing conten-
tion for shared resources (“collateral benefit”). Reducing load on the server can help
you delay or avoid upgrades or other more costly measures, and you can discover and
address poor user experiences, such as outliers.
MySQL is getting more instrumentation with each new release, and if the current trend
is a reliable indicator, it will soon have world-class support for measuring most impor-
tant aspects of its performance. But in terms of profiling queries and finding the most
expensive ones, we don't really need all that sophistication. The tool we need the most
has been there for a long time. It's the so-called slow query log .
Capturing MySQL's queries to a log
In MySQL, the slow query log was originally meant to capture just “slow” queries, but
for profiling, we need it to log all queries. And we need high-resolution response times,
not the one-second granularity that was available in MySQL 5.0 and earlier. Fortu-
nately, those old limitations are a thing of the past. In MySQL 5.1 and newer versions,
the slow query log is enhanced so that you can set the long_query_time server variable