postgres_locks_

#!/usr/bin/perl
#
# Copyright (C) 2009 Magnus Hagander, Redpill Linpro AB
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; version 2 dated June,
# 1991.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
# 02110-1301 USA.

=head1 NAME

postgres_locks_ - Plugin to monitor PostgreSQL locks.

=head1 CONFIGURATION

Configuration is done through libpq environment variables, for example
PGUSER, PGDATABASE, etc. For more information, see L<Munin::Plugin::Pgsql>.

To monitor a specific database, link to postgres_locks_<databasename>.
To monitor all databases, link to postgres_locks_ALL.
To monitor several instances, link to postgres_<tag>_locks_<databasename|ALL>
The <tag> can be what you want but without "_". It allows you to define several
database configuration.

Example :
  [postgres_pg91_*]
  env.PGPORT 5432
  [postgres_pg92_*]
  env.PGPORT 5432

=head1 SEE ALSO

L<Munin::Plugin::Pgsql>

=head1 MAGIC MARKERS

 #%# family=auto
 #%# capabilities=autoconf suggest

=head1 AUTHOR

Magnus Hagander <magnus@hagander.net>, Redpill Linpro AB

=head1 COPYRIGHT/License.

Copyright (c) 2009 Magnus Hagander, Redpill Linpro AB

All rights reserved. This program is free software; you can
redistribute it and/or modify it under the terms of the GNU General
Public License as published by the Free Software Foundation; version 2
dated June, 1991.

=cut

use strict;
use warnings;

use Munin::Plugin::Pgsql;

my $pg = Munin::Plugin::Pgsql->new(
    basename  => 'postgres_[^_]*_?locks_',
    title     => 'PostgreSQL locks',
    info      => 'PostgreSQL locks',
    vlabel    => 'Locks',
    basequery => [
        "SELECT tmp.mode,COALESCE(count,0) FROM
          (VALUES ('accesssharelock'),('rowsharelock'),('rowexclusivelock'),('shareupdateexclusivelock'),('sharelock'),('sharerowexclusivelock'),('exclusivelock'),('accessexclusivelock')) AS tmp(mode)
         LEFT JOIN
          (SELECT lower(mode) AS mode,count(*) AS count
           FROM pg_locks WHERE database IS NOT NULL %%FILTER%%
           GROUP BY lower(mode)
          ) AS tmp2
         ON tmp.mode=tmp2.mode ORDER BY 1", [
            8.1,
            "SELECT tmp.mode,COALESCE(count,0) FROM
             (SELECT 'accesssharelock' AS mode UNION ALL SELECT 'rowsharelock'
              UNION ALL SELECT 'rowexclusivelock' UNION ALL SELECT 'shareupdateexclusivelock'
              UNION ALL SELECT 'sharelock' UNION ALL SELECT 'sharerowexclusivelock'
              UNION ALL SELECT 'exclusivelock' UNION ALL SELECT 'accessexclusivelock'
            ) AS tmp
            LEFT JOIN
             (SELECT lower(mode) AS mode,count(*) AS count
              FROM pg_locks WHERE database IS NOT NULL %%FILTER%%
              GROUP BY lower(mode)
             ) AS tmp2
           ON tmp.mode=tmp2.mode ORDER BY 1"
        ],
    ],
    wildcardfilter => "AND database=(SELECT oid FROM pg_database WHERE datname=?)",
    configquery    => [
        "VALUES
         ('accesssharelock','AccessShareLock','Used by read only queries'),
         ('rowsharelock','RowShareLock','Used by SELECT FOR SHARE and SELECT FOR UPDATE queries'),
         ('rowexclusivelock','RowExclusiveLock','Used by UPDATE, DELETE and INSERT queries'),
         ('shareupdateexclusivelock','ShareUpdateExclusiveLock','Used by VACUUM, ANALYZE and CREATE INDEX CONCURRENTLY queries'),
         ('sharelock','ShareLock','Used by CREATE INDEX queries'),
         ('sharerowexclusivelock','ShareRowExclusiveLock','Only issued explicitly from applications'),
         ('exclusivelock','ExclusiveLock','Infrequently issued on system tables, or by applications'),
         ('accessexclusivelock','AccessExclusiveLock','Used by ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER and VACUUM FULL queries')", [
            8.1,
            "SELECT 'accesssharelock','AccessShareLock','Used by read only queries' UNION ALL
            SELECT 'rowsharelock','RowShareLock','Used by SELECT FOR SHARE and SELECT FOR UPDATE queries' UNION ALL
            SELECT 'rowexclusivelock','RowExclusiveLock','Used by UPDATE, DELETE and INSERT queries' UNION ALL
            SELECT 'shareupdateexclusivelock','ShareUpdateExclusiveLock','Used by VACUUM, ANALYZE and CREATE INDEX CONCURRENTLY queries' UNION ALL
            SELECT 'sharelock','ShareLock','Used by CREATE INDEX queries' UNION ALL
            SELECT 'sharerowexclusivelock','ShareRowExclusiveLock','Only issued explicitly from applications' UNION ALL
            SELECT 'exclusivelock','ExclusiveLock','Infrequently issued on system tables, or by applications' UNION ALL
            SELECT 'accessexclusivelock','AccessExclusiveLock','Used by ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER and VACUUM FULL queries'"
        ]
    ],
    suggestquery => "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate AND NOT datname='postgres' UNION ALL SELECT 'ALL' ORDER BY 1 LIMIT 10",
    graphdraw    => 'AREA',
    stack        => 1,
);

$pg->Process();
exit(0);