Hi Tomas, I will let you know about "check_postgres.pl".
We will explore "pgmonitor" as well. The other tool we are working on is "pgwatch", we found this very useful. Thanks VB On Wed, Sep 28, 2011 at 5:44 PM, Tomas Vondra <t...@fuzzy.cz> wrote: > On 28 Září 2011, 9:05, Greg Smith wrote: > > Venkat Balaji wrote: > >> > >> 1. Big Full Table Scans > >> 2. Table with high IOs (hot tables) > >> 3. Highly used Indexes > >> 4. Tables undergoing high DMLs with index scans 0 (with unused indexes) > >> 5. Index usage for heap blk hits > >> 6. Tracking Checkpoints > > > > This is fairly easy to collect and analyze. You might take a look at > > pgstatspack to see how one program collects snapshots of this sort of > > information: http://pgfoundry.org/projects/pgstatspack/ > > It's definitely fairly easy to collect, and pgstatspack help a lot. But > interpreting the collected data is much harder, especially when it comes > to indexes. For example UNIQUE indexes often have idx_scan=0, because > checking the uniqueness is not an index scan. Other indexes may be created > for rare queries (e.g. a batch running once a year), so you need a very > long interval between the snapshots. > > >> 8. Buffer cache usage > > > > High-level information about this can be collected by things like the > > pg_statio* views. If you want to actually look inside the buffer cache > > and get detailed statistics on it, that's a harder problem. I have some > > sample queries for that sort of thing in my book. > > There's an extension pg_buffercache for that (the queries are using it > IIRC). > > >> 9. Tables, Indexes and Database growth statistics > > > > This is valuable information to monitor over time, but I'm not aware of > > any existing tools that track it well. It won't be hard to collect it > > on your own though. > > What about check_postgres.pl script? > > >> 7. Tracking CPU, IO and memory usage ( by PG processes ) -- > >> desperately needed > > What about using check_postgres.pl and other plugins? Never used that > though, so maybe there are issues I'm not aware of. > > > I'm not aware of any open-source tool that tracks this information yet. > > PostgreSQL has no idea what CPU, memory, and I/O is being done by the OS > > when you execute a query. The operating system knows some of that, but > > has no idea what the database is doing. You can see a real-time > > snapshot combining the two pieces of info using the pg_top program: > > http://ptop.projects.postgresql.org/ but I suspect what you want is a > > historical record of it instead. > > > > Writing something that tracks both at once and logs all the information > > for later analysis is one of the big missing pieces in PostgreSQL > > management. I have some ideas for how to build such a thing. But I > > expect it will take a few months of development time to get right, and I > > haven't come across someone yet who wants to fund that size of project > > for this purpose yet. > > A long (long long long) time ago I wrote something like this, it's called > pgmonitor and is available here: > > http://sourceforge.net/apps/trac/pgmonitor/ > > But the development stalled (not a rare thing for projects developed by a > single person) and I'm not quite sure about the right direction. Maybe > it's worthless, maybe it would be a good starting point - feel free to > comment. > > Tomas > >