2.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:

  • Receiving and storing information from the Agents.

  • Configuring the types of information collected by the Agents.

  • Analyzing the collected data using the Advisors.

  • Generating alerts and sending mail or SNMP notifications based on the Advisor configuration.

  • Displaying the collected data, events and notifications.

  • Graphing and reporting on the collected data.

  • Analyzing the SQL queries performed on the monitored instance, in real-time, using the Query Analyzer.

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 utilize 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:

  • Overview provides an overview of the current health and status of all assets, or detailed information on the selection in the Target selection menus, a list of top critical and emergency events that should be handled, and graphs that relay database statistical information.

  • The Configuration section enables you to customize the Advisors and Event Handling for your system. For example, this includes setting thresholds for Advisors, Users & Roles, Group configuration, and email addresses to send alerts. This section also contains the Instances view, which lists all monitored MySQL instances, which can be analyzed, configured, and grouped and enables you to add and remove monitored instances.

  • Query Analyzer enables analysis of problematic queries.

  • Replication displays the details and health of your replication environment.

  • The Events view lists the events generated by all monitored assets, which can be sorted and searched.

  • Metrics view includes all graphs and reports with compiled data for your system, updated according to the assets selected in the target selector. The contents of the Metrics section are dependent on the selection made in the Target selectors. Some reports are only useful for NDB Cluster monitoring, or for Replication Groups, and are only displayed if one of those assets are selected.

MySQL Enterprise Advisors

Advisors filter and evaluate the information broadcast by the Monitoring Agents and present it to the Events view 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.

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

  • NDB Cluster: Checks the status of the monitored MySQL NDB Cluster.

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

For more information on advisors, see Chapter 20, Expression-Based Advisor Reference and Chapter 21, GUI-Based Advisor Reference.

Events and Notifications

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 in the Events view. 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.