Chapter 9 Performance Tuning MySQL Enterprise Monitor
Prev |  | Next

## Chapter 9 Performance Tuning MySQL Enterprise Monitor

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.

## 9.1 Tuning Memory

This section describes how to adjust the resources available to your MySQL Enterprise Service Manager installation.

### Tuning Tomcat

If you experience MySQL Enterprise Service Manager performance issues, increasing the amount of RAM available to the JVM installed with Tomcat can resolve those issues. The JVM memory settings are defined by the `JAVA_OPTS` line of the `setenv` file which sets the environment variables for Tomcat.

Table 9.1 Apache Tomcat configuration file location (default)

Operating System | Path
--- | ---
Microsoft Windows | `C:\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`

The following `setenv` variables are defined by the installation type:

Table 9.2 Installation Parameters

Parameter | Small | Medium | Large
--- | --- | --- | ---
Tomcat Heap Size | 256MB | 768MB | 2048MB
Tomcat MaxPermSize | 200MB | 512MB | 1024MB

- `--JvmMs` (Windows)/`-Xms` (all other platforms): sets the minimum size of the Tomcat JVM heap.

- `--JvmMx`(Windows)/`-Xmx` (all other platforms): sets the maximum size of the Tomcat JVM heap.

  The minimum and maximum heap size are set to the same value to have all the available memory set for the Tomcat JVM's sole use from startup.

- `MaxPermSize`: defines the maximum size of the pool containing the data used by Tomcat's JVM.

  Important

  `MaxPermSize` is not supported in Java 8. This parameter is not present in new installations of MySQL Enterprise Service Manager, but is not removed by the upgrade process. As a result, a log message is generated explaining the deprecation of the parameter.

This can be adjusted depending on the size of your installation, and the free memory on the host that MySQL Enterprise Service Manager is installed upon. For example, if you have installed the MySQL Enterprise Service Manager on a well-resourced server with a 64-bit operating system, 64GB of RAM, and are monitoring more than 100 agents, increasing the heap size to 5 or 6GB may be necessary. This depends on the MySQL server load, and amount of data collected by the agents.

Important

If you change these settings, you must restart the MySQL Enterprise Service Manager.

The following are examples of medium-sized, default settings, as defined by the medium installation choice:

UNIX, Linux, and Mac

    JAVA_OPTS="-Xmx768M -Xms768M -XX:+HeapDumpOnOutOfMemoryError
      -XX:HeapDumpPath=/opt/mysql/enterprise/monitor/apache-tomcat/temp
      -XX:+UseParallelOldGC -XX:MaxPermSize=512M"

Microsoft Windows

    set JAVA_OPTS=--JvmMs 768 --JvmMx 768 ++JvmOptions="-XX:+UseParallelOldGC"
      ++JvmOptions="-XX:+HeapDumpOnOutOfMemoryError"
      ++JvmOptions="-XX:HeapDumpPath=@@BITROCK_TOMCAT_ROOTDIR@@\temp"
      ++JvmOptions="-XX:MaxPermSize=512M"

If the MySQL Enterprise Service Manager is insufficiently resourced, the monitoring agents are also affected. If the agents are unable to communicate with the MySQL Enterprise Service Manager, their performance also degrades.

### Tuning InnoDB Memory

The MySQL Enterprise Monitor repository uses the InnoDB storage engine. The installation process sets a default value for InnoDB based on the installation size. Tuning the InnoDB Buffer Pool can have a significant impact on performance, for both interaction with the Web UI, and overall resource requirements on the host.

The configuration file for the MEM MySQL repository can be found in the following locations:

Table 9.3 MEM repository configuration file location (default)

Operating System | Path
--- | ---
Microsoft Windows | `C:\Program Files\MySQL\Enterprise\Monitor\mysql\my.ini`
Linux / Solaris | `home/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.

## 9.2 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 enables 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.4 MEM repository configuration tool location (default)

Operating System | Path
--- | ---
Microsoft Windows | `C:\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
    ------                   -----------
    --accept-keystore-password, --akp    If specified, the user will be asked
                                         the keystore password, otherwise the
                                         password "changeit" will be used to
                                         access the keystore
    --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
    --new-install, --ni                  Generates a keystore with a fresh self-
                                         signed certificate for a new installation
    --tbp, --tomcat-backup-path          Tomcat backup path to be used to
                                         perform upgrade
    --upg, --upgrade                     Upgrades certificates on an existing
                                         non OS X installation
    --upgo, --upgrade-osx                Upgrades keystore on an existing OS X
    -v, --version                        Displays the version of the agent and components

### Updating MySQL Enterprise Service Manager Configuration

    Option                   Description
    ------                   -----------

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

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 are prompted to enter the password for the new user, and the repository configuration is updated. Once finished, restart the Apache Tomcat server.

### Updating Security Configuration

    Option                   Description
    ------                   -----------
    --accept-keystore-password, --akp    If specified, the user will be asked
                                         the keystore password, otherwise the
                                         password "changeit" will be used to
                                         access the keystore
    --new-install, --ni                  Generates a keystore with a fresh self-
                                         signed certificate for a new installation
    --tbp, --tomcat-backup-path          Tomcat backup path to be used to
                                         perform upgrade
    --upg, --upgrade                     Upgrades certificates on an existing
                                         non OS X installation
    --upgo, --upgrade-osx                Upgrades keystore on an existing OS X

## 9.3 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.5 MEM repository configuration tool location (default)

Operating System | Path
--- | ---
Microsoft Windows | `C:\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.

## 9.4 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 increased by 2 for every 15-20 MySQL instances monitored.

Prev | Up | Next
Chapter 8 Unattended Installation Reference | Home | Chapter 10 Configuration Utilities