From: Tomas Vondra <tv@fuzzy.cz>
To: Greg Smith <greg@2ndquadrant.com>
Cc: Venkat Balaji <venkat.balaji@verse.in>
Cc: PGSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: PostgreSQL-9.0 Monitoring System to improve performance
Date: Wed, 28 Sep 2011 14:14:17 +0200
Message-ID: <11a62419885df13c49331bc05a4d7a45.squirrel@sq.gransy.com> (raw)
In-Reply-To: <4E82C73A.1050700@2ndquadrant.com>
References: <CAFrxt0g-fSSb4=10+FJBD3rgct=tYTV7Rd1DA=twCmuS2o3pYQ@mail.gmail.com>
<4E82C73A.1050700@2ndquadrant.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, greg@2ndquadrant.com, venkat.balaji@verse.in
Subject: Re: PostgreSQL-9.0 Monitoring System to improve performance
In-Reply-To: <11a62419885df13c49331bc05a4d7a45.squirrel@sq.gransy.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox