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