1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
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_<databasename>_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";
|
