DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=EpMRvsPY2q41zKyKspCJMEYrEPWINYP8zcJz99bAjpi0Pqd6smH2t4lcx6btiroSQg3bj5BTFMhAh6Td6zdc6D1USg6EDyKZGb7sZV9S6A3bUOOpjTbBe/QoFybWTW9+YP/d3bSGjrnLIFgLpzvAcGPLOs2oTX876PxbpCUlnbI=
Message-ID: <f3c0b40805092810562e05d94b@mail.gmail.com>
Date: Wed, 28 Sep 2005 12:56:28 -0500
From: Matthew Nuzum <mattnuzum@gmail.com>
Reply-To: newz@bearfruit.org
To: arnaulist@andromeiberica.com
Subject: Re: Monitoring Postgresql performance
Cc: pgsql-performance@postgresql.org
In-Reply-To: <433AA962.9020900@andromeiberica.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <433AA962.9020900@andromeiberica.com>

On 9/28/05, Arnau <arnaulist@andromeiberica.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=3D
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=3D`hostname`
WARNING_DB=3D"Database connections on $HOSTNAME is rather high"
WARNING_CPU=3D"CPU load on $HOSTNAME is rather high"
CONTACTS=3D"cellphone@mmode.com matt@blah.net newz@blah.net"
WARN=3D0

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

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

if (($WARN > 0))
then
  echo -=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- W A R N I N G -=3D-=3D-=3D-=3D-=3D=
-=3D-=3D-=3D- >> /tmp/warn.txt
  NOW=3D`date`
  echo -=3D-=3D-=3D-=3D-=3D-$NOW-=3D-=3D-=3D-=3D-=3D- >> /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=3D`date`
CPU_LOAD=3D`cat /proc/loadavg | cut --delimiter=3D" " -f 1,2,3
--output-delimiter=3D\|`
echo -e $NOW\|$CPU_LOAD\|$DB_LOAD >> ~/LOAD_MONITOR.LOG

--
Matthew Nuzum
www.bearfruit.org