So I wrote a quick and dirt mrtg plugin that graphs xacts/sec and I'm
wondering if it would be worthwhile to submit it as a contrib project (or
maybe I should submit to mrtg)? If so, where do I submit it?
Here it is for anyone who would find it useful and/or has ideas on how to
make it better:
---mrtg-pg-xact.sh
#!/bin/sh
PSQL=/usr/local/pgsql/bin/psql
ADMINDB=template1
PGSERVER=$1
PGUSER=postgres
XACT=`$PSQL --tuples-only -c 'select sum(xact_commit) + sum(xact_rollback) as
transactions from pg_stat_database;' -h $PGSERVER -U $PGUSER $ADMINDB`
echo $XACT
echo 0
echo `su -c "ssh $PGSERVER uptime" postgres`
echo $PGSERVER
---mrtg-pg-xact.sh
and here's the readme:
---mrtg-pg-xact.readme
This mrtg plugin queries the committed and rolledback transactions from
the DB and outputs it in a format that MRTG can graph.
You'll likely want to change these variables.
PSQL=/usr/local/pgsql/bin/psql
ADMINDB=template1
PGUSER=postgres
Also, you'll note this line which just grabs the uptime of the
db server:
echo `su -c "ssh $PGSERVER uptime" postgres`
it requires that the postgres user be able to ssh to the server with
no password (i.e. using key based authentication).
If you don't care about that or don't feel comfortable allowing your
postgres user to ssh into your DB server with no password, you might
want to just set that to:
echo
Here's an example mrtg config excerpt:
Target[db1-xacts]: `/usr/local/bin/mrtg-pg-xact.sh db1`
MaxBytes[db1-xacts]: 1250000
Title[db1-xacts]: db1 Database transactions per second
PageTop[db1-xacts]: <H1>Database transactions per
YLegend[db1]: xacts per second
ShortLegend[db1]: tps
Legend1[db1]: Transactions per second
LegendI[db1]: :
Options[db1]: nopercent
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954