source: trunk/packages/xvm-munin-config/master/usr/share/xvm-munin-master-config/plugins/postgres_block_read_ @ 1761

Last change on this file since 1761 was 1761, checked in by broder, 16 years ago

Initial checkin of xvm-munin-config package

  • Property svn:executable set to *
File size: 5.5 KB
RevLine 
[1761]1#!/usr/bin/perl -w
2# -*- perl -*-
3
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.
7#
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.
17#
18# Copyright BjØrn Ruberg <bjorn@linpro.no> 2006
19#
20# Licenced under GPL v2.
21#
22# Usage:
23#
24#       Symlink into /etc/munin/plugins/ and add the monitored
25#       database to the filename. e.g.:
26#
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.
30#
31#       If required, give username, password and/or Postgresql server
32#       host through environment variables.
33#
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:
38#
39#           stats_start_collector = true
40#           stats_block_level = true
41#
42#
43# Parameters:
44#
45#       config   (required)
46#
47# Config variables:
48#
49#       dbhost     - Which database server to use. Defaults to
50#                    'localhost'.
51#       dbport     - Which port on the database server to connect to.
52#                    Defaults to '5432'.
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.
60#
61# Magic markers
62#%# family=auto
63#%# capabilities=autoconf suggest
64
65use strict;
66use DBI;
67use Data::Dumper;
68use vars qw ( $debug $suggest $configure $dbh );
69
70# Need these variables at an early stage to enable
71# autoconf and suggest
72my $dbhost = $ENV{'dbhost'} || ''; # Connect to localhost by default
73my $dbport = $ENV{'dbport'} || '';
74my $dbuser = $ENV{'dbuser'} || 'postgres';
75my $dbpass = $ENV{'dbpass'} || '';
76
77if (exists $ARGV[0]) {
78    if ($ARGV[0] eq 'autoconf') {
79        my $dbname = $ENV{'dbname'} || 'template1';
80        # Check for DBD::Pg
81        if (! eval "require DBD::Pg;") {
82            print "no (DBD::Pg not found)";
83            exit 1;
84        }
85        # Then we try to detect Postgres presence by connecting to
86        # 'template1'.
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);
91        if ($tempdbh) {
92            print "yes\n";
93            exit 0;
94        } else {
95            print "no (Can't connect to given host, please check environment settings)\n";
96            exit 1;
97        }
98    } elsif ($ARGV[0] eq 'debug') {
99        # Set debug flag
100        $debug = 1;
101    } elsif ($ARGV[0] eq 'config') {
102        # Set config flag
103        $configure = 1;
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/^.*=//;
109            print "$db\n";
110        }
111        exit 0;
112    }
113}
114
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.
117my (undef, undef, undef, $dbname) = split (/_/, $0, 4);
118die "No dbname configured (did you make the proper symlink?)" unless $dbname;
119
120my @datasources = DBI->data_sources ('Pg')
121    or die ("Can't read any possible data sources: $?");
122
123my $dsn = "DBI:Pg:dbname=$dbname";
124$dsn .= ";host=$dbhost" if $dbhost;
125$dsn .= ";port=$dbport" if $dbport;
126print "#$dsn\n" if $debug;
127my $dbh = DBI->connect ($dsn, $dbuser, $dbpass, {RaiseError =>1});
128unless($dbh) {
129    die("Database $dbname\@$dbhost (". $DBI::errstr .")\n");
130}
131
132if ($configure) {
133    print <<EOF;
134graph_title Postgres data reads from $dbname
135graph_args --base 1000
136graph_vlabel Blocks read per \${graph_period}
137graph_category Postgresql
138graph_info Shows number of blocks read from disk and from memory
139from_disk.label Read from disk
140from_disk.info Read from disk
141from_disk.type DERIVE
142from_disk.min 0
143from_disk.draw AREA
144from_memory.label Cached in memory
145from_memory.info Cached in memory
146from_memory.type DERIVE
147from_memory.min 0
148from_memory.draw STACK
149EOF
150} else {
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);
158    $sth->execute();
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";
164    }
165}
Note: See TracBrowser for help on using the repository browser.