Chapter 9 Performance Tuning MySQL Enterprise Monitor

To conserve system resources and run on a moderately-sized machine, by default, MySQL Enterprise Monitor is configured to monitor only a handful of MySQL Instances. If you will be monitoring many MySQL Instances or Hosts, this section explains which configuration settings to adjust for your environment.

There are two major components of the Service Manager that require tuning -- the MySQL Instance that is used for the Repository, and the Apache Tomcat application server that serves the Web UI and performs the back-end collection and analysis of data. Both consume CPU and memory resources, whilst the MySQL Instance is the primary driver for I/O.

Tuning Memory

The first step is to cache as much data as possible in order to decrease I/O and CPU pressure on the hosts running the Service Manager and Repository. The main setting controlling memory usage by the Apache Tomcat server can be found in the following file*:

Table 9.1 Apache Tomcat configuration file location (default)

Operating SystemPath
Microsoft WindowsC:\Program Files\MySQL\Enterprise\Monitor\apache-tomcat\bin\setenv.bat
Linux / Solaris/opt/mysql/enterprise/monitor/apache-tomcat/bin/setenv.sh
Mac OS X/Applications/mysql/enterprise/monitor/apache-tomcat/bin/setenv.sh

Each of these have a line that sets a JAVA_OPTS variable with various settings that are passed to the JVM of Apache Tomcat on startup.

For Microsoft Windows, the variable to look for within this line is "--JvmMx 768", and for other operating systems, the variable is "-Xmx768M".

This can be adjusted anywhere from 1GiB to 4GiB depending on the size of your installation, and the free memory on the host that MySQL Enterprise Monitor is installed upon. You can monitor whether changing this variable has had any effect by looking within the All Timeseries Graphs page, by focusing on the MEM Built-in Agent node underneath the host where installed - this should show you these graphs:

Figure 9.1 Performance Tuning Memory: MEM Built-in Agent

Performance Tuning Memory: MEM Built-in Agent

Lower over all CPU Percentage, and both Eden GC Time and Old Generation GC Time, would indicate that tuning the memory has had some effect.

The MySQL Enterprise Monitor repository uses the InnoDB storage engine, with the InnoDB Buffer Pool set to 768MiB by default. Tuning the InnoDB Buffer Pool can also have a significant impact on performance, for both interaction with the Web UI, and overall CPU and I/O resource requirements on the host.

The configuration file for the MEM MySQL repository can be found in the its configuration file*:

Table 9.2 MEM repository configuration file location (default)

Operating SystemPath
Microsoft WindowsC:\Program Files\MySQL\Enterprise\Monitor\mysql\my.ini
Linux / Solaris/opt/mysql/enterprise/monitor/mysql/my.cnf
Mac OS X/Applications/mysql/enterprise/monitor/mysql/my.cnf

Modify the innodb_buffer_pool_size variable to as high as free memory allows on the machine - leave at least 1GiB free memory for other resources, such as temporary tables etc., and monitor the host via the Web UI to make sure that no swapping occurs.

Tuning CPU

If you have both the Apache Tomcat server and the MySQL Instance used as a repository installed upon the same host, the best option within large scale environments is to move the MySQL Instance to its own host. This will allow both processes to use up the resources of each host, and allows scaling to monitor hundreds of MySQL Instances and Hosts.

To do this, you should:

  1. Stop the application Apache Tomcat server and MySQL Instance.

  2. Copy the datadir contents to the new host (if moving to a fresh MySQL instance), or mysqldump and reload the dump in to the new MySQL instance.

  3. Modify the configuration to point the Apache Tomcat server to the new MySQL Instance on the new host.

The configuration should be updated with the configuration tool (as it encrypts the password for the repository instance), this tool can be found at the following location*:

Table 9.3 MEM repository configuration tool location (default)

Operating SystemPath
Microsoft WindowsC:\Program Files\MySQL\Enterprise\Monitor\bin\config.bat
Linux / Solaris/opt/mysql/enterprise/monitor/bin/config.sh
Mac OS X/Applications/mysql/enterprise/monitor/bin/config.sh

This Service Manager repository tool has the following options:


Option                   Description
------                   -----------
--help                   Prints this usage message
--md, --mysql-db         MySQL database for the Service Manager repository
--mp, --mysql-port       MySQL port for the Service Manager repository
--ms, --mysql-server     MySQL server for the Service Manager repository
--mu, --mysql-user       MySQL username for the Service Manager repository
-v, --version            Displays the version of the agent and components

To update the configuration, run the script in the following way:

shell> ./config.sh –mysql-server=[new host] –mysql-port=[new port] –mysql-user=[new user]
Important

The configuration script must be run by the same user as the MySQL Enterprise Service Manager.

You will be prompted to enter the password for the new user, and the repository configuration will then be updated. Once finished, restart the Apache Tomcat server.

Tuning Apache Tomcat Threads

When monitoring with a large number of Agent processes deployed, the default number of threads that are created within the Apache Tomcat server may not be sufficient. By default, it is configured to create 150 threads to communicate with the HTTPS port.

This is configured with the maxThreads setting within the server.xml configuration file*:

Table 9.4 MEM repository configuration tool location (default)

Operating SystemPath
Microsoft WindowsC:\Program Files\MySQL\Enterprise\Monitor\apache-tomcat\conf\server.xml
Linux / Solaris/opt/mysql/enterprise/monitor/apache-tomcat/conf/server.xml
Mac OS X/Applications/mysql/enterprise/monitor/apache-tomcat/conf/server.xml

The following section should be modified:


<Connector port="18443" 
  protocol="org.apache.coyote.http11.Http11Protocol" SSLEnabled="true"
  maxThreads="150" minSpareThreads="25" maxSpareThreads="75"

...

A good baseline to test would be the number of Agents that you have checking in to the Service Manager plus 50. For example if you have 150 Agents checking in, set the maxThreads variable to 200.

Note

* We list default paths to configuration files and tools, so adjust according to where the Service Manager was installed on your system.

Tuning Agent Memory Requirements

The following are the recommended settings for MySQL Enterprise Monitor Agent:

Agent Backlog

The agent backlog is a caching mechanism which stores monitoring data in the event the agent cannot communicate with the MySQL Enterprise Service Manager. The backlog can store 20MB of monitored data, 10MB in active RAM, and 10MB on the local filesystem.

The agent can lose contact with the MySQL Enterprise Service Manager in the event of a Service Manager restart, network connectivity issues and so on.

Under certain circumstances, if the server loses power for example, the backlog can be corrupted and be unreadable by the agent. When the agent restarts, if the backlog files are corrupted, the monitoring agent backs up the corrupted files to a new folder named backlog.xxx. Where xxx is the UNIX timestamp, marking the time the backup was created. The agent creates a new backlog file, and logs the error.