Thread: PostgreSQL-9.0 Monitoring System to improve performance

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

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

Looks like this is generally an area that can be targeted by some
businesses. Or an open source enthusiast.
One centre that captures all the information and produces a report
based on it would be a great thing. Especially in cases like mine,
where I have tens of postgresql installations on different hardware
and with different use patterns (but schemas and queries are the
same).

EnterpriseDB now has Postgres Enterprise Manager
(http://enterprisedb.com/products-services-training/products/postgres-enter
prise-manager) that has some of the information that is being asked for.
It has a hot table analysis report that shows the number of scans, rows
read, etc.  Since much of the tool is using the pgAdmin code base, much of
this is also available in pgAdmin but PEM will track the statistics at
given intervals and show you trending graphs over time.  It's still a very
new tool so I'm sure they are working to add new features and have been
looking for enhancement suggestions.  Of course, it requires a service
contract with them to use the tool, but it doesn't cost extra to add the
tool if you already have a contract with them.  It does have a 45 day
evaluation if you wanted to check it out.

Hope that helps.
Bobby

On 9/30/11 7:53 AM, "Gregg Jaskiewicz" <gryzman@gmail.com> wrote:

>Looks like this is generally an area that can be targeted by some
>businesses. Or an open source enthusiast.
>One centre that captures all the information and produces a report
>based on it would be a great thing. Especially in cases like mine,
>where I have tens of postgresql installations on different hardware
>and with different use patterns (but schemas and queries are the
>same).

doe sit include monitoring replicas ?

Cheers,
Ben-




On Fri, Oct 7, 2011 at 3:01 PM, Fernando Hevia <fhevia@gmail.com> wrote:
> pgwatch might also be worth taking a look
> at: http://www.cybertec.at/en/postgresql_products/pgwatch-cybertec-enterprise-postgresql-monitor
> Fernando.-
> On Fri, Sep 30, 2011 at 18:29, Bobby Dewitt <Bdewitt@appriss.com> wrote:
>>
>> EnterpriseDB now has Postgres Enterprise Manager
>>
>> (http://enterprisedb.com/products-services-training/products/postgres-enter
>> prise-manager) that has some of the information that is being asked for.
>> It has a hot table analysis report that shows the number of scans, rows
>> read, etc.  Since much of the tool is using the pgAdmin code base, much of
>> this is also available in pgAdmin but PEM will track the statistics at
>> given intervals and show you trending graphs over time.  It's still a very
>> new tool so I'm sure they are working to add new features and have been
>> looking for enhancement suggestions.  Of course, it requires a service
>> contract with them to use the tool, but it doesn't cost extra to add the
>> tool if you already have a contract with them.  It does have a 45 day
>> evaluation if you wanted to check it out.
>>
>> Hope that helps.
>> Bobby
>>
>> On 9/30/11 7:53 AM, "Gregg Jaskiewicz" <gryzman@gmail.com> wrote:
>>
>> >Looks like this is generally an area that can be targeted by some
>> >businesses. Or an open source enthusiast.
>> >One centre that captures all the information and produces a report
>> >based on it would be a great thing. Especially in cases like mine,
>> >where I have tens of postgresql installations on different hardware
>> >and with different use patterns (but schemas and queries are the
>> >same).
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
>