/[munin-modules]/postgres_space_
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /postgres_space_

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1 - (show annotations)
Tue Jun 12 11:02:00 2007 UTC (16 years, 9 months ago) by dpavlin
File size: 6151 byte(s)
import munin PostgreSQL modules from Bjorn Ruberg

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 }

Properties

Name Value
svn:executable

  ViewVC Help
Powered by ViewVC 1.1.26