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

41 thoughts on “MySQL Performance Graphs with pnp4nagios”

  1. With current 1.5, on Oracle MySQL-5.5, pnp4nagios complains about “invalid perfdata detected” in its log and doesn’t create the associated .xml and .rrd files. Problem seems to be the length of perfdata, that’s about 7KB; by stripping some metrics that appear quite useless to me:
    COM_ALTER*
    COM_CREATE*
    COM_DROP*
    COM_SHOW*
    PERFORMANCE_SCHEMA*

    I could bring it to about 5KB and pnp4nagios stopped complaining. But because template design uses numeric indexes to refer to individual values — instead of their labels — removing entries breaks the template :-/

    1. I am able to process perfdata as large as 12K here with npcd / bulk mode. What pnp4nagios processing mode are you using? Does the complete perfdata show up in the nagios webui for the check?

      The numeric indexing is a painful limitation but I have never been able to find an easily maintainable solution.

  2. Very strange !?!? After playing around with different array to string conversion tips from the web all of a sudden your original code is running without this array error. Is there a PHP option that makes this array to string conversion and that has been activated automatically by using some statements in code? I had this info from PHP while testing around with “echo” command output:

    PHP Notice: Array to string conversion in /usr/lib/nagios/plugins/check_mysql_counters on line 54

    BTW: Include a “\n” in the catch code 😉

    } catch (PDOException $e) {
    echo $e->getMessage();
    echo “\n”;
    $ret_val = $RET_UNK;
    }

  3. Using latest version 1.5 on a Raspbian Jessie wirth Nagios 3.5.1.

    First, for all who are searching for the “could not find driver root error”: Install package php5-mysql. Check with php –ini if now mysqli.ini is shown.

    Then there is a bug in your script, that is not correctly dealing with the command line option parameters. They are fetched in an array and then a variable is filled with the array. Example: $port = $options[‘P’];. But this makes $port also an ARRAY not a STRING, which then fails for PDO object generation:
    PHP Warning: PDO::__construct() expects parameter 3 to be string, array given in /usr/lib/nagios/plugins/check_mysql_counters on line 401

    If I assign $port and $password directly in the code, the script works. Do you know any “array to string” bash conversion that works here?

    1. I’m not sure what’s up here – in my version of php it’s a string:
      $ php -v
      PHP 5.6.16-1 (cli)
      $ cat test.php
      < ?php $options = getopt('H:'); $hostname = $options['H']; var_dump($hostname);
      $ php test.php -H foo.test.com
      string(12) "foo.test.com"

  4. Hi,
    First of all let me thank you for this useful plugin.
    One problem only.
    Everything is ok even with latest release from 4.01.rc but somehow not able to see the rigth template.
    Instead of seeing your (outstanding) template i’am just seeing a graph for every line that /usr/local/nagios/libexec/check_mysql_counters_56 returns.

    Where do I state (script name) that I want to use /usr/local/pnp4nagios/share/templates/check_mysql_counters_56.php

    Thank you
    Regards,
    J. Meirim

      1. Hi Jason,

        Thank you for the link.
        Done.
        Working.
        Happy user.

        Thank you,
        Jorge Meirim

  5. Hi,
    What I need to do with the script if I want only Innodb statistics? Do I need to remove all other counters except Innodb’s from $whitelist and put them into $remove part?
    Thanks!

    1. Or is it enough to remove counters I am not interested in from $whitelist and leave the rest of the code as it is?

      1. The plugin stores most counters in the rrd file and only displays a select few. This way, if I want to add more graphs in the future, all the historical data is already there.

        So I think for your approach it’s easiest to just modify the template file and remove the graphs you don’t want to display in that file. While you are editing, you may find some additional Innodb counters that I don’t currently display that are interesting in your environment.

  6. Hi Jason,

    I’m trying to implement this against an OMD‘s pnp4nagios, which seems not to have a libexec directory. I’d be glad if you can give me a hint about how to install this in OMD. Besides, I know this may sound stupid, but given that OMD is a canned solution and every path is not standard, it’s driving me mad: This needs to be installed not in the OS to be monitored but in the server which harvests the stats, am I right?

    Thank you for your time!

    1. You can install it on the OMD server if the OMD server can reach the mysql listening port on the DB server. If you can ssh to the DB server from OMD, you can install the plugin on the DB server and run it with check_by_ssh. You would call the plugin in a way to check localhost in that case.

  7. Thanks Jason for your reply.

    I’m using 0.6. I actually modified a DS(Data source) in template which was pointing to invalid location.

    I have modified the plugin and template for my requirement.

  8. Hey Jason

    Thanks for writing up this useful plugin.

    I did some modification in script to collect SELECT,INSERTS,UPDATES,DELETES,REPLACE data only.

    and modified php template file as bellow;

    $num = 1;

    $ds_name[$num] = ‘Command Counters’;
    $opt[$num] = “–title “$hostname – Command Counters””;
    $def[$num] .= rrd::def(‘select’, $RRDFILE[1], $DS[85], ‘AVERAGE’);
    $def[$num] .= rrd::def(‘delete’, $RRDFILE[1], $DS[40], ‘AVERAGE’);
    $def[$num] .= rrd::def(‘insert’, $RRDFILE[1], $DS[61], ‘AVERAGE’);
    $def[$num] .= rrd::def(‘update’, $RRDFILE[1], $DS[140], ‘AVERAGE’);
    $def[$num] .= rrd::def(‘replace’, $RRDFILE[1], $DS[76], ‘AVERAGE’);
    $def[$num] .= rrd::area(‘select’,’#FF0000′,rrd::cut(‘Select’),23,1);
    $def[$num] .= rrd::gprint(‘select’,array(‘LAST’,’AVERAGE’,’MAX’),”%4.0lf”);
    $def[$num] .= rrd::area(‘delete’,’#FF7D00′,rrd::cut(‘Delete’),23,1);
    $def[$num] .= rrd::gprint(‘delete’,array(‘LAST’,’AVERAGE’,’MAX’),”%4.0lf”);
    $def[$num] .= rrd::area(‘insert’,’#FFF200′,rrd::cut(‘Insert’),23,1);
    $def[$num] .= rrd::gprint(‘insert’,array(‘LAST’,’AVERAGE’,’MAX’),”%4.0lf”);
    $def[$num] .= rrd::area(‘update’,’#00CF00′,rrd::cut(‘Update’),23,1);
    $def[$num] .= rrd::gprint(‘update’,array(‘LAST’,’AVERAGE’,’MAX’),”%4.0lf”);
    $def[$num] .= rrd::area(‘replace’,’#2175D9′,rrd::cut(‘Replace’),23,1);
    $def[$num] .= rrd::gprint(‘replace’,array(‘LAST’,’AVERAGE’,’MAX’),”%4.0lf”);

    ?>

    When i hit from browser, i get below error ;
    Fatal error: Class ‘rrd’ not found in /usr/local/pnp4nagios/share/templates.dist/check_REM_MYSQLHEALTH.php on line 35

    when i removed this template and i see default pnp4nagios template with correct data.

    I have also verified php5-rrd is installed via phpinfo
    rrd tool module => enabled
    rrd tool module version => 1.0.5
    rrdtool library version => 1.4.7

    php -v
    PHP 5.3.10-1ubuntu3.5 with Suhosin-Patch (cli) (built: Jan 18 2013 23:40:19)
    Copyright (c) 1997-2012 The PHP Group
    Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies

    Any opinion would be highly appreciated.

    Alam

  9. Hi,

    I have got an issue with Pnp Template :

    Undefined offset: 311
    file [line]:

    templates/check_mysql_counters.php [57]

    I use the version mysql Ver 14.14 Distrib 5.5.18

    Do you know where it come from ?

    Thanks

    1. Can you please send me the output from these statements to the email address in the plugin:

      SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS;

      SELECT VARIABLE_NAME, VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'MAX_CONNECTIONS' OR VARIABLE_NAME='QUERY_CACHE_SIZE' OR VARIABLE_NAME='TABLE_OPEN_CACHE' ORDER BY VARIABLE_NAME;

      We need to see how your server variables differ from the 5.5 variables that were previously sent to me.

      1. Hello, I’m having the same issue florian reported using “mysqld Ver 5.5.31-0ubuntu0.12.04.2-log for debian-linux-gnu on x86_64 ((Ubuntu))”:

        Undefined offset: 311
        file [line]:

        templates/check_mysql_counters.php [57]

  10. Executed via check_nrpe i receive only 1024 bytes. Is there a way to fix it without compiling it by self?

  11. Nice script, but the perfdata comes in wrong, so no graphs are being generated.
    It looks like this:
    ‘ABORTED_CLIENTS’=0c ‘ABORTED_CONNECTS’=0c ‘BINLOG_CACHE_DISK_USE’=0c ‘BINLOG_CACHE_USE’=0c ‘BYTES_RECEIVED’=1267168719B ‘BYTES_SENT’=1472391504B — cut —

    1. That output fragment looks OK to me. What version of MySQL/Percona are you using? Do you see any errors in pnp4nagios when you try to view the graphs, or are they just blank?

        1. That’s strange. If you could make sure you are using version 1.1.1 and send me the output when you run

          check_mysql_counters -H host -P port -u mysqluser -p mysqlpassword

          by hand from your Nagios server. You can use the email address in the check script itself.

  12. Version 1.1 for MySQL-5.1 expects MySQL-5.5 and percona in the version strings.

    $ ./check_mysql_counters -H 127.0.0.1 -u ${USER} -p ${PASS}
    UNKNOWN: Unsupported MySQL version (Array)

    $ mysql -V
    mysql Ver 14.14 Distrib 5.1.61, for redhat-linux-gnu (x86_64) using readline 5.1

    1. I’m going to release a test version for Percona Server 5.5 next week. But I doubt that it will work with the mainline MySQL 5.5 server because of the extra counters in Percona. You are welcome to give it a try and report back. I have no plans on running Oracle MySQL 5.5 here. If you want me to port it to MySQL 5.5 run the SELECT statements below and send me the output.

    1. We are migrating from 5.1 to Percona Server 5.5 so I am updating the plugin to work with that version. I’m guessing that this will not work with Oracle’s 5.5 because of additional performance counters added by Percona.

      If you can email me the output of these statements from your Oracle 5.5 server I can try to create a compatible version after I am done with Percona.

      SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS;

      SELECT VARIABLE_NAME, VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = ‘MAX_CONNECTIONS’ OR VARIABLE_NAME=’QUERY_CACHE_SIZE’ OR VARIABLE_NAME=’TABLE_OPEN_CACHE’ ORDER BY VARIABLE_NAME;

  13. Now i geht follow error:
    Please check the documentation for information about the following error.
    Undefined offset: 267

    file [line]:
    templates/check_mysql_counters.php [140]:

    1. Sorry, this plugin is only supported on MySQL 5.1.x at the moment. I am testing a version for Percona Server 5.5.x right now. I don’t know when there will be a version for Oracle MySQL 5.5.x.

  14. Thank you for the plugin. But under pnp4nagios 0.6.19 and Icinga i get follow message:
    Please check the documentation for information about the following error.
    Undefined offset: 267
    file [line]:
    templates/check_mysql_counters.php [140]:

    Also in Line 32 is a comment mark “*/”. So can explain me where is the fault?!

Comments are closed.