JDBC Collection configuration for MySQL

From OpenNMS
Revision as of 10:29, 9 September 2014 by Fuhrmann (talk | contribs) (category cleanup)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Summary

This are example files for the data collection with the JdbcCollector and MySQL. For newer versions of MySQL this can be changed to fewer SQL statements, versions before 5.1 did not have a table in the information_schema for performance variables and used variables instead.


jdbc-datacollection-config.xml

<?xml version="1.0"?>                                     
<jdbc-datacollection-config rrdRepository="/var/lib/opennms/rrd/snmp/" xmlns="http://xmlns.opennms.org/xsd/config/jdbc-datacollection">                                                                                                                   
    <jdbc-collection name="default">                                                                                         
        <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="opennmsQuery">                                                                                          
          <statement data-source="opennms">                                                                                  
            <queryString>SELECT COUNT(eventid) as EventCount                                                                 
FROM events                                                                                                                  
WHERE eventtime                                                                                                              
BETWEEN (CURRENT_TIMESTAMP - INTERVAL '1 day')                                                                               
AND CURRENT_TIMESTAMP;</queryString>                                                                                         
          </statement>                                                                                                       
          <columns>                                                                                                          
            <column name="eventCount" data-source-name="EventCount" type="GAUGE"/>                                           
          </columns>                                                                                                         
        </query>                                                                                                             
      </queries>                                                                                                             
    </jdbc-collection>                                                                                                       

    <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="Value" data-source-name="Uptime" 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="Value" data-source-name="Bytes_received" 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="Value" data-source-name="Bytes_sent" 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="Value" data-source-name="Com_delete" 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="Value" data-source-name="Com_delete_multi" 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="Value" data-source-name="Com_insert" 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="Value" data-source-name="Com_insert_select" 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="Value" data-source-name="Com_select" 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="Value" data-source-name="Com_stmt_execute" 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="Value" data-source-name="Com_update" 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="Value" data-source-name="Com_update_multi" 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="Value" data-source-name="Created_tmp_disk_tables" 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="Value" data-source-name="Created_tmp_tables" 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="Value" data-source-name="key_buffer_size" 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="Value" data-source-name="key_cache_block_size" 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="Value" data-source-name="Key_blocks_unused" 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="Value" data-source-name="Key_read_requests" 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="Value" data-source-name="Key_reads" 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="Key_write_requests" 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="Value" data-source-name="Key_writes" 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="Value" data-source-name="Open_files" 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="Value" data-source-name="Open_tables" 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="Value" data-source-name="table_cache" alias="MyTableCache" type="GAUGE"/>                    
          </columns>                                                                                                  
        </query>                                                                                                      
        <query name="Questions" ifType="all" >                                                                        
          <statement>                                                                                                 
            <queryString>show global status like 'Questions'</queryString>                                            
          </statement>                                                                                                
          <columns>                                                                                                   
           <column name="Value" data-source-name="Questions" 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="Value" data-source-name="Slow_queries" alias="MySlowQueries" type="COUNTER"/>                
          </columns>                                                                                                  
        </query>                                                                                                      
        <query name="Connections" ifType="all" >                                                                      
          <statement>                                                                                                 
            <queryString>show global status like 'Connections'</queryString>                                          
          </statement>                                                                                                
          <columns>                                                                                                   
           <column name="Value" data-source-name="Connections" 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="Value" data-source-name="Threads_created" 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="Value" data-source-name="Threads_cached" 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="Value" data-source-name="Threads_connected" 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="Value" data-source-name="Threads_running" alias="MyThreadsRunng" type="GAUGE"/>
          </columns>
        </query>
      </queries>

    </jdbc-collection>
</jdbc-datacollection-config>


snmp-graph.properties

[...]
mysql.connections, mysql.threads, \
mysql.keybuffer.usage, mysql.open.tables, mysql.bytes, mysql.slow.queries, mysql.queries, \
mysql.key.reads, mysql.key.writes, \
[...]
report.mysql.connections.name=MySQL Connections
report.mysql.connections.columns=MyConnections,MyThreadsCreatd
report.mysql.connections.type=nodeSnmp
report.mysql.connections.command=--title="MySQL Connections/Threads Created" \
 --vertical-label="Connections, Thre Created" \
 DEF:connections={rrd1}:MyConnections:AVERAGE \
 DEF:threadsCreated={rrd2}:MyThreadsCreatd:AVERAGE \
 LINE2:connections#000000:"Connections     " \
 GPRINT:connections:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:connections:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:connections:MAX:"Max  \\: %8.2lf %s\\n" \
 LINE2:threadsCreated#0000ff:"Threads Created " \
 GPRINT:threadsCreated:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:threadsCreated:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:threadsCreated:MAX:"Max  \\: %8.2lf %s\\n" \

report.mysql.threads.name=MySQL Threads
report.mysql.threads.columns=MyThreadsCnnctd,MyThreadsCachd,MyThreadsRunng
report.mysql.threads.type=nodeSnmp
report.mysql.threads.command=--title="MySQL Threads" \
 --vertical-label="Number of Threads" \
 DEF:connected={rrd1}:MyThreadsCnnctd:AVERAGE \
 DEF:cached={rrd2}:MyThreadsCachd:AVERAGE \
 DEF:running={rrd3}:MyThreadsRunng:AVERAGE \
 LINE2:connected#000000:"Threads Connected " \
 GPRINT:connected:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:connected:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:connected:MAX:"Max  \\: %8.2lf %s\\n" \
 LINE2:cached#0000ff:"Threads Cached    " \
 GPRINT:cached:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:cached:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:cached:MAX:"Max  \\: %8.2lf %s\\n" \
 LINE2:running#00ff00:"Threads Running   " \
 GPRINT:running:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:running:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:running:MAX:"Max  \\: %8.2lf %s\\n" \

report.mysql.open.tables.name=MySQL Open Tables
report.mysql.open.tables.columns=MyOpenTables,MyTableCache
report.mysql.open.tables.type=nodeSnmp
report.mysql.open.tables.command=--title="MySQL Open Tables" \
 --vertical-label="Nr Tables" \
 DEF:open={rrd1}:MyOpenTables:AVERAGE \
 DEF:max={rrd2}:MyTableCache:AVERAGE \
 LINE2:open#0000ff:"open tables  " \
 GPRINT:open:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:open:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:open:MAX:"Max  \\: %8.2lf %s\\n" \
 LINE2:max#000000:"table_cache  " \
 GPRINT:max:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:max:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:max:MAX:"Max  \\: %8.2lf %s\\n" \

report.mysql.keybuffer.usage.name=MySQL Key Buffer Usage
report.mysql.keybuffer.usage.columns=MyKeyBlkUnused,MyKeyCacheBlkSize,MyKeyBufferSize
report.mysql.keybuffer.usage.type=nodeSnmp
report.mysql.keybuffer.usage.command=--title="MySQL Key Buffer Usage" \
 --lower-limit=0 \
 --upper-limit=100 \
 --vertical-label="Prozent" \
 DEF:blocksunused={rrd1}:MyKeyBlkUnused:AVERAGE \
 DEF:cacheblocksize={rrd2}:MyKeyCacheBlkSize:AVERAGE \
 DEF:keybuffersize={rrd3}:MyKeyBufferSize:AVERAGE \
 CDEF:freefraction=blocksunused,cacheblocksize,*,keybuffersize,/ \
 CDEF:usage=1.0,freefraction,- \
 CDEF:usagePct=usage,100,* \
 LINE2:usagePct#0000ff:"Buffer Usage (Prozent) " \
 GPRINT:usagePct:AVERAGE:"Avg  \\: %6.2lf %s" \
 GPRINT:usagePct:MIN:"Min  \\: %6.2lf %s" \
 GPRINT:usagePct:MAX:"Max  \\: %6.2lf %s\\n" \

report.mysql.bytes.name=MySQL Bytes Transferred
report.mysql.bytes.columns=MyBytesReceived,MyBytesSent
report.mysql.bytes.type=nodeSnmp
report.mysql.bytes.command=--title="MySQL Bytes Transferred" \
 --vertical-label="Byte per Second" \
 DEF:recv={rrd1}:MyBytesReceived:AVERAGE \
 DEF:sent={rrd2}:MyBytesSent:AVERAGE \
 AREA:recv#00ff00:"Bytes Received " \
 GPRINT:recv:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:recv:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:recv:MAX:"Max  \\: %8.2lf %s\\n" \
 LINE2:sent#0000ff:"Bytes Sent     " \
 GPRINT:sent:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:sent:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:sent:MAX:"Max  \\: %8.2lf %s\\n" \

report.mysql.slow.queries.name=MySQL Slow Queries
report.mysql.slow.queries.columns=MySlowQueries
report.mysql.slow.queries.type=nodeSnmp
report.mysql.slow.queries.command=--title="MySQL Slow Queries" \
 --vertical-label="Queries per Second" \
 DEF:slow={rrd1}:MySlowQueries:AVERAGE \
 LINE2:slow#0000ff:"Slow Queries     " \
 GPRINT:slow:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:slow:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:slow:MAX:"Max  \\: %8.2lf %s\\n" \

report.mysql.queries.name=MySQL Queries
report.mysql.queries.columns=MyComDelete,MyComDeleteMulti,MyComInsert,MyComInsertSelect,MyComUpdate,MyComUpdateMulti,MyComSelect,MyQuestions
report.mysql.queries.type=nodeSnmp
report.mysql.queries.command=--title="MySQL Queries" \
 --vertical-label="Queries per Second" \
 DEF:del={rrd1}:MyComDelete:AVERAGE \
 DEF:delmulti={rrd2}:MyComDeleteMulti:AVERAGE \
 DEF:insert={rrd3}:MyComInsert:AVERAGE \
 DEF:insertselect={rrd4}:MyComInsertSelect:AVERAGE \
 DEF:update={rrd5}:MyComUpdate:AVERAGE \
 DEF:updatemulti={rrd6}:MyComUpdateMulti:AVERAGE \
 DEF:select={rrd7}:MyComSelect:AVERAGE \
 DEF:questions={rrd8}:MyQuestions:AVERAGE \
 AREA:del#ff0000:"Delete        " \
 GPRINT:del:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:del:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:del:MAX:"Max  \\: %8.2lf %s\\n" \
 STACK:delmulti#ffc000:"Delete Multi  " \
 GPRINT:delmulti:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:delmulti:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:delmulti:MAX:"Max  \\: %8.2lf %s\\n" \
 STACK:insert#0000ff:"Insert        " \
 GPRINT:insert:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:insert:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:insert:MAX:"Max  \\: %8.2lf %s\\n" \
 STACK:insertselect#00c0ff:"Insert Select " \
 GPRINT:insertselect:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:insertselect:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:insertselect:MAX:"Max  \\: %8.2lf %s\\n" \
 STACK:update#ff00c0:"Update        " \
 GPRINT:update:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:update:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:update:MAX:"Max  \\: %8.2lf %s\\n" \
 STACK:updatemulti#ffc0c0:"Update Multi  " \
 GPRINT:updatemulti:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:updatemulti:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:updatemulti:MAX:"Max  \\: %8.2lf %s\\n" \
 STACK:select#00ff00:"Select        " \
 GPRINT:select:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:select:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:select:MAX:"Max  \\: %8.2lf %s\\n" \
 LINE2:questions#000000:"Questions     " \
 GPRINT:questions:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:questions:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:questions:MAX:"Max  \\: %8.2lf %s\\n" \

report.mysql.key.reads.name=MySQL Key Reads
report.mysql.key.reads.columns=MyKeyReads,MyKeyReadReqs
report.mysql.key.reads.type=nodeSnmp
report.mysql.key.reads.command=--title="MySQL Key Reads" \
 --vertical-label="Reads per Second" \
 DEF:reads={rrd1}:MyKeyReads:AVERAGE \
 DEF:readreqs={rrd2}:MyKeyReadReqs:AVERAGE \
 LINE2:reads#0000ff:"Reads          " \
 GPRINT:reads:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:reads:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:reads:MAX:"Max  \\: %8.2lf %s\\n" \
 LINE2:readreqs#000000:"Read Requests  " \
 GPRINT:readreqs:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:readreqs:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:readreqs:MAX:"Max  \\: %8.2lf %s\\n" \

report.mysql.key.writes.name=MySQL Key Writes
report.mysql.key.writes.columns=MyKeyWrites,MyKeyWriteReqs
report.mysql.key.writes.type=nodeSnmp
report.mysql.key.writes.command=--title="MySQL Key Writes" \
 --vertical-label="Writes per Second" \
 DEF:writes={rrd1}:MyKeyWrites:AVERAGE \
 DEF:writereqs={rrd2}:MyKeyWriteReqs:AVERAGE \
 LINE2:writes#0000ff:"Writes          " \
 GPRINT:writes:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:writes:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:writes:MAX:"Max  \\: %8.2lf %s\\n" \
 LINE2:writereqs#000000:"Write Requests  " \
 GPRINT:writereqs:AVERAGE:"Avg  \\: %8.2lf %s" \
 GPRINT:writereqs:MIN:"Min  \\: %8.2lf %s" \
 GPRINT:writereqs:MAX:"Max  \\: %8.2lf %s\\n" \
[...]

--Michael 08:41, 31 October 2010 (EDT)

Update

To get this to work required patching the OpenNMS code for MySQL 5.0. We are tracking this change as Issue NMS-4512 and we should be patching it in 1.8.11. However, this will require some changes to the config file, shown below:

jdbc-datacollection-config.xml

    <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>