- What is the MySQL Query Analyzer?
MySQL Query Analyzer
Integrated into the Enterprise Monitor the Query Analyzer is designed to help Developers and DBAs accurately pinpoint SQL code that is causing a slowdown, quickly diagnose the inefficiencies and get the affected application back to peak performance. To this end, the Query Analyzer leverages the MySQL Proxy technology to extend the Monitor Service Agent to listen on a user defined port for application queries, collect the SQL code and performance metrics, and then report the results back to the Service Manager for monitoring and presentation.
Query Analyzer configuration is straightforward (and very well documented in Chapter 10 of the Enterprise Monitor documentation):
- A monitor Service Agent is installed on each of the MySQL servers to be monitored.
- The monitored application is configured to connect to the Service Agent on a user defined port (4040 by default).
- Service Agent connects to MySQL server (single server, master, read slave).
- Service Agent is enabled to collect queries or to serve as a pass-thru until needed.
- Service Agent reports MySQL, OS and query metrics back to Service Manager for monitoring.
Enabling active query collection with the Proxy enabled Service Agent is controlled using the Enterprise Dashboard and allows users to enable query collection for individual servers or complete applications. Once enabled the Query Analyzer provides the DBA with an aggregated, searchable view into all queries, across all monitored servers. Queries are presented in canonical form (no variables) with roll ups for total number of executions, total execution time, total data size and date/time of when a query was "first seen".
The Query Analyzer is also tightly integrated with the Enterrpise Monitor graphs. To correlate graph data with query activity, users can simply highlight any area or region of any graph and launch the Query Analyzer with a context-sensitive view into the queries that were running at the selected time. Query specific execution graphs (execution counts, time, result sets) are also available so users can track the performance of their queries for specific windows of time.
Using Query Analyzer Reported Data
The Query Analyzer is designed to save DBA time parsing and collecting atomic query metrics from the MySQL Slow Query Log and command line SHOW PROCESS LIST result set. The general benefits include:
- An integrated monitoring solution for all supported versions of MySQL (4.1 and higher).
- Aggregated query content and performance stats in real time with no reliance on MySQL logs or SHOW PROCESS LIST.
- A consolidated view into query activity across all MySQL servers, no user parsing required.
- Historical browsing/analysis of queries across all MySQL servers.
- Aggregated, searchable roll ups of all queries in canonical form (no variables) with total number of executions, total execution time, total data size and date/time of when query was “first seen”.
The Query Analyzer provides an aggregated view into query performance across all MySQL servers so DBAs and Developers see the total expense of their SQL. The key performance metrics and what the insights they provide are summarized here:
- Total Executions by Query
This helps the DBA/Developer see queries that are running too often or in error. It also helps to identify queries that are candidate for application caching or result sets that may be better selected from smaller, summary tables.
- Total Execution Time by Query
This identifies the most expensive queries across all of the monitor MySQL servers. This helps DBAs see where systems are spending the most time and resources and where tuning efforts should be focused. It also helps to determine if query processing load is balanced across read slaves for read-intensive scale-out environments.
- Total Data Size (Rows and Bytes)
This helps a DBA analyze if there are queries that are returning more data than an application is actually using. Sorting on this value, examining the underlying queries and comparing the returned rows and columns with application requirements will the DBA tune the application and schema for better performance. Also, queries with high execution times, but small result sets are indication that a query is too complex or querying a high number of rows across non-indexed columns.
- "First Seen"
This allows the DBA to easily monitor when queries attributed to new application deployments are affecting the performance of production systems. It also provides a quick security check to see if any "rogue" queries have entered a production environment.
Query Analyzer Deployment and Use Cases
The Query Analyzer can be implemented in Development, QA and Production environments to help diagnose and tune SQL code. The most common deployments are summarized below:
Monitoring Queries in Development and QA
Developers can use the Query Analyzer to proactively monitor and tune their application code before it is promoted to production. In this scenario applications are connected directly to the port the Proxy/Service Agent is listening on (4040 by default, but user definable). The Proxy/Agent listens on 4040 and "proxies" the communication stream to the back-end MySQL server to collect queries and aggregate the performance metrics. Query data and performance metrics are sent to the Enterprise Monitor, query result sets are sent to requesting application. It is important to note that the Proxy/Service Agent continues to collect and send MySQL and OS specific metrics to the Enterprise Monitor while the application is connected to the Proxy/Service Agent port.
Monitoring Queries in Production Environments
DBAs and System Administrators will find the Query Analyzer valuable for finding SQL code running in their production environments that is executing too often, resource intensive across servers or newly implemented and causing performance slowdowns. It is important to note that there is overhead attributed to enabling the Query Analyzer by directing applications queries to the Proxy/Service Agent defined port (4040 by default, but user definable). The magnitude of the overhead is dependent on the application being monitored, the average and peak query loads and the content of the data being queried and returned. Given the value of the query monitoring data returned, some DBAs will find the overhead of having the Query Analyzer enabled at all times acceptable for some production applications.
Planned/Scheduled Query Sampling
Alternative implementations of the Query Analyzer involve planned sampling of queries during non-peak system hours and monitoring a subset of production systems that handle a representative load of monitored application queries. For planned/scheduled sampling application and web servers get their connection strings from files stored in the application server cache or file system. During non-peak hours the application is "gracefully" restarted to connect to the Proxy/Service Agent port. The application directs queries to the defined port, where they are collected and aggregated and then sent on to the Enterprise Monitor for analysis. Collected queries are a representative sample of the queries that are submitted throughout the day, so the DBA can see the potential bottlenecks and tuning that can be done to speed up the application. After the sample is taken, the application is again restarted to connect to the back-end MySQL server. It is important to note that the Service Agent continues to collect and send MySQL and OS specific metrics to the Enterprise Monitor while the application is connected to the Proxy/Service Agent port.
Monitoring Queries on a Subset of Production Servers
In this deployment primary application servers are connected to the back-end MySQL server or a load balanced read-slave farm and 1 is configured to direct queries to a dedicated read-slave configured to listen for queries on the Proxy/Service Agent port. Queries directed to this read-slave are representative of the queries that are directed to all read-slaves, so the DBA can see the potential bottlenecks and tuning that can be done to speed up the application. This deployment can also be used in conjunction with production sampling so a specific application server is only directing queries for query analysis for specific windows of time. Again, it is important to note that the Service Agent continues to collect and send MySQL and OS specific metrics to the Enterprise Monitor while the application is connected to the Proxy/Service Agent port.