A better nagios check_mysql

The standard check_mysql that comes with the nagios plugin distribution is, to be frank, a bit disappointing. It’ll tell you if mysql is up or down, but that’s really about it. In particular, it’s performance data is rubbish. Although it claims to report the average query rate, it’s actually telling you the average since the database started, which isn’t an awfully good statistic.

So, I determined to make a better one. Here, in clunkiest ruby, it is.

It uses the output of ‘mysqladmin extended-status’, filtering out just the counts of each different query type, and then stores them in a file along with the uptime, so it only starts giving you stats on the second run. It gives you the average number of selects/inserts/updates/deletes, plus the number of threads, as perfdata, so you can plug it into pnp and get lovely munin-style graphs.

It doesn’t do threshold-based warnings for the query numbers, though I don’t think that would be very hard to build in.

It would also be trivial to hack this to get other kinds of perf data out of mysql (though you might run into the 256-char limit on plugin responses if you added any more, so you probably want multiple instances of the check). If you wanted, say, the innoDB stats, just replace the regexes in the awk statement with /Inodb/.

Now I think about it, I should probably round down all the numbers to 2 decimal places; then I could pass back a bit more perfdata without worrying about it…
enjoy!

def parse_status(status)
  h={}
  status.each_line do |line|
    data=line.split
    h[data.first.strip]=data.last
  end
  h
end

status_file='/tmp/mysql.status'
mysql_status=`mysqladmin --defaults-extra-file=/export/home/nagios/.my.cnf extended-status | awk '( /Com_select/ || /Com_insert/ || /Com_delete/ || /Com_update/|| /Uptime / ||/Threads/ ) {print $2, $4}'`
if (mysql_status.collect.length ==0) then
  print "CRITCAL: mysqadmin extended-status failure: mysql down?" 
  exit 2
end
old_status="" 

if (File.exists?(status_file)) then
  File.open(status_file, "r") { |f|
    old_status = f.read
  }
end

File.open(status_file,"w+")do |f|
   f.write(mysql_status )
end 
old_s=parse_status(old_status)
new_s=parse_status(mysql_status)

duration=new_s["Uptime"].to_f - old_s["Uptime"].to_f
diffs={}
old_s.each_pair do |key, value|
  if (key != "Uptime") then
     diffs[key]= (new_s[key].to_f - value.to_f) /duration
  end
end
print "OK|" 
print "threads=#{new_s["Threads_running"]};;;; " 
diffs.each_pair do |key, val|
   if (key !~/Threads/) then
     print "#{key.downcase}=#{val};;;;" 
   end
end
exit 0

Chris May : 11 Jun 2009 21:02 |  Comments (2) |  Report a problem