Re: PostgreSQL-9.0 Monitoring System to improve performance

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