/[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

Annotation of /postgres_space_

Parent Directory Parent Directory | Revision Log Revision Log


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

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     }

Properties

Name Value
svn:executable

  ViewVC Help
Powered by ViewVC 1.1.26