Re: [PERFORM] Monitoring Postgresql performance
On 9/28/05, Matthew Nuzum [EMAIL PROTECTED] wrote: On 9/28/05, Arnau [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Monitoring Postgresql performance
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 [EMAIL PROTECTED] wrote: On 9/28/05, Arnau [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Monitoring Postgresql performance
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/msg0.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
Re: [PERFORM] Monitoring Postgresql performance
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
Re: [PERFORM] Monitoring Postgresql performance
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
