Re: PostgreSQL-9.0 Monitoring System to improve performance

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 <tv(at)fuzzy(dot)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
>
>