PostgreSQL Performance - Monitoring Postgresql performance

Author

Monitoring Postgresql performance

Arnau

2005-09-28, 11:24 am

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Dan Harris

2005-09-28, 1:24 pm

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Matthew Nuzum

2005-09-28, 1:24 pm

On 9/28/05, Arnau < arnaulist@andromeibe
rica.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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Alex Stapleton

2005-09-28, 8:25 pm

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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Gaetano Mendola

2005-09-29, 9:24 am

Tony Wasson

2005-09-29, 8:24 pm

On 9/28/05, Matthew Nuzum <mattnuzum@gmail.com> wrote:[color=darkred
]
> On 9/28/05, Arnau < arnaulist@andromeibe
rica.com> wrote:

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,ss
hd)

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

Juraj Holtak

2005-09-29, 8:24 pm

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:
>
> 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,ss
hd)
>
> 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
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Cosimo Streppone

2005-09-29, 8:24 pm

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/pgsq...05/msg00000.php

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

--
Cosimo

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links