Download

The first step of the installation is to download MySQL Enterprise Monitor. There are two places you can download MySQL Enterprise Monitor. Existing MySQL customers can download it from the Patches & Updates tab in My Oracle Support (MOS) . This is the recommended location for customers as Patches & Updates gets updated more frequently and includes all versions since 2011. The alternative location is the Oracle Software Delivery Cloud at https://edelivery.oracle.com/ which also allows registered users to download a 30-day trial version. These instructions cover the Oracle Software Delivery Cloud.

You start out at the “homepage” as shown in Figure 10-2.

If you do not have a login, you need to create a new user using the New User? Register Here icon. Once you have logged in, you get to the search page. Figure 10-3 shows part of the search form.

Choose Release in the drop box to the left of the text field. If you are interested in other products as well, you leave it on the default value which is All Categories which includes software packages. In the text field, enter MySQL Enterprise Monitor and click MySQL Enterprise Monitor in the search list that shows up or click the Search button to the right of the text field (neither the list nor button is shown in the figure). Then click Add to Cart next to the result for MySQL Enterprise Monitor.

When the product has been added to the cart, you can click the Checkout link near the top right of the page (also not shown in the figure). The next screen is shown in Figure 10-4 and allows you to choose which platforms to download for.

Choose the platforms you are interested in. If you plan on having the Service Manager on one platform while monitoring instances with an Agent installed on another platform, you need to choose both platforms. When you have decided which platforms you want to download for, click Continue.

The next step is to accept the license agreement. Please read carefully before accepting it. The Oracle Trial License Agreement is at the end of the document. Once you have accepted the terms and conditions, click Continue.

The last step is to choose which parts of MySQL Enterprise Monitor you want to download. This is shown in Figure 10-5.

There are two packages for each platform with one package for the Service Manager and one for the Agent. Optionally (recommended), you can click the View Digest Details link at the center bottom of the screenshot to show the SHA-1 and SHA-256 checksums for each file. You can use these to validate the download completed successfully.

You can download the files in two ways. If you click the filename, you download the files one by one. Alternatively, check the files you want and click the Download button to start the download using a download manager. If you do not have the download manager installed, you will be guided through installing it before the download commences.

Once the download has completed, you can start the installation process.

Installation Process

MySQL Enterprise Monitor uses its own installer which works the same on all platforms. There is support for performing the installation using a wizard mode either through a graphical user interface or in text mode, or you can provide all the arguments on the command line and use the unattended mode.

The exact filenames and sizes depend on the platform and MySQL Enterprise Monitor version. Notice that there are two executables, in this case mysqlmonitor-8.0.17.1195-windows64-installer.exe and mysqlmonitor-8.0.17.1195-windows64-update-installer.exe. The former is for installing MySQL Enterprise Monitor from scratch, while the other (also sometimes called the update installer) is for performing an upgrade of an existing installation. The PDF and HTML files are the manual, but you are usually better off using the online manual at https://dev.mysql.com/doc/mysql-monitor/en/ as that is updated regularly.

This discussion will continue using the graphical user interface for the installation. You start the installation by executing the installer without any arguments. The first step is to choose the language (English, Japanese, and Simplified Chinese are available). Then you are told that you need to make sure you keep the usernames and passwords you enter during the installation in a secure location.

After passing the welcome screen, the configuration starts in proper by specifying the installation location. On Microsoft Windows, the default location is C:Program FilesMySQLEnterpriseMonitor, and on Linux it is /opt/mysql/enterprise/monitor when installing as the root user or mysql/enterprise/monitor relative to the home directory when installing as a non-privileged user.

The next screen shown in Figure 10-6 asks you to choose how large a system you will monitor.

The system size determines the default settings for things like the memory configuration of the Service Manager. You can tune the memory settings manually after the installation has completed, but choosing the correct system size means you do not have to worry about these settings initially. Unless you just want to try MySQL Enterprise Monitor with a few instances, choose the medium or large system.

Next, you need to specify the port numbers to use. MySQL Enterprise Monitor uses Tomcat server for the front end with port 18080 as the default unencrypted port and 18443 as the default SSL port. You will always be using the SSL port. (The non-SSL port is there for legacy reasons but cannot be used for the front end.)

At this point, if you are installing on Linux using the root account, you will be asked which user account you want to run the Tomcat processes under (the MySQL Server repository process will use the mysql user). The default is mysqlmem . If you are installing on Linux with a non-root account, you will be notified that it is not possible for the installer to set up auto-start.

The Service Manager uses a MySQL instance to store the data including the collected metrics. You have a choice (see Figure 10-7) between using the MySQL instance bundled with the installer and using an existing MySQL instance.

Unless you have very good reasons to choose otherwise, it is recommended to use the bundled MySQL database. This not only allows the installer to use a base configuration that is known to work well with the Service Manager, it also simplifies upgrades.

You now get to choose the username and password for the connection the Service Manager uses to the MySQL instance as well as the port number and schema name. This is shown in Figure 10-8.

Do not take the choice of password lightly. The monitoring will include many details about your system including the hostnames and queries. This means it is important to choose a strong password.

That is the end of the configuration, and the installer is ready to commence the actual installation step. The installation takes a little while as it includes both installing a MySQL Server instance and the Tomcat server front end. When the installation is done, a confirmation screen is shown followed by the warning in Figure 10-9.

The installer creates a self-signed certificate for the SSL connections. This will encrypt the communication just fine, but it does not allow for validating that you are connected to the correct server. You can choose to purchase a certificate signed by a trusted provider and make MySQL Enterprise Monitor use that. If you continue to use the default self-signed certificate (which is assumed here), the browser will complain the first time you connect to the Service Manager that you cannot trust the connection (this is harmless in this case).

That completes the installation. The final screen shows a confirmation that you have completed the wizard, and you can choose to open the readme file and launch the browser. The installer has started the Service Manager in the background, so you do not need to do anything else than open the URL to the Service Manager in the browser. If your browser is on the same host as where you installed the Service Manager and you chose the default SSL port (18443), the URL is https://localhost:18443/.

As mentioned, if you use the default self-signed certificate, the browser will warn you that there is a potential security risk. An example of this from Firefox is shown in Figure 10-10.

You will need to accept this risk. How to do this depends on your browser and version. In the case of Firefox 68, you go to the Advanced option and choose Accept the Risk and Continue.

The first step when you connect to the Service Manager is a little more configuration. Most of this is collected in one screen as shown in Figure 10-11.

The top part requires you to configure two users. The user with the manager role is the administration user that you use to log in to the Service Manager through the browser (you can later create more users with less privileges if required). The user with the Agent role is the user that you use if you install Agents to monitor MySQL instances on other hosts. Make sure you choose strong passwords for both users.

The lower left allows you to configure whether MySQL Enterprise Monitor should check for upgrades automatically and, if so, whether proxy settings are required. To the lower right, you can configure how long data should be kept. The longer you keep data, the further you can go back in time to investigate issues, and the more details you keep. The cost is that the size of the database increases.

Once you have completed the setup, you will be taken to a What’s New page, and you can set the time zone and locale you want to use for the newly created administration user.

Since it is likely you will need to start and stop the Service Manager during you testing, the next section will show how to do that.

Microsoft Windows

On Microsoft Windows, the installer always requires the administrator privilege to run, which means it can also install the Service Manager processes as services. By default, the services are set to automatically start and stop when you boot and shut down the computer.

You can edit the settings for the services by opening the Services application. On Windows 10, the easiest way to do this is to use the Windows key on the keyboard (alternatively open the Start menu by clicking the Windows icon in the lower-left corner) and type Services as shown in Figure 10-12.

The search result may look different to some degree compared to the screenshot. Click the Services app match under Best match. This opens the application where you can control the services. From the Services application, you can control the services by starting, stopping, pausing, or restarting the service. The repository service is named MySQL Enterprise MySQL, and the Tomcat service is named MySQL Enterprise Tomcat as shown in Figure 10-13.

When you click a service, you get the basic control actions in the pane to the left of the services list. You can also right-click the service to get the actions as well as the option to edit the properties of the service. These properties include whether to automatically start and stop the service.

Linux

How you start and stop MySQL Enterprise Monitor on Linux depends on whether you performed the installation using the root operating system user. If you used the root user, you start and stop the processes using the service command (there is no native support for systemd) with the mysql-monitor-server service; otherwise, you use the mysqlmonitorctl.sh script that is to the base of the installation directory. Either way, you can add the tomcat or mysql argument to just change the status of one of the processes.

First, both services are started and then restarted, and finally the services are stopped one by one. It is not necessary to stop the services one by one, but it can be useful, for example, if you need to do maintenance on the repository.

The steps are very similar to the previous example with the service command. In fact, the script invoked by the service command is the same as the mysqlmonitorctl.sh script except that the paths and usernames in it depend on the operating user used to install the Service Manager and the installation path.

General Navigation

The MySQL Enterprise Monitor user interface divides the features into logical groups with support for filtering by the group, host, Agent, or instance. This section will give a brief tour of the interface with the aim that when graphs or reports are mentioned later in the book, you can find them in the interface, if you want to explore it closer.

Figure 10-17 shows the top-left part of the page in the user interface. This is where you choose which feature to access and for which targets you want data to be displayed.

The navigation to the features is centered on the left-hand pane with the filters applied in the two search fields at the top of the page. The search field with the label Global Summaries in the screenshot allows you to choose a group of instances. Groups can be created manually but are also created automatically for instances that are replicating between each other. Global Summaries is a special group that includes all instances. The right-hand search field allows you to limit the instances, Agents, or hosts included from the group.

One term that is necessary to explain further is advisors.

Advisors

Advisor is the name MySQL Enterprise Monitor uses for the rules that define how often data is collected, which conditions trigger events, and which severity the event is. This is an important concept that you should take some time to understand and configure.

One of the most important steps to get a useful monitoring solution is to ensure you get the right events (alerts) at the right time but avoid unnecessary events. This includes ensuring that each alert is set to the appropriate severity. You may at first think that the more events the better, so that you know everything that goes on. However, that is not how you best use a monitoring system. If you have many false positives when you examine the events or you get woken up unnecessarily at 3:00 a.m. for an issue that easily can wait until the morning, then you start ignoring events, and that is the sure recipe to miss an important event sooner or later. In short, your work with the advisors should be ongoing to keep improving them to trigger “just the right” events at “just the right” time.

The advisors can be configured under the Configuration item in the left-hand pane. The advisors are organized in groups as shown in Figure 10-18.

Each group covers advisors of similar type, for example, there is the Performance group with 22 advisors such as Excessive Number of Locked Processes and Indexes Not Being Used Efficiently.

By default, all advisors are enabled with the thresholds for the severity levels set to values that work well in many cases. However, as no two systems are the same, you will need to fine-tune the settings which you do by expanding the group and clicking the menu icon to the left of the name of the advisor as shown in Figure 10-19.

You can also expand the advisor using the + icon to the left of the advisor item which allows you to edit the advisor for a specific group of instances or a single instance. The ? icon in the Info column provides additional information such as the expression evaluated or the source of the data for the advisor. There is additional information available which is not shown in the figure.

Timeseries Graphs

The timeseries graphs are the graphs that show the metrics over time. This is a standard feature of all monitoring solutions. You can filter which graphs to display and change the time frame to plot as well as the plotting style.

Figure 10-20 shows a part of the timeseries graphs page focusing on the controls to access filtering and the plotting style.

Above the graphs are the options to choose which graphs to display and the time frame of the graphs. The search field to the left in the screenshot allows you to choose between saved timeseries groups. By default, there is a single group called All Timeseries Graphs that – as the name suggests – includes all timeseries graphs appropriate for the instance filtering in place.

You access the options for the timeseries graphs by using the funnel icon in the top-right corner of the screenshot. This will open a frame that allows you to choose which graphs to display and the time frame to cover.

The two small buttons below each graph allow you to toggle between using the line and stacked plotting modes. The screenshot shows an example of the stacked mode in the topmost graph and the line mode in the lower graph. The line mode is the default. You can also change the height of the graphs using a slider (not included in the screenshot) to the left of the field to select between saved graph groups.

The three icons above the graph become visible when you hover above the graph and allows you to export the data for the graph in CSV format, open the graph as a PNG image, or move the graph which allow you to reorder the graphs as it suits you best. In this case where there are two graphs grouped together, the controls apply to both graphs.

An alternative way to change the time frame of the graphs is to highlight the part of a graph that is of interest and zoom in on that part. This also allows you to go to the Query Analyzer to examine which queries were executed during that period. Figure 10-21 shows an example of highlighting a time frame in a graph.

Notice that in the upper-right part of the highlighted area, there are three icons to control what to do with the selection. The X in the box discards the selection, the database cylinder opens the graph for the selected time frame in the Query Analyzer, and the magnifying glass zooms the timeseries graphs to use the selected time frame.

The Query Analyzer

The Query Analyzer is a feature that makes MySQL Enterprise Monitor stand out from other monitoring solutions. It allows you to see which queries execute on the instance in a given period which is invaluable when investigating performance problems.

The Query Analyzer page is divided into three areas. At the top there is access to filtering options, then optionally there is one or more graphs, and the rest of the page is a list of statements. Figure 10-22 shows an example of this.

The drop box at the top saying All Statements in the screenshot allows you to choose the statement type to show statements for. The default is to include all statements. To the right there is the Configuration View button which takes you to a page where you can configure how the Query Analyzer page should be configured. This includes the time frame to cover, which graphs to display, filtering options, and which information to include for each statement.

By default, the Query Analyzer includes the graph for the query response time index (QRTi) . The definition of the query response time index and how to use it will be covered in Chapter 19 when the Query Analyzer is used to find candidates for optimization.

That concludes the tour of MySQL Enterprise Monitor. You are encouraged to explore the user interface further on your own.