1 |
dpavlin |
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 |
|
|
} |