Thread: Performance monitor

Performance monitor

From
Bruce Momjian
Date:
I have started coding a PostgreSQL performance monitor.  It will be like
top, but allow you to click on a backend to see additional information.

It will be written in Tcl/Tk.  I may ask to add something to 7.1 so when
a backend receives a special signal, it dumps a file in /tmp with some
backend status.  It would be done similar to how we handle Cancel
signals.

How do people feel about adding a single handler to 7.1?  Is it
something I can slip into the current CVS, or will it have to exist as a
patch to 7.1.  Seems it would be pretty isolated unless someone sends
the signal, but it is clearly a feature addition.

We don't really have any way of doing process monitoring except ps, so I
think this is needed.  I plan to have something done in the next week or
two.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
The Hermit Hacker
Date:
On Wed, 7 Mar 2001, Bruce Momjian wrote:

> I have started coding a PostgreSQL performance monitor.  It will be like
> top, but allow you to click on a backend to see additional information.
>
> It will be written in Tcl/Tk.  I may ask to add something to 7.1 so when
> a backend receives a special signal, it dumps a file in /tmp with some
> backend status.  It would be done similar to how we handle Cancel
> signals.
>
> How do people feel about adding a single handler to 7.1?  Is it
> something I can slip into the current CVS, or will it have to exist as a
> patch to 7.1.  Seems it would be pretty isolated unless someone sends
> the signal, but it is clearly a feature addition.

Totally dead set against it ...

... the only hold up on RC1 right now was awaiting Vadim getting back so
that he and Tom could work out the WAL related issues ... adding a new
signal handler *definitely* counts as "adding a new feature" ...





Re: Performance monitor

From
Bruce Momjian
Date:
> > How do people feel about adding a single handler to 7.1?  Is it
> > something I can slip into the current CVS, or will it have to exist as a
> > patch to 7.1.  Seems it would be pretty isolated unless someone sends
> > the signal, but it is clearly a feature addition.
> 
> Totally dead set against it ...
> 
> ... the only hold up on RC1 right now was awaiting Vadim getting back so
> that he and Tom could work out the WAL related issues ... adding a new
> signal handler *definitely* counts as "adding a new feature" ...

OK, I will distribute it as a patch.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Tom Lane
Date:
The Hermit Hacker <scrappy@hub.org> writes:
>> How do people feel about adding a single handler to 7.1?

> Totally dead set against it ...

Ditto.  Particularly a signal handler that performs I/O.  That's going
to create all sorts of re-entrancy problems.
        regards, tom lane


Re: Performance monitor

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> How do people feel about adding a single handler to 7.1?  Is it
> something I can slip into the current CVS, or will it have to exist as a
> patch to 7.1.  Seems it would be pretty isolated unless someone sends
> the signal, but it is clearly a feature addition.

> OK, I will distribute it as a patch.

Patch or otherwise, this approach seems totally unworkable.  A signal
handler cannot do I/O safely, it cannot look at shared memory safely,
it cannot even look at the backend's own internal state safely.  How's
it going to do any useful status reporting?

Firing up a separate backend process that looks at shared memory seems
like a more useful design in the long run.  That will mean exporting
more per-backend status into shared memory, however, and that means that
this is not a trivial change.
        regards, tom lane


Re: Performance monitor

From
Bruce Momjian
Date:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > How do people feel about adding a single handler to 7.1?  Is it
> > something I can slip into the current CVS, or will it have to exist as a
> > patch to 7.1.  Seems it would be pretty isolated unless someone sends
> > the signal, but it is clearly a feature addition.
> 
> > OK, I will distribute it as a patch.
> 
> Patch or otherwise, this approach seems totally unworkable.  A signal
> handler cannot do I/O safely, it cannot look at shared memory safely,
> it cannot even look at the backend's own internal state safely.  How's
> it going to do any useful status reporting?

Why can't we do what we do with Cancel, where we set a flag and check it
at safe places?

> Firing up a separate backend process that looks at shared memory seems
> like a more useful design in the long run.  That will mean exporting
> more per-backend status into shared memory, however, and that means that
> this is not a trivial change.

Right, that is a lot of work.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Bruce Momjian
Date:
> All in all, I do not see this as an easy task that you can whip out and
> then release as a 7.1 patch without extensive testing.  And given that,
> I'd rather see it done with what I consider the right long-term approach,
> rather than a dead-end hack.  I think doing it in a signal handler is
> ultimately going to be a dead-end hack.

Well, the signal stuff will get me going at least.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Philip Warner
Date:
At 18:05 7/03/01 -0500, Bruce Momjian wrote:
>> All in all, I do not see this as an easy task that you can whip out and
>> then release as a 7.1 patch without extensive testing.  And given that,
>> I'd rather see it done with what I consider the right long-term approach,
>> rather than a dead-end hack.  I think doing it in a signal handler is
>> ultimately going to be a dead-end hack.
>
>Well, the signal stuff will get me going at least.

Didn't someone say this can't be done safely - or am I missing something?

ISTM that doing the work to put things in shared memory will be much more
profitable in the long run. You have previously advocated self-tuning
algorithms for performance - a prerequisite for these will be performance
data in shared memory.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


Re: Performance monitor

From
Justin Clift
Date:
Hi all,

Wouldn't another approach be to write a C function that does the
necessary work, then just call it like any other C function?

i.e.  Connect to the database and issue a "select
perf_stats('/tmp/stats-2001-03-08-01.txt')" ?

Or similar?

Sure, that means another database connection which would change the
resource count but it sounds like a more consistent approach.

Regards and best wishes,

Justin Clift

Philip Warner wrote:
> 
> At 18:05 7/03/01 -0500, Bruce Momjian wrote:
> >> All in all, I do not see this as an easy task that you can whip out and
> >> then release as a 7.1 patch without extensive testing.  And given that,
> >> I'd rather see it done with what I consider the right long-term approach,
> >> rather than a dead-end hack.  I think doing it in a signal handler is
> >> ultimately going to be a dead-end hack.
> >
> >Well, the signal stuff will get me going at least.
> 
> Didn't someone say this can't be done safely - or am I missing something?
> 
> ISTM that doing the work to put things in shared memory will be much more
> profitable in the long run. You have previously advocated self-tuning
> algorithms for performance - a prerequisite for these will be performance
> data in shared memory.
> 
> ----------------------------------------------------------------
> Philip Warner                    |     __---_____
> Albatross Consulting Pty. Ltd.   |----/       -  \
> (A.B.N. 75 008 659 498)          |          /(@)   ______---_
> Tel: (+61) 0500 83 82 81         |                 _________  \
> Fax: (+61) 0500 83 82 82         |                 ___________ |
> Http://www.rhyme.com.au          |                /           \|
>                                  |    --________--
> PGP key available upon request,  |  /
> and from pgp5.ai.mit.edu:11371   |/
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


Re: Performance monitor

From
Philip Warner
Date:
At 11:33 8/03/01 +1100, Justin Clift wrote:
>Hi all,
>
>Wouldn't another approach be to write a C function that does the
>necessary work, then just call it like any other C function?
>
>i.e.  Connect to the database and issue a "select
>perf_stats('/tmp/stats-2001-03-08-01.txt')" ?
>

I think Bruce wants per-backend data, and this approach would seem to only
get the data for the current backend. 

Also, I really don't like the proposal to write files to /tmp. If we want a
perf tool, then we need to have something like 'top', which will
continuously update. With 40 backends, the idea of writing 40 file to /tmp
every second seems a little excessive to me.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


RE: Performance monitor

From
Mike Mascari
Date:
I like the idea of updating shared memory with the performance statistics, 
current query execution information, etc., providing a function to fetch 
those statistics, and perhaps providing a system view (i.e. pg_performance) 
based upon such functions which can be queried by the administrator.

FWIW,

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From:    Philip Warner [SMTP:pjw@rhyme.com.au]
Sent:    Wednesday, March 07, 2001 7:42 PM
To:    Justin Clift
Cc:    Bruce Momjian; Tom Lane; The Hermit Hacker; PostgreSQL-development
Subject:    Re: [HACKERS] Performance monitor

At 11:33 8/03/01 +1100, Justin Clift wrote:
>Hi all,
>
>Wouldn't another approach be to write a C function that does the
>necessary work, then just call it like any other C function?
>
>i.e.  Connect to the database and issue a "select
>perf_stats('/tmp/stats-2001-03-08-01.txt')" ?
>

I think Bruce wants per-backend data, and this approach would seem to only
get the data for the current backend.

Also, I really don't like the proposal to write files to /tmp. If we want a
perf tool, then we need to have something like 'top', which will
continuously update. With 40 backends, the idea of writing 40 file to /tmp
every second seems a little excessive to me.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



RE: Performance monitor

From
Philip Warner
Date:
At 19:59 7/03/01 -0500, Mike Mascari wrote:
>I like the idea of updating shared memory with the performance statistics, 
>current query execution information, etc., providing a function to fetch 
>those statistics, and perhaps providing a system view (i.e. pg_performance) 
>based upon such functions which can be queried by the administrator.

This sounds like The Way to me. Although I worry that using a view (or
standard libpq methods) might be too expensive in high load situations
(this is not based on any knowledge of the likely costs, however!).

We do need to make this as cheap as possible since we don't want to distort
the stats, and it will often be used to diagnose perormance problems, and
we don't want to contribute to those problems.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


Re: Performance monitor

From
Bruce Momjian
Date:
> I think Bruce wants per-backend data, and this approach would seem to only
> get the data for the current backend. 
> 
> Also, I really don't like the proposal to write files to /tmp. If we want a
> perf tool, then we need to have something like 'top', which will
> continuously update. With 40 backends, the idea of writing 40 file to /tmp
> every second seems a little excessive to me.

My idea was to use 'ps' to gather most of the information, and just use
the internal stats when someone clicked on a backend and wanted more
information.


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Bruce Momjian
Date:
> At 18:05 7/03/01 -0500, Bruce Momjian wrote:
> >> All in all, I do not see this as an easy task that you can whip out and
> >> then release as a 7.1 patch without extensive testing.  And given that,
> >> I'd rather see it done with what I consider the right long-term approach,
> >> rather than a dead-end hack.  I think doing it in a signal handler is
> >> ultimately going to be a dead-end hack.
> >
> >Well, the signal stuff will get me going at least.
> 
> Didn't someone say this can't be done safely - or am I missing something?

OK, I will write just the all-process display part, that doesn't need
any per-backend info because it gets it all from 'ps'.  Then maybe
someone will come up with a nifty idea, or I will play with my local
copy to see how it can be done.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Justin Clift
Date:
Mike Mascari's idea (er... his assembling of the other ideas) still
sounds like the Best Solution though.

:-)

+ Justin

+++

I like the idea of updating shared memory with the performance
statistics, 
current query execution information, etc., providing a function to fetch 
those statistics, and perhaps providing a system view (i.e.
pg_performance) 
based upon such functions which can be queried by the administrator.

FWIW,

Mike Mascari
mascarm@mascari.com

+++

Bruce Momjian wrote:
> 
> > I think Bruce wants per-backend data, and this approach would seem to only
> > get the data for the current backend.
> >
> > Also, I really don't like the proposal to write files to /tmp. If we want a
> > perf tool, then we need to have something like 'top', which will
> > continuously update. With 40 backends, the idea of writing 40 file to /tmp
> > every second seems a little excessive to me.
> 
> My idea was to use 'ps' to gather most of the information, and just use
> the internal stats when someone clicked on a backend and wanted more
> information.
> 
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026


Re: Performance monitor

From
Bruce Momjian
Date:
Yes, seems that is best.  I will probably hack something up here so I
can do some testing of the app itself.

> Mike Mascari's idea (er... his assembling of the other ideas) still
> sounds like the Best Solution though.
> 
> :-)
> 
> + Justin
> 
> +++
> 
> I like the idea of updating shared memory with the performance
> statistics, 
> current query execution information, etc., providing a function to fetch 
> those statistics, and perhaps providing a system view (i.e.
> pg_performance) 
> based upon such functions which can be queried by the administrator.
> 
> FWIW,
> 
> Mike Mascari
> mascarm@mascari.com
> 
> +++
> 
> Bruce Momjian wrote:
> > 
> > > I think Bruce wants per-backend data, and this approach would seem to only
> > > get the data for the current backend.
> > >
> > > Also, I really don't like the proposal to write files to /tmp. If we want a
> > > perf tool, then we need to have something like 'top', which will
> > > continuously update. With 40 backends, the idea of writing 40 file to /tmp
> > > every second seems a little excessive to me.
> > 
> > My idea was to use 'ps' to gather most of the information, and just use
> > the internal stats when someone clicked on a backend and wanted more
> > information.
> > 
> > --
> >   Bruce Momjian                        |  http://candle.pha.pa.us
> >   pgman@candle.pha.pa.us               |  (610) 853-3000
> >   +  If your life is a hard drive,     |  830 Blythe Avenue
> >   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
> 


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Bruce Momjian
Date:
> I wouldn't be at all surprised if you found a better approach - my
> configuration above, to my mind at least, is not pretty. I hope you do find
> a better approach - I know I'll be peeking at your code to see. 

Yes, I have an idea and hope it works.
--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Karel Zak
Date:
On Wed, Mar 07, 2001 at 10:06:38PM -0500, Bruce Momjian wrote:
> > I think Bruce wants per-backend data, and this approach would seem to only
> > get the data for the current backend. 
> > 
> > Also, I really don't like the proposal to write files to /tmp. If we want a
> > perf tool, then we need to have something like 'top', which will
> > continuously update. With 40 backends, the idea of writing 40 file to /tmp
> > every second seems a little excessive to me.
> 
> My idea was to use 'ps' to gather most of the information, and just use
> the internal stats when someone clicked on a backend and wanted more
> information.
Are you sure about 'ps' stuff portability? I don't known how data you
want read from 'ps', but /proc utils are very OS specific and for example
on Linux within a few years was libproc several time overhauled.I spent several years with /proc stuff (processes
manager:
 
http://home.zf.jcu.cz/~zakkr/kim).
    Karel

-- Karel Zak  <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz


Re: Performance monitor

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Are you sure about 'ps' stuff portability?

> I am not going to do a huge amount with the actual ps columns, except
> allow you to sort on them.  I will do most on the ps status display we
> use as the command line in ps.

... which in itself is not necessarily portable.  How many of our
supported platforms actually have working ps-status code?  (This is
an honest question: I don't know.)
        regards, tom lane


Re: Performance monitor

From
Bruce Momjian
Date:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> Are you sure about 'ps' stuff portability?
> 
> > I am not going to do a huge amount with the actual ps columns, except
> > allow you to sort on them.  I will do most on the ps status display we
> > use as the command line in ps.
> 
> ... which in itself is not necessarily portable.  How many of our
> supported platforms actually have working ps-status code?  (This is
> an honest question: I don't know.)

No idea.  My first version will probably only work a few platforms.

The problem I see with the shared memory idea is that some of the
information needed may be quite large.  For example, query strings can
be very long.  Do we just allocate 512 bytes and clip off the rest.  And
as I add more info, I need more shared memory per backend.  I just liked
the file system dump solution because I could modify it pretty easily,
and because the info only appears when you click on the process, it
doesn't happen often.

Of course, if we start getting the full display partly from each
backend, we will have to use shared memory.

I could have started on a user admin tool, or GUC config tool, but a
performance monitor is the one item we really don't have yet.  Doing
'ps' over and over is sort of lame.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Bruce Momjian
Date:
> On Wed, Mar 07, 2001 at 10:06:38PM -0500, Bruce Momjian wrote:
> > > I think Bruce wants per-backend data, and this approach would seem to only
> > > get the data for the current backend. 
> > > 
> > > Also, I really don't like the proposal to write files to /tmp. If we want a
> > > perf tool, then we need to have something like 'top', which will
> > > continuously update. With 40 backends, the idea of writing 40 file to /tmp
> > > every second seems a little excessive to me.
> > 
> > My idea was to use 'ps' to gather most of the information, and just use
> > the internal stats when someone clicked on a backend and wanted more
> > information.
> 
>  Are you sure about 'ps' stuff portability? I don't known how data you
> want read from 'ps', but /proc utils are very OS specific and for example
> on Linux within a few years was libproc several time overhauled.
>  I spent several years with /proc stuff (processes manager: 
> http://home.zf.jcu.cz/~zakkr/kim).

I am not going to do a huge amount with the actual ps columns, except
allow you to sort on them.  I will do most on the ps status display we
use as the command line in ps.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Richard J Kuhns
Date:
Bruce Momjian writes:
...> The problem I see with the shared memory idea is that some of the> information needed may be quite large.  For
example,query strings can> be very long.  Do we just allocate 512 bytes and clip off the rest.  And> as I add more
info,I need more shared memory per backend.  I just liked> the file system dump solution because I could modify it
prettyeasily,> and because the info only appears when you click on the process, it> doesn't happen often.> 
 
Have you thought about using a named pipe?  They've been around for quite a
while, and should (he said with a :-)) be available on most-if-not-all
currently supported systems.
-- 
Richard Kuhns            rjk@grauel.com
PO Box 6249            Tel: (765)477-6000 \
100 Sawmill Road                    x319
Lafayette, IN  47903             (800)489-4891 /


Re: Performance monitor

From
Bruce Momjian
Date:
> Bruce Momjian writes:
> ...
>  > The problem I see with the shared memory idea is that some of the
>  > information needed may be quite large.  For example, query strings can
>  > be very long.  Do we just allocate 512 bytes and clip off the rest.  And
>  > as I add more info, I need more shared memory per backend.  I just liked
>  > the file system dump solution because I could modify it pretty easily,
>  > and because the info only appears when you click on the process, it
>  > doesn't happen often.
>  > 
> Have you thought about using a named pipe?  They've been around for quite a
> while, and should (he said with a :-)) be available on most-if-not-all
> currently supported systems.

Nifty idea.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Peter Eisentraut
Date:
Tom Lane writes:

> How many of our supported platforms actually have working ps-status
> code?  (This is an honest question: I don't know.)

BeOS, DG/UX, and Cygwin don't have support code, the rest *should* work.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/



Re: Performance monitor

From
Bruce Momjian
Date:
> Tom Lane writes:
> 
> > How many of our supported platforms actually have working ps-status
> > code?  (This is an honest question: I don't know.)
> 
> BeOS, DG/UX, and Cygwin don't have support code, the rest *should* work.

Seems we will find out when people complain my performance monitor
doesn't show the proper columns.  :-)

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Larry Rosenman
Date:
I don't believe that UnixWare will take the PS change without having 
ROOT.

LER

>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 3/8/01, 3:54:31 PM, Peter Eisentraut <peter_e@gmx.net> wrote regarding 
Re: [HACKERS] Performance monitor :


> Tom Lane writes:

> > How many of our supported platforms actually have working ps-status
> > code?  (This is an honest question: I don't know.)

> BeOS, DG/UX, and Cygwin don't have support code, the rest *should* work.

> --
> Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


Re: Performance monitor

From
Gunnar R|nning
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:

> 
> Seems we will find out when people complain my performance monitor
> doesn't show the proper columns.  :-)
> 

So what is the proper columns ? Or let me rephrase, what will your DBA be
able to monitor using the performance monitor ? 

Query stats, IO stats, cache hit/miss ratios ? 

As someone often recomending the database, I would like to have more
precise info about where the problem is when pgsql hits the roof - but this
might more into the auditing land, than straight performance
territory. Anyway it would be very nice to have tools that could help to
identify the database bottlenecks of your apps. I've already got some tools
on the Java side, but getting recorded data from the database side could
only help me analyze the system and blast bottlenecks.

regards, 
Gunnar


Re: Performance monitor

From
Bruce Momjian
Date:
> So what is the proper columns ? Or let me rephrase, what will your DBA be
> able to monitor using the performance monitor ? 
> 
> Query stats, IO stats, cache hit/miss ratios ? 

For starters, it will be more of a wrapper around ps.  In the future, I
hope to add more features.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
"Gordon A. Runkle"
Date:
In article <200103081735.MAA06567@candle.pha.pa.us>, "Bruce Momjian"
<pgman@candle.pha.pa.us> wrote:
> The problem I see with the shared memory idea is that some of the
> information needed may be quite large.  For example, query strings can
> be very long.  Do we just allocate 512 bytes and clip off the rest.  And
> as I add more info, I need more shared memory per backend.  I just liked
> the file system dump solution because I could modify it pretty easily,
> and because the info only appears when you click on the process, it
> doesn't happen often.
> 
> Of course, if we start getting the full display partly from each
> backend, we will have to use shared memory.

Long-term, perhaps a monitor server (like Sybase ASE uses) might 
be a reasonable approach.  That way, only one process (and a well-
regulated one at that) would be accessing the shared memory, which
should make it safer and have less of an impact performance-wise
if semaphores are needed to regulate access to the various regions
of shared memory.

Then, 1-N clients may access the monitor server to get performance
data w/o impacting the backends.

Gordon.
-- 
It doesn't get any easier, you just go faster.  -- Greg LeMond


Re: Performance monitor

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Patch or otherwise, this approach seems totally unworkable.  A signal
>> handler cannot do I/O safely, it cannot look at shared memory safely,
>> it cannot even look at the backend's own internal state safely.  How's
>> it going to do any useful status reporting?

> Why can't we do what we do with Cancel, where we set a flag and check it
> at safe places?

There's a lot of assumptions hidden in that phrase "safe places".
I don't think that everyplace we check for Cancel is going to be safe,
for example.  Cancel is able to operate in places where the internal
state isn't completely self-consistent, because it knows we are just
going to clean up and throw away intermediate status anyhow if the
cancel occurs.

Also, if you are expecting the answers to come back in a short amount of
time, then you do have to be able to do the work in the signal handler
in cases where the backend is blocked on a lock or something like that.
So that introduces a set of issues about how you know when it's
appropriate to do that and how to be sure that the signal handler
doesn't screw things up when it tries to do the report in-line.

All in all, I do not see this as an easy task that you can whip out and
then release as a 7.1 patch without extensive testing.  And given that,
I'd rather see it done with what I consider the right long-term approach,
rather than a dead-end hack.  I think doing it in a signal handler is
ultimately going to be a dead-end hack.
        regards, tom lane


Re: Performance monitor

From
Karl DeBisschop
Date:
On 2001.03.07 22:06 Bruce Momjian wrote:
> > I think Bruce wants per-backend data, and this approach would seem to
> only
> > get the data for the current backend. 
> > 
> > Also, I really don't like the proposal to write files to /tmp. If we
> want a
> > perf tool, then we need to have something like 'top', which will
> > continuously update. With 40 backends, the idea of writing 40 file to
> /tmp
> > every second seems a little excessive to me.
> 
> My idea was to use 'ps' to gather most of the information, and just use
> the internal stats when someone clicked on a backend and wanted more
> information.

My own experience is that parsing ps can be difficult if you want to be
portable and want more than basic information. Quite clearly, I could just
be dense, but if it helps, you can look at the configure.in in the CVS tree
at http://sourceforge.net/projects/netsaintplug (GPL, sorry. But if you
find anything worthwhile, and borrowing concepts results in similar code, I
won't complain).

I wouldn't be at all surprised if you found a better approach - my
configuration above, to my mind at least, is not pretty. I hope you do find
a better approach - I know I'll be peeking at your code to see. 

-- 
Karl



Re: Performance monitor

From
Denis Perchine
Date:
On Wednesday 07 March 2001 21:56, Bruce Momjian wrote:
> I have started coding a PostgreSQL performance monitor.  It will be like
> top, but allow you to click on a backend to see additional information.
>
> It will be written in Tcl/Tk.  I may ask to add something to 7.1 so when
> a backend receives a special signal, it dumps a file in /tmp with some
> backend status.  It would be done similar to how we handle Cancel
> signals.

Small question... Will it work in console? Or it will be X only?

-- 
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------


Re: Performance monitor

From
Bruce Momjian
Date:
[ Charset KOI8-R unsupported, converting... ]
> On Wednesday 07 March 2001 21:56, Bruce Momjian wrote:
> > I have started coding a PostgreSQL performance monitor.  It will be like
> > top, but allow you to click on a backend to see additional information.
> >
> > It will be written in Tcl/Tk.  I may ask to add something to 7.1 so when
> > a backend receives a special signal, it dumps a file in /tmp with some
> > backend status.  It would be done similar to how we handle Cancel
> > signals.
> 
> Small question... Will it work in console? Or it will be X only?

It will be tck/tk, so I guess X only.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Denis Perchine
Date:
> > Small question... Will it work in console? Or it will be X only?
>
> It will be tck/tk, so I guess X only.

That's bad. Cause it will be unuseful for people having databases far away...
Like me... :-((( Another point is that it is a little bit strange to have 
X-Window on machine with database server... At least if it is not for play, 
but production one...

Also there should be a possibility of remote monitoring of the database. But 
that's just dream... :-)))

-- 
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------


RE: Performance monitor

From
Mike Mascari
Date:
I don't want to look a gift horse in the mouth, but it seems to me that the 
performance monitor should wait until the now-famous query tree redesign 
which will allow for sets from functions. I realize that the shared memory 
requirements might be a bit large, but somehow Oracle accomplishes this 
nicely, with some > 50 views (V$ACCESS through V$WAITSTAT) which can be 
queried, usually via SQL*DBA, for performance statistics. More then 50 
performance views may be over-kill, but having the ability to fetch the 
performance statistics with normal queries sure is nice. Perhaps a 
postmaster option which would enable/disable the use of accumulating 
performance statistics in shared memory might ease the hesitation against 
it?

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From:    Denis Perchine [SMTP:dyp@perchine.com]

That's bad. Cause it will be unuseful for people having databases far 
away...
Like me... :-((( Another point is that it is a little bit strange to have
X-Window on machine with database server... At least if it is not for play, 
but production one...

Also there should be a possibility of remote monitoring of the database. 
But
that's just dream... :-)))

--
Sincerely Yours,
Denis Perchine



Re: Performance monitor

From
Gunnar R|nning
Date:
Denis Perchine <dyp@perchine.com> writes:

> > > Small question... Will it work in console? Or it will be X only?
> >
> > It will be tck/tk, so I guess X only.
> 
> That's bad. Cause it will be unuseful for people having databases far away...
> Like me... :-((( Another point is that it is a little bit strange to have 
> X-Window on machine with database server... At least if it is not for play, 
> but production one...

Well, I use X all the time over far distances - it depends on your
connection... And you are not running an X server on the database server, only
an X client.

But I agree that it would be nice to have monitoring architecture that
allowed a multitude of clients...

regards, 
Gunnar




Re: Performance monitor

From
Bruce Momjian
Date:
> > It will be tck/tk, so I guess X only.
> 
>  Good point. A typical DB server -- where is performance important -- 
> has install Xwin?
> 
>  BTW, I hate Oracle 8.x.x because has X+java based installer, but some
> my servers hasn't monitor and keyboard let alone to Xwin.
> 
>  What implement performance monitor as client/server application where
> client is some shared lib? This solution allows to create more clients
> for more differents GUI. I know... it's easy planning, but the other 
> thing is programming it  :-)

My idea is that they can telnet into the server machine and do remote-X
with the application.  Just set the DISPLAY variable and it should work.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Re: Performance monitor'

From
Bruce Momjian
Date:
> Denis Perchine <dyp@perchine.com> writes:
> 
> > > > Small question... Will it work in console? Or it will be X only?
> > >
> > > It will be tck/tk, so I guess X only.
> > 
> > That's bad. Cause it will be unuseful for people having databases far away...
> > Like me... :-((( Another point is that it is a little bit strange to have 
> > X-Window on machine with database server... At least if it is not for play, 
> > but production one...
> 
> Well, I use X all the time over far distances - it depends on your
> connection... And you are not running an X server on the database server, only
> an X client.

Yes, works fine.

> But I agree that it would be nice to have monitoring architecture that
> allowed a multitude of clients...

Right now, pgmonitor is just 'ps' with some gdb/kill actions.  If I add
anything to the backend, you can be sure I will make it so any app can
access it.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Gunnar R|nning
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:

> 
> My idea is that they can telnet into the server machine and do remote-X
> with the application.  Just set the DISPLAY variable and it should work.
> 

Well, actually you would want to tunnel your X session through ssh if
security of the database server is of any importance...  But this works
fine on high bandwidth connections, but X is a real pain if you are sitting
with low bandwidth(e.g. cellphone connection when you're in the middle of
nowhere on your favorite vacation ;-)

regards, 
Gunnar


Re: Performance monitor

From
Bruce Momjian
Date:
> I don't want to look a gift horse in the mouth, but it seems to me that the 
> performance monitor should wait until the now-famous query tree redesign 
> which will allow for sets from functions. I realize that the shared memory 
> requirements might be a bit large, but somehow Oracle accomplishes this 
> nicely, with some > 50 views (V$ACCESS through V$WAITSTAT) which can be 
> queried, usually via SQL*DBA, for performance statistics. More then 50 
> performance views may be over-kill, but having the ability to fetch the 
> performance statistics with normal queries sure is nice. Perhaps a 
> postmaster option which would enable/disable the use of accumulating 
> performance statistics in shared memory might ease the hesitation against 
> it?

I don't think query design is an issue here.  We can already create
views to do such things.  Right now, pgmonitor simply uses 'ps'. and
uses gdb to attach to the running process and show the query being
executed.  For 7.2, I hope to improve it.  I like the shared memory
ideas, and the ability to use a query rather than accessing shared
memory directly.

Seems we should have each backend store query/stat information in shared
memory, and create special views to access that information.  We can
restrict such views to the postgres super-user.


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Bruce Momjian
Date:
[ Charset KOI8-R unsupported, converting... ]
> > > Small question... Will it work in console? Or it will be X only?
> >
> > It will be tck/tk, so I guess X only.
> 
> That's bad. Cause it will be unuseful for people having databases far away...
> Like me... :-((( Another point is that it is a little bit strange to have 
> X-Window on machine with database server... At least if it is not for play, 
> but production one...
> 
> Also there should be a possibility of remote monitoring of the database. But 
> that's just dream... :-)))

What about remote-X using the DISPLAY variable?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> My idea is that they can telnet into the server machine and do remote-X
> with the application.  Just set the DISPLAY variable and it should work.

Remote X pretty well sucks in the real world.  Aside from speed issues
there is the little problem of firewalls filtering out X connections.

If you've got ssh running then you can tunnel the X connection through
the ssh connection, which fixes the firewall problem, but it makes the
speed problem worse.  And getting ssh plus X forwarding working is not
something I want to have to hassle with when my remote database is down.

If you are thinking of telnet-based remote admin then I suggest you get
out your curses man page and do up a curses GUI.  (No smiley... I'd
seriously prefer that to something that depends on remote X.)
        regards, tom lane


Re: Performance monitor

From
Tom Lane
Date:
Denis Perchine <dyp@perchine.com> writes:
>>> Small question... Will it work in console? Or it will be X only?
>> 
>> It will be tck/tk, so I guess X only.

> That's bad.

tcl/tk is cross-platform; there's no reason that a tcl-coded
performance monitor client couldn't run on Windows or Mac.

The real problem with the ps-based implementation that Bruce is
proposing is that it cannot work remotely at all, because there's
no way to get the ps data from another machine (unless you're
oldfashioned/foolish enough to be running a finger server that
allows remote ps).  This I think is the key reason why we'll
ultimately want to forget about ps and go to a shared-memory-based
arrangement for performance info.  That could support a client/server
architecture where the server is a backend process (or perhaps a
not-quite-backend process, but anyway attached to shared memory)
and the client is communicating with it over TCP.
        regards, tom lane


Re: Performance monitor

From
Bruce Momjian
Date:
> Denis Perchine <dyp@perchine.com> writes:
> >>> Small question... Will it work in console? Or it will be X only?
> >> 
> >> It will be tck/tk, so I guess X only.
> 
> > That's bad.
> 
> tcl/tk is cross-platform; there's no reason that a tcl-coded
> performance monitor client couldn't run on Windows or Mac.
> 
> The real problem with the ps-based implementation that Bruce is
> proposing is that it cannot work remotely at all, because there's
> no way to get the ps data from another machine (unless you're
> oldfashioned/foolish enough to be running a finger server that
> allows remote ps).  This I think is the key reason why we'll
> ultimately want to forget about ps and go to a shared-memory-based
> arrangement for performance info.  That could support a client/server
> architecture where the server is a backend process (or perhaps a
> not-quite-backend process, but anyway attached to shared memory)
> and the client is communicating with it over TCP.

Hard to say.  'ps' gives some great information about cpu/memory usage
that may be hard/costly to put in shared memory.  One idea should be to
issue periodic 'ps/kill' commands though a telnet/ssh pipe to the
remote machine, or just to the remote X display option.

Of course, getrusage() gives us much of that information.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Bruce Momjian
Date:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > My idea is that they can telnet into the server machine and do remote-X
> > with the application.  Just set the DISPLAY variable and it should work.
> 
> Remote X pretty well sucks in the real world.  Aside from speed issues
> there is the little problem of firewalls filtering out X connections.
> 
> If you've got ssh running then you can tunnel the X connection through
> the ssh connection, which fixes the firewall problem, but it makes the
> speed problem worse.  And getting ssh plus X forwarding working is not
> something I want to have to hassle with when my remote database is down.
> 
> If you are thinking of telnet-based remote admin then I suggest you get
> out your curses man page and do up a curses GUI.  (No smiley... I'd
> seriously prefer that to something that depends on remote X.)

Aren't there tools to allow tcl/tk on non-X displays.  I thought SCO had
something.

FYI, about the getrusage() idea, seems that only works for the current
process or it its children, so each backend would have to update its own
statistics.  Seems expensive compared to having 'ps do it.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Bruce Momjian
Date:
> Hard to say.  'ps' gives some great information about cpu/memory usage
> that may be hard/costly to put in shared memory.  One idea should be to
> issue periodic 'ps/kill' commands though a telnet/ssh pipe to the
> remote machine, or just to the remote X display option.
> 
> Of course, getrusage() gives us much of that information.

Forget getrusage().  Only works on current process, so each backend
would have to update its own statistics.  Sounds expensive.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: Performance monitor

From
Justin Clift
Date:
Hi Guys,

I'd just like to point out that for most secure installations, X is
removed from servers as part of the "remove all software which isn't
absolutely needed."

I know of Solaris machines which perform as servers with a total of 19
OS packages installed, and then precompiled binaries of the server
programs are loaded onto these machines.

Removal of all not-absolutely-necessary software iss also the
recommended procedure by Sun for setting up server platforms.

Having something based on X will be useable by lots of people, just not
by those who make the effort to take correct security precautions.

Regards and best wishes,

Justin Clift

Bruce Momjian wrote:
> 
> > > It will be tck/tk, so I guess X only.
> >
> >  Good point. A typical DB server -- where is performance important --
> > has install Xwin?
> >
> >  BTW, I hate Oracle 8.x.x because has X+java based installer, but some
> > my servers hasn't monitor and keyboard let alone to Xwin.
> >
> >  What implement performance monitor as client/server application where
> > client is some shared lib? This solution allows to create more clients
> > for more differents GUI. I know... it's easy planning, but the other
> > thing is programming it  :-)
> 
> My idea is that they can telnet into the server machine and do remote-X
> with the application.  Just set the DISPLAY variable and it should work.
> 
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org