Re: [PERFORM] pg_stat_* collection
On Fri, May 04, 2007 at 12:53:55AM -0400, Greg Smith wrote: It's also completely inappropriate for any environment I work in, because there really is no thought of security whatsoever in the whole thing. That makes it sound more like snmp, not less. :-) Mike Stone ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] pg_stat_* collection
Today's survey is: just what are *you* doing to collect up the information about your system made available by the various pg_stat views? I have this hacked together script that dumps them into a file, imports them into another database, and then queries against some of the more interesting data. You would thing there would be an organized project addressing this need around to keep everyone from reinventing that wheel, but I'm not aware of one. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] pg_stat_* collection
On 5/3/07, Greg Smith [EMAIL PROTECTED] wrote: Today's survey is: just what are *you* doing to collect up the information about your system made available by the various pg_stat views? I have this hacked together script that dumps them into a file, imports them into another database, and then queries against some of the more interesting data. You would thing there would be an organized project addressing this need around to keep everyone from reinventing that wheel, but I'm not aware of one. I have a bunch of plugin scripts for Munin (http://munin.projects.linpro.no/) that collect PostgreSQL statistics. Graphs like this are useful: http://purefiction.net/paste/pg_munin_example.png I have been considering tarring them up as a proper release at some point. Anyone interested? Alexander. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] pg_stat_* collection
On Thu, May 03, 2007 at 10:45:48AM -0400, Greg Smith wrote: Today's survey is: just what are *you* doing to collect up the information about your system made available by the various pg_stat views? I have this hacked together script that dumps them into a file, imports them into another database, and then queries against some of the more interesting data. You would thing there would be an organized project addressing this need around to keep everyone from reinventing that wheel, but I'm not aware of one. If you're interested in exposing them with snmp, join the pgsnmpd project :-) //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] pg_stat_* collection
[Alexander Staubo - Thu at 04:52:55PM +0200] I have been considering tarring them up as a proper release at some point. Anyone interested? Yes. Eventually I have my own collection as well: db_activity - counts the number of (all, slow, very slow, stuck idle in transaction) queries in progress; this is one of the better indicators on how busy/overloaded the database is. (I also have a separate script dumping the contents from pg_stat_activity to a log file, which I frequentlymonitor by tail -F). db_commits + db_rollbacks pr database - I'm not sure if those are useful for anything, will eventually remove them. Maybe nice to be able to compare the activity between different databases running on the same host, if they are comparable. db_connections - num of connections compared to max connections. Useful for alarms. db_hanging_transactions - age of oldest transaction. Useful for alarms, since hanging transactions can be very bad for the db performance. db_locks - monitors the number of locks. I've never actually needed this for anything, maybe I should remove it. db_num_backends - number of backends, sorted by databases. Probably not so useful. db_space (one for each database) - monitors space usage, found this script through google. db_xid_wraparound - gives alarms if the databases aren't beeing vacuumed. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] pg_stat_* collection
On Thu, 2007-05-03 at 10:45 -0400, Greg Smith wrote: Today's survey is: just what are *you* doing to collect up the information about your system made available by the various pg_stat views? I have this hacked together script that dumps them into a file, imports them into another database, and then queries against some of the more interesting data. You would thing there would be an organized project addressing this need around to keep everyone from reinventing that wheel, but I'm not aware of one. Is anyone out there collecting their own statistics? What's the easiest way to take statistical samples of the data in a table without reading the entire thing? Regards, Jeff Davis ---(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] pg_stat_* collection
On Thu, 3 May 2007, Alexander Staubo wrote: I have a bunch of plugin scripts for Munin that collect PostgreSQL statistics. I have been considering tarring them up as a proper release at some point. Excellent plan. Pop out a tar file, trade good ideas with Tobias, have some other people play with the code and improve it. Let me know if you need a place to put the files at, since I'd like to look at them anyway I could easily dump them onto a web page while I was at it. Munin is a very interesting solution to this class of problem. They've managed to streamline the whole data collection process by layering clever Perl hacks three deep. It's like the anti-SNMP--just build the simplest possible interface that will work and then stop designing. The result is so easy to work with that it's no surprise people like Munin. It's also completely inappropriate for any environment I work in, because there really is no thought of security whatsoever in the whole thing. What I'm still thinking about is whether it's possible to fix that issue while still keeping the essential simplicity that makes Munin so friendly. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] pg_stat_* collection
[Greg Smith - Fri at 12:53:55AM -0400] Munin is a very interesting solution to this class of problem. They've managed to streamline the whole data collection process by layering clever Perl hacks three deep. It's like the anti-SNMP--just build the simplest possible interface that will work and then stop designing. The result is so easy to work with that it's no surprise people like Munin. It's fairly easy to throw in new graphs, and I like that. One of the drawbacks is that it spends a lot of CPU building the graphs etc - if I continue adding graphs in my current speed, and we set up even more servers, soon it will take us more than five minutes generating the graphs. Also, local configuration can be tricky. Locally I fix this by loading a config file with a hard-coded path. Luckily, as long as the postgres munin plugins are run at localhost as the postgres user, most of them don't need any configuration. Still, it can be useful to tune the alarm thresholds. It's also completely inappropriate for any environment I work in, because there really is no thought of security whatsoever in the whole thing. What I'm still thinking about is whether it's possible to fix that issue while still keeping the essential simplicity that makes Munin so friendly. What layers of security do you need? We're using https, basic auth and ssh-tunnels. We've considered the munin data to be regarded as confidential, at the other hand it's nothing ultra-secret there; i.e. securing the backups of the production database probably deserves more attention. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
