PGQ Munin Plugin

From PostgreSQL wiki
Jump to navigationJump to search

Indicators

PGQ provides lag and last_seen indicators.

lag
age of next event to be processed by given consumer
last_seen
age of the last time a consumer asked for work (next_batch())

Code

The wikimedia software isn't setup to accept text file uploads, so here's the content of it:

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

Installation

Place the file in /usr/share/munin/plugins or some documented place then run

/usr/sbin/munin-node-configure --shell

The command will give you the needed ln -s commands to run in order to have the plugin installed, depending on your configuration. You want to run this on the server hosting a PostgreSQL database hosting PGQ queue.