Thread

Performance monitor

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-07T15:56:33Z

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

Re: Performance monitor

Marc G. Fournier <scrappy@hub.org> — 2001-03-07T18:44:24Z

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-07T21:41:00Z

> > 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

Re: Performance monitor

Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-07T21:42:05Z

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

Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-07T22:30:08Z

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-07T22:42:05Z

> 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

Re: Performance monitor

Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-07T23:02:55Z

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-07T23:05:47Z

> 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

Re: Performance monitor

Philip Warner <pjw@rhyme.com.au> — 2001-03-07T23:51:41Z

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

Justin Clift <aa2@bigpond.net.au> — 2001-03-08T00:33:45Z

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

Philip Warner <pjw@rhyme.com.au> — 2001-03-08T00:42:28Z

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-08T03:06:38Z

> 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

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-08T03:12:16Z

> 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

Re: Performance monitor

Justin Clift <aa2@bigpond.net.au> — 2001-03-08T03:14:40Z

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-08T03:17:51Z

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

Re: Performance monitor

Karl DeBisschop <karl@debisschop.net> — 2001-03-08T03:32:48Z

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-08T03:34:39Z

> 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

Re: Performance monitor

Karel Zak <zakkr@zf.jcu.cz> — 2001-03-08T10:26:02Z

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

Denis Perchine <dyp@perchine.com> — 2001-03-08T10:52:44Z

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-08T17:18:01Z

> 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

Re: Performance monitor

Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-08T17:25:39Z

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-08T17:35:54Z

> 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

Re: Performance monitor

Richard Kuhns <rjk@grauel.com> — 2001-03-08T18:44:05Z

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 /

Re: Performance monitor

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-08T19:46:03Z

> 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

Re: Performance monitor

Peter Eisentraut <peter_e@gmx.net> — 2001-03-08T21:54:31Z

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-08T22:06:13Z

> 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

Re: Performance monitor

Larry Rosenman <ler@lerctr.org> — 2001-03-08T22:25:06Z

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

Gunnar R|nning <gunnar@candleweb.no> — 2001-03-09T04:05:37Z

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-09T04:13:29Z

> 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

Re: Performance monitor

Gordon Runkle <gar@integrated-dynamics.com> — 2001-03-10T01:29:29Z

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-12T22:08:24Z

[ 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

Re: Performance monitor

Denis Perchine <dyp@perchine.com> — 2001-03-13T10:06:48Z

> > 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

Gunnar R|nning <gunnar@candleweb.no> — 2001-03-13T10:56:05Z

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

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-13T14:57:42Z

[ 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

Re: Performance monitor

Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-13T15:19:20Z

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: Re: Performance monitor'

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-13T15:49:29Z

> 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

Re: Performance monitor

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-13T15:56:58Z

> 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

Re: Performance monitor

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-13T16:37:40Z

> 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