Thread: Performance monitor
I have started coding a PostgreSQL performance monitor. It will be like top, but allow you to click on a backend to see additional information. It will be written in Tcl/Tk. I may ask to add something to 7.1 so when a backend receives a special signal, it dumps a file in /tmp with some backend status. It would be done similar to how we handle Cancel signals. How do people feel about adding a single handler to 7.1? Is it something I can slip into the current CVS, or will it have to exist as a patch to 7.1. Seems it would be pretty isolated unless someone sends the signal, but it is clearly a feature addition. We don't really have any way of doing process monitoring except ps, so I think this is needed. I plan to have something done in the next week or two. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
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, Pennsylvania19026
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, Pennsylvania19026
> All in all, I do not see this as an easy task that you can whip out and > then release as a 7.1 patch without extensive testing. And given that, > I'd rather see it done with what I consider the right long-term approach, > rather than a dead-end hack. I think doing it in a signal handler is > ultimately going to be a dead-end hack. Well, the signal stuff will get me going at least. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
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 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
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, Pennsylvania19026
> At 18:05 7/03/01 -0500, Bruce Momjian wrote: > >> All in all, I do not see this as an easy task that you can whip out and > >> then release as a 7.1 patch without extensive testing. And given that, > >> I'd rather see it done with what I consider the right long-term approach, > >> rather than a dead-end hack. I think doing it in a signal handler is > >> ultimately going to be a dead-end hack. > > > >Well, the signal stuff will get me going at least. > > Didn't someone say this can't be done safely - or am I missing something? OK, I will write just the all-process display part, that doesn't need any per-backend info because it gets it all from 'ps'. Then maybe someone will come up with a nifty idea, or I will play with my local copy to see how it can be done. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
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
Yes, seems that is best. I will probably hack something up here so I can do some testing of the app itself. > Mike Mascari's idea (er... his assembling of the other ideas) still > sounds like the Best Solution though. > > :-) > > + Justin > > +++ > > I like the idea of updating shared memory with the performance > statistics, > current query execution information, etc., providing a function to fetch > those statistics, and perhaps providing a system view (i.e. > pg_performance) > based upon such functions which can be queried by the administrator. > > FWIW, > > Mike Mascari > mascarm@mascari.com > > +++ > > Bruce Momjian wrote: > > > > > I think Bruce wants per-backend data, and this approach would seem to only > > > get the data for the current backend. > > > > > > Also, I really don't like the proposal to write files to /tmp. If we want a > > > perf tool, then we need to have something like 'top', which will > > > continuously update. With 40 backends, the idea of writing 40 file to /tmp > > > every second seems a little excessive to me. > > > > My idea was to use 'ps' to gather most of the information, and just use > > the internal stats when someone clicked on a backend and wanted more > > information. > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> I wouldn't be at all surprised if you found a better approach - my > configuration above, to my mind at least, is not pretty. I hope you do find > a better approach - I know I'll be peeking at your code to see. Yes, I have an idea and hope it works. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
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
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, Pennsylvania19026
> On Wed, Mar 07, 2001 at 10:06:38PM -0500, Bruce Momjian wrote: > > > I think Bruce wants per-backend data, and this approach would seem to only > > > get the data for the current backend. > > > > > > Also, I really don't like the proposal to write files to /tmp. If we want a > > > perf tool, then we need to have something like 'top', which will > > > continuously update. With 40 backends, the idea of writing 40 file to /tmp > > > every second seems a little excessive to me. > > > > My idea was to use 'ps' to gather most of the information, and just use > > the internal stats when someone clicked on a backend and wanted more > > information. > > Are you sure about 'ps' stuff portability? I don't known how data you > want read from 'ps', but /proc utils are very OS specific and for example > on Linux within a few years was libproc several time overhauled. > I spent several years with /proc stuff (processes manager: > http://home.zf.jcu.cz/~zakkr/kim). I am not going to do a huge amount with the actual ps columns, except allow you to sort on them. I will do most on the ps status display we use as the command line in ps. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian writes: ...> The problem I see with the shared memory idea is that some of the> information needed may be quite large. For example,query strings can> be very long. Do we just allocate 512 bytes and clip off the rest. And> as I add more info,I need more shared memory per backend. I just liked> the file system dump solution because I could modify it prettyeasily,> and because the info only appears when you click on the process, it> doesn't happen often.> Have you thought about using a named pipe? They've been around for quite a while, and should (he said with a :-)) be available on most-if-not-all currently supported systems. -- Richard Kuhns rjk@grauel.com PO Box 6249 Tel: (765)477-6000 \ 100 Sawmill Road x319 Lafayette, IN 47903 (800)489-4891 /
> Bruce Momjian writes: > ... > > The problem I see with the shared memory idea is that some of the > > information needed may be quite large. For example, query strings can > > be very long. Do we just allocate 512 bytes and clip off the rest. And > > as I add more info, I need more shared memory per backend. I just liked > > the file system dump solution because I could modify it pretty easily, > > and because the info only appears when you click on the process, it > > doesn't happen often. > > > Have you thought about using a named pipe? They've been around for quite a > while, and should (he said with a :-)) be available on most-if-not-all > currently supported systems. Nifty idea. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
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, Pennsylvania19026
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
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, Pennsylvania19026
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
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
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
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 ----------------------------------
[ Charset KOI8-R unsupported, converting... ] > On Wednesday 07 March 2001 21:56, Bruce Momjian wrote: > > I have started coding a PostgreSQL performance monitor. It will be like > > top, but allow you to click on a backend to see additional information. > > > > It will be written in Tcl/Tk. I may ask to add something to 7.1 so when > > a backend receives a special signal, it dumps a file in /tmp with some > > backend status. It would be done similar to how we handle Cancel > > signals. > > Small question... Will it work in console? Or it will be X only? It will be tck/tk, so I guess X only. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > 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 ----------------------------------
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
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
> > It will be tck/tk, so I guess X only. > > Good point. A typical DB server -- where is performance important -- > has install Xwin? > > BTW, I hate Oracle 8.x.x because has X+java based installer, but some > my servers hasn't monitor and keyboard let alone to Xwin. > > What implement performance monitor as client/server application where > client is some shared lib? This solution allows to create more clients > for more differents GUI. I know... it's easy planning, but the other > thing is programming it :-) My idea is that they can telnet into the server machine and do remote-X with the application. Just set the DISPLAY variable and it should work. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Denis Perchine <dyp@perchine.com> writes: > > > > > Small question... Will it work in console? Or it will be X only? > > > > > > It will be tck/tk, so I guess X only. > > > > That's bad. Cause it will be unuseful for people having databases far away... > > Like me... :-((( Another point is that it is a little bit strange to have > > X-Window on machine with database server... At least if it is not for play, > > but production one... > > Well, I use X all the time over far distances - it depends on your > connection... And you are not running an X server on the database server, only > an X client. Yes, works fine. > But I agree that it would be nice to have monitoring architecture that > allowed a multitude of clients... Right now, pgmonitor is just 'ps' with some gdb/kill actions. If I add anything to the backend, you can be sure I will make it so any app can access it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
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
> I don't want to look a gift horse in the mouth, but it seems to me that the > performance monitor should wait until the now-famous query tree redesign > which will allow for sets from functions. I realize that the shared memory > requirements might be a bit large, but somehow Oracle accomplishes this > nicely, with some > 50 views (V$ACCESS through V$WAITSTAT) which can be > queried, usually via SQL*DBA, for performance statistics. More then 50 > performance views may be over-kill, but having the ability to fetch the > performance statistics with normal queries sure is nice. Perhaps a > postmaster option which would enable/disable the use of accumulating > performance statistics in shared memory might ease the hesitation against > it? I don't think query design is an issue here. We can already create views to do such things. Right now, pgmonitor simply uses 'ps'. and uses gdb to attach to the running process and show the query being executed. For 7.2, I hope to improve it. I like the shared memory ideas, and the ability to use a query rather than accessing shared memory directly. Seems we should have each backend store query/stat information in shared memory, and create special views to access that information. We can restrict such views to the postgres super-user. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
[ Charset KOI8-R unsupported, converting... ] > > > Small question... Will it work in console? Or it will be X only? > > > > It will be tck/tk, so I guess X only. > > That's bad. Cause it will be unuseful for people having databases far away... > Like me... :-((( Another point is that it is a little bit strange to have > X-Window on machine with database server... At least if it is not for play, > but production one... > > Also there should be a possibility of remote monitoring of the database. But > that's just dream... :-))) What about remote-X using the DISPLAY variable? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
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
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
> Denis Perchine <dyp@perchine.com> writes: > >>> Small question... Will it work in console? Or it will be X only? > >> > >> It will be tck/tk, so I guess X only. > > > That's bad. > > tcl/tk is cross-platform; there's no reason that a tcl-coded > performance monitor client couldn't run on Windows or Mac. > > The real problem with the ps-based implementation that Bruce is > proposing is that it cannot work remotely at all, because there's > no way to get the ps data from another machine (unless you're > oldfashioned/foolish enough to be running a finger server that > allows remote ps). This I think is the key reason why we'll > ultimately want to forget about ps and go to a shared-memory-based > arrangement for performance info. That could support a client/server > architecture where the server is a backend process (or perhaps a > not-quite-backend process, but anyway attached to shared memory) > and the client is communicating with it over TCP. Hard to say. 'ps' gives some great information about cpu/memory usage that may be hard/costly to put in shared memory. One idea should be to issue periodic 'ps/kill' commands though a telnet/ssh pipe to the remote machine, or just to the remote X display option. Of course, getrusage() gives us much of that information. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > My idea is that they can telnet into the server machine and do remote-X > > with the application. Just set the DISPLAY variable and it should work. > > Remote X pretty well sucks in the real world. Aside from speed issues > there is the little problem of firewalls filtering out X connections. > > If you've got ssh running then you can tunnel the X connection through > the ssh connection, which fixes the firewall problem, but it makes the > speed problem worse. And getting ssh plus X forwarding working is not > something I want to have to hassle with when my remote database is down. > > If you are thinking of telnet-based remote admin then I suggest you get > out your curses man page and do up a curses GUI. (No smiley... I'd > seriously prefer that to something that depends on remote X.) Aren't there tools to allow tcl/tk on non-X displays. I thought SCO had something. FYI, about the getrusage() idea, seems that only works for the current process or it its children, so each backend would have to update its own statistics. Seems expensive compared to having 'ps do it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Hard to say. 'ps' gives some great information about cpu/memory usage > that may be hard/costly to put in shared memory. One idea should be to > issue periodic 'ps/kill' commands though a telnet/ssh pipe to the > remote machine, or just to the remote X display option. > > Of course, getrusage() gives us much of that information. Forget getrusage(). Only works on current process, so each backend would have to update its own statistics. Sounds expensive. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Hi Guys, I'd just like to point out that for most secure installations, X is removed from servers as part of the "remove all software which isn't absolutely needed." I know of Solaris machines which perform as servers with a total of 19 OS packages installed, and then precompiled binaries of the server programs are loaded onto these machines. Removal of all not-absolutely-necessary software iss also the recommended procedure by Sun for setting up server platforms. Having something based on X will be useable by lots of people, just not by those who make the effort to take correct security precautions. Regards and best wishes, Justin Clift Bruce Momjian wrote: > > > > It will be tck/tk, so I guess X only. > > > > Good point. A typical DB server -- where is performance important -- > > has install Xwin? > > > > BTW, I hate Oracle 8.x.x because has X+java based installer, but some > > my servers hasn't monitor and keyboard let alone to Xwin. > > > > What implement performance monitor as client/server application where > > client is some shared lib? This solution allows to create more clients > > for more differents GUI. I know... it's easy planning, but the other > > thing is programming it :-) > > My idea is that they can telnet into the server machine and do remote-X > with the application. Just set the DISPLAY variable and it should work. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org