pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_stat_* collection
8+ messages / 6 participants
[nested] [flat]
* pg_stat_* collection
@ 2007-05-03 14:45 Greg Smith <gsmith@gregsmith.com>
0 siblings, 3 replies; 8+ messages in thread
From: Greg Smith @ 2007-05-03 14:45 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_* collection
@ 2007-05-03 14:52 Alexander Staubo <alex@purefiction.net>
parent: Greg Smith <gsmith@gregsmith.com>
2 siblings, 2 replies; 8+ messages in thread
From: Alexander Staubo @ 2007-05-03 14:52 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_* collection
@ 2007-05-03 15:03 Magnus Hagander <magnus@hagander.net>
parent: Greg Smith <gsmith@gregsmith.com>
2 siblings, 0 replies; 8+ messages in thread
From: Magnus Hagander @ 2007-05-03 15:03 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_* collection
@ 2007-05-03 16:16 Tobias Brox <tobias@nordicbet.com>
parent: Alexander Staubo <alex@purefiction.net>
1 sibling, 0 replies; 8+ messages in thread
From: Tobias Brox @ 2007-05-03 16:16 UTC (permalink / raw)
To: Alexander Staubo <alex@purefiction.net>; +Cc: Greg Smith <gsmith@gregsmith.com>; pgsql-performance
[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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_* collection
@ 2007-05-03 18:21 Jeff Davis <pgsql@j-davis.com>
parent: Greg Smith <gsmith@gregsmith.com>
2 siblings, 0 replies; 8+ messages in thread
From: Jeff Davis @ 2007-05-03 18:21 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_* collection
@ 2007-05-04 04:53 Greg Smith <gsmith@gregsmith.com>
parent: Alexander Staubo <alex@purefiction.net>
1 sibling, 2 replies; 8+ messages in thread
From: Greg Smith @ 2007-05-04 04:53 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_* collection
@ 2007-05-04 05:08 Tobias Brox <tobias@nordicbet.com>
parent: Greg Smith <gsmith@gregsmith.com>
1 sibling, 0 replies; 8+ messages in thread
From: Tobias Brox @ 2007-05-04 05:08 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
[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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_* collection
@ 2007-05-04 12:47 Michael Stone <mstone+postgres@mathom.us>
parent: Greg Smith <gsmith@gregsmith.com>
1 sibling, 0 replies; 8+ messages in thread
From: Michael Stone @ 2007-05-04 12:47 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-05-04 12:47 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-03 14:45 pg_stat_* collection Greg Smith <gsmith@gregsmith.com>
2007-05-03 14:52 ` Alexander Staubo <alex@purefiction.net>
2007-05-03 16:16 ` Tobias Brox <tobias@nordicbet.com>
2007-05-04 04:53 ` Greg Smith <gsmith@gregsmith.com>
2007-05-04 05:08 ` Tobias Brox <tobias@nordicbet.com>
2007-05-04 12:47 ` Michael Stone <mstone+postgres@mathom.us>
2007-05-03 15:03 ` Magnus Hagander <magnus@hagander.net>
2007-05-03 18:21 ` Jeff Davis <pgsql@j-davis.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox