1.3 MySQL Enterprise Service Manager

MySQL Enterprise Service Manager is the central hub of the MySQL Enterprise Monitor installation and is responsible for the following:

MySQL Enterprise Service Manager is a web application which runs on the Apache Tomcat server.

MySQL Enterprise Service Manager also contains its own Agent which, in a default installation, is used to monitor the repository and host. It can also be used to monitor other, remote MySQL instances. This Agent is installed automatically, as part of the MySQL Enterprise Service Manager installation.

MySQL Enterprise Service Manager Repository

The repository is a MySQL instance which stores all data collected by the Agent. The majority of the data collected by the Agent is analyzed on-the-fly by the Advisors, then stored in the repository. The graphs and reports utilise the stored data to present information in the MySQL Enterprise Monitor User Interface.

MySQL Enterprise Monitor installer installs and configures the MySQL repository. It is also possible to use an existing MySQL instance for this purpose.

MySQL Enterprise Monitor User Interface

The MySQL Enterprise Monitor User Interface is a web-based interface to the MySQL Enterprise Service Manager. MySQL Enterprise Monitor User Interface provides a quick overview of the current status of your hosts and MySQL instances, and enables you to drill down into the current status, events, and historical information submitted by each MySQL Enterprise Monitor Agent.

The main features of the MySQL Enterprise Monitor User Interface include:

  • A simple Overview dashboard that gives an overview of the current health and status of all assets, a list of top critical and emergency events that should be handled, and graphs that relay database statistical information.

  • The Configuration page lets you customize the Advisors and Event Handling for your system. For example, this includes setting thresholds for Advisors, and email addresses to send alerts.

  • The Query Analyzer page helps you identify problematic queries.

  • The Replication dashboard monitors the structure and health of your replication environment.

  • The Events page lists all monitored events, which can be sorted and searched.

  • The MySQL Instances dashboard lists all monitored MySQL instances, which can be analyzed, configured, and grouped.

  • The Graphs & Reports section includes graphs with compiled data for your system that are updated according to the chosen assets. This includes the All Timeseries Graphs and InnoDB Buffer Pool Usage graph pages.

  • The What's New? tab gives a live connection to the My Oracle Support site, with news about the latest releases, critical fixes and patches, current service requests, and suggestions for completing your installation.

MySQL Enterprise Advisors

Advisors filter and evaluate the information broadcast by the Monitoring Agents and present it to the Events page when the defined thresholds are breached. They also present advice on what caused the breach and how to correct it. There are more than 200 Advisors, all of which are enabled by default. Thresholds are the predefined limits for Advisors. If the monitored data breaches the defined threshold, an event is generated and displayed on the Events page. Advisor thresholds use a variety of different value types, depending on the monitored value. Some use percentages, such as percentage of maximum number of connections. Others use timed durations, such as the average statement execution time. It is also possible to check if specific configuration elements are present or correct.

The following types of Advisor are provided:

  • 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.

It is also possible to create custom Advisors.

The Advisors configure the type of data collected by the Agent. If you do not want to monitor for a specific type of data, disabling the Advisor responsible for that data type instructs the Agent to stop collecting that data.

Events and Notifications

The MySQL Enterprise Service Manager alerts you of Threshold breaches in the following ways:

  • Events: If an Advisor's defined Threshold is breached, an Event is generated and displayed on the Events page. This is the default alert method.

  • Notifications: MySQL Enterprise Service Manager can be configured to send alerts by e-mail, or SNMP traps. These methods must be configured and are not enabled by default.

Query Analyzer

The Query Analyzer enables you to monitor all SQL statements executed on the monitored MySQL databases. The query data can be provided in one of the following ways:

  • Performance Schema: for monitored versions of MySQL 5.6.14 or higher, the Agent retrieves query information from the Performance Schema. For more information, see Section 23.1.1, “Using the MySQL Performance Schema”

  • MySQL Enterprise Monitor Aggregator: aggregates raw query statistics taken directly from client connections, but analyzed out-of-band and transmitted to the MySQL Enterprise Service Manager saving memory and processing overhead for client statements. MySQL Enterprise Monitor Aggregator can provide data from the Connector/PHP or, when used with the MySQL Enterprise Monitor Proxy, directly from the client application.

    For more information, see Chapter 11, Proxy and Aggregator Installation

  • MySQL Connectors: combined with the corresponding MySQL Enterprise Plugin can provide tracing and statistical information directly to MySQL Enterprise Service Manager.

    For more information, see Chapter 12, Configuring Connectors

Important

Currently, it is possible to use only one source for the Query Analyzer. That is, if you are using MySQL Enterprise Monitor Proxy and Aggregator, you must deactivate Performance Schema on the monitored instance. The same is true if you are using MySQL Connectors to aggregate query data for the Query Analyzer.