Re: munin plugin for bloat - Mailing list pgsql-admin

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)