Message-ID: <11a62419885df13c49331bc05a4d7a45.squirrel@sq.gransy.com>
In-Reply-To: <4E82C73A.1050700@2ndquadrant.com>
References: 
 <CAFrxt0g-fSSb4=10+FJBD3rgct=tYTV7Rd1DA=twCmuS2o3pYQ@mail.gmail.com>
	<4E82C73A.1050700@2ndquadrant.com>
Date: Wed, 28 Sep 2011 14:14:17 +0200
Subject: Re: PostgreSQL-9.0 Monitoring System to improve
 performance
From: "Tomas Vondra" <tv@fuzzy.cz>
To: "Greg Smith" <greg@2ndquadrant.com>
Cc: "Venkat Balaji" <venkat.balaji@verse.in>,
	"PGSQL Performance" <pgsql-performance@postgresql.org>
User-Agent: SquirrelMail/1.4.21
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit
Importance: Normal

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