Skip site navigation (1) Skip section navigation (2)

Re: Monitoring Postgresql performance

From: Matthew Nuzum <mattnuzum(at)gmail(dot)com>
To: arnaulist(at)andromeiberica(dot)com
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Monitoring Postgresql performance
Date: 2005-09-28 17:56:28
Message-ID: f3c0b40805092810562e05d94b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 9/28/05, Arnau <arnaulist(at)andromeiberica(dot)com> wrote:
> Hi all,
>
>    I have been "googling" a bit searching info about a way to monitor
> postgresql (CPU & Memory, num processes, ... ) and I haven't found
> anything relevant. I'm using munin to monitor others parameters of my
> servers and I'd like to include postgresql or have a similar tool. Any
> of you is using anything like that? all kind of hints are welcome :-)
>
> Cheers!
> --
> Arnau

I have a cronjob that runs every 5 minutes and checks the number of
processes. When things get unruly I get a text message sent to my cell
phone. It also creates a detailed log entry. I'll paste in an example
of one of my scripts that does this below. This is on a dual purpose
server and monitors both cpu load average and postgres. You can have
the text message sent to multiple email addresses, just put a space
separated list of e-mail addresses between quotes in the CONTACTS=
line. It's simple, but it works and its always nice to know when
there's a problem *before the boss discovers it* ;-)

# Create some messages
HOSTNAME=`hostname`
WARNING_DB="Database connections on $HOSTNAME is rather high"
WARNING_CPU="CPU load on $HOSTNAME is rather high"
CONTACTS="cellphone(at)mmode(dot)com matt(at)blah(dot)net newz(at)blah(dot)net"
WARN=0

#calculate the db load
DB_LOAD=`ps -ax | grep postgres | wc -l`
if (($DB_LOAD > 150))
then
  WARN=1
  echo "$WARNING_DB ($DB_LOAD) " | mail -s "db_load is high
($DB_LOAD)" $CONTACTS
fi

#calculate the processor load
CPU_LOAD=`cat /proc/loadavg | cut --delimiter=" " -f 2 | cut
--delimiter="." -f 1`
if (($CPU_LOAD > 8))
then
  WARN=1
  echo "$WARNING_CPU ($CPU_LOAD) " | mail -s "CPU_load is high
($CPU_LOAD)" $CONTACTS
fi

if (($WARN > 0))
then
  echo -=-=-=-=-=-=-=-=- W A R N I N G -=-=-=-=-=-=-=-=- >> /tmp/warn.txt
  NOW=`date`
  echo -=-=-=-=-=-$NOW-=-=-=-=-=- >> /tmp/warn.txt
  echo CPU LOAD: $CPU_LOAD DB LOAD: $DB_LOAD >> /tmp/warn.txt
  echo >> /tmp/warn.txt
  top -bn 1 >> /tmp/warn.txt
  echo >> /tmp/warn.txt
fi

NOW=`date`
CPU_LOAD=`cat /proc/loadavg | cut --delimiter=" " -f 1,2,3
--output-delimiter=\|`
echo -e $NOW\|$CPU_LOAD\|$DB_LOAD >> ~/LOAD_MONITOR.LOG

--
Matthew Nuzum
www.bearfruit.org

In response to

Responses

pgsql-performance by date

Next:From: Matthew NuzumDate: 2005-09-28 20:02:51
Subject: Logarithmic change (decrease) in performance
Previous:From: Rajesh Kumar MallahDate: 2005-09-28 17:44:12
Subject: Re: Slow concurrent update of same row in a given table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group