Munin contributed plugins

#!/usr/bin/perl -w

# re-write of python version of pgbouncer stats

# data from stats, pools (client, server)

use strict;

use Munin::Plugin;

use DBD::Pg;

# check that multigraph is available

need_multigraph();

# get the script name

my $plugin_name = $Munin::Plugin::me;

# set the DB connection vars

my $db_user = $ENV{'pgbouncer_user'}  || 'postgres';

my $db_port = $ENV{'pgbouncer_port'}  || '6432';

my $db_host = $ENV{'pgbouncer_host'}  || 'localhost';

my $db_pass = $ENV{'pgbouncer_pass'}  || '';

my $db_pool = $ENV{'pgbouncer_pool'}  || '';

my $db_name = 'pgbouncer';

my @data = ();

# get the DB (pool) name we want to fetch

$plugin_name =~ /pgbouncer_(.*)$/;

my $plugin_suffix = $1;

#if pool name is specified explicitly in config file

#use plugin name together with pool name in graph title:

my $pool_name = ($db_pool) ? $db_pool : $plugin_suffix;

my $plugin_title = ($db_pool) ? $plugin_suffix." ".$pool_name : $pool_name;

# bail if no name

if (!$pool_name)

{

	print "Cannot get pool name\n";

	exit 1; 

}

# command line arguments for autconf and config

if (defined($ARGV[0]))

{

	# autoconf, nothing to do

	if ($ARGV[0] eq 'autoconf')

	{

		my $dbh = DBI->connect("DBI:Pg:dbname=$db_name;host=$db_host;port=$db_port", $db_user, $db_pass);

		if (!$dbh)

		{

			print "no\n";

			exit 1;

		}

		else

		{

			print "yes\n";

			exit 0;

		}

		$dbh->disconnect();

	}

	if ($ARGV[0] eq 'config')

	{

		# create the basic RRD

		# stats: average connections

		print "multigraph ".$plugin_name."_stats_avg_req\n";

		print "graph_title PgBouncer $plugin_title average connections\n";

		print "graph_args --base 1000\n"; # numbers not bytes

		print "graph_vlabel Average connections\n";

		print "graph_scale no\n"; # so we do not print "micro, milli, kilo, etc"

    		print "graph_category db\n";

		print $pool_name."_avg_req.type GAUGE\n";

		print $pool_name."_avg_req.label Avg Req\n";

		print $pool_name."_avg_req.min 0\n";

		print $pool_name."_avg_req.draw LINE2\n";

		# stats: average time for query

		print "multigraph ".$plugin_name."_stats_avg_query\n";

		print "graph_title PgBouncer $plugin_title average query time\n";

		print "graph_args --base 1000\n"; # numbers not bytes

		print "graph_vlabel Average time per query (microseconds)\n";

    		print "graph_category db\n";

		print $pool_name."_avg_query.type GAUGE\n";

		print $pool_name."_avg_query.label Avg Time\n";

		print $pool_name."_avg_query.min 0\n";

		print $pool_name."_avg_query.draw LINE2\n";

		# stats: in/out bytes

		print "multigraph ".$plugin_name."_stats_bytesinout\n";

		print "graph_title PgBouncer $plugin_title average bytes received/sent\n";

		print "graph_args --base 1024\n"; # numbers in bytes

		print "graph_vlabel Average bytes received (-)/sent (+)\n";

    		print "graph_category db\n";

		# bytes received

		print $pool_name."_avg_recv.type GAUGE\n";

		print $pool_name."_avg_recv.label Avg received\n";

		print $pool_name."_avg_recv.min 0\n";

		print $pool_name."_avg_recv.draw LINE1\n";

		print $pool_name."_avg_recv.graph no\n";

		# bytes sent

		print $pool_name."_avg_sent.type GAUGE\n";

		print $pool_name."_avg_sent.label Avg rcvd/sent\n";

		print $pool_name."_avg_sent.min 0\n";

		print $pool_name."_avg_sent.draw LINE1\n";

		print $pool_name."_avg_sent.negative ".$pool_name."_avg_recv\n";

		# pools: server (sv_)

		print "multigraph ".$plugin_name."_pools_server\n";

		print "graph_title PgBouncer $plugin_title servers\n";

		print "graph_category db\n";

		print "graph_args --base 1000\n"; # numbers not bytes

		print "graph_vlabel Server connections\n";

		print "graph_scale no\n";

		# active connections

		print $pool_name."_server_active.label active\n";

		print $pool_name."_server_active.min 0\n";

		print $pool_name."_server_active.type GAUGE\n";

		print $pool_name."_server_active.draw AREA\n";

		# idle connections

		print $pool_name."_server_idle.label idle\n";

		print $pool_name."_server_idle.min 0\n";

		print $pool_name."_server_idle.type GAUGE\n";

		print $pool_name."_server_idle.draw STACK\n";

		# used connections

		print $pool_name."_server_used.label used\n";

		print $pool_name."_server_used.min 0\n";

		print $pool_name."_server_used.type GAUGE\n";

		print $pool_name."_server_used.draw STACK\n";

		# tested connections

		print $pool_name."_server_tested.label tested\n";

		print $pool_name."_server_tested.min 0\n";

		print $pool_name."_server_tested.type GAUGE\n";

		print $pool_name."_server_tested.draw STACK\n";

		# logged in connections

		print $pool_name."_server_login.label login\n";

		print $pool_name."_server_login.min 0\n";

		print $pool_name."_server_login.type GAUGE\n";

		print $pool_name."_server_login.draw STACK\n";

		# pools: client (cl_)

		print "multigraph ".$plugin_name."_pools_client\n";

		print "graph_title PgBouncer $plugin_title clients\n";

		print "graph_category db\n";

		print "graph_args --base 1000\n"; # numbers not bytes

		print "graph_vlabel Client connections\n";

		print "graph_scale no\n";

		# active client connections

		print $pool_name."_client_active.label active\n";

		print $pool_name."_client_active.min 0\n";

		print $pool_name."_client_active.type GAUGE\n";

		print $pool_name."_client_active.draw AREA\n";

		# waiting client connections

		print $pool_name."_client_waiting.label waiting\n";

		print $pool_name."_client_waiting.min 0\n";

		print $pool_name."_client_waiting.type GAUGE\n";

		print $pool_name."_client_waiting.draw STACK\n";

		# pools: maxwait (longest waiting connection, should be 0)

		print "multigraph ".$plugin_name."_pools_maxwait\n";

		print "graph_title PgBouncer $plugin_title maximum waiting time\n";

		print "graph_args --base 1000\n"; # numbers not bytes

		print "graph_vlabel Maximum wait time (seconds)\n";

    		print "graph_category db\n";

		print $pool_name."_maxwait.type GAUGE\n";

		print $pool_name."_maxwait.label Wait Time\n";

		print $pool_name."_maxwait.min 0\n";

		print $pool_name."_maxwait.draw LINE2\n";

		print $pool_name."_maxwait.warning 1\n"; # warn if not 0

		print $pool_name."_maxwait.critical 10\n"; # go critical if 10 seconds waiting

		# END graph

		exit 0;

	}

}

# connect to data

my $dbh = DBI->connect("DBI:Pg:dbname=$db_name;host=$db_host;port=$db_port", $db_user, $db_pass) 

	 or die ("Cannot connect to database");

# go trough each set and get the data

foreach my $get ('pools', 'stats')

{

	# prep and execute the show query

	my $pre = $dbh->prepare("SHOW $get")

		or die ("Cannot prepare query");

	$pre->execute()

		or die ("Cannot execute statement");

	while (@data = $pre->fetchrow)

	{

		# first defines the pool

		if ($data[0] eq $pool_name)

		{

			# print values for the stats: average request, average query time, bytes in/out

			if ($get eq 'stats')

			{

				print "multigraph ".$plugin_name."_".$get."_avg_req\n";

				print $pool_name."_avg_req.value ".$data[5]."\n";

				print "multigraph ".$plugin_name."_".$get."_avg_query\n";

				print $pool_name."_avg_query.value ".$data[8]."\n";

				print "multigraph ".$plugin_name."_".$get."_bytesinout\n";

				print $pool_name."_avg_recv.value ".$data[6]."\n";

				print $pool_name."_avg_sent.value ".$data[7]."\n";

			}

			# print data for the pools: server, client

			if ($get eq 'pools')

			{

				print "multigraph ".$plugin_name."_".$get."_server\n";

				print $pool_name."_server_active.value ".$data[4]."\n";

				print $pool_name."_server_idle.value ".$data[5]."\n";

				print $pool_name."_server_used.value ".$data[6]."\n";

				print $pool_name."_server_tested.value ".$data[7]."\n";

				print $pool_name."_server_login.value ".$data[8]."\n";

				print "multigraph ".$plugin_name."_".$get."_client\n";

				print $pool_name."_client_active.value ".$data[2]."\n";

				print $pool_name."_client_waiting.value ".$data[3]."\n";

				print "multigraph ".$plugin_name."_".$get."_maxwait\n";

				print $pool_name."_maxwait.value ".$data[9]."\n";

			}

		}

	}

}

# close connection

$dbh->disconnect();

exit 0;

__END__

=head1 NAME

pgbouncer_ is a plugin to get the pool and stat values for a single pgbouncer pool name

=head1 APPLICATION

perl and DBD::Pg is required, and pgbouncer must been installed with a correct setup access for a stat account

=head1 CONFIGURATION

the plugin that will be run needs to have the pool name after the plugin base name.

alternatively, pool name can be specified in config file as env.pgbouncer_pool option, separating plugin name from pool name.

=head2 plugin configuration

eg: pgbouncer_foo will run for the pool named foo.

see SHOW POOLS database list for the pool name

=head2 munin plugin config file

in the plugin config file under the [pgbouncer] name the access information ca be set.

eg:

  [pgbouncer*]

    env.pgbouncer_pass barfoo

more extended would be:

  [pgbouncer*]

    env.pgbouncer_pass barfoo

    env.pgbouncer_user bar

    env.pgbouncer_port 6542

    env.pgbouncer_host localhost

another example, where different pgbouncers (and so munin plugins) connecting to same db:

   [pgbouncer_weblogin]

     env.pgbouncer_pass barfoo

     env.pgbouncer_user bar

     env.pgbouncer_port 6542

     env.pgbouncer_host localhost

     env.pgbouncer_pool dbname

   [pgbouncer_webmain]

     env.pgbouncer_pass barfoo

     env.pgbouncer_user bar

     env.pgbouncer_port 6543

     env.pgbouncer_host localhost

     env.pgbouncer_pool dbname

The database name is always pgbouncer

=head1 OUTPUT

The plugin will output 5 graphs in the group pgbouncer

=head2 Average bytes received/sent

This graph will show the average bytes sent and received by the pgbouncer for this pool

=head2 Average connections

This graph will show the average amount of connections to the pgbouncer for this pool 

=head2 Average query time

This graph shows the average query time as processed by the pgbouncer for this pool in microseconds. The data will be shorted by standard SI. eg, m = milli, k = kilo.

So 4.61K is 4610 milliseconds

=head2 Client connections

This graph shows the active and waiting client connections to pgbouncer for this pool

=head2 Server connections

This graph shows the server connections to pgbouncer for this pool. The following data sets are shown: active, idle, used, tested, login

=head2 Max wait

how long the oldest client the queue has waited, should be always 0

=head1 ACKNOWLEDGEMENTS

Original idea derived from a simple python script by Dimitri Fontaine

=head1 SEE ALSO

See further info on stats and pools on the pgbouncer homepage:

  http://pgbouncer.projects.postgresql.org/doc/usage.html#_show_commands

=head1 VERSION

1.0

=head1 AUTHOR

Clemens Schwaighofer <gullevek@gullevek.org>

=head1 LICENSE

GPLv2

=cut