How To Use The Percona Toolkit To Speed Up Event Database Optimization

Applies To

• Zenoss 4.2.4, SP 336 or later; or, Zenoss 4.2.5 or later
• ZenDS 5.5.36 or later

Summary

By default, the Zenoss event server daemon, zeneventserver, optimizes a set of important tables in the event database every 12 hours using the SQL OPTIMIZE TABLE statement. Each table is locked while it is being optimized, which can delay event processing. This is a particular issue for the event_summary table as it can become very large and therefore take a long time to optimize.

The pt-online-schema-change script, part of the Percona Toolkit for MySQL from percona.com, can optimize tables without locking them. The zeneventserver daemon has built-in support for the use of this tool. This article describes how to install and enable the use of pt-online-schema-change for optimization of the event_summary table by zeneventserver.

Critical Note

To prevent possible database corruption, once the Percona Toolkit is enabled for zeneventserver, you must use the --enterprise-tools option when backing up the Zenoss databases with zenbackup. Additionally, you cannot use the mysqldump command on the event database (zenoss_zep) or any of its tables. Note that you can make the --enterprise-tools option the default for zenbackup by following the directions in the Update and Test zenbackup's Configuration section below.

Procedure

Determine the Host Name of Your Database Server

To determine the host name of your ZenDS server, examine the value of the zodb-host option in $ZENHOME/etc/global.conf on the Resource Manager host (sometimes referred to as the Zenoss master server).

Run the following command as the zenoss user to display the ZenDS server's host name:
$ awk '/^zodb-host/{print $2}' $ZENHOME/etc/global.conf

Determine Your Resource Manager and ZenDS Versions

1. Run the following command as zenoss on the Resource Manager host (sometimes referred to as the Zenoss master server):
   $ zenup status

   Product: zenoss-resmgr-4.2.5 (id = zenoss-resmgr-4.2.5)
   Home: /opt/zenoss
   Revision: 80
   Updated On: Mon Apr 28 15:33:51 2014

2. Consult the output and verify the following:
   
   • The product (zenoss-resmgr) version is 4.2.5 or greater
   • OR, the product (zenoss-resmgr) version 4.2.4, and the revision number is 336 or greater
3. Run the following command as zenoss on the ZenDS database host:
   $ zends -V

   /opt/zends/bin/.mysql Ver 14.14 Distrib 5.5.36, for linux2.6 (x86_64) using EditLine wrapper

4. Verify that the version number following “Distrib” is 5.5.36 or greater.

If your Zenoss instance does not meet both of these requirements, or if the zenup command was not found, do not proceed with this article and contact Zenoss support for assistance.

Install the Percona Toolkit

1. Download the latest version of the Percona Toolkit to the Resource Manager host from
   https://www.percona.com/downloads/percona-toolkit/LATEST/
2. Install the toolkit as the root user on the Resource Manager host:
   # yum -y install percona-toolkit-*.noarch.rpm
3. Switch to the zenoss user:
   # su - zenoss
4. Verify the toolkit was successfully installed:
   $ which pt-online-schema-change

   /usr/bin/pt-online-schema-change

Update the Configuration for ZenEventServer

1. Login to the Resource Manager host as the zenoss user.
2. Edit $ZENHOME/etc/zeneventserver.conf
   1. Update the zep.database.optimize_use_external_tool option line to read:
      zep.database.optimize_use_external_tool=true

      Be sure to remove the leading pound sign (#) from the line.

   2. Save the file.
3. Restart the zeneventserver daemon:
   $ zeneventserver restart

Update the PATH and Prepare SSH access for the zenoss User on a Remote ZenDS Host

Follow the instructions in this section only if ZenDS is run on a host other than the Resource Manager host.

1. Login to the ZenDS host as the root user.
2. Set a password for for the zenoss user:
   $ passwd

   The password command will prompt you for the new password (twice).

3. Switch to the zenoss user:
   # su - zenoss
4. Add the following line to ~/.bashrc:

   export PATH="${ZENDSHOME}/bin:${PATH}"

5. Prepare SSH access for the zenoss user:
   1. Create the SSH configuration directory:
      $ mkdir -p ~/.ssh
   2. Set the directory access permissions:
      $ chmod 700 ~/.ssh
   3. Create a placeholder authorized keys file:
      $ touch ~/.ssh/authorized_keys
   4. Set the file access permissions:
      chmod 600 ~/.ssh/authorized_keys
6. Log into the Resource Manager host as the zenoss user.
7. Copy the zenoss user's public key to the ZenDS host.
1. Run the following command, and substitute the fully-qualified host name of the ZenDS host for zends-host:

    ssh zends-host "cat >> ~/.ssh/authorized_keys" < ~/.ssh/id_rsa.pub

2. Enter yes when prompted for confirmation by ssh.

   The ssh command adds the ZenDS host to the known hosts file of the zenoss user on the Resource Manager host.

3. Enter the password for the zenoss user on the ZenDS host when prompted by ssh.

   The ssh command appends the public key of the zenoss user on the Resource Manager host to the zenoss user's authorized keys file on the ZenDS host.

8. Test the connection to the ZenDS host using the following command. Substitute the fully-qualified host name of the ZenDS host for zends-host:

   ssh zends-host hostname

   The host name of the ZenDS host should be displayed without any prompting.

Update and Test the Configuration for ZenBackup

1. As the zenoss user on the Resource Manager host, add the following line to $ZENHOME/etc/zenbackup.conf:

    enterprise-tools True 

2. Run the zenbackup command to test the new configuration:

   $ zenbackup

   If the zenbackup command does not complete successfully, contact Zenoss support for assistance.