Обсуждение: Monitoring Postgresql performance
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
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
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
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.
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
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.
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 >
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
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