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

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

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

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

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

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

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

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

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

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

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

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

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

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:

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

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

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

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

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

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

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

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

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 /

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

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/

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

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 :

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

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

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

[ 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

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

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

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

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

[ 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

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

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

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

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

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

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

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

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

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: