/[munin-modules]/postgres_block_read_
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_block_read_

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: 5432 byte(s)
import munin PostgreSQL modules from Bjorn Ruberg

1 #!/usr/bin/perl
2
3 # Plugin to monitor Postgresql memory usage; gives number of blocks
4 # read from disk and from memory, showing how much of the database is
5 # served from Postgresql's memory buffer.
6 #
7 # PLEASE NOTE: This plugin may not present the whole truth - the truth
8 # may actually be even better than this plugin will show you! That is
9 # because Postgresql statistics only considers memory block reads from
10 # its own allocated memory. When Postgresql reads from disk, it may
11 # actually still be read from memory, but from the _kernel_'s
12 # memory. Summarily, your database server may run even better than
13 # this plugin will indicate. See
14 # http://www.postgresql.org/docs/7.4/interactive/monitoring-stats.html
15 # for a (short) description.
16 #
17 # Copyright Bjørn Ruberg <bjorn@linpro.no> 2006
18 #
19 # Licenced under GPL v2.
20 #
21 # Usage:
22 #
23 # Symlink into /etc/munin/plugins/ and add the monitored
24 # database to the filename. e.g.:
25 #
26 # ln -s /usr/share/munin/plugins/postgres_block_read_ \
27 # /etc/munin/plugins/postgres_block_read_SomeDatabase
28 # This should, however, be given through autoconf and suggest.
29 #
30 # If required, give username, password and/or Postgresql server
31 # host through environment variables.
32 #
33 # You must also activate Postgresql statistics. See
34 # http://www.postgresql.org/docs/7.4/interactive/monitoring-stats.html
35 # for how to enable this. Specifically, the following lines must
36 # exist in your postgresql.conf:
37 #
38 # stats_start_collector = true
39 # stats_block_level = true
40 #
41 #
42 # Parameters:
43 #
44 # config (required)
45 #
46 # Config variables:
47 #
48 # dbhost - Which database server to use. Defaults to
49 # 'localhost'.
50 # dbuser - A Postgresql user account with read permission to
51 # the given database. Defaults to
52 # 'postgres'. Anyway, Munin must be told which user
53 # this plugin should be run as.
54 # dbpass - The corresponding password, if
55 # applicable. Default to undef. Remember that
56 # pg_hba.conf must be configured accordingly.
57 #
58 # Magic markers
59 #%# family=auto
60 #%# capabilities=autoconf suggest
61
62 use strict;
63 use DBI;
64 use Data::Dumper;
65 use vars qw ( $debug $suggest $configure $dbh );
66
67 # Need these variables at an early stage to enable
68 # autoconf and suggest
69 my $dbhost = $ENV{'dbhost'} || ''; # Connect to localhost by default
70 my $dbname = $ENV{'dbname'} || 'template1';
71 my $dbuser = $ENV{'dbuser'} || 'postgres';
72 my $dbpass = $ENV{'dbpass'} || '';
73
74 if (exists $ARGV[0]) {
75 if ($ARGV[0] eq 'autoconf') {
76 # Check for DBD::Pg
77 if (! eval "require DBD::Pg;") {
78 print "no (DBD::Pg not found)";
79 exit 1;
80 }
81 # Then we try to detect Postgres presence by connecting to
82 # 'template1'.
83 my $dsn = "dbi:Pg:dbname=template1";
84 $dsn .= ";host=$dbhost" if $dbhost;
85 my $tempdbh = DBI->connect ($dsn, $dbuser, $dbpass);
86 if ($tempdbh) {
87 print "yes\n";
88 exit 0;
89 } else {
90 print "no (Can't connect to given host, please check environment settings)\n";
91 exit 1;
92 }
93 } elsif ($ARGV[0] eq 'debug') {
94 # Set debug flag
95 $debug = 1;
96 } elsif ($ARGV[0] eq 'config') {
97 # Set config flag
98 $configure = 1;
99 } elsif ($ARGV[0] eq 'suggest') {
100 # doesn't always work
101 my @datasources = DBI->data_sources ('Pg');
102 foreach my $dsn (grep !/\=template\d$/, @datasources) {
103 (my $db = $dsn) =~ s/^.*=//;
104 print "$db\n";
105 }
106 exit 0;
107 }
108 }
109
110 # Must do this here, after checking for autoconf/suggest/etc, because the
111 # plugin must be able to run before it is linked to the databases.
112 my (undef, undef, undef, $dbname) = split (/_/, $0, 4);
113 die "No dbname configured (did you make the proper symlink?)" unless $dbname;
114
115 my @datasources = DBI->data_sources ('Pg')
116 or die ("Can't read any possible data sources: $?");
117
118 my $dsn = "DBI:Pg:dbname=$dbname";
119 $dsn .= ";host=$dbhost" if $dbhost;
120 print "#$dsn\n" if $debug;
121 my $dbh = DBI->connect ($dsn, $dbuser, $dbpass, {RaiseError =>1});
122 unless($dbh) {
123 die("Database $dbname\@$dbhost (". $DBI::errstr .")\n");
124 }
125
126 if ($configure) {
127 print <<EOF;
128 graph_title Postgres data reads from $dbname
129 graph_args --base 1000
130 graph_vlabel Blocks read per \${graph_period}
131 graph_category Postgresql
132 graph_info Shows number of blocks read from disk and from memory
133 from_disk.label Read from disk
134 from_disk.info Read from disk
135 from_disk.type DERIVE
136 from_disk.min 0
137 from_disk.draw AREA
138 from_memory.label Cached in memory
139 from_memory.info Cached in memory
140 from_memory.type DERIVE
141 from_memory.min 0
142 from_memory.draw STACK
143 EOF
144 } else {
145 my $sql = "SELECT (SUM (heap_blks_read) + SUM (idx_blks_read) + ";
146 $sql .= "SUM (toast_blks_read) + SUM (tidx_blks_read)) AS disk, ";
147 $sql .= "(SUM (heap_blks_hit) +SUM (idx_blks_hit) + ";
148 $sql .= "SUM (toast_blks_hit) + SUM (tidx_blks_hit)) AS mem ";
149 $sql .= "from pg_statio_user_tables ";
150 print "# $sql\n" if $debug;
151 my $sth = $dbh->prepare ($sql);
152 $sth->execute();
153 if ($sth->rows > 0) {
154 printf ("# Rows: %d\n", $sth->rows) if $debug;
155 my ($disk, $mem) = $sth->fetchrow_array();
156 print "from_disk.value $disk\n";
157 print "from_memory.value $mem\n";
158 }
159 }

Properties

Name Value
svn:executable

  ViewVC Help
Powered by ViewVC 1.1.26