MySQL connections ? 🔗 link

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

thanks
Sabeer MZ

Hi,

Sabeer MZ wrote :

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 - user-98f9625a7a59@xymon.invalid - (+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";
}

Hi,

Thanks for the modified version, but I can't get the graphs working. What exactly do I have to do to have these graphs? I already added ',mysql' to the TEST2RRD line in hobbitserver.cfg and TRENDS:*,mysql to the related host in bb-hosts. It seems that there aren't any mysql.rrd files created.

Thanks,

Joost

Charles Goyard wrote:

Joost van den Broek wrote :

You have to add ",mysql=ncv" to TEST2RRD, and then restart the
hobbitd_rrd processes. On Linux, try :

killall `pidof hobbitd_rrd`
or
killall `pidof hobbitd_larrd` if you have an old hobbit installation.

The processes will respawn a few seconds later.

(or restart you whole hobbit service if you prefer).


-- 
Charles Goyard - user-98f9625a7a59@xymon.invalid - (+33) 1 45 38 01 31

Thanks, that did the trick!

Regards,

Joost

Charles Goyard wrote:

Hi Charles, Many thanks for the info.

i am newbie to hobbit.

1. I am getting error on rrd-status.log. what may be the issue.

2007-02-28 02:41:50 Tried to down BOARDBUSY: Invalid argument
2007-02-28 02:42:29 RRD error updating
/home/hobbit/data/rrd/monitor.jrg.com/mysql.rrd from 64.88.160.214: not a
simple integer: '0.027'
2007-02-28 02:43:30 RRD error updating
/home/hobbit/data/rrd/monitor.jrg.com/mysql.rrd from 64.88.160.214: not a
simple integer: '0.027'
2007-02-28 02:45:30 RRD error updating
/home/hobbit/data/rrd/monitor.jrg.com/mysql.rrd from 64.88.160.214: not a
simple integer: '0.027'


2. I am not sure abt the  "getconfig("bb-mysql.cfg-$MACHINEDOTS");"
i.e I am not getting idea how to create this file.
if possible give me one example.-

# 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

3. how to setup a client using this perl script.


thanks
Sabeer MZ

On 2/23/07, Joost van den Broek <user-5bf70bf7662e@xymon.invalid> wrote:

-- 

Warm Regards
Sabeer MZ

Hi,

also on clientdata.log, im getting Unknown token error.

2007-02-28 01:43:04 Tried to down BOARDBUSY: Invalid argument
2007-02-28 01:43:50 Unknown token '[mysql]' ignored at line 382
2007-02-28 01:43:50 Unknown token 'DEF:t=mysql.rrd:threads:AVERAGE' ignored
at line 383
2007-02-28 01:43:50 Unknown token 'DEF:q=mysql.rrd:questions:AVERAGE'
ignored at line 384
2007-02-28 01:43:50 Unknown token 'DEF:s=mysql.rrd:slowqueries:AVERAGE'
ignored at line 385
2007-02-28 01:43:50 Unknown token 'TITLE' ignored at line 386
2007-02-28 01:43:50 Unknown token 'YAXIS' ignored at line 387
2007-02-28 01:43:50 Unknown token 'LINE1:q' ignored at line 388
2007-02-28 01:43:50 Unknown token 'GPRINT:q:LAST:' ignored at line 389
2007-02-28 01:43:50 Unknown token 'GPRINT:q:MIN:min\:' ignored at line 390

Thanks
Sabeer MZ

On 2/28/07, Sabeer MZ <user-c73a09452f94@xymon.invalid> wrote:

# or use server-side config : create a etc/clientside/bb-
mysql.cfg-HOSTNAME

-- 

Warm Regards
Sabeer MZ