Re: PostgreSQL-9.0 Monitoring System to improve performance

Thanks Greg !

Sorry, I should have put it the other way.

Actually, I am looking for any tool (if exists) which gets me the following
information with one installation or so.

Please see my replies below.

Thanks
VB

On Wed, Sep 28, 2011 at 12:35 PM, Greg Smith <greg(at)2ndquadrant(dot)com> 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/<http://pgfoundry.org/projects/pgstatspack/>
>
> I am in the process of installing pgstatspack ( i have used it before ).
>> We are waiting for the downtime (to load this through shared preloaded
>> libraries).
>
>

>
>> 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.
>
> I do have pgstattuple contrib module installed and is collecting the data
> and loading it into the auditing tables.
>

>
> 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.
>
> We are getting it done on daily basis and we also have metrics of data
> growth
>
> 7. Tracking CPU, IO and memory usage ( by PG processes ) -- desperately
>> needed
>>
>
> 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/<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.
>
> As of now i am relying on MPSTAT and will be testing NMON analyzer (this
gets me the graph)

> --
> Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
> PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
>
>