[hobbit] MySQL connections ?

[Charles Goyard]

Hi,

Sabeer MZ wrote :
> One question: does anybody know if there is a module for Hobbit to
> monitor the number of MySQL connections ?

there's one here : http://www.trantor.org/theshire/doku.php/monitors:bb-mysql

There's anoter version that can fetch its configuration from the hobbit
server, provided as a attachement in this message.

And here's the graph setup I use :

[mysql]
        DEF:t=mysql.rrd:threads:AVERAGE
        DEF:q=mysql.rrd:questions:AVERAGE
        DEF:s=mysql.rrd:slowqueries:AVERAGE
        TITLE Requêtes SQL
        YAXIS count
        LINE1:q#FF0000:Requêtes
        GPRINT:q:LAST:     cur\: %3.0lf
        GPRINT:q:MIN:min\: %3.0lf
        GPRINT:q:MAX:max\: %3.0lf
        GPRINT:q:AVERAGE:avg\: %3.0lf\n
        LINE1:s#0000FF:Req. lentes
        GPRINT:s:LAST:  cur\: %3.0lf
        GPRINT:s:MIN:min\: %3.0lf
        GPRINT:s:MAX:max\: %3.0lf
        GPRINT:s:AVERAGE:avg\: %3.0lf\n
        LINE1:t#00FF00:Threads
        GPRINT:t:LAST:      cur\: %3.0lf
        GPRINT:t:MIN:min\: %3.0lf
        GPRINT:t:MAX:max\: %3.0lf
        GPRINT:t:AVERAGE:avg\: %3.0lf\n


There is also the a multi-db agent here, that looks very fine (never
tried it) : http://sourceforge.net/projects/hobbit-perl-cl


-- 
Charles Goyard - cgoyard at cvf.fr - (+33) 1 45 38 01 31
-------------- next part --------------
#!/usr/bin/perl -w
#
# bb-mysql - mysql check and metrics
# cgoyard:2006-07-18

use strict;

my $DEBUG       = 0;

$ENV{BBPROG}    = "bb-mysql.pl";
my $TESTNAME    = "mysql";

my $BBHOME      = $ENV{BBHOME};
my $BB          = $ENV{BB};       # full path to the bin/bb util
my $BBDISP      = $ENV{BBDISP};   # IP of the BBDISPLAY server
my $BBVAR       = $ENV{BBVAR};
my $MACHINE     = $ENV{MACHINE};  # hostname, fqdn
my $MACHINEDOTS = $ENV{MACHINEDOTS}; # hostname, fqdn, dotted
my $COLOR       = "clear";        # global color for the test
my $MSG         = "";             # body of the message
my $HEAD        = "";             # first line of the message (short, optional)
my $DATA        = "";             # data for NCV records (hobbit only)

if ($DEBUG == 1) {
	$BBHOME  = "/tmp" unless $BBHOME;
	$BBVAR   = "/tmp" unless $BBVAR;
	$MACHINE = "test.host.cvf" unless $MACHINE;
}

sub clear; sub green; sub yellow; sub red;
sub setcolor; sub head; sub msg; sub data;
sub sendreport; sub resetreport;


######################################################################
# here we go
############

# First, create a dumb user that can do status and show variables :
# grant select on devnull.* to monitoring at localhost identified by 'monitoring';
# revoke all on devnull.* from monitoring at localhost;
# or use server-side config : create a etc/clientside/bb-mysql.cfg-HOSTNAME
# on the hobbit server, with the following setup :
# mysqlclient=/some/other/client
# auth=-uthisuser -pthatpassword -Sothersocket

my $auth    = "-umonitoring -pmonitoring";
my $client  = "/usr/local/bin/mysql";

my $confnotfound = 0;

my @conf = getconfig("bb-mysql.cfg-$MACHINEDOTS");

if(@conf) {
	my $line;
	foreach $line (@conf) {
		chomp $line;
		if($line =~ /^mysqlclient(\s+|=)(.+)$/) {
			$client = $2;
		}
		elsif($line =~ /^auth(\s+|=)(.+)/) {
			$auth = $2;
		}
	}
}
else {
	$confnotfound = 1;
}


my (@output, $version, $metrics, $uptime, $maxcon, $ts);
@output = `$client $auth -Bs -e "select 'DATE', now() ; status; show variables"`;
if($? == 0 and $output[0] =~ /^DATE\s+(.+)$/) {
	$ts = $1;
	green;
}
else {
	head("MySQL Server DOWN");
	msg("&red MySQL Server is broken");
	msg("(hint: configuration file was not found") if $confnotfound;
	red;
	sendreport;
	exit 1;
}

# metrics
foreach (@output) {
	if ( /^Server version:*\s+(.+)$/ )    { $version = $1     }
	elsif ( /^Uptime:\s+(.+)/ )          { $uptime  = $1     }
	elsif ( /^(Threads:.+)$/ )           { $metrics = lc($1) }
	elsif ( /^max_connections\s+(\d+)/ ) { $maxcon  = $1     }
}

$metrics =~ s/(\d)\s+/$1\n/g; # remplace les espaces qui suivent un chiffre par des \n
msg $metrics;
head("MySQL Server OK");
msg "maxcon: $maxcon\n";
msg("Server version: $version\n") if $version;
msg("Uptime: $uptime\n");
msg("Server reports timestamp of $ts");
sendreport;
exit 0;




######################################################################
# toolbox
###########

####
# get module configuration from the server
####

sub getconfig
{
	my $file = shift;
	my $cmd = "$BB $BBDISP \"config $file\"";
	return `$cmd`;
}

####
# sends the report
####

sub sendreport
{
	$MACHINE =~ s/\./,/g;
	my $date = localtime;
	$BB = "/bin/echo $BB" if($DEBUG == 1);
	my $cmd = "$BB $BBDISP \"status $MACHINE.$TESTNAME $COLOR $date $HEAD\n$DATA\n$MSG\"";
	system($cmd);
}

sub resetreport
{
	$MSG = $DATA = $HEAD = '';
	$COLOR = 'clear';
}

# sets the global color of the test
# prevents downgrading severity
# clear == green < yellow < red
sub setcolor
{
	my $newcolor = shift;
	if($newcolor eq "red") {
		$COLOR = "red";
	}
	elsif($COLOR eq "green" or $COLOR eq "clear") {
		$COLOR = "$newcolor";
	}
	
	return $COLOR;
}
sub clear  { setcolor 'clear'  }
sub green  { setcolor 'green'  }
sub yellow { setcolor 'yellow' }
sub red    { setcolor 'red'    }


sub data
{
	my ($n, $v) = @_;
	$DATA .= "$n: $v\n";
}

sub head
{
	$HEAD = "@_";
}

sub msg
{
	$MSG .= join("\n", @_) . "\n";
}