What is the MySQL Enterprise Monitor?
Provided as part of MySQL Enterprise, the MySQL Enterprise Monitor is aÂ Virtual DBA assistant that helps MySQL DBAs manage more MySQL servers, tune MySQL servers, and find and fix problems with MySQL database applications before they can become serious problems or costly outages. Running completely within the corporate firewall, the Enterprise Monitor proactively monitors enterprise database environments and provides expert advice so that even those who are new to MySQL can tighten security, optimize performance and reduce downtime of their MySQL powered systems.
The Enterprise Monitor feature set is powered by a distributed web-based application that is deployed within the confines of the corporate firewall. The key features of this application include:
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 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.
MySQL Enterprise Monitor Advisors
The MySQL Enterprise Monitor differs from traditional third-party database monitors because right out of the box, it supplies a set of MySQL Advisors designed to examine a MySQL server’s configuration, security, and performance levels automatically, to identify problems and tuning opportunities, and to provide the MySQL DBA with specific corrective actions.
The Enterprise Monitor ships with the following set of MySQL Advisors:
- Administration Advisor
Monitors and advises on problems relating to general database administration, recoverability and performance configuration settings.
- Upgrade Advisor
Monitors and advises on specific bugs that can potentially impact versions of MySQL Enterprise Server deployed within an environment. Advises on specific upgrade path to version of MySQL that provides fix for identified bugs.
- Performance Advisor
Monitors and advises on deviations from MySQL performance best-practice rules.
- Schema Advisor
Monitors and advises on unexpected changes to database schema and objects.
- Memory Usage Advisor
Monitors and advises on the optimal use of memory and cache settings for specific MySQL implementations.
- Security Advisor
Monitors and advises on security vulnerabilities in a MySQL Server.
- Replication Advisor
Monitors and advises on issues relating to replication setup, synchronization or performance problems.
- Custom Advisor
Allows a DBA to define best practices to fit their organization’s specific needs.
Each of the MySQL Advisors is designed to cover specific DBAÂ areas of concern and is comprised of a set of MySQL Advisor Rules that help DBAs proactively find problems and tuning opportunities they may not have the time or expertise to find themselves.
MySQL Replication Monitor
The Replication Monitor provides a consolidated, real-time view into the health, performance and availability of all master/slave topologies. Working with the Replication Advisor Rules, the Replication Monitor helps the DBA to proactively identify and correct Replication related problems before they can become costly outages. As the Replication Advisor identifies a problem and sends out an alert, the DBA can use the alert content along with the new Replication Monitor to drill into the status of the affected master and/or slave. Using the Replication Monitor and the expert advice from the Replication Advisor they can review the current master/slave status and view metrics (such as Slave I/O, Slave SQL thread, seconds behind master, master binlog position, last error, etc.) that are relevant to diagnosing and correcting the problem. The Replication Monitor is designed and implemented to save DBAs time writing and maintaining scripts that collect, consolidate and monitor similar MySQL Replication status and diagnostic data.
MySQL Enterprise Monitor Application Architecture
The Enterprise Monitor distributed web application components are described below:
The MySQL Enterprise Service Agent
Service Agents are the foot soldiers of the Enterprise Monitor application and are installed on each monitored MySQL server. Written in lightweight C and running as a Linux/Unix process or Windows service, the Service Agent uses a combination of MySQL specific commands, SQL queries and custom scripts to collect or report on MySQL server or OS specific data. The Service Agent initiates a heartbeat to the Service Manager on a regular basis to ensure specific MySQL server and OS level data collections are current. In the overall architecture, the Service Agent is the only component of the Enterprise Monitor application that establishes or maintains a connection with the monitored MySQL Server. As with any MySQL client, the Service Agent is authenticated on the monitored MySQL server and requires a specific user id and password to establish a connection.
The privileges required for the Service Agent user account vary depending on the data it will be used to collect. The following privileges will allow the Service Agent to perform its assigned duties without limitation:
- SELECT: Allows the Service Agent to collect statistics for table objects.
- REPLICATION CLIENT: Allows the Service Agent to gather Replication master/slave status data. This privilege is only needed if the MySQL Replication Advisor Rules are employed.
- SHOW DATABASES: Allows the Service Agent to gather inventory about the monitored MySQL server.
The MySQL Enterprise Service Manager
The Service Manager is the heart and soul of the Enterprise Monitor application. It is built on a collection of integrated server-side Java services which are hosted on a single Linux or Windows server. The Service Manager interacts with all of the Service Agents under its domain to collect MySQL server and OS level data for each of the monitored MySQL servers in the MySQL environment. The Service Manager performs many duties including:
- Auto discovery of monitored MySQL Servers.
- Auto discovery and grouping of Replication and Scale-out topologies.
- Creation and management of Service Agent tasks.
- Storage of data collections from Service Agents.
- Monitoring of key MySQL server and OS level metric data collections. Data collections are evaluated using MySQL best practice Advisor Rules. Collected values that fall outside of specific rule thresholds are deemed to be in violation and require attention to correct.
- Reporting MySQL best practice events and violations.
- Providing MySQL expert advice for MySQL best practice violations.
The MySQL Enterprise Repository is built on the MySQL Enterprise Server and is used to store MySQL server and OS level data collections for each of the monitored MySQL Servers. Data collections are used by the Service Manager to evaluate and report the health and status of the monitored MySQL environment.
The Web Client
The MySQL Enterprise Web client is written in Java server pages and provides the graphical user interface (GUI) for the Enterprise Monitor application. The Service Manager uses the Web Client to proactively inform users of MySQL Advisor Rule violations and to provide advice on how best to address and correct the underlying issue.