pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL-9.0 Monitoring System to improve performance
9+ messages / 7 participants
[nested] [flat]
* PostgreSQL-9.0 Monitoring System to improve performance
@ 2011-09-27 16:35 Venkat Balaji <venkat.balaji@verse.in>
0 siblings, 1 reply; 9+ messages in thread
From: Venkat Balaji @ 2011-09-27 16:35 UTC (permalink / raw)
To: pgsql-performance
Hello Everyone,
I am implementing a PostgreSQL performance monitoring system (to monitor the
below) which would help us understand the database behavior -
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
7. Tracking CPU, IO and memory usage ( by PG processes ) -- desperately
needed
8. Buffer cache usage
9. Tables, Indexes and Database growth statistics
and more..
I am struck at building a script or monitoring tool which gets us CPU usage,
IO metrics and RAM usage of the database server.
Can someone please help me achieve this ?
I need to monitor a 12 processor system with 6 cores. I need to know how
each CPU is performing.
Please help me know the availability of any open source monitoring tools or
scripts for PG-9.0 on RHEL5.
I will hit back with questions regarding monitoring in coming days.
Thanks
VB
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL-9.0 Monitoring System to improve performance
@ 2011-09-28 07:05 Greg Smith <greg@2ndquadrant.com>
parent: Venkat Balaji <venkat.balaji@verse.in>
0 siblings, 2 replies; 9+ messages in thread
From: Greg Smith @ 2011-09-28 07:05 UTC (permalink / raw)
To: Venkat Balaji <venkat.balaji@verse.in>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL-9.0 Monitoring System to improve performance
@ 2011-09-28 12:14 Tomas Vondra <tv@fuzzy.cz>
parent: Greg Smith <greg@2ndquadrant.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tomas Vondra @ 2011-09-28 12:14 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Venkat Balaji <venkat.balaji@verse.in>; pgsql-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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL-9.0 Monitoring System to improve performance
@ 2011-09-30 10:30 Venkat Balaji <venkat.balaji@verse.in>
parent: Greg Smith <greg@2ndquadrant.com>
1 sibling, 0 replies; 9+ messages in thread
From: Venkat Balaji @ 2011-09-30 10:30 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: pgsql-performance
Thanks Greg !
Sorry, I should have put it the other way.
Actually, I am looking for any tool (if exists) which gets me the following
information with one installation or so.
Please see my replies below.
Thanks
VB
On Wed, Sep 28, 2011 at 12:35 PM, Greg Smith <greg@2ndquadrant.com> 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/<http://pgfoundry.org/projects/pgstatspack/;
>
> I am in the process of installing pgstatspack ( i have used it before ).
>> We are waiting for the downtime (to load this through shared preloaded
>> libraries).
>
>
>
>> 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.
>
> I do have pgstattuple contrib module installed and is collecting the data
> and loading it into the auditing tables.
>
>
> 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.
>
> We are getting it done on daily basis and we also have metrics of data
> growth
>
> 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/<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.
>
> As of now i am relying on MPSTAT and will be testing NMON analyzer (this
gets me the graph)
> --
> Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
> PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL-9.0 Monitoring System to improve performance
@ 2011-09-30 10:32 Venkat Balaji <venkat.balaji@verse.in>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 1 reply; 9+ messages in thread
From: Venkat Balaji @ 2011-09-30 10:32 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: Greg Smith <greg@2ndquadrant.com>; pgsql-performance
Hi Tomas,
I will let you know about "check_postgres.pl".
We will explore "pgmonitor" as well.
The other tool we are working on is "pgwatch", we found this very useful.
Thanks
VB
On Wed, Sep 28, 2011 at 5:44 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
> 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
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL-9.0 Monitoring System to improve performance
@ 2011-09-30 11:53 Gregg Jaskiewicz <gryzman@gmail.com>
parent: Venkat Balaji <venkat.balaji@verse.in>
0 siblings, 1 reply; 9+ messages in thread
From: Gregg Jaskiewicz @ 2011-09-30 11:53 UTC (permalink / raw)
To: Venkat Balaji <venkat.balaji@verse.in>; +Cc: Tomas Vondra <tv@fuzzy.cz>; Greg Smith <greg@2ndquadrant.com>; pgsql-performance
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).
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL-9.0 Monitoring System to improve performance
@ 2011-09-30 21:29 Bobby Dewitt <Bdewitt@appriss.com>
parent: Gregg Jaskiewicz <gryzman@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Bobby Dewitt @ 2011-09-30 21:29 UTC (permalink / raw)
To: Gregg Jaskiewicz <gryzman@gmail.com>; Venkat Balaji <venkat.balaji@verse.in>; +Cc: Tomas Vondra <tv@fuzzy.cz>; Greg Smith <greg@2ndquadrant.com>; pgsql-performance
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).
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL-9.0 Monitoring System to improve performance
@ 2011-10-07 22:01 Fernando Hevia <fhevia@gmail.com>
parent: Bobby Dewitt <Bdewitt@appriss.com>
0 siblings, 1 reply; 9+ messages in thread
From: Fernando Hevia @ 2011-10-07 22:01 UTC (permalink / raw)
To: Bobby Dewitt <Bdewitt@appriss.com>; +Cc: Gregg Jaskiewicz <gryzman@gmail.com>; Venkat Balaji <venkat.balaji@verse.in>; Tomas Vondra <tv@fuzzy.cz>; Greg Smith <greg@2ndquadrant.com>; pgsql-performance
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL-9.0 Monitoring System to improve performance
@ 2011-10-07 22:13 Ben Ciceron <ben@triggit.com>
parent: Fernando Hevia <fhevia@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Ben Ciceron @ 2011-10-07 22:13 UTC (permalink / raw)
To: Fernando Hevia <fhevia@gmail.com>; +Cc: Bobby Dewitt <Bdewitt@appriss.com>; Gregg Jaskiewicz <gryzman@gmail.com>; Venkat Balaji <venkat.balaji@verse.in>; Tomas Vondra <tv@fuzzy.cz>; Greg Smith <greg@2ndquadrant.com>; pgsql-performance
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
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2011-10-07 22:13 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-27 16:35 PostgreSQL-9.0 Monitoring System to improve performance Venkat Balaji <venkat.balaji@verse.in>
2011-09-28 07:05 ` Greg Smith <greg@2ndquadrant.com>
2011-09-28 12:14 ` Tomas Vondra <tv@fuzzy.cz>
2011-09-30 10:32 ` Venkat Balaji <venkat.balaji@verse.in>
2011-09-30 11:53 ` Gregg Jaskiewicz <gryzman@gmail.com>
2011-09-30 21:29 ` Bobby Dewitt <Bdewitt@appriss.com>
2011-10-07 22:01 ` Fernando Hevia <fhevia@gmail.com>
2011-10-07 22:13 ` Ben Ciceron <ben@triggit.com>
2011-09-30 10:30 ` Venkat Balaji <venkat.balaji@verse.in>
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