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...

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--=-=-=

Kevin Kempter <kevink [at] 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


--=-=-=
Content-Type: text/x-sh
Content-Disposition: inline; filename=pg_queue_
Content-Description: pgq munin plugin

#!/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)

--=-=-=
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--=-=-=--

--nextPart3280851.ZM7sPWD77p
Content-Type: Text/Plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Le mercredi 07 octobre 2009 19:44:05, Kevin Kempter a =E9crit :
> 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 plugi=
ns.
(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 blo=
ated
too much.


>
> Thanks in advance...
>

=2D-
C=E9dric Villemain
Administrateur de Base de Donn=E9es
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org

--nextPart3280851.ZM7sPWD77p
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)

iEYEABECAAYFAkrNvMcACgkQo/dppWjpEvxYkwCeOzTxLpeuzsXv1oAZPMnB 6LDS
qgcAnRufPo1OLlMjQbTbMkbmPtJjfp1U
=fdW5
-----END PGP SIGNATURE-----

--nextPart3280851.ZM7sPWD77p--