Обсуждение: munin plugin for bloat

Поиск
Список
Период
Сортировка

munin plugin for bloat

От
Kevin Kempter
Дата:
Hi all;

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?

Thanks in advance...

Re: munin plugin for bloat

От
Dimitri Fontaine
Дата:
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)

Re: munin plugin for bloat

От
Cédric Villemain
Дата:
Le mercredi 07 octobre 2009 19:44:05, Kevin Kempter a écrit :
> Hi all;
>
> 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?

Munin 1.4 will be out soon (not really soon), it may help you doing such
things. And there is new class inside munin code to handle postgresql plugins.
(very simple and usefull)

But I wonder if it is really interesting :  you will have the most bloated
table reduce to the less bloated table after vacuuming, probably.

I prefer graph specific relations and just alert me if some relation is bloated
too much.


>
> Thanks in advance...
>

--
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org

Вложения