The post provides an overview of what features the MySQL Enterprise Monitor provides and how it can be used to improve MySQL administration and performance. 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 pinpointing SQL code that is causing a slowdown, quickly diagnose the inefficiencies and get the affected application back to peak performance.
MySQL 5.6.14 and later make it easier than ever to collect this critical query performance information, with built-in support for collecting query performance data; no special client configuration as required with older MySQL versions.
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 the 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 – Checks the MySQL instance installation and configuration.
- Agent – Checks the status of each MySQL Enterprise Monitor Agent.
- Availability – Checks the availability of the MySQL process and the connection load.
- Backup – Checks whether backup jobs succeed or fail, required resources, and information about MySQL Enterprise Backup specific tasks.
- Cluster – Checks the status of the monitored MySQL Cluster.
- Graphing – Data for graphs.
- Memory Usage – Indicate how efficiently you are using various memory caches, such as the InnoDB buffer pool, MyISAM key cache, query cache, table cache, and thread cache.
- Monitoring and Support Services – Advisors related to the MySQL Enterprise Monitoring services itself.
- Operating System – Checks the Host Operating System performance.
- Performance – Identifies potential performance bottlenecks, and suggests optimizations.
- Query Analysis – Advisors related to Queries and Query Analysis.
- Replication – Identifies replication bottlenecks, and suggests replication design improvements.
- Schema – Identifies schema changes.
- Security – Checks MySQL Servers for known security issues.
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 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.