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 |
} |