Re: Monitoring Postgresql performance

On 9/28/05, Matthew Nuzum <mattnuzum(at)gmail(dot)com> wrote:
> 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 :-)

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.