Get OpenNMS
Get Involved
Get to Know Us
From OpenNMS
Contents |
HOWTO: Monitoring MySQL with the HTTP collector
There are sometimes situations where SNMP monitoring isn´t possible. If you have a hosted website and would like monitor your MySQL statistics you can use the HTTP-Collector and little PHP-page to collect some statistics.
First you have to create a little PHP file (e.g. mystats.php) like this:
<?php
##
## sqlstats()
## -> Get mysql stats and display them
##
sqlstats();
function sqlstats() {
$db_host = "<your-mysql-server>";
$db_user = "<your-db-user>";
$db_pass = "<your-db-password>";
$db_name = "<your-db>";
## Connect to mysql and gather data
$link = mysql_connect($db_host,$db_user,$db_pass) or die("Unable to connect to MySQL");
$stats = explode(' ', mysql_stat($link));
$myLink = mysql_stat($link);
echo $myLink;
## Data gather complete, close MySQL connection
mysql_close();
}
?>
If you deployed this file to your server correctly the output looks like this:
Uptime: 2132074 Threads: 2 Questions: 5917908 Slow queries: 3 Opens: 118392 Flush tables: 1 Open tables: 768 Queries per second avg: 2.776
Next we can start to use OpenNMS to graph this stuff.
Identify data collection
To identify the node to do the HTTP datacollection. I created a service HTTP-MySQL-Stats in the configuration file capsd-configuration.xml. I prefer the manual provisioning method so I turned scan=off. You can also create detection with Capsd to make HTTP call to /mystats.php.
<protocol-plugin protocol="HTTP-MySQL-Stats" class-name="org.opennms.netmgt.capsd.plugins.HttpPlugin" scan="off">
<property key="port" value="80" />
<property key="timeout" value="3000" />
<property key="retry" value="1" />
</protocol-plugin>
Create a collection package
Tell the collectd to use a http-collection package. For this we have to add this service in collectd-configuration.xml. The package we will collect is named mysql-stats.
<package name="default">
<filter>IPADDR != '0.0.0.0'</filter>
<include-range begin="1.1.1.1" end="254.254.254.254"/>
<service name="SNMP" interval="300000" user-defined="false" status="on">
<parameter key="collection" value="default"/>
</service>
<service name="HTTP-MySQL-Stats" interval="300000" user-defined="false" status="on">
<parameter key="collection" value="mysql-stats"/>
</service>
</package>
...
<collector service="HTTP-MySQL-Stats" class-name="org.opennms.netmgt.collectd.HttpCollector"/>
Define a HTTP collection package
Now we have to define the specific values to collect in the http-datacollection-config.xml.
<http-collection name="mysql-stats">
<rrd step="300">
<rra>RRA:AVERAGE:0.5:1:8928</rra>
<rra>RRA:AVERAGE:0.5:12:8784</rra>
<rra>RRA:MIN:0.5:12:8784</rra>
<rra>RRA:MAX:0.5:12:8784</rra>
</rrd>
<uris>
<uri name="mysql-stats">
<url path="/mystats.php"
virtual-host="<your-host>"
user-agent="OpenNMS HTTP Datacollection"
matches="(?s).*Uptime:\s([0-9]+).*Threads:\s([0-9]+).*Questions:\s([0-9]+).*Slow queries:\s([0-9]+).* \
Opens:\s([0-9]+).*Flush tables:\s([0-9]+).*Open tables:\s([0-9]+).*Queries per second avg:\s([0-9]+). \
([0-9]+).*" response-range="100-399">
</url>
<attributes>
<attrib alias="uptime" match-group="1" type="integer"/>
<attrib alias="threads" match-group="2" type="gauge32"/>
<attrib alias="questions" match-group="3" type="counter32"/>
<attrib alias="slowQueries" match-group="4" type="gauge32"/>
<attrib alias="opens" match-group="5" type="counter32"/>
<attrib alias="flushTables" match-group="6" type="gauge32"/>
<attrib alias="openTables" match-group="7" type="gauge32"/>
<attrib alias="queriesPerSec" match-group="8" type="integer"/>
<attrib alias="queriesPerSecMilli" match-group="9" type="integer"/>
</attributes>
</uri>
</uris>
</http-collection>
The line matches should be a one liner but for better reading the line break is marked with \.
Define graphs
To display the collected values in the WebUI we have to define a graph definition in snmp-graph.properties. First define the new reports in the PREFAB section:
reports= ... mysql.stats.uptime, mysql.stats.threads, mysql.stats.questions, mysql.stats.slowqueries, \
mysql.stats.opens, mysql.stats.flushtables, mysql.stats.opentables, mysql.stats.queries
Now the graphs itself
#########
## MySQL stats http data collection
########
report.mysql.stats.uptime.name=MySQL Uptime
report.mysql.stats.uptime.columns=uptime
report.mysql.stats.uptime.type=nodeSnmp
report.mysql.stats.uptime.command=--title="MySQL Uptime" \
--vertical-label Days \
DEF:time={rrd1}:uptime:AVERAGE \
DEF:minTime={rrd1}:uptime:MIN \
DEF:maxTime={rrd1}:uptime:MAX \
CDEF:days=time,86400,/ \
CDEF:minDays=minTime,86400,/ \
CDEF:maxDays=maxTime,86400,/ \
LINE2:days#0000ff:"Uptime (Days)" \
GPRINT:days:AVERAGE:"Avg \\: %8.1lf %s" \
GPRINT:days:MIN:"Min \\: %8.1lf %s" \
GPRINT:days:MAX:"Max \\: %8.1lf %s\\n"
report.mysql.stats.threads.name=MySQL Threads
report.mysql.stats.threads.columns=threads
report.mysql.stats.threads.type=nodeSnmp
report.mysql.stats.threads.command=--title="MySQL Threads" \
--vertical-label="count" \
--lower-limit 0 \
DEF:avgThreads={rrd1}:threads:AVERAGE \
DEF:minThreads={rrd1}:threads:MIN \
DEF:maxThreads={rrd1}:threads:MAX \
LINE2:avgThreads#000000:"Threads" \
GPRINT:avgThreads:AVERAGE:"Avg \\: %10.2lf %s" \
GPRINT:minThreads:MIN:"Min \\: %10.2lf %s" \
GPRINT:maxThreads:MAX:"Max \\: %10.2lf %s\\n"
report.mysql.stats.questions.name=MySQL Questions
report.mysql.stats.questions.columns=questions
report.mysql.stats.questions.type=nodeSnmp
report.mysql.stats.questions.command=--title="MySQL Questions" \
--vertical-label="count" \
--lower-limit 0 \
DEF:avgQuest={rrd1}:questions:AVERAGE \
DEF:minQuest={rrd1}:questions:MIN \
DEF:maxQuest={rrd1}:questions:MAX \
AREA:avgQuest#0000ff:"Questions" \
GPRINT:avgQuest:AVERAGE:"Avg \\: %10.2lf %s" \
GPRINT:minQuest:MIN:"Min \\: %10.2lf %s" \
GPRINT:maxQuest:MAX:"Max \\: %10.2lf %s\\n"
report.mysql.stats.slowqueries.name=MySQL Slow Queries
report.mysql.stats.slowqueries.columns=slowQueries
report.mysql.stats.slowqueries.type=nodeSnmp
report.mysql.stats.slowqueries.command=--title="MySQL Slow Queries" \
--vertical-label="count" \
--lower-limit 0 \
DEF:avgSlowQueries={rrd1}:slowQueries:AVERAGE \
DEF:minSlowQueries={rrd1}:slowQueries:MIN \
DEF:maxSlowQueries={rrd1}:slowQueries:MAX \
LINE2:avgSlowQueries#0000ff:"Slow Queries" \
GPRINT:avgSlowQueries:AVERAGE:"Avg \\: %10.2lf %s" \
GPRINT:minSlowQueries:MIN:"Min \\: %10.2lf %s" \
GPRINT:maxSlowQueries:MAX:"Max \\: %10.2lf %s\\n"
report.mysql.stats.opens.name=MySQL Opens
report.mysql.stats.opens.columns=opens
report.mysql.stats.opens.type=nodeSnmp
report.mysql.stats.opens.command=--title="MySQL Opens" \
--vertical-label="count" \
--lower-limit 0 \
DEF:avgopens={rrd1}:opens:AVERAGE \
DEF:minopens={rrd1}:opens:MIN \
DEF:maxopens={rrd1}:opens:MAX \
LINE2:avgopens#bc5500:"Opens" \
GPRINT:avgopens:AVERAGE:"Avg \\: %10.2lf %s" \
GPRINT:minopens:MIN:"Min \\: %10.2lf %s" \
GPRINT:maxopens:MAX:"Max \\: %10.2lf %s\\n"
report.mysql.stats.flushtables.name=MySQL Flush Tables
report.mysql.stats.flushtables.columns=flushTables
report.mysql.stats.flushtables.type=nodeSnmp
report.mysql.stats.flushtables.command=--title="MySQL Flush tables" \
--vertical-label="count" \
--lower-limit 0 \
DEF:avgflshTbles={rrd1}:flushTables:AVERAGE \
DEF:minflshTbles={rrd1}:flushTables:MIN \
DEF:maxflshTbles={rrd1}:flushTables:MAX \
LINE2:avgflshTbles#00ff00:"Flush Tables" \
GPRINT:avgflshTbles:AVERAGE:"Avg \\: %10.2lf %s" \
GPRINT:minflshTbles:MIN:"Min \\: %10.2lf %s" \
GPRINT:maxflshTbles:MAX:"Max \\: %10.2lf %s\\n"
report.mysql.stats.opentables.name=MySQL Open Tables
report.mysql.stats.opentables.columns=openTables
report.mysql.stats.opentables.type=nodeSnmp
report.mysql.stats.opentables.command=--title="MySQL Open Tables" \
--vertical-label="count" \
--lower-limit 0 \
DEF:avgOpenTbles={rrd1}:openTables:AVERAGE \
DEF:minOpenTbles={rrd1}:openTables:MIN \
DEF:maxOpenTbles={rrd1}:openTables:MAX \
LINE2:avgOpenTbles#ff0000:"Flush Tables" \
GPRINT:avgOpenTbles:AVERAGE:"Avg \\: %10.2lf %s" \
GPRINT:minOpenTbles:MIN:"Min \\: %10.2lf %s" \
GPRINT:maxOpenTbles:MAX:"Max \\: %10.2lf %s\\n"
report.mysql.stats.queries.name=MySQL Queries per second (avg)
report.mysql.stats.queries.columns=queriesPerSec, queriesPerSecMilli
report.mysql.stats.queries.type=nodeSnmp
report.mysql.stats.queries.command=--title="MySQL Queries per second (avg)" \
--vertical-label="second" \
DEF:avgQryPerSec={rrd1}:queriesPerSec:AVERAGE \
DEF:minQryPerSec={rrd1}:queriesPerSec:MIN \
DEF:maxQryPerSec={rrd1}:queriesPerSec:MAX \
DEF:rawAvgQryPerSecMilli={rrd2}:queriesPerSecMilli:AVERAGE \
DEF:rawMinQryPerSecMilli={rrd2}:queriesPerSecMilli:MIN \
DEF:rawMaxQryPerSecMilli={rrd2}:queriesPerSecMilli:MAX \
CDEF:avgQryPerSecMilli=rawAvgQryPerSecMilli,1000,/ \
CDEF:minQryPerSecMilli=rawMinQryPerSecMilli,1000,/ \
CDEF:maxQryPerSecMilli=rawMaxQryPerSecMilli,1000,/ \
CDEF:avgDrQryPerSec=avgQryPerSec,avgQryPerSecMilli,+ \
CDEF:minDrQryPerSec=minQryPerSec,minQryPerSecMilli,+ \
CDEF:maxDrQryPerSec=maxQryPerSec,maxQryPerSecMilli,+ \
LINE2:avgDrQryPerSec#0000FF:"Queries per second " \
GPRINT:avgDrQryPerSec:AVERAGE:"Avg \\: %8.2lf %s" \
GPRINT:minDrQryPerSec:MIN:"Min \\: %8.2lf %s" \
GPRINT:maxDrQryPerSec:MAX:"Max \\: %8.2lf %s\\n"
It is a good idea to add all your self defined graphs at the end of a section, to make it easier to keep your OpenNMS up to date. Feel free to use and extend this little How-To.
