Обсуждение: 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 gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On 5/3/07, Greg Smith <gsmith@gregsmith.com> 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.
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
[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 ofthe 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.
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
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 gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
[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.
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