Monitoring MySQL with Hyperic HQ

 

Introduction: Hyperic HQ - the Complete Solution

This document is a basic primer for monitoring your MYSQL deployment with Hyperic HQ. The intended audience is the MYSQL administrator who has limited or no experience with HQ. After reading this HOWTO, you should be able to monitor your MYSQL environment instances with little trouble.

About Hyperic HQ
Hyperic HQ provides extensive system management coverage that saves time and money when managing your entire web infrastructure, including MYSQL. HQ provides comprehensive monitoring for your business-critical services, enabling your operations and development teams to focus on improving the business rather than monitoring your systems performance. The key benefits of HQ include:

• Auto-Discovery of asset inventory with one click
• Monitor metrics of 65+ technologies across 9 operating systems and architectures
• Track performance, configuration and security changes
• Set alerts based on a wide variety of metrics and triggers
• Issue control actions that allow admins to easily restart a server or service
• Extensible plugin development kit to easily customize HQ to your unique environment

So what does this mean for MYSQL admins? It means that with Hyperic HQ, you can auto-discover your MYSQL assets and view their metrics, including JTA transactions, open sockets, and memory usage, almost immediately. Also, with HQ's plugin architecture, future editions of MYSQL will be easily supported, thus giving you the assurance that upgrading MYSQL or any other data center products will not break your system management platform.

MYSQL Management Support

HQ uses the capabilities of MySQL’s built-in administrative console to provide comprehensive management of MySQL databases. HQ also gives you visibility into the parts of your infrastructure that your MySQL databases depend on. This range of coverage allows you to determine the correlation, if any, between the memory consumption of a MySQL Server instance and the CPU load on a separate machine that sends and receives data from MySQL.
HQ provides dynamic database auto-discovery and granular monitoring and control of every table in each database. This deep, low-overhead monitoring provides visibility into MYSQL internals, as well as the ability to compare and correlate these metrics across multiple tables and databases.

Hyperic monitors the following MYSQL metrics by default.  Additional metrics can be selected for monitoring.

MYSQL Metrics

Availability

Number of Databases

Virtual Memory Size

CPU Usage

Bytes Received

Data Length

Index Length

Bytes Received Per Minute

Bytes Sent

Number of Rows

Figure 1: MYSQL Configuration Using Hyperic – shows the user changing the configuration properties of the server.

Figure 2: Monitoring MYSQL Using Hyperic – shows the trends for the key metrics

Getting Started: Installing HQ

Now that we've covered the basics of monitoring MYSQL with HQ, let's go through the process of installing HQ and auto-discovering your MYSQL inventory. For the installation process, let's assume you're installing on a Unix-based machine. Note that the HQ Server and Agent support Linux, Solaris, Windows, HP-UX, and Mac OS X. If necessary, consult the list of supported platforms to determine which Windows environments are supported.
HQ includes three basic components: a server (featuring a web-based dashboard), an agent (installed on the machines to be monitored), and command-line server tools. The HQ Installer bundle includes the HQ Server, the HQ Agent, command-line tools, a PostgreSQL database server, and a JRE (Sun JRE 1.5). To run the HQ Server on the same machine that is being monitored, that's all you need. To monitor a heterogeneous mix of machines with various operating systems and chip architectures, download at least the Installer bundle and one of the Agent files.
For platforms not supported by HQ, either download a "Platform Independent" bundle, which requires a separate database and an already-configured JRE, or download and build from source.

(the following was taken from http://support.hyperic.com/confluence/display/DOC/Installation+Overview )

This document assumes an installation of HQ in a Unix or Linux environment.

Step 1. Download HQ

The first step is to download HQ from the Hyperic HQ Download Page. Download the hyperic-hq-installer-x.x.x-xxx-.tgz file for your architecture. If you already have a JRE and want to use that, or if you want to host your database on a separate machine, you can download the noJRE—or "Platform Neutral"—version. If you are using the noJRE package, be sure that your JAVA_HOME environment variable is set correctly. If you have a particular JRE you want HQ to run on, you can set an HQ_JAVA_HOME environment variable.
It usually takes a few minutes for the Installer package to be downloaded.

Step 2. Unpack the HQ package

Now that you have downloaded the package, you must unpack it. Simply untar the .tgz file.

Step 3. Install the HQ Server and Agent

After the package is untarred:
1. Run setup.sh. This can take several minutes.
./hyperic-hq-installer/setup.sh
Initializing Hyperic HQ Installation...
Choose which software to install:
1: Hyperic HQ Server
2: Hyperic HQ Shell
3: Hyperic HQ Agent
You may enter multiple choices, separated by commas.
Select the HQ Server and Agent for installation:
1,3

2. Specify where to install the HQ Server:
HQ server installation path [default '/home/hyperic']:

Install the HQ Server to any location you wish. You must have write access to that location, of course. The Agent install path can be the same as the Server install path (they install themselves to different subdirectories). At this point, the installer will begin installing the Server and Agent. When the installation has completed, the installer will exit.

Step 4. Start the HQ Server

After the HQ Server and Agent have been installed, start the HQ Server.
To start the server:
/bin/hq-server.sh start

Note that the server may be run as a Unix service and started at boot time. See this howto on the HyperFORGE for setting up HQ on a Debian-based system.

What a Successful Server Start-Up Looks Like. The following is the output from a successful Server start-up via the command line:
Starting HQ server...
Initializing HQ server configuration...
Starting HQ built-in database...
HQ built-in database started.
Booting the HQ server...
HQ server booted.
Login to HQ at: http://127.0.0.1:7080/

This should be displayed quickly. The Server will start up in the background. Depending on the hardware, the HQ Server can take several minutes to completely boot. The HQ UI will display this start-up status until the Server is completely booted; then it will display the login page.

Step 5. Start the HQ Agent

The Agent can be run either manually or integrated into your startup scripts. To learn how to integrate the agent with the /etc/init.d system on a Debian-based system, see this howto on the HyperFORGE.
To start the HQ Agent:

  Run:

/hq-agent.sh

This is displayed:
Starting agent
- Unable to load agent token file.  Generating a new one ... Done
- Invoking agent
Agent successfully started

[Running agent setup]
What is the HQ server IP address?

• Specify the HQ Server IP address. Assuming the HQ Server and HQ Agent are installed on the same machine, the address is 127.0.0.1. If the Agent is on a remote host, specify the IP address of the HQ Server.
• Specify whether the Agent will communicate with the Server via SSL.

Should Agent communications to HQ always be secure [default=no]:

It is generally safe to take the default of 'no'. In the case where the Agent and Server are on the same machine, it is very safe to say 'no'. SSL is useful if the Agent and Server need to use the open internet to communicate with each other.

• Accept the default HTTP HQ Server port (7080).

What is the HQ server port     [default=7080]:

Check Firewall Configuration
Make sure no firewalls are blocking TCP port 7080 (or 7443) on the HQ Server host. If there is a firewall device between the HQ Agent and HQ Server:

• Specify the IP address of the device as the "HQ server IP address" above.
• Configure the device to forward traffic on TCP port 7080 (or 7443) to the host running the HQ Server.

• Accept the default login and password (both are hqadmin) or specify other values:

Testing insecure connection ... Success
What is your HQ login [default=hqadmin]:
What is your HQ password:

• Specify the IP address that the Server will use to contact the Agent.

What IP should HQ use to contact the agent [default=127.0.0.1]:

Accept the default value (127.0.00.1) if the Agent and Server are on the same machine, making communication over the loopback device the most efficent. If the Agent is running on a remote host, instead specify the IP the HQ Server will use to contact this agent; the value depends on network configuration, but it is usually the IP address of the external interface of the machine.

• Accept the default port (2144) the HQ Agent listens on.

What port should HQ use to contact the agent [default=2144]:

Check Firewall Configuration
Make sure any firewalls are not blocking incoming TCP port 2144. If there is a firewall device between the HQ Agent and HQ Server:

• Enter the IP of the device as the IP address the HQ server will use to contact the agent (as specified above).
• Configure the device to forward traffic on TCP port 2144 to the host running the HQ Agent.

What a Successful Agent Start-Up Looks Like. The following is the output from a successful Agent start-up via the CLI.
- Received temporary auth token from agent
- Registering agent with HQ
- HQ gave us the following agent token
    1104817706987-8534327776788402831-4021000845020676153
- Informing agent of new HQ server
- Validating
- Successfully setup agent
The Hyperic HQ Server and Agent are now up and running.

Brass Tacks: Monitoring MYSQL with HQ

Now that installation is complete, open a browser window to:
http://hq-server-name:7080/ (substitute your HQ Server's IP address or name)
Enter the username and password, then press Enter. By default, the username is hqadmin and the password is also hqadmin.
Upon entering the Hyperic HQ Portal, you first see the Dashboard. The left panel allows you to search resources, view favorite resources, and view the available-resources summary. The right side displays the latest performance data and recent alerts, as well as other portlets for auto-discovered assets, recent control actions, and more.

Importing Resources

Once you have deployed an HQ agent on a MySQL machine, the HQ Server will auto-discover your MySQL assets. At the bottom of the “Auto-Discovery” portlet, click Add to Inventory to import the resources into the HQ inventory and start collecting metrics.

Auto-Discovery of Applications and Services

After importing, you will see on the left side of the dashboard a section for “Newly Added Resources”, which should include the platform you just added with the MySQL Server instance. After following the link to this newly-added platform, you'll see all of the auto-discovered assets in the left column, one of which will be your MySQL Server.
Clicking on the MySQL server will take you to the default MySQL monitoring page, but you will not immediately see metrics because HQ does not yet know the username and password to use to access MySQL.
Click the Tools Menu drop-down in the upper-right corner and select Configure Server. On the “Configuration Properties” screen, fill in the username and the password that you use to access MySQL. Make sure to check the option Auto-Discover Services?  HQ can now access MySQL and auto-discover all its components.
After the auto-discovery completes, you can carry out more fine-grained monitoring, viewing the metrics for all of the services of which your MYSQL deployment is composed.
Hyperic HQ is ideal for the situation where usage and resource consumption will increase the server load. HQ's ability to show graphs of performance metrics over time coupled with the ability to determine upper and lower data bounds allows sysadmins to plan future resource allocation. HQ gathers a wide swath of metric data, such as the query time, server threads, memory usage,, and more. You can also view a breakdown of these for each database on your MySQL server.

Alerting

You also may set alerts for thresholds corresponding to any type of data or combinations of data. Alerts may be sent via email, SMS, or RSS feed. When viewing an alert, whether in the Alert Center in the top navigation, email, RSS, or the recent alerts portlet on the dashboard, you can see what threshold was passed, what the actual value is at that moment, and well as a link for acknowledging the alert to let others know that you are taking action.
As an example of HQ's alerting system, you can define an alert to fire when the  number of server connections rise to more than 20, letting you take timely action, if needed. Furthermore, you can pair data types together for really powerful alerting. In this example, you can couple the server connections trigger above with any other metrics for that resource, such as CPU load, memory usage, etc. You can set the alert to fire when all or one of multiple triggers are thrown.

Summary

Hyperic HQ is one of a few open source systems management solutions. What is striking about HQ is the easy-to-use UI, the breadth of system-health data available, and its simple installation. HQ is flexible, robust and intuitive, especially with its auto-discovery function, which automatically finds the servers, network equipment, databases, and many other data center products. Most sysadmins do not require much specialized knowledge to get a complete, functional installation working in 30 minutes. Much of the work is automatically completed without interference from the admin.
HQ can monitor over 65 data center products, so integrating MYSQL into your existing system management infrastructure is a snap. If you've been looking for an easier solution to MYSQL monitoring, if you need more flexibility, and if your current monitoring solution does not adequately address root cause analysis and resource allocation needs, then HQ is worth a try. Download today from http://www.hyperic.com/downloads/.

For more information and for relavent screenshots click on http://support.hyperic.com/confluence/display/PRIVATE/Monitoring+MySQL+with+Hyperic+HQ