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 <tv@xxxxxxxx> wrote:
On 28 Září 2011, 9:05, Greg Smith wrote:It's definitely fairly easy to collect, and pgstatspack help a lot. But
> 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/
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.
There's an extension pg_buffercache for that (the queries are using it IIRC).
>> 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.
What about check_postgres.pl script?
>> 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 using check_postgres.pl and other plugins? Never used that
>> 7. Tracking CPU, IO and memory usage ( by PG processes ) --
>> desperately needed
though, so maybe there are issues I'm not aware of.
A long (long long long) time ago I wrote something like this, it's called
> 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.
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
