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 1GB to 4GB depending on the size of your installation, and the free memory on the host that MySQL Enterprise Monitor is installed upon.

The MySQL Enterprise Monitor repository uses the InnoDB storage engine, with the InnoDB Buffer Pool set to 768MB 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 following locations:

Table 9.2 MEM repository configuration file location (default)

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

It is possible to increase the value of the innodb_buffer_pool_size variable to as high as 80% of the physical memory available on the host machine. It is not recommended to raise it higher.

Tuning CPU

If both Apache Tomcat server and MySQL repository are installed on the same host, the best option within large scale environments is to move the MySQL Instance to its own host. This will enable 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 run mysqldump and import the dump into the new MySQL instance.

  3. Modify Tomcat's configuration to use 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:

  • A single agent, with default settings and all advisors enabled, should monitor no more than 10 MySQL instances.

  • If the agent is monitoring more than 10 MySQL instances, the agent heapsize must be increased by 64MB for every 10 additional MySQL instances.

  • The data-reporting-threads parameter must be set to 2 for every 15-20 MySQL instances monitored.

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.

  • Monitoring one MySQL instance: the agent backlog can store up to 30 minutes of monitored data before the backlog cache is filled and data dropped.

  • Monitoring 10 MySQL instances: the agent backlog can store up to 3 minutes of monitored data before the backlog cache is filled and data dropped.

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.