From b4fd40ac48b7a67dd35e3a1e079c1ad38ddcb105 Mon Sep 17 00:00:00 2001 From: andreas Date: Fri, 1 Feb 2008 11:46:02 +0000 Subject: =?UTF-8?q?neuer=20versuch=20mit=20munin=20f=C3=BCr=20immer1-5?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- files/plugins/pg__connections | 142 ++++++++++++++++++++++++++++++++++++++++++ files/plugins/pg__locks | 119 +++++++++++++++++++++++++++++++++++ files/plugins/pg_conn | 51 +++++++++++++++ 3 files changed, 312 insertions(+) create mode 100755 files/plugins/pg__connections create mode 100755 files/plugins/pg__locks create mode 100755 files/plugins/pg_conn (limited to 'files') diff --git a/files/plugins/pg__connections b/files/plugins/pg__connections new file mode 100755 index 0000000..ca95f56 --- /dev/null +++ b/files/plugins/pg__connections @@ -0,0 +1,142 @@ +#!/usr/bin/perl -w +# Plugin for monitor postgres connections. +# +# Licenced under GPL v2. +# +# Usage: +# +# Symlink into /etc/munin/plugins/ and add the monitored +# database to the filename. e.g.: +# +# ln -s /usr/share/munin/plugins/pg__connections \ +# /etc/munin/plugins/pg__connections +# This should, however, be given through autoconf and suggest. +# +# If required, give username, password and/or Postgresql server +# host through environment variables. +# +# You must also activate Postgresql statistics. See +# http://www.postgresql.org/docs/7.4/interactive/monitoring-stats.html +# for how to enable this. Specifically, the following lines must +# exist in your postgresql.conf: +# +# stats_start_collector = true +# stats_block_level = true +# +# +# Parameters: +# +# config (required) +# +# Config variables: +# +# dbhost - Which database server to use. Defaults to +# 'localhost'. +# dbname - Which database to use. Defaults to template1 +# dbuser - A Postgresql user account with read permission to +# the given database. Defaults to +# 'postgres'. Anyway, Munin must be told which user +# this plugin should be run as. +# dbpass - The corresponding password, if +# applicable. Default to undef. Remember that +# pg_hba.conf must be configured accordingly. +# +# Magic markers +#%# family=auto +#%# capabilities=autoconf + +use strict; +use DBI; + +my $dbhost = $ENV{'dbhost'} || '127.0.0.1'; +my $dbname = $ENV{'dbname'} || 'template1'; +my $dbuser = $ENV{'dbuser'} || 'postgres'; +my $dbuserx = $ENV{'dbuserx'} || ''; +my $dbport = $ENV{'dbport'} || '5432'; +my $dbpass = $ENV{'dbpass'} || ''; + +# Check for DBD::Pg +if (! eval "require DBD::Pg;") { + print "requires DBD::Pg\n"; + exit 1; +} + +my $dsn = "DBI:Pg:dbname=$dbname;host=$dbhost;port=$dbport"; +#print "$dsn\n"; +my $dbh = DBI->connect ($dsn, $dbuser, + $dbpass, + {RaiseError =>1}) || die ""; + + + +if (exists $ARGV[0]) { + if ($ARGV[0] eq 'autoconf') { + # Check for DBD::Pg + if (! eval "require DBD::Pg;") { + print "no (DBD::Pg not found)"; + exit 1; + } + if ($dbh) { + print "yes\n"; + exit 0; + } else { + print "no Unable to access Database $dbname on host $dbhost as user $dbuser.\nError returned was: ". $DBI::errstr; + exit 1; + } + } + + if ($ARGV[0] eq "config") { + my $sql_max = "SHOW max_connections;"; + my $sth_max = $dbh->prepare($sql_max); + $sth_max->execute(); + my ($max_connections) = $sth_max->fetchrow(); + my $warning = int ($max_connections * 0.7); + my $critical = int ($max_connections * 0.8); + print "graph_title PostgresSQL active connections\n"; + print "graph_args -l 0 --base 1000\n"; + print "graph_vlabel Connections\n"; + print "graph_category Postgresql\n"; + print "graph_info Shows active Postgresql connections from $dbname\n"; + + + my $sql = "select datname, count(*) from pg_stat_activity group by datname"; + my $sth = $dbh->prepare($sql); + $sth->execute(); + my $setarea = "yes"; + while ( my ($datname,$curr_conn) = $sth->fetchrow_array ) { + print "$datname.label $datname active connections\n"; + print "$datname.info $datname active connections\n"; + print "$datname.type GAUGE\n"; + if ($setarea eq "yes") { + print "$datname.draw AREA\n"; + $setarea=""; + } else { + print "$datname.draw STACK\n"; + } + } + + print "max_connections.label Max. connections\n"; + print "max_connections.info Max. connections\n"; + print "max_connections.type GAUGE\n"; + + print "warning $warning\n"; + print "critical $critical\n"; + exit 0; + } +} + + +# select datname, count(*) from pg_stat_activity group by datname +my $sql_max = "SHOW max_connections;"; +my $sth_max = $dbh->prepare($sql_max); +$sth_max->execute(); +my ($max_connections) = $sth_max->fetchrow(); + +#my $sql = "SELECT COUNT (*) FROM pg_stat_activity;"; +my $sql = "select datname, count(*) from pg_stat_activity group by datname"; +my $sth = $dbh->prepare($sql); +$sth->execute(); +while ( my ($datname,$curr_conn) = $sth->fetchrow_array ) { + print "$datname.value $curr_conn\n"; +} +print "max_connections.value $max_connections\n"; diff --git a/files/plugins/pg__locks b/files/plugins/pg__locks new file mode 100755 index 0000000..33a9a8a --- /dev/null +++ b/files/plugins/pg__locks @@ -0,0 +1,119 @@ +#!/usr/bin/perl -w +# Plugin for monitor postgres connections. +# +# Licenced under GPL v2. +# +# Usage: +# +# Symlink into /etc/munin/plugins/ and add the monitored +# database to the filename. e.g.: +# +# ln -s /usr/share/munin/plugins/pg__locks \ +# /etc/munin/plugins/pg__locks +# This should, however, be given through autoconf and suggest. +# +# If required, give username, password and/or Postgresql server +# host through environment variables. +# +# You must also activate Postgresql statistics. See +# http://www.postgresql.org/docs/8.1/interactive/monitoring-locks.html +# for how to enable this. Specifically, the following lines must +# exist in your postgresql.conf: +# +# stats_start_collector = true +# stats_block_level = true +# +# +# Parameters: +# +# config (required) +# +# Config variables: +# +# dbhost - Which database server to use. Defaults to +# 'localhost'. +# dbname - Which database to use. Defaults to template1 +# dbuser - A Postgresql user account with read permission to +# the given database. Defaults to +# 'postgres'. Anyway, Munin must be told which user +# this plugin should be run as. +# dbpass - The corresponding password, if +# applicable. Default to undef. Remember that +# pg_hba.conf must be configured accordingly. +# +# Magic markers +#%# family=auto +#%# capabilities=autoconf + +use strict; +use DBI; + +my $dbhost = $ENV{'dbhost'} || '127.0.0.1'; +my $dbname = $ENV{'dbname'} || 'template1'; +my $dbuser = $ENV{'dbuser'} || 'postgres'; +my $dbport = $ENV{'dbport'} || '5432'; +my $dbpass = $ENV{'dbpass'} || ''; + +# Check for DBD::Pg +if (! eval "require DBD::Pg;") { + exit 1; +} + +my $dsn = "DBI:Pg:dbname=$dbname;host=$dbhost;port=$dbport"; +#print "$dsn\n"; +my $dbh = DBI->connect ($dsn, $dbuser, + $dbpass, + {RaiseError =>1}) || die ""; + + + +if (exists $ARGV[0]) { + if ($ARGV[0] eq 'autoconf') { + # Check for DBD::Pg + if (! eval "require DBD::Pg;") { + print "no (DBD::Pg not found)"; + exit 1; + } + if ($dbh) { + print "yes\n"; + exit 0; + } else { + print "no Unable to access Database $dbname on host $dbhost as user $dbuser.\nError returned was: ". $DBI::errstr; + exit 1; + } + } + + if ($ARGV[0] eq "config") { + print "graph_title PostgresSQL locks\n"; + print "graph_args --base 1000\n"; + print "graph_vlabel Locks\n"; + print "graph_category Postgresql\n"; + print "graph_info Shows Postgresql locks\n"; + print "locks.label Locks\n"; + print "locks.info Locks (more info here, please... :)\n"; + print "locks.type GAUGE\n"; + print "locks.warning 5\n"; + print "locks.critical 10\n"; + print "exlocks.label Exclusive locks\n"; + print "exlocks.info Exclusive locks (here too, please... :)\n"; + print "exlocks.type GAUGE\n"; + print "exlocks.warning 5\n"; + print "exlocks.critical 10\n"; + exit 0; + } +} + +my $sql="SELECT mode,COUNT(mode) FROM pg_locks GROUP BY mode ORDER BY mode;"; +my $sth = $dbh->prepare ($sql); +$sth->execute (); +my $locks = 0; +my $exlocks = 0; +while (my ($mode, $count) = $sth->fetchrow ()) { + if ($mode =~ /exclusive/i) { + $exlocks = $exlocks + $count; + } + $locks = $locks+$count; +} +print "locks.value $locks\n"; +print "exlocks.value $exlocks\n"; + diff --git a/files/plugins/pg_conn b/files/plugins/pg_conn new file mode 100755 index 0000000..aa2ebb7 --- /dev/null +++ b/files/plugins/pg_conn @@ -0,0 +1,51 @@ +#!/bin/sh +# +# Plugin to monitor PostgreSQL connections. +# +# Parameters: +# +# config (required) +# autoconf (optional - only used by munin-config) +# Based on netstat plugin +# $Log$ +# eric@ohmforce.com +# +# +# Magic markers (optional - used by munin-config and some installation +# scripts): +#%# family=auto +#%# capabilities=autoconf + + + +if [ "$1" = "autoconf" ]; then + if ( netstat -s 2>/dev/null >/dev/null ); then + echo yes + exit 0 + else + if [ $? -eq 127 ] + then + echo "no (netstat program not found)" + exit 1 + else + echo no + exit 1 + fi + fi +fi + +if [ "$1" = "config" ]; then + + echo 'graph_title PostgreSQL' + echo 'graph_args -l 0 ' + echo 'graph_vlabel Number of PostgreSQL connections' + echo 'graph_category postgresql' + echo 'graph_period second' + echo 'graph_info This graph shows the number of opened connections on PostgreSQL.' + echo 'established.label established' + echo 'established.type GAUGE' + echo 'established.max 500' + echo 'established.info The number of currently open connections.' + exit 0 +fi +netstat -a | awk '{ print $4 }'| grep postgres | wc -l | xargs echo established.value -- cgit v1.2.3