1 |
#!/usr/bin/perl |
2 |
|
3 |
# (Temporary) source: http://munin.projects.linpro.no/ticket/63 |
4 |
# Written by Bjørn Ruberg (bjorn@linpro.no) 2006 |
5 |
# Rewritten by Moses Moore 2006-04-08 moc.iazom@sesom |
6 |
# Licenced under GPL |
7 |
|
8 |
# Magic markers |
9 |
#%# family=auto |
10 |
#%# capabilities=autoconf suggest |
11 |
|
12 |
use strict; |
13 |
use DBI; |
14 |
use vars qw ( $debug $suggest $configure $dbh ); |
15 |
|
16 |
# Package maintainers should provide an environment |
17 |
# file for the /etc/munin/plugin-conf.d/ directory |
18 |
# to override these values if necessary. |
19 |
# NOTE: The plugin (also when auto configured) should |
20 |
# be run by the postgresql user account. |
21 |
|
22 |
# Need these variables at an early stage to enable |
23 |
# autoconf and suggest |
24 |
my $dbhost = $ENV{'dbhost'} || ''; # Connect to localhost by default |
25 |
my $dbname = $ENV{'dbname'} || 'template1'; |
26 |
my $dbuser = $ENV{'dbuser'} || 'postgres'; |
27 |
my $dbpass = $ENV{'dbpass'} || ''; |
28 |
|
29 |
if (exists $ARGV[0]) { |
30 |
if ($ARGV[0] eq 'autoconf') { |
31 |
# Check for DBD::Pg |
32 |
if (! eval "require DBD::Pg;") { |
33 |
print "no (DBD::Pg not found)"; |
34 |
exit 1; |
35 |
} |
36 |
# Then we try to detect Postgres presence by connecting to |
37 |
# 'template1'. |
38 |
my $dsn = "dbi:Pg:dbname=template1"; |
39 |
$dsn .= ";host=$dbhost" if $dbhost; |
40 |
my $tempdbh = DBI->connect ($dsn, $dbuser, $dbpass); |
41 |
if ($tempdbh) { |
42 |
print "yes\n"; |
43 |
exit 0; |
44 |
} else { |
45 |
print "no (Can't connect to given host, please check environment settings)\n"; |
46 |
exit 1; |
47 |
} |
48 |
} elsif ($ARGV[0] and $ARGV[0] eq 'debug') { |
49 |
# Set config flag |
50 |
$debug = 1; |
51 |
} elsif ($ARGV[0] and $ARGV[0] eq 'config') { |
52 |
# Set config flag |
53 |
$configure = 1; |
54 |
} elsif ($ARGV[0] eq 'suggest') { |
55 |
# doesn't always work |
56 |
my @datasources = DBI->data_sources ('Pg'); |
57 |
foreach my $dsn (grep !/\=template\d$/, @datasources) { |
58 |
(my $db = $dsn) =~ s/^.*=//; |
59 |
print "$db\n"; |
60 |
} |
61 |
exit 0; |
62 |
} |
63 |
} |
64 |
|
65 |
# Must do this here, after checking for autoconf/suggest/etc, because the |
66 |
# plugin must be able to run before it is linked to the databases. |
67 |
my (undef, undef, $dbname) = split (/_/, $0, 3); |
68 |
die "No dbname configured (did you make the proper symlink?)" unless $dbname; |
69 |
|
70 |
my @datasources = DBI->data_sources ('Pg') |
71 |
or die ("Can't read any possible data sources: $?"); |
72 |
|
73 |
my $dsn = "DBI:Pg:dbname=$dbname"; |
74 |
$dsn .= ";host=$dbhost" if $dbhost; |
75 |
print "#$dsn\n" if $debug; |
76 |
my $dbh = DBI->connect ($dsn, $dbuser, $dbpass, {RaiseError =>1}); |
77 |
unless($dbh) { |
78 |
die("Database $dbname\@$dbhost (". $DBI::errstr .")\n"); |
79 |
} |
80 |
|
81 |
if ($configure) { |
82 |
print <<_EOM; |
83 |
graph_title Postgres database $dbname |
84 |
graph_args -l 0 --base 1024 |
85 |
graph_vlabel bytes |
86 |
graph_category Postgresql |
87 |
graph_info Size |
88 |
size.label Database size (bytes) |
89 |
size.info Database size |
90 |
size.type GAUGE |
91 |
size.draw AREA |
92 |
indexsize.label Index size (bytes) |
93 |
indexsize.info Index size |
94 |
indexsize.type GAUGE |
95 |
indexsize.draw STACK |
96 |
metasize.label Meta database size (bytes) |
97 |
metasize.info Meta database size |
98 |
metasize.type GAUGE |
99 |
metasize.draw STACK |
100 |
metaindexsize.label Meta index size (bytes) |
101 |
metaindexsize.info Meta index size |
102 |
metaindexsize.type GAUGE |
103 |
metaindexsize.draw STACK |
104 |
_EOM |
105 |
} else { |
106 |
my $database_pages = 0; |
107 |
my $database_indexes = 0; |
108 |
my $metadatabase_pages = 0; |
109 |
my $metadatabase_indexes = 0; |
110 |
my @names = $dbh->tables; |
111 |
|
112 |
# Find relfilenode and relpages from the given table |
113 |
my $q_ind = "SELECT relkind, relfilenode, relpages FROM pg_class |
114 |
WHERE relname = ? |
115 |
UNION |
116 |
SELECT relkind, relfilenode, relpages FROM pg_class |
117 |
WHERE relfilenode IN (SELECT indexrelid FROM pg_index |
118 |
WHERE indrelid IN (SELECT relfilenode FROM pg_class |
119 |
WHERE relname = ?))"; |
120 |
my $sth = $dbh->prepare ($q_ind) or die $dbh->errstr; |
121 |
|
122 |
# Iterate over the tables in the database |
123 |
foreach my $table (@names) { |
124 |
my $meta = 1; |
125 |
print "#TABLE: $table\n" if $debug; |
126 |
my $table_pages = 0; |
127 |
my $table_indexes = 0; |
128 |
my $metatable_pages = 0; |
129 |
my $metatable_indexes = 0; |
130 |
# "public" tables are the user data |
131 |
$meta = 0 if $table =~ /^public\./; |
132 |
$table =~ s/^.*\.//; |
133 |
|
134 |
# Call the query with $table twice for each side of the UNION |
135 |
$sth->execute ($table, $table) or die $dbh->errstr; |
136 |
while (my ($relkind, $relfilenode, $relpages) = $sth->fetchrow_array) { |
137 |
if ($relkind eq 'r') { |
138 |
$table_pages += $relpages if $meta == 0; |
139 |
$metatable_pages += $relpages if $meta == 1; |
140 |
} elsif ($relkind eq 'i') { |
141 |
$table_indexes += $relpages if $meta == 0; |
142 |
$metatable_indexes += $relpages if $meta == 1; |
143 |
} |
144 |
# Define the query |
145 |
my $q2 = "SELECT SUM(relpages) |
146 |
FROM pg_class |
147 |
WHERE relname IN (?, ?)"; |
148 |
my $sth2 = $dbh->prepare ($q2); |
149 |
$sth2->execute ("pg_toast_${relfilenode}", |
150 |
"pg_toast_${relfilenode}_index"); |
151 |
my $relpages = $sth2->fetchrow_array; |
152 |
if ($relkind eq 'r') { |
153 |
$table_pages += $relpages if $meta == 0; |
154 |
$metatable_pages += $relpages if $meta == 1; |
155 |
} elsif ($relkind eq 'i') { |
156 |
$table_indexes += $relpages if $meta == 0; |
157 |
$metatable_indexes += $relpages if $meta == 1; |
158 |
} |
159 |
print "#\tR:$relfilenode\tP:$table_pages\tI:$table_indexes\n" if $debug; |
160 |
} |
161 |
$database_pages += $table_pages; |
162 |
$database_indexes += $table_indexes; |
163 |
$metadatabase_pages += $metatable_pages; |
164 |
$metadatabase_indexes += $metatable_indexes; |
165 |
} |
166 |
$sth->finish; |
167 |
$dbh->disconnect; |
168 |
print "size\.value " . $database_pages * 8192 . "\n"; |
169 |
print "indexsize\.value " . $database_indexes * 8192 . "\n"; |
170 |
print "metasize\.value " . $metadatabase_pages * 8192 . "\n"; |
171 |
print "metaindexsize\.value " . $metadatabase_indexes * 8192 . "\n"; |
172 |
} |