Created
August 16, 2012 15:27
-
-
Save johndobrien/3371086 to your computer and use it in GitHub Desktop.
A really cool script to write stats from postgres to graphite.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
# Copyright 2012 Canonical Ltd. | |
# Author: JuanJo Ciarlante <jjo@canonical.com> | |
# License: GPLv2 | |
DATABASE=<your databases separated by spaces> | |
ROLE=master | |
: ${PUSH_CMD:="nc <your corbon host> <your carbon port>"} | |
: ${FREQ:=10min} | |
for db in ${DATABASES};do | |
( | |
metric_prefix="<some prefix for graphite>.${ROLE}.${db}" | |
stat=pg_stat_user_tables;keypos=3;nodename=relname; | |
psql -A ${db} -c "select * from ${stat};" | awk -v freq=${FREQ} -v timestamp="$(date +%s)" -v keypos=$keypos -v nodename=$nodename -v metric_prefix="${metric_prefix}" -vFS='|' '(NR==1){ for(i=0;i<NF;i++) { fieldname[i]=$i } } (NR>1) { for(i=keypos+1;i<NF;i++) { if (gsub(":"," ", $i)) value=mktime($i); else value=$i ;printf ("%s.%s.%s.%s.%s %d %d\n", metric_prefix, nodename, $keypos, fieldname[i], freq, value, timestamp); }}' | |
stat=pg_statio_user_tables;keypos=3;nodename=relname; | |
psql -A ${db} -c "select * from ${stat};" | awk -v freq=${FREQ} -v timestamp="$(date +%s)" -v keypos=$keypos -v nodename=$nodename -v metric_prefix="${metric_prefix}" -vFS='|' '(NR==1){ for(i=0;i<NF;i++) { fieldname[i]=$i } } (NR>1) { for(i=keypos+1;i<NF;i++) { if (gsub(":"," ", $i)) value=mktime($i); else value=$i ;printf ("%s.%s.%s.%s.%s %d %d\n", metric_prefix, nodename, $keypos, fieldname[i], freq, value, timestamp); }}' | |
stat=pg_stat_database;keypos=2;nodename=datname; | |
psql -A ${db} -c "select * from ${stat};" | awk -v freq=${FREQ} -v timestamp="$(date +%s)" -v keypos=$keypos -v nodename=$nodename -v metric_prefix="${metric_prefix}" -vFS='|' '(NR==1){ for(i=0;i<NF;i++) { fieldname[i]=$i } } (NR>1) { for(i=keypos+1;i<NF;i++) { if (gsub(":"," ", $i)) value=mktime($i); else value=$i ;printf ("%s.%s.%s.%s.%s %d %d\n", metric_prefix, nodename, $keypos, fieldname[i], freq, value, timestamp); }}' | sed -nr "s/[.]${db}/.total/p" | |
## slony replication delay: | |
nodename=repl_delay; | |
value="$(psql -At -c "SELECT MAX(EXTRACT(EPOCH FROM st_lag_time)) FROM _sl.sl_status WHERE st_origin = _sl.getlocalnodeid('_sl') AND st_received!=0;" ${db} 2>/dev/null)" | |
test -n "${value}" && printf "%s.%s.%s.%s.%s %s %d\n" ${metric_prefix} ${nodename} "slony" "max" ${FREQ} ${value} $(date +%s) | |
nodename=process; | |
ps -opid= -C postgres| xargs -I@ sed -rn 's/^se.//p' /proc/@/sched 2>/dev/null|awk -v timestamp="$(date +%s)" -v freq=${FREQ} -v nodename=$nodename -v metric_prefix="${metric_prefix}" -v OFMT=%d '{ acc[$1]+=$3} /exec_start/ { nr_procs++}; END { acc["nr_procs"]=nr_procs;for (i in acc) printf("%s.%s.%s.%s %d %d\n", metric_prefix, nodename, i, freq, acc[i], timestamp);}' | |
)| $PUSH_CMD | |
done |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I believe line 9 should read:
for db in ${DATABASE};do