Thread: Monitoring Postgresql performance

Monitoring Postgresql performance

From
Arnau
Date:
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


Re: Monitoring Postgresql performance

From
Dan Harris
Date:
On Sep 28, 2005, at 8:32 AM, Arnau wrote:

> Hi all,
>
>   I have been "googling" a bit searching info about a way to
> monitor postgresql (CPU & Memory, num processes, ... )

You didn't mention your platform, but I have an xterm open pretty
much continuously for my DB server that runs plain old top.  I have
customized my settings enough that I can pretty much see anything I
need to from there.

-Dan


Re: Monitoring Postgresql performance

From
Matthew Nuzum
Date:
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=
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@mmode.com matt@blah.net newz@blah.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

Re: Monitoring Postgresql performance

From
Alex Stapleton
Date:
On 28 Sep 2005, at 15:32, Arnau 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!
>

Have you looked at SNMP? It's a bit complex but there's lots of tools
for monitoring system data / sending alerts based on SNMP already.


Re: Monitoring Postgresql performance

From
Gaetano Mendola
Date:
Arnau 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!

We use Cricket + Nagios ( new Netsaint release ).


Regards
Gaetano Mendola



Re: Monitoring Postgresql performance

From
Tony Wasson
Date:
On 9/28/05, Matthew Nuzum <mattnuzum@gmail.com> wrote:
> 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 :-)

We are also using cricket + nagios.

On each DB server: Setup snmpd and use snmpd.conf to set disk quotas
and mark processes that need to be running  (like
postmaster,syslog,sshd)

On the monitoring server(s): Use cricket for long term trends &
graphs. Use nagios for current status and alerting and some trending.
(Nagios has plugins over SNMP for load,cpu,memory,disk and processes)

Here's the nagios plugins I have hacked up over the past few months
and what they do. I'd imagine some could use better names. I can
provide these of package them up if anyone is interested.

check_pgconn.pl - Shows percentage of connections available. It uses
"SELECT COUNT(*) FROM pg_stat_activity" / "SHOW max_connections". It
can also alert when less than a certain number of connections are
available.

check_pgqueries.pl - If you have query logging enabled this summarizes
the types of queries running (SELECT ,INSERT ,DELETE ,UPDATE ,ALTER
,CREATE ,TRUNCATE, VACUUM, COPY) and warns if any queries have been
running longer than 5 minutes (configurable).

check_pglocks.pl - Look for locks that block and for baselining lock activity.

check_pgtime.pl - Makes sure that postgresql's time is in sync with
the monitoring server.

check_pgqueries.pl - Whines if any queries are in the "waiting" state.
The script that runs on each DB server does "ps auxww | grep postgres
| grep -i "[W]aiting"" and exposes that through SNMP using the exec
functionality. Nagios then alerts if queries are being blocked.

Re: Monitoring Postgresql performance

From
Juraj Holtak
Date:
Hi,

impressive

But you forgot to include those scipts as attachment or they got lost
somehow ;-)

could you post them (again)?

thanx,
Juraj

Am Donnerstag, den 29.09.2005, 13:02 -0700 schrieb Tony Wasson:
> On 9/28/05, Matthew Nuzum <mattnuzum@gmail.com> wrote:
> > 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 :-)
>
> We are also using cricket + nagios.
>
> On each DB server: Setup snmpd and use snmpd.conf to set disk quotas
> and mark processes that need to be running  (like
> postmaster,syslog,sshd)
>
> On the monitoring server(s): Use cricket for long term trends &
> graphs. Use nagios for current status and alerting and some trending.
> (Nagios has plugins over SNMP for load,cpu,memory,disk and processes)
>
> Here's the nagios plugins I have hacked up over the past few months
> and what they do. I'd imagine some could use better names. I can
> provide these of package them up if anyone is interested.
>
> check_pgconn.pl - Shows percentage of connections available. It uses
> "SELECT COUNT(*) FROM pg_stat_activity" / "SHOW max_connections". It
> can also alert when less than a certain number of connections are
> available.
>
> check_pgqueries.pl - If you have query logging enabled this summarizes
> the types of queries running (SELECT ,INSERT ,DELETE ,UPDATE ,ALTER
> ,CREATE ,TRUNCATE, VACUUM, COPY) and warns if any queries have been
> running longer than 5 minutes (configurable).
>
> check_pglocks.pl - Look for locks that block and for baselining lock activity.
>
> check_pgtime.pl - Makes sure that postgresql's time is in sync with
> the monitoring server.
>
> check_pgqueries.pl - Whines if any queries are in the "waiting" state.
> The script that runs on each DB server does "ps auxww | grep postgres
> | grep -i "[W]aiting"" and exposes that through SNMP using the exec
> functionality. Nagios then alerts if queries are being blocked.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>


Re: Monitoring Postgresql performance

From
Cosimo Streppone
Date:
Arnau 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 :-)

Probably, as you said, this is not so much relevant,
as it is something at *early* stages of usability :-)
but have you looked at pgtop?

The basic requirement is that you enable your postmaster
stats collector and query command strings.

Here is the first announcement email:
   http://archives.postgresql.org/pgsql-announce/2005-05/msg00000.php

And its home on the CPAN:
   http://search.cpan.org/dist/pgtop/pgtop

--
Cosimo


Re: Monitoring Postgresql performance

From
Tony Wasson
Date:
On 9/29/05, Juraj Holtak <juraj@proaut.org> wrote:
> Hi,
>
> impressive
>
> But you forgot to include those scipts as attachment or they got lost
> somehow ;-)
>
> could you post them (again)?
>
> thanx,
> Juraj

Attached you'll find the scripts I use in nagios to monitor
postgresql. I cleaned these up a bit but they are still rough. Please
let me know if you find these useful. I will see about getting these
are part of the nagios plugins package.

Tony Wasson

Attachment