PostgreSQL Monitoring With Munin

Munin Resources

General Munin on Linux information:

RHEL specific guides:

Debian/Ubuntu specific guides:

Note that reading the guides even for the distribution you’re not using might
be useful, as each of these gives a slightly different angle on things like
securing the installation.

RHEL Installation

These instructions aim at RHEL5 and RHEL6; there isn’t much difference
between the two as far as Munin is considered.

Package Installation

Munin is a Perl program and requires the following library to speak to
PostgreSQL:

yum install perl-DBD-Pg

Munin is not packaged by default in RHEL5. While it’s possible to build
it from source, the easiest way to install is to use the Fedora project’s
EPEL repository. Guidelines for EPEL are at http://fedoraproject.org/wiki/EPEL

You can follow those instructions to enable EPEL, which will normally lead to
something like this (examples for RHEL5 and RHEL6):

rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-5.noarch.rpm
yum install http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-5.noarch.rpm

Whether you download the file and install manually with RPM, or directly
install

Once EPEL is working, the Munin installation is done with:

sudo yum install munin munin-node

The munin package is the server side, while munin-node is the client
that gets installed on every system to be monitored.

There may be some additional Perl related depedencies also pulled in by this
installation. There is also a munin-java-plugins package which may be
useful if you want to monitor software such as Tomcat with Munin.

You may want to figure out which packages from EPEL are required to install
Munin, and restrict the use of EPEL to only pull those in. The risk if this
isn’t done is that you may see installation of other software with yum that
isn’t actually available in the official RHEL repos. And that can pull in
much larger portions of EPEL than you may intend. You can expect that several
Perl modules not bundled with RHEL will be installed from EPEL at a minimum.

Startup

To start munin now and make it start by default after reboot:

chkconfig --levels 235 munin-node on
/etc/init.d/munin-node start

Which plug-ins are available to you is adjusted using:

/usr/sbin/munin-node-configure

The Postgres plug-in is already part of the default configuration, so you
may not need to make any changes here.

General munin configuration

The main configuration files are /etc/munin/munin-node.conf and
a series under /etc/munin/plugin-conf.d

The default config includes a "host tree" to monitor localhost and
to put the resulting files into a web server directory. The server
data collection side is configured using /etc/munin/munin-node.conf
and at a minimum, you’ll need to put the appropriate host descriptions
into there. Individual nodes may configure themselves correctly to
report information, presuming DNS is correct for them. You can check
if your munin node setup is correct and what name it is reporting
using telnet into its port:

telnet localhost 4949

you’ll need to put your host information into the munin-node.conf
file.

Initial Web Page Creation

Munin server-side installation adds an entry into /etc/cron.d/munin to run the collection:

*/5 * * * *     munin test -x /usr/bin/munin-cron && /usr/bin/munin-cron

Once that runs for the first time, it will create the necessary directory
tree to expose this to a standard RHEL Apache directory tree in
/var/www/html/munin On RHEL5 you should
get an e-mail such as this documenting this fact, that doesn’t appear to
happen on RHEL6:

Subject: Cron <munin@pyramid> test -x /usr/bin/munin-cron && /usr/bin/munin-cron

mkdir /var/lib/munin/localhost
mkdir /var/www/html/munin/localhost
mkdir /var/www/html/munin/localhost/localhost
mkdir /var/www/html/munin/localhost/localhost/diskstats_latency
mkdir /var/www/html/munin/localhost/localhost/diskstats_iops
mkdir /var/www/html/munin/localhost/localhost/diskstats_throughput
mkdir /var/www/html/munin/localhost/localhost/diskstats_utilization

Once those directories exist, there will now be a page at
http://localhost/munin with the extensive set of built-in monitoring
(CPU, disk, etc.) populated.

Bugs in munin disk scripts, RHEL6

There are a few problems with munin on RHEL6. The main visible
symptom is that all of the graphs related to disk activity (but not
capacity) will not be populated; all values will read "NaN" instead.
There are three bugs here:

The two easy ones are resolved just by fixing some permissions
and creating a missing file:

chmod g+w /var/lib/munin/plugin-state/
touch /var/lib/munin/plugin-state/yum.state
chown munin:munin /var/lib/munin/plugin-state/yum.state

If you have selinux running and enforcing, you’ll need to update its
policy to work around the third bug. The munin process isn’t allowed
to look at /var/lib by selinux, which causes all sorts of things to fail.
You also need to allow munin services to access the PostgreSQL server
socket file in /tmp

To fix all that, create a muninpol.te file with these lines:

module muninpol 1.3;

require {
        type tmp_t;
        type munin_t;
        type munin_disk_plugin_t;
        type munin_services_plugin_t;
        type initrc_t;
        type var_lib_t;
        class process { siginh noatsecure rlimitinh };
        class dir search;
        class sock_file write;
        class unix_stream_socket connectto;
}

#============= munin_services_plugin_t ==============
allow munin_services_plugin_t initrc_t:unix_stream_socket connectto;
allow munin_services_plugin_t tmp_t:sock_file write;

#============= munin_disk_plugin_t ==============
allow munin_disk_plugin_t var_lib_t:dir search;

#============= munin_t ==============
allow munin_t munin_disk_plugin_t:process { siginh rlimitinh noatsecure };
allow munin_t munin_services_plugin_t:process { siginh rlimitinh noatsecure };

And then you can build/install it like this:

checkmodule -M -m -o muninpol.mod muninpol.te
semodule_package -o muninpol.pp -m muninpol.mod
rm muninpol.mod
semodule -i muninpol.pp

There will still be errors from the postfix_mailvolume plugin; that
should just be disabled for a database server though. It’s also helpful
to disable the SELinux vector cache and the entropy graph as ones that
aren’t very useful on a database server. The service has to be restarted
after changes to plug-ins:

rm /etc/munin/plugins/postfix_mailvolume /etc/munin/plugins/postfix_mailqueue
rm /etc/munin/plugins/selinux_avcstat
rm /etc/munin/plugins/entropy
service munin-node restart

After fixing all that, if you still have a problem you should look for
additional errors in /var/log/munin/munin-node.log You might
want to clear that out after fixing all the above errors, so that you’ll
only see reports from problems after these are resolved:

cp /dev/null /var/log/munin/munin-node.log

Plugin cleanup on RHEL5

There are several munin plugins that are enabled by default, but not
likely to be interesting on a database server. The likely candidates
can be removed like this, with a restart of the node service required
after any change:

rm -f /etc/munin/plugins/ntp_*
rm -f /etc/munin/plugins/entropy
rm -f /etc/munin/plugins/sendmail_mail*
service munin-node restart

PostgreSQL Plugin Setup

To configure the PostgreSQL plugin to speak to a database, edit

/etc/plugin-conf.d/munin-node (Debian) or /etc/munin/plugin-conf.d/munin-node
(RHEL) and add something like this:

[postgres_*]
user postgres
env.PGUSER postgres
env.PGPORT 5432
#env.PGPASSWORD password

You may want to put this into its own file in the plugin-conf.d directory
instead.

You must restart munin after changing this:

/sbin/service munin-node restart

Additional information about the PostgreSQL plug-in’s options are included
in its man pages, which can view online at
http://manpages.ubuntu.com/manpages/lucid/man3/Munin::Plugin::Pgsql.3pm.html

Recommended practice here is to setup a PostgreSQL superuser account
for munin, with very restricted rights for who can use it. This is covered
below.

Selecting PostgreSQL components to monitor

You can see a list of the available plug-ins for Munin

/usr/sbin/munin-node-configure --suggest

This will tells you the ones that it suspects will work on your system,
after asking the plug-in itself for information. You should see something
like this:

# postgres_bgwriter          | no   | yes
# postgres_cache_            | no   | yes (+ALL +pgbench)
# postgres_checkpoints       | no   | yes
# postgres_connections_      | no   | yes (+ALL +pgbench)
# postgres_connections_db    | no   | yes
# postgres_locks_            | no   | yes (+ALL +pgbench)
# postgres_querylength_      | no   | yes (+ALL +pgbench)
# postgres_scans_            | no   | no
# postgres_size_             | no   | yes (+ALL +pgbench)
# postgres_transactions_     | no   | yes (+ALL +pgbench)
# postgres_tuples_           | no   | no
# postgres_users             | no   | yes
# postgres_xlog              | no   | yes

Where pgbench is the only database on this particular system. Some
plug-ins such ag postgres_bgwriter run against every database on the system.
Others such as postgres_cache can either be configured to run against a
single database, or you can ask munin to link to all databases that exist
when you set it up.

If you see the following errors:

# postgres_scans_:
#     Non-zero exit during autoconf (2)
# postgres_tuples_:
#     Non-zero exit during autoconf (2)

This is due to a bug in the PostgreSQL plug-in for munin that you’ll have
to install a patch to fix; this is covered in more detail below.

In order to install these plug-ins, munin uses symbolic links between the
possible choices and the active configuration. You can get it to suggest
syntax you can use like this:

/usr/sbin/munin-node-configure --shell

This produces the following on the above server:

ln -s '/usr/share/munin/plugins/postgres_bgwriter' '/etc/munin/plugins/postgres_bgwriter'
ln -s '/usr/share/munin/plugins/postgres_cache_' '/etc/munin/plugins/postgres_cache_ALL'
ln -s '/usr/share/munin/plugins/postgres_cache_' '/etc/munin/plugins/postgres_cache_pgbench'
ln -s '/usr/share/munin/plugins/postgres_checkpoints' '/etc/munin/plugins/postgres_checkpoints'
ln -s '/usr/share/munin/plugins/postgres_connections_' '/etc/munin/plugins/postgres_connections_ALL'
ln -s '/usr/share/munin/plugins/postgres_connections_' '/etc/munin/plugins/postgres_connections_pgbench'
ln -s '/usr/share/munin/plugins/postgres_connections_db' '/etc/munin/plugins/postgres_connections_db'
ln -s '/usr/share/munin/plugins/postgres_locks_' '/etc/munin/plugins/postgres_locks_ALL'
ln -s '/usr/share/munin/plugins/postgres_locks_' '/etc/munin/plugins/postgres_locks_pgbench'
ln -s '/usr/share/munin/plugins/postgres_querylength_' '/etc/munin/plugins/postgres_querylength_ALL'
ln -s '/usr/share/munin/plugins/postgres_querylength_' '/etc/munin/plugins/postgres_querylength_pgbench'
ln -s '/usr/share/munin/plugins/postgres_size_' '/etc/munin/plugins/postgres_size_ALL'
ln -s '/usr/share/munin/plugins/postgres_size_' '/etc/munin/plugins/postgres_size_pgbench'
ln -s '/usr/share/munin/plugins/postgres_transactions_' '/etc/munin/plugins/postgres_transactions_ALL'
ln -s '/usr/share/munin/plugins/postgres_transactions_' '/etc/munin/plugins/postgres_transactions_pgbench'
ln -s '/usr/share/munin/plugins/postgres_users' '/etc/munin/plugins/postgres_users'
ln -s '/usr/share/munin/plugins/postgres_xlog' '/etc/munin/plugins/postgres_xlog'

You can now cut and paste the subset of these you do want to monitor into
a shell session, and they will then be active as of the next server
collection run.

Bugs in Munin/PostgreSQL scripts, RHEL5

The main library all of the munin’s communication happens through is
/usr/lib/perl5/vendor_perl/5.8.8/Munin/Plugin/Pgsql.pm

Some versions of this distributed by EPEL do not work correctly. If you
get a corrected Pgsql.pm from 2ndQuadrant, you can overwrite this one with that
copy.

After fixing this, you should see the previous bad components available:

# /usr/sbin/munin-node-configure --shell
ln -s '/usr/share/munin/plugins/postgres_scans_' '/etc/munin/plugins/postgres_scans_pgbench'
ln -s '/usr/share/munin/plugins/postgres_tuples_' '/etc/munin/plugins/postgres_tuples_pgbench'

Note that there is no all target for these that applies to every database.

You might activate, installed, and restart as usual like this:

ln -s '/usr/share/munin/plugins/postgres_scans_' '/etc/munin/plugins/postgres_scans_pgbench'
ln -s '/usr/share/munin/plugins/postgres_tuples_' '/etc/munin/plugins/postgres_tuples_pgbench'
service munin-node restart

Setup Apache authentication on the server running Munin

Munin for RHEL comes configured with an Apache .htaccess file that
limits who can see its data that looks like this:

AuthUserFile /etc/munin/munin-htpasswd
AuthName "Munin"
AuthType Basic
require valid-user

Before this will work you may need an Apache configuration change, as
documented in the .htaccess file itself:

# For the .htaccess file option to work the munin www directory
# (/var/www/html/munin) must have "AllowOverride all" or something close
# to that set.

You will then need to create the munin-htpassword file to get access to
this subdirectory. This is done with htpasswd. This example creates
the file with one user and than adds a second:

htpasswd -c /etc/munin/munin-htpasswd user1
htpasswd  /etc/munin/munin-htpasswd user2

Be careful not to use -c except the first time, or you will overwrite
the entire configuration file without any warning.

Adding new servers to be monitored

Additional servers to monitor are listed in /etc/munin/munin.conf using
this format:

[myserver]
    address 127.0.0.1
    use_node_name yes
[db.mydomain.com]
    address 192.168.1.6
    use_node_name yes

Here myserver is monitoring the node the munin server is actually running
on, usually a good idea, while data is also being pulled from another
client.

munin pulls data from clients by connecting directly to the node on port 4949.
You may need to create appropriate firewall rules in order for the server to
reach this port on each client. You can test that using telnet, as shown
in the "General munin configuration" section above.

Restricting access to the Munin client

In addition to the firewall setup, you can limit munin responding to server
requests altogether. In the /etc/munin/munin-node.conf section for this node
customize it like this:

host_name db.mydomain.com
# 192.168.1.3
allow ^192\.168\.1\.3$

And restart munin as usual. Afterwards it should only listen to servers
asking to pull data listed with an allow statement.

Creating a database user for munin monitoring

Munin needs to run as a superuser in order to access all of the data
it monitors. For example, the counts of what clients are doing can
only be computed if you have the superuser-only ability to see that.

You might create such a role using:

psql -c "CREATE ROLE munin WITH SUPERUSER LOGIN;"

Then edit the PostgreSQL configuration file by default located
at /var/lib/pgsql/<version>/data/pg_hba.conf to add a line like this:

local   all         munin                             ident

This will only allow access to the database as the munin user to happen
from the local system running the munin client when logged in as the
munin user, which only root is allowed to do.

Afterwards you will need to signal the database to note this change:

/sbin/service postgresql reload

Debian Installation

Server installation

Munin packages are included with Debian Lenny, and installed like this:

sudo apt-get install munin munin-node munin-plugins-extra libdbd-pg-perl

A guide that covers installation of the server side of things is available
at http://www.howtoforge.com/monitoring-multiple-systems-with-munin-debian-etch

You will likely need apache to serve the Munin files to other systems:

apt-get install apache2 apache2-doc apache2-utils

Its configuration files will be /etc/apache2/

Munin output pages are stored by default in /var/cache/munin/www/ and you can
access them with http://localhost/munin/

The installation will be setup by default with an /etc/munin/munin.conf file
monitoring localhost:

[localhost.localdomain]
    address 127.0.0.1
    use_node_name yes

If you make any munin configuration changes, restart the munin-node software
to make sure they take:

/etc/init.d/munin-node restart

Once you see files created by munin appear in the web server directory
/var/cache/munin/www/ you may need to setup Apache to allow access to that
directory. Munin installs an Apache configuration file at
/etc/apache2/conf.d/munin but it only allows access to that data from
localhost. Edit the file so that it looks like this (saving the original
configuration in case you want to lock this down later):

#Allow from localhost 127.0.0.0/8 ::1
Allow from all

Problems with the site can be seen by looking at the log files in
/var/log/apache2/ and you need to change apache’s configuration you may need to
restart it before they take effect:

/etc/init.d/apache2 restart

Node installation

The munin client is installed like this:

sudo apt-get install munin-node

This adds a munin user and group that monitoring is done as. You will also
need libdbd-pg-perl on client nodes that monitor PostgreSQL.

Restricting access

Munin needs to run as a database superuser, and it will need an account for
that:

psql -c "CREATE ROLE munin WITH SUPERUSER LOGIN;"

The ability access the database user as the munin superuser should be
restricted to the minimum possible. On Debian you could edit
/etc/postgresql/8.4/main/pg_hba.conf and only allow ident access:

local   all         munin                             ident

Followed by getting the database to reload its configuration:

pg_ctlcluster 8.4 main reload

It’s possible to install munin more securely, without using a superuser
account, using security definer functions. See the PostgreSQL
documentation for CREATE FUNCTION for more information. All of the
system views Munin uses, such as the various pg_stat* ones and
pg_stat_activity, will need to be wrapped into a schema where they
override the system ones.

Enable postgresql monitoring

Edit /etc/munin/plugin-conf.d/munin-node
On Debian systems a postgres section already exists that assumes it can
connect as the postgres user. If you’re using another database user, update it
to reference the monitoring user instead:

[postgres_*]
user munin
env.PGUSER munin
env.PGPORT 5432

Then restart:

/etc/init.d/munin-node restart

After this you should see munin autoconf data appear when doing:

munin-node-configure --suggest

Unlike RHEL, all of the PostgreSQL plug-ins themselves are turned on by default,
so as soon as you get the right login information their data should appear.

Munin sample firewall rules

iptables -A INPUT -s 71.179.240.8 -p tcp -m tcp --dport 4949 -j ACCEPT
iptables -A INPUT -s 127.0.0.1 -p tcp -m tcp --dport 4949 -j ACCEPT

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>