I’m really proud to announce the release of the version 1.0 of mysql-snmp.

What is mysql-snmp?

mysql-snmp is a mix between the excellent MySQL Cacti Templates and a Net-SNMP agent. The idea is that combining the power of the MySQL Cacti Templates and any SNMP based monitoring would unleash a powerful mysql monitoring system. Of course this project favorite monitoring system is OpenNMS.

mysql-snmp is shipped with the necessary OpenNMS configuration files, but any other SNMP monitoring software can be used (provided you configure it).

To get there, you need to run a SNMP agent on each MySQL server, along with mysql-snmp. Then OpenNMS (or any SNMP monitoring software) will contact it and fetch the various values.

Mysql-snmp exposes a lot of useful values including but not limited to:

• SHOW STATUS values
• SHOW ENGINE INNODB STATUS parsed values (MySQL 5.0, 5.1, XtraDB or Innodb plugin are supported)

Here are some graph examples produced with OpenNMS 1.6.5 and mysql-snmp 1.0 on one of Days of Wonder MySQL server (running a MySQL 5.0 Percona build):

This graph shows the number of SQL commands per unit of time

You can see the effect of MySQL bug #47991

Where to get it

mysql-snmp is available in my github repository. The repository contains a spec file to build a RPM and what is needed to build a Debian package. Refer to the README or the mysql-snmp page for more information.

Thanks to gihub, it is possible to download the tarball instead of using Git:

Mysql-snmp v1.0 tarball

Changelog

This lists all new features/options from the initial version v0.6:

• Spec file to build RPM
• Use of configuration file for storing mysql password
• Fix of slave handling
• Fix for mk-heartbeat slave lag
• Support of InnoDB plugin and Percona XtraDB
• Automated testing of InnoDB parsing
• Removed some false positive errors
• OpenNMS configuration generation from MySQL Cacti Templates core files
• 64 bits computation done in Perl instead of (ab)using MySQL
• More InnoDB values (memory, locked tables, …)

Reporting Issues

Please use Github issue system to report any issues.

Requirements

There is a little issue here. mysql-snmp uses Net-Snmp. Not all versions of Net-Snmp are supported as some older versions have some bug for dealing with Counter64. Version 5.4.2.1 with this patch is known to work fine.

Also note that this project uses some Counter64, so make sure you configure your SNMP monitoring software to use SNMP v2c or v3 (SNMP v1 doesn’t support 64 bits values).

Final words!

I wish everybody an happy new year. Consider this new version as my Christmas present to the community

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

Have you ever wondered why net-snmp doesn’t report a correct interface speed on Linux?

I was also wondering, until this morning… I tried to run net-snmp as root, and miracle, the right interface speed was detected for my interfaces.

In fact net-snmp uses the SIOCETHTOOL ioctl to access this information. Unfortunately the get settings variant of this ioctl needs to have the CAP_NET_ADMIN enabled. Of course root has this capability set, but when net-snmp drops its privileges to an unprivileged user, this capability is lost and the ioctl fails with EPERM

That’s too bad because getting this information is at most harmless and shouldn’t require special privileges to succeed. Someone even posted a Linux Kernel patch to remove CAP_NET_ADMIN check for SIOCETHTOOL which doesn’t seem to have been merged.
The fix could also be on the snmpd side before dropping privileges.

The workaround is to tell net-snmp how the interface are looking:

interface eth0 6 10000000
interface eth1 6 100000000

Here I defined eth0 as a 100mbit/s FastEthernet interface, and eth1 as a GigabitEthernet interface.