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, Pennsylvania 19026

Wed, 7 Mar 2001 10:56:33 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Performance monitor
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" ...




Wed, 7 Mar 2001 14:44:24 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: Performance monitor
> > 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, Pennsylvania 19026

Wed, 7 Mar 2001 16:41:00 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor
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

Wed, 07 Mar 2001 16:42:05 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Performance monitor 
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

Wed, 07 Mar 2001 17:30:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Performance monitor 
> 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, Pennsylvania 19026

Wed, 7 Mar 2001 17:42:05 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor
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

Wed, 07 Mar 2001 18:02:55 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Performance monitor 
> 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, Pennsylvania 19026

Wed, 7 Mar 2001 18:05:47 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor
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   |/

Thu, 08 Mar 2001 10:51:41 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Performance monitor
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

Thu, 08 Mar 2001 11:33:45 +1100
Justin Clift <aa2@bigpond.net.au>
Re: 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   |/

Thu, 08 Mar 2001 11:42:28 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Performance monitor
> 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

Wed, 7 Mar 2001 22:06:38 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor
> 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, Pennsylvania 19026

Wed, 7 Mar 2001 22:12:16 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor
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

Thu, 08 Mar 2001 14:14:40 +1100
Justin Clift <aa2@bigpond.net.au>
Re: Performance monitor
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, Pennsylvania 19026

Wed, 7 Mar 2001 22:17:51 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor

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


Wed, 7 Mar 2001 22:32:48 -0500
Karl DeBisschop <karl@debisschop.net>
Re: Performance monitor
> 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, Pennsylvania 19026

Wed, 7 Mar 2001 22:34:39 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor
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

Thu, 8 Mar 2001 11:26:02 +0100
Karel Zak <zakkr@zf.jcu.cz>
Re: Performance monitor
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
----------------------------------

Thu, 8 Mar 2001 16:52:44 +0600
Denis Perchine <dyp@perchine.com>
Re: Performance monitor
> 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, Pennsylvania 19026

Thu, 8 Mar 2001 12:18:01 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor
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

Thu, 08 Mar 2001 12:25:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Performance monitor 
> 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, Pennsylvania 19026

Thu, 8 Mar 2001 12:35:54 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor
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.
-- 
Richard Kuhns			rjk@grauel.com
PO Box 6249			Tel: (765)477-6000 \
100 Sawmill Road				    x319
Lafayette, IN  47903		     (800)489-4891 /

Thu, 8 Mar 2001 13:44:05 -0500
Richard J Kuhns <rjk@grauel.com>
Re: Performance monitor
> 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, Pennsylvania 19026

Thu, 8 Mar 2001 14:46:03 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 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/


Thu, 8 Mar 2001 22:54:31 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: 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.

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, Pennsylvania 19026

Thu, 8 Mar 2001 17:06:13 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor

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

Thu, 08 Mar 2001 22:25:06 GMT
Larry Rosenman <ler@lerctr.org>
Re: Performance monitor 
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

09 Mar 2001 05:05:37 +0100
Gunnar R|nning <gunnar@candleweb.no>
Re: Performance monitor
> 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, Pennsylvania 19026

Thu, 8 Mar 2001 23:13:29 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor
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

Fri, 09 Mar 2001 20:29:29 -0500
"Gordon A. Runkle" <gar@integrated-dynamics.com>
Re: Performance monitor
[ 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, Pennsylvania 19026

Mon, 12 Mar 2001 17:08:24 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor
> > 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
----------------------------------

Tue, 13 Mar 2001 16:06:48 +0600
Denis Perchine <dyp@perchine.com>
Re: Performance monitor
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



13 Mar 2001 11:56:05 +0100
Gunnar R|nning <gunnar@candleweb.no>
Re: Performance monitor
[ 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, Pennsylvania 19026

Tue, 13 Mar 2001 09:57:42 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor
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

Tue, 13 Mar 2001 10:19:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Performance monitor 
> 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, Pennsylvania 19026

Tue, 13 Mar 2001 10:49:29 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: Performance monitor'
> 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, Pennsylvania 19026

Tue, 13 Mar 2001 10:56:58 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor
> 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, Pennsylvania 19026

Tue, 13 Mar 2001 11:37:40 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor