| 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 |
|
|
} |
Parent Directory
| 
Revision Log