4. Query Analyzer
The newest feature of MySQL Enterprise is the Query Analyzer. Savvy administrators have long known of
the standalone Query Analyzer for MySQL, but until recently it was not
integrated into the Enterprise tools.
The Query Analyzer works by intercepting SQL commands using MySQL
Proxy and processing them, then passing them on to the local server for
execution. This allows it to record statistics so that you can view them
at any time. The Query Analyzer also supports advisors that fire alerts
for slow queries. Figure 10 shows a conceptual
drawing of how MySQL Proxy intercepts queries and reports the statistics to the
MEM.
Note:
The Query Analyzer runs over the user-defined port 6446 (by
default) and can introduce some performance delay. Thus, you should
enable it only when you are searching for problems.
To collect data for the Query Analyzer, you must direct your
client to connect to the MySQL Proxy port and configure the agent to use
port 6446. Be sure you can connect to this port if you do not see any
queries reported in the Enterprise Dashboard.
While it is possible for MySQL Proxy to introduce some minor
delays in query execution throughput times, the benefits of analyzing
poorly running queries more than pay for the delays. You may want to use
the Query Analyzer only on certain development or experimental machines
rather than on your live production servers. One nice thing about the
Query Analyzer and the Enterprise Dashboard is the tight integration
among the tools. If you have an alert about a slow query or drill down
into a CPU utilization report or other MySQL-based statistic, you will
see the Query Analyzer page (which you can also access directly by
clicking the Query Analyzer tab). Figure 11 shows an example of the Query
Analyzer page on the Enterprise Dashboard.
The Query Analyzer page displays a list of servers on the left.
You can click a specific server to see a list of queries executed on
that server, sorted by longest running query. You can also use the chart
at the top to narrow the time window to see queries during a specific
time period.
You can sort the list on any column by clicking the column
heading. This makes it a bit easier to see repetitive queries and
diagnostics by sorting the rows with the longest-running queries at the
top, showing the query statement, the amount of data manipulated,
etc.
You can click any row to get a more detailed report on the query.
Figure 12 shows a sample canonical query
report. As with the other reports, you can get more detailed
information, including the actual query on the Example Query tab, the
output of the EXPLAIN command on the
Explain Query tab (if you enabled that option), and graphs for execution
time, number of executions, and number of rows returned on the Graphs tab.
This report presents details of the query captured, including the
canonical form of the query (a pictorial representation of the query as
it was written), details of its execution including time taken, and rows
returned or affected.
Once again, we see a monitoring tool that will save you a
considerable amount of time diagnosing problems with MySQL. The Query
Analyzer component of the MySQL Enterprise tools is a vital monitoring
tool that will help you keep your servers running well and your queries
executing efficiently.
The MySQL Enterprise tools can also function well in a cloud computing environment.
The same rules apply, however, regarding the persistence of the data
and server instances. Just be sure to request a persistent IP address
for your MEM server. Repeatedly starting and stopping your instances
will cause no ill effects, but changing hostnames and some types of
reconfiguration can cause the monitoring agents to stop reporting.
Typically, truncating the mysql.inventory table
solves this, but it is best to use the same hostnames and IP addresses
for all of your servers. There is one excellent benefit of running MySQL Enterprise tools
in a commercial provider’s cloud: you pay only for computation and
storage space. Data transfer within the cloud is usually free or much
less than shipping data in and out of the cloud. |
