Improved Cacti monitoring templates for MySQL
Download MySQL Cacti templates
As promised, I’ve created some improved software for monitoring MySQL via Cacti. I began using the de facto MySQL Cacti templates a while ago, but found some things I needed to improve about them. As time passed, I rewrote everything from scratch. The resulting templates are much improved.
You can grab the templates by browsing the source repository on the project’s homepage.
In no particular order, here are some things I improved:
- Standard polling interval and graph size by default.
- Full captions on every graph; you don’t have to guess at how big the values are. Each graph has current, max, and average values printed at the bottom for every value on it.
- Much more data is captured. I’ve graphed almost everything I could think of.
- The graphs are grouped better. Most graphs have only related values. There are some exceptions, but not many.
- The templates don’t hijack your existing installation. They don’t depend on or alter anything in your default Cacti installation.
- The script that gathers the data is totally rewritten from scratch, and much improved. For example, the math works on 32-bit systems. It has caching built-in so each poll cycle results in just one request to the server, instead of one request per graph. (This is a weakness of Cacti I’m trying to work around). It also has debugging aids and other good coding stuff.
- By default, it assumes you have the same username and password across every server you’re monitoring, so you don’t have to fill in a username and password for every single graph you create.
- One data template == one graph template. This helps work around another Cacti limitation.
- Lots more. Honestly I can’t really remember everything I’ve done. I’m sure you’ll help me remember by asking me how to get X feature working the way you want, and I’ll go “oh, yeah, that’s another thing I improved…”
Cacti templates are very laborious to create if they’re complex at all; it takes a long time and is very error-prone. Instead of doing it through Cacti’s web interface and exporting a huge XML file, I eliminated the redundancies and created a small, easy-to-maintain file from which I generate the XML template with a Perl script. This gives the added benefit of letting me (or you) generate templates with different parameters such as polling interval or graph size. The README file has the full details. However, I’ve pre-generated a set of templates that matches Cacti’s defaults, so you can probably just use that.
This has taken a lot of time. In particular, I spent a lot of time working on it at my former employer, The Rimm-Kaufman Group (kudos to them for letting me open-source the work) and I just spent most of my weekend writing the scripts to convert from the compact format to XML templates, so it’s possible to maintain these beasts. Plus I had to develop the compact format, too. This took a lot of time because I had to understand the Cacti data model, which is pretty complex.
Please enter issue reports for bugs, feature requests, etc at the Google project homepage, not in the comments of this blog post. I do not look through comments on my blog when I’m trying to remember what I should be working on for a software project.
If these templates help you and you feel like visiting my Amazon.com wishlist and sending something my way, I’d appreciate it!
PS: You may also be interested in Alexey Kovyrin’s list of templates for monitoring servers.
Very nice! Can’t wait to try them in the coming week. Thanks for sharing.
Frank Mashraqi
27 Apr 08 at 9:36 pm
Oh man. Bless you. I’m going to try them out first thing in the morning. The temysql scripts were a great start, but haven’t changed in years. Thanks so much for the work on this!
Mike
28 Apr 08 at 1:32 am
Great stuff, working perfectly here, and came right on time. I needed to comment out the Innodb stuff since I have –skip-innodb enabled.
Pieter
28 Apr 08 at 3:27 pm
One of the things I was thinking about doing was adding options to the make-template.pl script so you can easily generate templates with or without the InnoDB-specific things and the MyISAM-specific things, or even generate one template at a time so you can pick which ones you want. Do you think this is helpful?
Also, in this case the PHP script should not try to monitor SHOW INNODB STATUS, of course :-) I haven’t tested it on servers that don’t have InnoDB enabled. Did it crash, or just return all 0 results? I would like it to return all 0 results, not crash.
Xaprb
28 Apr 08 at 3:37 pm
Very glad someone took a crack at this, but i’m having some difficulties.
All NAN in the graphs, this error when you try to run the php script as the cacti user.
AS binary_log_space ‘: Thread stack overrun: 120304 bytes used of a 131072 byte stack, and 10788 bytes needed. Use ‘mysqld -O thread_stack=#’
I’ve used the older mysql templates with no issues, and fairly sure this is not a cacti issue, used for a lot of other stuff.
Matt
29 Apr 08 at 8:21 pm
Matt – this may be related – on my test system (no replication, hence no bin logging), the script crapped out when it tried to retrieve information about said logs. I commented out anything referring to them- ie: lines 163ish-170ish (I’ve added some debug code to my own copy, so the line numbers might be a little off) (including “SHOW MASTER LOGS”), and lines 353ish-363ish (“including AS binary_log_space”). That seems to allow the script to run cleanly, though I haven’t gone back to recreate the rrds, and whatnot. Cacti kinda makes my brain bleed.
Mike
29 Apr 08 at 8:26 pm
Great, thanks Mike.
That did clear up those script errors, and provides data when calling it directly. Still no joy when running poller.
It’s parsing correctly in the multi output parser section, but fails on the rrd update.
04/29/2008 07:40:10 PM – POLLER: Poller[0] Parsed MULTI output field ‘rows_deleted:5066′ [map rows_deleted->rows_deleted]
04/29/2008 07:40:10 PM – POLLER: Poller[0] CACTI2RRD: /usr/bin/rrdtool update /var/www/cacti/rra/mysql_server_rows_deleted_52.rrd –template 1209516001:U
ERROR: Not enough arguments
Gotta be something in the data template, but too much staring at it today.
Thanks for the help, and thanks again to the author for the software =)
Matt
29 Apr 08 at 8:42 pm
When I try to import your xml I get the following error
Error: XML: Hash version does not exist.
Is this a cacti version issue? I have 0.8.6i?
Pleas help me?
Dejc
6 May 08 at 1:54 am
When I try to use templates, my graphs don’t update. Rrd files are not being created. I dont what is wrong. I imported the host template, copyied the script. I can manually run the script from the command line without any problem. I gave privileges to cactiuser account on the mysql side. In addition, on some cacti servers, when I tried to add a device using mysql host template, I get this error:
om SNMP not valid. Partial Result: …
230 [main] ? (5436) C:\cygwin\bin\sh.exe: *** fatal error – system shared me
mory version mismatch detected – 0x2D1E009C/0x8A88009C.
This problem is probably due to using incompatible versions of the cygwin DLL.
Search for cygwin1.dll using the Windows Start->Find/Search facility
and delete all but the most recent version. The most recent version *should*
reside in x:\cygwin\bin, where ‘x’ is the drive on which you have
installed the cygwin distribution. Rebooting is also suggested if you
are unable to find another cygwin DLL.
Can anybody help?
mike
19 May 08 at 11:40 am
Please seek help for your issue in the Cacti forums. Nobody reads the comments on this blog when they’re looking for help, so a) nobody will notice you here and b) nobody will ever benefit from you posting the solution here, if you solve the issue. Thanks!
Xaprb
19 May 08 at 11:52 am