Munin contributed plugins

#!/usr/bin/env perl

# postgres_queries3: see stats on number of rows 

# read, inserted, updated and deleted on a per table basis

#

# Author:

#    Samuel Smith leon36 <snail> gmail <dot> com

#

# Created:

#    20140701

#

# Usage:

#    Place in /etc/munin/plugins/ (or link it there using ln -s)

#    Place table names after '_' and delimit with '-'

#    EX: postgres_queries3_table1-table2-table3

#

# Parameters:

#    config   (required)

#    conf:

#    [postgres_*]

#    user postgres

#

#

# General info:

#    Require permission for database access and read (no writes are processed).

#    Recommended user is PostgreSQL database owner.

#    On debian systems install libipc-run3-perl

#

# Log info:

# 20140701 - Initial 

# 20140924 - 

#	-ignore internal pg tables

#	-missing stuff in config	

use strict;

use IPC::Run3 qw( run3 );

my %values;

my $query = \<<EOF;

select	

	'sel_seq.value '	|| SUM(seq_scan)	|| E'\n' ||

	'sel_seq_rows.value '	|| SUM(seq_tup_read)	|| E'\n' ||

	'sel_idx.value '	|| SUM(idx_scan)	|| E'\n' ||

	'sel_idx_rows.value '	|| SUM(idx_tup_fetch)	|| E'\n' ||

	'inserts.value '	|| SUM(n_tup_ins)	|| E'\n' ||

	'updates.value '	|| SUM(n_tup_upd)	|| E'\n' ||

	'deletes.value '	|| SUM(n_tup_del)

		from pg_stat_all_tables where relname not like 'pg_%';

EOF

$0 =~ /postgres_queries3_(.*)/;

my @dbs = split(/-/,$1);

if( defined $ARGV[0] and $ARGV[0] eq "config" ){

print qq/graph_title Postgres All Queries

graph_args --base 1000 -l 0 -r

graph_vlabel Queries per \${graph_period}

graph_category db 

graph_info Shows number of select, insert, update and delete queries

sel_seq.label s_selects

sel_seq.info Sequential selects on all tables

sel_seq.type DERIVE

sel_seq.min 0

sel_seq.max 100

sel_seq_rows.label s_select rows

sel_seq_rows.info Rows returned from sequential selects

sel_seq_rows.type DERIVE

sel_seq_rows.min 0

sel_seq_rows.max 100

sel_idx.label i_selects

sel_idx.info Sequential selects on all indexes

sel_idx.type DERIVE

sel_idx.min 0

sel_idx.max 100

sel_idx_rows.label i_select rows

sel_idx_rows.info Rows returned form index selects

sel_idx_rows.type DERIVE

sel_idx_rows.min 0

sel_idx_rows.min 100

inserts.label inserts

inserts.info Rows inserted on all tables

inserts.type DERIVE

inserts.min 0

inserts.max 100

updates.label updates

updates.info Rows updated on all tables

updates.type DERIVE

updates.min 0

updates.max 100

deletes.label deletes

deletes.info Rows deleted from all tables

deletes.type DERIVE

deletes.min 0

deletes.max 100

/;

exit;

}

foreach my $db (@dbs){

	my @out;

	run3( "psql -At $db", $query, \@out);

	foreach my $line (@out){

		my($key, $value) = split(/ /, $line);

		$values{$key} += $value;

	}

}

foreach my $key (keys %values){

	print "$key $values{$key}\n";

}