120 lines
2.7 KiB
Perl
Executable File
120 lines
2.7 KiB
Perl
Executable File
#!/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";
|
|
}
|