321 lines
10 KiB
Perl
Executable File
321 lines
10 KiB
Perl
Executable File
#!/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";
|
|
}
|
|
else
|
|
{
|
|
print "yes\n";
|
|
}
|
|
$dbh->disconnect();
|
|
exit 0;
|
|
}
|
|
|
|
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
|