Using MySQL Enterprise (part 3) - Query Analyzer

12/20/2011 5:43:44 PM

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.

Figure 10. Using MySQL Proxy to collect data for Query Analyzer


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.

Figure 11. Query Analyzer display

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.

Figure 12. Canonical query report

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.

MySQL Enterprise and Cloud Computing

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.

Video tutorials
- How To Install Windows 8

- How To Install Windows Server 2012

- How To Install Windows Server 2012 On VirtualBox

- How To Disable Windows 8 Metro UI

- How To Install Windows Store Apps From Windows 8 Classic Desktop

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
programming4us programming4us