crypt's blog: 04/05/2011

There are different ways to monitor MySQL using OpenNMS: mysql snmp project, with php script and agregation with OpenNMS http collector and using JDBC facility in OpenNMS. Some people say that using MySQL SNMP is more beneficial in sense of traffic usage and reporting results. JDBC is good if you don't have access on remote host to compile mysql snmp (or don't want to make rpm,deb and port:) and just want to ask DBA to add user for monitoring.
This is a compilation of the wiki articles, which you can found here and here.

Although OpenNMS wiki describes Capsd settings, we will ignore this and use it's replacemen, that is provisiond+detectors.

a) OpenNMS dataflow assumes that first every service have to be detected with simple check. Detector configured in WebUI: Admin->Provisioning Groups->Edit Foreign Source->Add detector->JDBC

Here I put foreign source xml file, so that you can pick up necessary parameters:

<detector class="org.opennms.netmgt.provision.detector.jdbc.JdbcDetector" name="JDBC for MySQL">
            <parameter value="opennms" key="user"/>
            <parameter value="abc" key="password"/>
            <parameter value="com.mysql.jdbc.Driver" key="dbDriver"/>
            <parameter value="jdbc:mysql://xx.xx.xx.xx/information_schema" key="url"/>
            <parameter value="3" key="retries"/>
            <parameter value="3306" key="port"/>
</detector>

Database URL is constructed according to this.

OpenNMS needs addition Java components to make DB requests. For that reason I installed libmysql-java (deb) which provided me with
/usr/share/java/mysql-connector-java-5.1.10.jar
This file should be included in /etc/opennms/opennms.conf like this:
ADDITIONAL_CLASSPATH="/usr/share/java/mysql-connector-java-5.1.10.jar:/usr/share/java/postgresql-jdbc3-8.4.jar"
or just copied into $OPENNMS_HOME/lib/

Otherwise OpenNMS complains about it:

INFO [scanExecutor-9] NullDetectorMonitor: JDBC for MySQL: An undeclared throwable exception was caught contating address xx.xx.xx.xx port 3306
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

b) After detection data may be collected via collectd. That is why we add following to the collectd-configuration.xml (xx.xx.xx.xx - may be a host name or an ip)

<package name="mysql">
    <filter>IPADDR != '0.0.0.0'</filter>
    <specific xmlns="">xx.xx.xx.xx</specific>
        <service name="MySQL" interval="300000" user-defined="false" status="on">
       <parameter key="retry" value="2"/>
       <parameter key="timeout" value="3000"/>
       <parameter key="collection" value="mysql"/>
       <parameter key="url" value="jdbc:mysql://xx.xx.xx.xx/information_schema"/>
       <parameter key="user" value="opennms"/>
       <parameter key="password" value="abc"/>
       <parameter key="driver" value="com.mysql.jdbc.Driver"/>
    </service>
</package>

Collection service from first part should be linked with actual Java collector. Put this also in collectd-configuration.xml

<collector service="MySQL" class-name="org.opennms.netmgt.collectd.JdbcCollector"/>

c) Collection "mysql" from the above should be linked with corresponding datacollection description (snmp, http, jdbc etc). Thus place this into jdbc-datacollection-config.xml (If my formating sux just copy from original wiki)

<jdbc-collection name="mysql">
        <rrd step="300">
                  <rra>RRA:AVERAGE:0.5:1:2016</rra>
                  <rra>RRA:AVERAGE:0.5:12:1488</rra>
                  <rra>RRA:AVERAGE:0.5:288:366</rra>
                  <rra>RRA:MAX:0.5:288:366</rra>
                  <rra>RRA:MIN:0.5:288:366</rra>
        </rrd>
      <queries>
        <query name="Uptime" ifType="all" >
          <statement>
            <queryString>show global status like 'Uptime'</queryString>
          </statement>
          <columns>
           <column name="MyUptime" data-source-name="Value" alias="MyUptime" type="GAUGE"/>
          </columns>
        </query>
        <query name="Bytes_received" ifType="all" >
          <statement>
            <queryString>show global status like 'Bytes_received'</queryString>
          </statement>
          <columns>
           <column name="MyBytesReceived" data-source-name="Value" alias="MyBytesReceived" type="COUNTER"/>
          </columns>
        </query>
        <query name="Bytes_sent" ifType="all" >
          <statement>
            <queryString>show global status like 'Bytes_sent'</queryString>
          </statement>
          <columns>
           <column name="MyBytesSent" data-source-name="Value" alias="MyBytesSent" type="COUNTER"/>
          </columns>
        </query>
        <query name="Com_delete" ifType="all" >
          <statement>
            <queryString>show global status like 'Com_delete'</queryString>
          </statement>
          <columns>
           <column name="MyComDelete" data-source-name="Value" alias="MyComDelete" type="COUNTER"/>
          </columns>
        </query>
        <query name="Com_delete_multi" ifType="all" >
          <statement>
            <queryString>show global status like 'Com_delete_multi'</queryString>
          </statement>
          <columns>
           <column name="MyComDeleteMulti" data-source-name="Value" alias="MyComDeleteMulti" type="COUNTER"/>
          </columns>
        </query>
        <query name="Com_insert" ifType="all" >
          <statement>
            <queryString>show global status like 'Com_insert'</queryString>
          </statement>
          <columns>
           <column name="MyComInsert" data-source-name="Value" alias="MyComInsert" type="COUNTER"/>
          </columns>
        </query>
        <query name="Com_insert_select" ifType="all" >
          <statement>
            <queryString>show global status like 'Com_insert_select'</queryString>
          </statement>
          <columns>
           <column name="MyComInsertSelect" data-source-name="Value" alias="MyComInsertSelect" type="COUNTER"/>
          </columns>
        </query>
        <query name="Com_select" ifType="all" >
          <statement>
            <queryString>show global status like 'Com_select'</queryString>
          </statement>
          <columns>
           <column name="MyComSelect" data-source-name="Value" alias="MyComSelect" type="COUNTER"/>
          </columns>
        </query>
        <query name="Com_stmt_execute" ifType="all" >
          <statement>
            <queryString>show global status like 'Com_stmt_execute'</queryString>
          </statement>
          <columns>
           <column name="MyComStmtExecute" data-source-name="Value" alias="MyComStmtExecute" type="COUNTER"/>
          </columns>
        </query>
        <query name="Com_update" ifType="all" >
          <statement>
            <queryString>show global status like 'Com_update'</queryString>
          </statement>
          <columns>
           <column name="MyComUpdate" data-source-name="Value" alias="MyComUpdate" type="COUNTER"/>
          </columns>
        </query>
        <query name="Com_update_multi" ifType="all" >
          <statement>
            <queryString>show global status like 'Com_update_multi'</queryString>
          </statement>
          <columns>
           <column name="MyComUpdateMulti" data-source-name="Value" alias="MyComUpdateMulti" type="COUNTER"/>
          </columns>
        </query>
        <query name="Created_tmp_disk_tables" ifType="all" >
          <statement>
            <queryString>show global status like 'Created_tmp_disk_tables'</queryString>
          </statement>
          <columns>
           <column name="MyCreatTmpDiskTbl" data-source-name="Value" alias="MyCreatTmpDiskTbl" type="COUNTER"/>
          </columns>
        </query>
        <query name="Created_tmp_tables" ifType="all" >
          <statement>
            <queryString>show global status like 'Created_tmp_tables'</queryString>
          </statement>
          <columns>
           <column name="MyCreatTmpTables" data-source-name="Value" alias="MyCreatTmpTables" type="COUNTER"/>
          </columns>
        </query>
        <query name="key_buffer_size" ifType="all" >
          <statement>
            <queryString>show global variables like 'key_buffer_size'</queryString>
          </statement>
          <columns>
           <column name="MyKeyBufferSize" data-source-name="Value" alias="MyKeyBufferSize" type="GAUGE"/>
          </columns>
        </query>
        <query name="key_cache_block_size" ifType="all" >
          <statement>
            <queryString>show global variables like 'key_cache_block_size'</queryString>
          </statement>
          <columns>
           <column name="MyKeyCacheBlkSize" data-source-name="Value" alias="MyKeyCacheBlkSize" type="GAUGE"/>
          </columns>
        </query>
        <query name="Key_blocks_unused" ifType="all" >
          <statement>
            <queryString>show global status like 'Key_blocks_unused'</queryString>
          </statement>
          <columns>
           <column name="MyKeyBlkUnused" data-source-name="Value" alias="MyKeyBlkUnused" type="GAUGE"/>
          </columns>
        </query>
        <query name="Key_read_requests" ifType="all" >
          <statement>
            <queryString>show global status like 'Key_read_requests'</queryString>
          </statement>
          <columns>
           <column name="MyKeyReadReqs" data-source-name="Value" alias="MyKeyReadReqs" type="COUNTER"/>
          </columns>
        </query>
        <query name="Key_reads" ifType="all" >
          <statement>
            <queryString>show global status like 'Key_reads'</queryString>
          </statement>
          <columns>
           <column name="MyKeyReads" data-source-name="Value" alias="MyKeyReads" type="COUNTER"/>
          </columns>
        </query>
        <query name="Key_write_requests" ifType="all" >
          <statement>
            <queryString>show global status like 'Key_write_requests'</queryString>
          </statement>
          <columns>
           <column name="Value" data-source-name="Value" alias="MyKeyWriteReqs" type="COUNTER"/>
          </columns>
        </query>
        <query name="Key_writes" ifType="all" >
          <statement>
            <queryString>show global status like 'Key_writes'</queryString>
          </statement>
          <columns>
           <column name="MyKeyWrites" data-source-name="Value" alias="MyKeyWrites" type="COUNTER"/>
          </columns>
        </query>
        <query name="Open_files" ifType="all" >
          <statement>
            <queryString>show global status like 'Open_files'</queryString>
          </statement>
          <columns>
           <column name="MyOpenFiles" data-source-name="Value" alias="MyOpenFiles" type="GAUGE"/>
          </columns>
        </query>
        <query name="Open_tables" ifType="all" >
          <statement>
            <queryString>show global status like 'Open_tables'</queryString>
          </statement>
          <columns>
           <column name="MyOpenTables" data-source-name="Value" alias="MyOpenTables" type="GAUGE"/>
          </columns>
        </query>
        <query name="table_cache" ifType="all" >
          <statement>
            <queryString>show global variables like 'table_cache'</queryString>
          </statement>
          <columns>
           <column name="MyTableCache" data-source-name="Value" alias="MyTableCache" type="GAUGE"/>
          </columns>
        </query>
        <query name="Questions" ifType="all" >
          <statement>
            <queryString>show global status like 'Questions'</queryString>
          </statement>
          <columns>
           <column name="MyQuestions" data-source-name="Value" alias="MyQuestions" type="COUNTER"/>
          </columns>
        </query>
        <query name="Slow_queries" ifType="all" >
          <statement>
            <queryString>show global status like 'Slow_queries'</queryString>
          </statement>
          <columns>
           <column name="MySlowQueries" data-source-name="Value" alias="MySlowQueries" type="COUNTER"/>
          </columns>
        </query>
        <query name="Connections" ifType="all" >
          <statement>
            <queryString>show global status like 'Connections'</queryString>
          </statement>
          <columns>
           <column name="MyConnections" data-source-name="Value" alias="MyConnections" type="COUNTER"/>
          </columns>
        </query>
        <query name="Threads_created" ifType="all" >
          <statement>
            <queryString>show global status like 'Threads_created'</queryString>
          </statement>
          <columns>
           <column name="MyThreadsCreatd" data-source-name="Value" alias="MyThreadsCreatd" type="COUNTER"/>
          </columns>
        </query>
        <query name="Threads_cached" ifType="all" >
          <statement>
            <queryString>show global status like 'Threads_cached'</queryString>
          </statement>
          <columns>
           <column name="MyThreadsCachd" data-source-name="Value" alias="MyThreadsCachd" type="GAUGE"/>
          </columns>
        </query>
        <query name="Threads_connected" ifType="all" >
          <statement>
            <queryString>show global status like 'Threads_connected'</queryString>
          </statement>
          <columns>
           <column name="MyThreadsCnnctd" data-source-name="Value" alias="MyThreadsCnnctd" type="GAUGE"/>
          </columns>
        </query>
        <query name="Threads_running" ifType="all" >
          <statement>
            <queryString>show global status like 'Threads_running'</queryString>
          </statement>
          <columns>
           <column name="MyThreadsRunng" data-source-name="Value" alias="MyThreadsRunng" type="GAUGE"/>
          </columns>
        </query>
      </queries>

    </jdbc-collection>

d) In snmp-graph.properties (don't know why snmp) the graph description should be added. Exactly like in the official howto:

http://www.opennms.org/wiki/JDBC_Collection_configuration_for_MySQL

e) MySQL user created just to allow OpenNMS to connect and perform request on information schema.

In case you locked the user, allow access to MySQL again on the database host by:

mysqladmin flush-hosts

crypt's blog

Wednesday, May 4, 2011

MySQL monitoring with OpenNMS 1.8.11 and JDBC