MySQL Performance Graphs with pnp4nagios

If you are managing MySQL servers, you’ve probably encountered Percona’s suite of enhanced database servers and management tools. Percona released a set of cacti templates (now part of the monitoring plugins toolkit) for measuring MySQL performance trends. I don’t use cacti but have a pretty extensive pnp4nagios setup, so I ported those templates to pnp4nagios. The results are below.

These graphs are very useful for visualizing trends in your database workload and identifying future problems before they occur. MySQL 5.1, MySQL 5.5, MySQL 5.6, and Percona 5.5 and 5.6 are supported. The amount of performance data is too large to be returned via NRPE, you will need a direct connection from Nagios to the database to use the plugin or else use check_by_ssh. A future version may address this problem by breaking up the plugin into multiple checks.

Downloads

Version 1.5 adds support for Percona 5.6 and fixes the Oracle 5.5 template.

check_mysql_counters – version 1.5

There is now a github repository for check_mysql_counters!

The following graphs are only available with Percona Server 5.5. Some are also available with Oracle 5.5 or later.

check_mysql_counters_013

check_mysql_counters_014

check_mysql_counters_015

check_mysql_counters_024

check_mysql_counters_016

check_mysql_counters_017

check_mysql_counters_018

check_mysql_counters_019

check_mysql_counters_020

check_mysql_counters_021

check_mysql_counters_022

check_mysql_counters_023

Graphing Nagios services with pnp4nagios 0.6

A few years ago I wrote a guide on using pnp4nagios with Nagios. Since then pnp4nagios has had a major version update and the guide could use a refresh.

pnp4nagios is a graphing framework with portions written in perl, PHP and C for automatically parsing performance data collected by Nagios plugins. The data is collected into RRD databases for display in the Nagios web interface. The graphs created by pnp4nagios are similar to other monitoring tools like Cacti.

pnp4nagios is designed to work well with the standard Nagios plugins and create useable graphs right out of the box. The appearance of the graphs can be customized.

Pages of related graphs (for instance, CPU usage or TCP connections of each server in a web farm) can be easily defined and displayed in HTML or exported to PDF.

pnp4nagios requires perl, PHP (built with XML, zlib and GD support) and rrdtool. It can optionally use the rrd perl modules for better performance. Packages for Fedora, Debian and Ubuntu are available. My example will use Ubuntu Server 12.04 with the bundled perl, PHP and Apache packages. Nagios has been installed from source to the default location of /usr/local/nagios.

Installing pnp4nagios

By default, pnp4nagios 0.6 installs into its own directory – a change from version 0.4 which was installed in your nagios directory.

jason@ubuntu:~/pnp4nagios-0.6.17$ ./configure
...
*** Configuration summary for pnp4nagios-0.6.17 03-25-2012 ***

  General Options:
  -------------------------         -------------------
  Nagios user/group:                nagios nagios
  Install directory:                /usr/local/pnp4nagios
  HTML Dir:                         /usr/local/pnp4nagios/share
  Config Dir:                       /usr/local/pnp4nagios/etc
  Location of rrdtool binary:       /usr/bin/rrdtool Version 1.4.7
  RRDs Perl Modules:                FOUND (Version 1.4007)
  RRD Files stored in:              /usr/local/pnp4nagios/var/perfdata
  process_perfdata.pl Logfile:      /usr/local/pnp4nagios/var/perfdata.log
  Perfdata files (NPCD) stored in:  /usr/local/pnp4nagios/var/spool

  Web Interface Options:
  -------------------------         -------------------
  HTML URL:                         http://localhost/pnp4nagios
  Apache Config File:               /etc/apache2/conf.d/pnp4nagios.conf

jason@ubuntu:~/pnp4nagios-0.6.17$ sudo make all
jason@ubuntu:~/pnp4nagios-0.6.17$ sudo make install
jason@ubuntu:~/pnp4nagios-0.6.17$ sudo make install-webconf
jason@ubuntu:~/pnp4nagios-0.6.17$ sudo make install-config

Configuring pnp4nagios

The main configuration file for pnp4nagios is /usr/local/pnp4nagios/etc/config.php

Before configuring pnp, we need to decide how we want Nagios to process the performance data. This largely depends on the number of monitored hosts and services of Nagios.

Default mode, where process_perfdata.pl is executed after each host and service check, is ok for very small installations.

Bulk mode, where performance information is appended to a temporary file and processed after a short interval, is fine for medium-sized installations.

Setups with hundreds of hosts and services should use bulk mode with npcd, where a separate multi-threaded daemon handles the processing. Version 0.6 introduced integration with gearman – which might be useful if your monitored hosts grow beyond what your nagios server can handle.

Our example will use Bulk mode, but it is possible to switch between modes as your Nagios setup grows.

Edit the performance data section of /usr/local/nagios/etc/nagios.cfg, making the changes noted in Bold:

# PROCESS PERFORMANCE DATA OPTION 
# This determines whether or not Nagios will process performance 
# data returned from service and host checks.  If this option is 
# enabled, host performance data will be processed using the 
# host_perfdata_command (defined below) and service performance 
# data will be processed using the service_perfdata_command (also 
# defined below).  Read the HTML docs for more information on 
# performance data. 
# Values: 1 = process performance data, 0 = do not process performance data 

process_performance_data=1

# HOST AND SERVICE PERFORMANCE DATA PROCESSING COMMANDS 
# These commands are run after every host and service check is 
# performed.  These commands are executed only if the 
# enable_performance_data option (above) is set to 1.  The command 
# argument is the short name of a command definition that you 
# define in your host configuration file.  Read the HTML docs for 
# more information on performance data. 

#host_perfdata_command=process-host-perfdata 
#service_perfdata_command=process-service-perfdata 

# HOST AND SERVICE PERFORMANCE DATA FILES 
# These files are used to store host and service performance data. 
# Performance data is only written to these files if the 
# enable_performance_data option (above) is set to 1. 

host_perfdata_file=/usr/local/pnp4nagios/var/host-perfdata
service_perfdata_file=/usr/local/pnp4nagios/var/service-perfdata

# HOST AND SERVICE PERFORMANCE DATA FILE TEMPLATES 
# These options determine what data is written (and how) to the 
# performance data files.  The templates may contain macros, special 
# characters (\t for tab, \r for carriage return, \n for newline) 
# and plain text.  A newline is automatically added after each write 
# to the performance data file.  Some examples of what you can do are 
# shown below. 

host_perfdata_file_template=DATATYPE::HOSTPERFDATA\tTIMET::$TIMET$\tHOSTNAME::$HOSTNAME$\tHOSTPERFDATA::$HOSTPERFDATA$\tHOSTCHECKCOMMAND::$HOSTCHECKCOMMAND$\tHOSTSTATE::$HOSTSTATE$\tHOSTSTATETYPE::$HOSTSTATETYPE$\tHOSTOUTPUT::$HOSTOUTPUT$
service_perfdata_file_template=DATATYPE::SERVICEPERFDATA\tTIMET::$TIMET$\tHOSTNAME::$HOSTNAME$\tSERVICEDESC::$SERVICEDESC$\tSERVICEPERFDATA::$SERVICEPERFDATA$\tSERVICECHECKCOMMAND::$SERVICECHECKCOMMAND$\tHOSTSTATE::$HOSTSTATE$\tHOSTSTATETYPE::$HOSTSTATETYPE$\tSERVICESTATE::$SERVICESTATE$\tSERVICESTATETYPE::$SERVICESTATETYPE$\tSERVICEOUTPUT::$SERVICEOUTPUT$

# HOST AND SERVICE PERFORMANCE DATA FILE MODES 
# This option determines whether or not the host and service 
# performance data files are opened in write ("w") or append ("a") 
# mode. If you want to use named pipes, you should use the special 
# pipe ("p") mode which avoid blocking at startup, otherwise you will 
# likely want the defult append ("a") mode.

host_perfdata_file_mode=a
service_perfdata_file_mode=a

# HOST AND SERVICE PERFORMANCE DATA FILE PROCESSING INTERVAL 
# These options determine how often (in seconds) the host and service 
# performance data files are processed using the commands defined 
# below.  A value of 0 indicates the files should not be periodically 
# processed. 

host_perfdata_file_processing_interval=15
service_perfdata_file_processing_interval=15

# HOST AND SERVICE PERFORMANCE DATA FILE PROCESSING COMMANDS 
# These commands are used to periodically process the host and 
# service performance data files.  The interval at which the 
# processing occurs is determined by the options above. 

host_perfdata_file_processing_command=process-host-perfdata-file
service_perfdata_file_processing_command=process-service-perfdata-file

At the end of /usr/local/nagios/etc/objects/commands.cfg, add the command definitions:

define command {
command_name    process-service-perfdata-file
command_line    /usr/local/pnp4nagios/libexec/process_perfdata.pl --bulk=/usr/local/pnp4nagios/var/service-perfdata
}

define command {
command_name    process-host-perfdata-file
command_line    /usr/local/pnp4nagios/libexec/process_perfdata.pl --bulk=/usr/local/pnp4nagios/var/host-perfdata
}

There is one other thing to check before running pnp4nagios for the first time. If you don’t want the default resolution for your performance data (1 minute intervals for 2 days up to 6 hour intervals for 4 years), you must create a /usr/local/pnp4nagios/etc/rra.cfg file with your preferences. You can use the snippet below as a template.

# 2880 entries with 1 minute step = 48 hours
RRA:AVERAGE:0.5:1:2880
# 2880 entries with 5 minute step = 10 days
RRA:AVERAGE:0.5:5:2880
# 4320 entries with 30 minute step = 90 days
RRA:AVERAGE:0.5:30:4320
# 5840 entries with 360 minute step = 4 years
RRA:AVERAGE:0.5:360:5840
RRA:MAX:0.5:1:2880
RRA:MAX:0.5:5:2880
RRA:MAX:0.5:30:4320
RRA:MAX:0.5:360:5840
RRA:MIN:0.5:1:2880
RRA:MIN:0.5:5:2880
RRA:MIN:0.5:30:4320
RRA:MIN:0.5:360:5840

Now restart Nagios. If you look in /usr/local/pnp4nagios/var/perfdata, you should start to see rrd files created by pnp4nagios for all your monitored hosts and services.

Verify that you have all the components to use the web interface by checking http://your-nagios-host/pnp4nagios/

Correct any missing components, then delete the /usr/local/pnp4nagios/share/install.php file to see your first graphs:

There is one more step to complete the setup. We need to enable extended info in Nagios so that links to the graphs are created for each applicable host and service.

Append two entries to /usr/local/nagios/etc/objects/templates.cfg:

define host {
name            host-pnp
action_url      /pnp4nagios/index.php/graph?host=$HOSTNAME$&srv=_HOST_
register        0
}

define service {
name            srv-pnp
action_url      /pnp4nagios/index.php/graph?host=$HOSTNAME$&srv=$SERVICEDESC$
register        0
}

These are templates that you add to each host and service definition with graphs:

define host { 
    use                     linux-server,host-pnp 
    host_name               ubuntu 
    alias                   ubuntu 
    address                 127.0.0.1 
} 

define service { 
    use                             local-service,srv-pnp 
    host_name                       ubuntu 
    service_description             PING 
    check_command                   check_ping!100.0,20%!500.0,60% 
}

After restarting Nagios again, the icons for the graphs should appear next to the hosts and services. The image for the graph icon in Nagios in the new exfoliation skin works well – but in the previous Nagios skin you should change /usr/local/nagios/share/images/action.gif to something more appropriate than a bullet hole.

If you’d like to see some more advanced examples of graphing, you can see two in my original presentation.