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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
