postgres_queries3_

#!/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_db1-db2-db3
#
# 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";
}