[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 | |
---|
| 65 | use strict; |
---|
| 66 | use DBI; |
---|
| 67 | use Data::Dumper; |
---|
| 68 | use vars qw ( $debug $suggest $configure $dbh ); |
---|
| 69 | |
---|
| 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'} || ''; |
---|
| 76 | |
---|
| 77 | if (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. |
---|
| 117 | my (undef, undef, undef, $dbname) = split (/_/, $0, 4); |
---|
| 118 | die "No dbname configured (did you make the proper symlink?)" unless $dbname; |
---|
| 119 | |
---|
| 120 | my @datasources = DBI->data_sources ('Pg') |
---|
| 121 | or die ("Can't read any possible data sources: $?"); |
---|
| 122 | |
---|
| 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}); |
---|
| 128 | unless($dbh) { |
---|
| 129 | die("Database $dbname\@$dbhost (". $DBI::errstr .")\n"); |
---|
| 130 | } |
---|
| 131 | |
---|
| 132 | if ($configure) { |
---|
| 133 | print <<EOF; |
---|
| 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 |
---|
| 142 | from_disk.min 0 |
---|
| 143 | from_disk.draw AREA |
---|
| 144 | from_memory.label Cached in memory |
---|
| 145 | from_memory.info Cached in memory |
---|
| 146 | from_memory.type DERIVE |
---|
| 147 | from_memory.min 0 |
---|
| 148 | from_memory.draw STACK |
---|
| 149 | EOF |
---|
| 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 | } |
---|