From: Greg Smith <greg@2ndquadrant.com>
To: 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 00:05:30 -0700
Message-ID: <4E82C73A.1050700@2ndquadrant.com> (raw)
In-Reply-To: <CAFrxt0g-fSSb4=10+FJBD3rgct=tYTV7Rd1DA=twCmuS2o3pYQ@mail.gmail.com>
References: <CAFrxt0g-fSSb4=10+FJBD3rgct=tYTV7Rd1DA=twCmuS2o3pYQ@mail.gmail.com>
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/>
> 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.
> 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.
> 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/ 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.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
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: greg@2ndquadrant.com, venkat.balaji@verse.in
Subject: Re: PostgreSQL-9.0 Monitoring System to improve performance
In-Reply-To: <4E82C73A.1050700@2ndquadrant.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