Kevin Kempter <kevink@consistentstate.com> writes:
> I have an sql query I like to run that shows the top 10 bloated tables. I'm
> wanting to turn it into a munin graph however the issue is that the list of
> tables (the top 10) are not constant which seems to indicate that I need
> something more dynamic than what's available as a standard munin plugin.
>
> Has anyone out there already tackled this?
Please see in attachement an example of a dynamic legend munin graph
that we use here. I think it's available somewhere online, but attaching
it in this mail was quicker ;)
In short it's supposed to work out-of-the-box, but I've had strange bugs
with that that were uneasy and hacky to fix. Try this, try that, oh that
works. I may have overlooked something there though.
Regards,
--
dim
#!/bin/bash
# Magic markers - optional - used by installation scripts and
# munin-node-configure:
#
#%# family=auto
#%# capabilities=autoconf suggest
#
prog=$(basename $0)
dbname=$(echo $prog | cut -d_ -f 3- | cut -d. -f1)
queue=$(echo $prog | cut -d_ -f 2- | cut -d. -f2)
psql="psql -At"
[ ! -z "$dbhost" ] && psql="$psql -h $dbhost"
[ ! -z "$dbport" ] && psql="$psql -p $dbport"
[ ! -z "$dbuser" ] && psql="$psql -U postgres"
case "$1" in
config)
echo "graph_category pgq"
echo "graph_title PgQ $dbname $queue consumer lag"
echo "graph_vlabel seconds"
echo "graph_args --lower-limit 1 --base 1000 --logarithmic"
echo "graph_info Shows Pgq Lag "
select="SELECT consumer_name FROM pgq.get_consumer_info('$queue');"
while read consumer
do
consumer=$(echo $consumer | tr .- __)
echo ${consumer}_lag.label $consumer lag
echo ${consumer}_lag.type GAUGE
echo ${consumer}_lag.draw LINE
echo ${consumer}_last_seen.label $consumer last seen
echo ${consumer}_last_seen.type GAUGE
echo ${consumer}_last_seen.draw LINE
done < <($psql $dbname -c "$select" 2>/dev/null)
exit 0
;;
autoconf)
sql="SELECT DISTINCT(queue_name) FROM pgq.get_consumer_info();"
working=false
for db in $($psql -Alt 2>/dev/null | cut -d\| -f1)
do
for queue in `$psql $db -c "$sql" 2>/dev/null`
do
working=true
done
done
if [ $working = 'true' ]
then
echo yes
exit 0
else
echo no
exit 1
fi
;;
suggest)
sql="SELECT DISTINCT(queue_name) FROM pgq.get_consumer_info();"
coderetour=1
for db in $($psql -Alt 2>/dev/null | cut -d\| -f1)
do
for queue in `$psql $db -c "$sql" 2>/dev/null`
do
echo $db.$queue
coderetour=0
done
done
exit $coderetour
;;
esac
sql="SELECT consumer_name, EXTRACT(epoch from lag) AS lag, \
EXTRACT(epoch FROM last_seen) AS last_seen \
FROM pgq.get_consumer_info('$queue');"
while read consumer lag last_seen
do
consumer=$(echo $consumer | tr .- __)
echo ${consumer}_lag.value $lag
echo ${consumer}_last_seen.value $last_seen
done < <($psql $dbname -F ' ' -c "$sql" 2>/dev/null)