Introducing mysql-snmp!

Author: masterzen

13 Apr

Thanks to Days of Wonder the company I work for, I’m proud to release in Free Software (GPL):

mysql-snmp – monitor a MySQL server with SNMP

History

At Days of Wonder, we’re using MySQL for almost everything since the beginning of the company. We were initially monitoring all our infrastructure with mon and Cricket, including our MySQL servers. Nine months ago I migrated the monitoring infrastructure to OpenNMS, and at the same we lost the Cricket MySQL monitoring (which was done with direct SQL SHOW STATUS LIKE commands).

I had to find another way, and since OpenNMS excels at SNMP, it was natural to monitor MySQL through SNMP. My browsing crossed this blog post. At about the same time I noticed that Baron Schwartz had released some very good MySQL Cacti Templates, so I decided I should cross both project and started working on mysql-snmp on my free time.

Hopefully, Days of Wonder has an IANA SNMP enterprises sub-number (20267, we use this for monitoring our game servers), so the MIB I wrote for this project is hosted in a natural place in the MIB hierarchy.

What’s this?

It’s a Net-SNMP perl subagent that connects to your MySQL server, and reports various statistics (from show status or show innodb status, or even replication) through SNMP.

But wait, there’s more, there’s OpenNMS support!

If you followed this blog from the very start, you know we’re using OpenNMS to monitor Days of Wonder infrastructure. So I included the various OpenNMS configuration bit to display nice and usable graphs, inspired by the excellent MySQL Cacti Templates.

Here are some examples:

So, I want it! Where should I look?

The code is hosted in my github repository, and everything you should know is in the mysql-snmp page on my site.

If you use this software, please do not hesitate to contribute, and/or fix bugs

Share and Enjoy:

Filed under: Monitoring, MySQL, System Administration, snmp

3 Responses for "Introducing mysql-snmp!"

Neil Ferguson May 12th, 2009 at 6:32 pm 1
I’m using this, it’s exactly what I needed, great work. (I’m also a huge fan of Ticket To Ride!)
adrian December 4th, 2009 at 6:47 pm 2
I have found this very useful for the last few months. Unfortunately, when I upgraded to OpenNMS 1.6.7, it quit working.

After some effort, I came up with a workaround, though I haven’t yet found the actual problem. It appears that when OpenNMS sends a lot of requests in one packet, mysql-agent responds to all of them, and passes the response back to net-snmp, without any apparent problem. But net-snmp doesn’t like the response and produces an error and unregisters the mysql-agent (which automatically reregisters itself after a few seconds). I was able to reproduce the problem by doing:

snmpgetnext -r0 -t 5 -m “+:/usr/share/snmp/mibs/MYSQL-SERVER-MIB.txt” -v1 -c public hostname .1.3.6.1.4.1.20267.200.1.1 .1.3.6.1.4.1.20267.200.1.2 .1.3.6.1.4.1.20267.200.1.3 .1.3.6.1.4.1.20267.200.1.4 .1.3.6.1.4.1.20267.200.1.5 .1.3.6.1.4.1.20267.200.1.6 .1.3.6.1.4.1.20267.200.1.7

The above will produce an error. If I drop the last OID from the request, it works fine. I am curious if others have the same problem or not. It’s possible there is a problem in the mysql-agent code, but it may also be in the perl snmp library, or even in my OpenNMS configuration… I am running this on a RHEL 5 box, with all of the official patches as of a few weeks ago. None of the newer patches seem related, but I’ll try to patch it again soon.

The workaround was to reconfigure snmp-config.xml so that when talking to the mysql servers, it only sends 1 oid at a time. (I think you could probably go up to 5 or 6, but I just set it down to 1 to be safe.)
masterzen December 4th, 2009 at 10:54 pm 3
@adrian,

First: better open an issue here:
http://github.com/masterzen/mysql-snmp/issues
if you want to get support.

While I don’t exactly know why it behaves like that for you, make sure you at least SNMP v2c or v3 to query mysql-snmp otherwise it won’t answer correctly 64bits counter and will stop scanning the table when encountering a 64 bit value.

You can configure opennms to query less oid per requests (see maxVarsPerPdu in datacollection-config.xml).

Still on the same subject, make sure to use a recent net-snmp on which you should add the following patch:
http://sourceforge.net/tracker/?func=detail&aid=2890931&group_id=12694&atid=312694

This patch (which I originally contributed but wasn’t correctly applied uptream) fixes how counter 64 are handled in perl.

Also, I plan to release 1.0 soon, make sure to try the current 1.0rc2 (from the github repository or download section).

Note: I tried your snmpgetnext command successfully on my own servers and it displays the correct results (with 1.0rc2) and net-snmp 5.4.1 (patched) under debian lenny.
Of course with -v2c it reports the correct result and doesn’t choke on the last oid.
What net-snmp version are you running?