Bruce Momjian wrote:
I have talked to Jan over the phone, and he has convinced me that UDP is
the proper way to communicate stats to the collector, rather than my
shared memory idea.
The advantages of his UDP approach is that the collector can sleep on
the UDP socket rather than having the
I have talked to Jan over the phone, and he has convinced me that UDP is
the proper way to communicate stats to the collector, rather than my
shared memory idea.
The advantages of his UDP approach is that the collector can sleep on
the UDP socket rather than having the collector poll the shared
Bruce Momjian [EMAIL PROTECTED] writes:
Only shared memory gives us near-zero cost for write/read. 99% of
backends will not be using stats, so it has to be cheap.
Not with a circular buffer it's not cheap, because you need interlocking
on writes. Your claim that you can get away without that
Bruce Momjian [EMAIL PROTECTED] writes:
Only shared memory gives us near-zero cost for write/read. 99% of
backends will not be using stats, so it has to be cheap.
Not with a circular buffer it's not cheap, because you need interlocking
on writes. Your claim that you can get away
I have a new statistics collection proposal.
I suggest three shared memory areas:
One per backend to hold the query string and other per-backend stats
One global area to hold accumulated stats for all backends
One global circular buffer to hold per-table/object stats
Bruce Momjian wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
Only shared memory gives us near-zero cost for write/read. 99% of
backends will not be using stats, so it has to be cheap.
Not with a circular buffer it's not cheap, because you need interlocking
on writes. Your claim
I figured it could just wake up every few seconds and check. It will
remember the loop counter and current pointer, and read any new
information. I was thinking of a 20k buffer, which could cover about 4k
events.
Here I wonder what your EVENT is. With an Oid as identifier
On Fri, Mar 16, 2001 at 05:25:24PM -0500, Jan Wieck wrote:
Jan Wieck wrote:
...
Just to get some evidence at hand - could some owners of
different platforms compile and run the attached little C
source please?
...
Seems Tom is (unfortunately) right. The pipe blocks
Jan Wieck [EMAIL PROTECTED] writes:
Just to get some evidence at hand - could some owners of
different platforms compile and run the attached little C
source please?
(The program tests how much data can be stuffed into a pipe
or a Sys-V message queue before the
At 13:49 16/03/01 -0500, Jan Wieck wrote:
Similar problem as with shared memory - size. If a long
running backend of a multithousand table database needs to
send access stats per table - and had accessed them all up to
now - it'll be alot of wasted bandwidth.
Not if you
Philip Warner wrote:
At 13:49 16/03/01 -0500, Jan Wieck wrote:
Similar problem as with shared memory - size. If a long
running backend of a multithousand table database needs to
send access stats per table - and had accessed them all up to
now - it'll be alot of
On Sat, Mar 17, 2001 at 09:33:03AM -0500, Jan Wieck wrote:
The general problem remains. We only have one central
collector with a limited receive capacity. The more load is
on the machine, the smaller it's capacity gets. The more
complex the DB schemas get
Samuel Sieb [EMAIL PROTECTED] writes:
Just as another suggestion, what about sending the data to a different
computer, so instead of tying up the database server with processing the
statistics, you have another computer that has some free time to do the
processing.
Some drawbacks are that
... and a lot more load on the CPU. Same-machine "network" connections
are much cheaper (on most kernels, anyway) than real network
connections.
I think all of this discussion is vast overkill. No one has yet
demonstrated that it's not sufficient to have *one* collector process
and a
Bruce Momjian [EMAIL PROTECTED] writes:
The only open issue is per-table stuff, and I would like to see some
circular buffer implemented to handle that, with a collection process
that has access to shared memory.
That will get us into locking/contention issues. OTOH, frequent trips
to the
Bruce Momjian [EMAIL PROTECTED] writes:
The only open issue is per-table stuff, and I would like to see some
circular buffer implemented to handle that, with a collection process
that has access to shared memory.
That will get us into locking/contention issues. OTOH, frequent trips
to
Bruce Momjian [EMAIL PROTECTED] writes:
Even better, have an SQL table updated with the per-table stats
periodically.
That will be horribly expensive, if it's a real table.
But per-table stats aren't something that people will look at often,
right? They can sit in the collector's memory
But per-table stats aren't something that people will look at often,
right? They can sit in the collector's memory for quite a while. See
people wanting to look at per-backend stuff frequently, and that is why
I thought share memory should be good, and a global area for aggregate
Tom Lane wrote:
Samuel Sieb [EMAIL PROTECTED] writes:
Just as another suggestion, what about sending the data to a different
computer, so instead of tying up the database server with processing the
statistics, you have another computer that has some free time to do the
processing.
Jan Wieck [EMAIL PROTECTED] writes:
Uh - not much time to spend if the statistics should at least
be half accurate. And it would become worse in SMP systems.
So that was a nifty idea, but I think it'd cause much more
statistic losses than I assumed at first.
Back to
At 17:10 15/03/01 -0800, Alfred Perlstein wrote:
Which is why the backends should not do anything other than maintain the
raw data. If there is atomic data than can cause inconsistency, then a
dropped UDP packet will do the same.
The UDP packet (a COPY) can contain a consistant snapshot of
Alfred Perlstein wrote:
* Jan Wieck [EMAIL PROTECTED] [010316 08:08] wrote:
Philip Warner wrote:
But I prefer the UDP/Collector model anyway; it gives use greater
flexibility + the ability to keep stats past backend termination, and,as
you say, removes any possible locking
Tom Lane wrote:
Jan Wieck [EMAIL PROTECTED] writes:
Uh - not much time to spend if the statistics should at least
be half accurate. And it would become worse in SMP systems.
So that was a nifty idea, but I think it'd cause much more
statistic losses than I assumed
Jan Wieck [EMAIL PROTECTED] writes:
Does a pipe guarantee that a buffer, written with one atomic
write(2), never can get intermixed with other data on the
readers end?
Yes. The HPUX man page for write(2) sez:
o Write requests of {PIPE_BUF} bytes or less will not
Tom Lane wrote:
Jan Wieck [EMAIL PROTECTED] writes:
Does a pipe guarantee that a buffer, written with one atomic
write(2), never can get intermixed with other data on the
readers end?
Yes. The HPUX man page for write(2) sez:
o Write requests of {PIPE_BUF}
Tom Lane wrote:
Now this would put a pretty tight time constraint on the collector:
fall more than 4K behind, you start losing data. I am not sure if
a UDP socket would provide more buffering or not; anyone know?
Looks like Linux has something around 16-32K of buffer space
for UDP
Jan Wieck wrote:
Tom Lane wrote:
Now this would put a pretty tight time constraint on the collector:
fall more than 4K behind, you start losing data. I am not sure if
a UDP socket would provide more buffering or not; anyone know?
Looks like Linux has something around 16-32K of
Jan Wieck [EMAIL PROTECTED] writes:
Just to get some evidence at hand - could some owners of
different platforms compile and run the attached little C
source please?
HPUX 10.20:
Pipe buffer is 8192 bytes
Sys-V message queue buffer is 16384 bytes
Just to get some evidence at hand - could some owners of
different platforms compile and run the attached little C
source please?
$ uname -srm
FreeBSD 4.1.1-STABLE
$ ./jan
Pipe buffer is 16384 bytes
Sys-V message queue buffer is 2048 bytes
$ uname -srm
NetBSD 1.5 alpha
* Jan Wieck [EMAIL PROTECTED] [010316 16:35]:
Jan Wieck wrote:
Tom Lane wrote:
Now this would put a pretty tight time constraint on the collector:
fall more than 4K behind, you start losing data. I am not sure if
a UDP socket would provide more buffering or not; anyone know?
* Larry Rosenman [EMAIL PROTECTED] [010316 20:47]:
* Jan Wieck [EMAIL PROTECTED] [010316 16:35]:
$ ./queuetest
Pipe buffer is 32768 bytes
Sys-V message queue buffer is 4096 bytes
$ uname -a
UnixWare lerami 5 7.1.1 i386 x86at SCO UNIX_SVR5
$
I think some of these are configurable...
Jan Wieck [EMAIL PROTECTED] writes:
What about a collector deamon, fired up by the postmaster and
receiving UDP packets from the backends. Under heavy load, it
might miss some statistic messages, well, but that's not as
bad as having locks causing backends to loose
Tom Lane wrote:
Jan Wieck [EMAIL PROTECTED] writes:
What about a collector deamon, fired up by the postmaster and
receiving UDP packets from the backends. Under heavy load, it
might miss some statistic messages, well, but that's not as
bad as having locks causing
* Philip Warner [EMAIL PROTECTED] [010315 16:14] wrote:
At 06:57 15/03/01 -0500, Jan Wieck wrote:
And shared memory has all the interlocking problems we want
to avoid.
I suspect that if we keep per-backend data in a separate area, then we
don;t need locking since there is only
At 06:57 15/03/01 -0500, Jan Wieck wrote:
And shared memory has all the interlocking problems we want
to avoid.
I suspect that if we keep per-backend data in a separate area, then we
don;t need locking since there is only one writer. It does not matter if a
reader gets an inconsistent
At 16:17 15/03/01 -0800, Alfred Perlstein wrote:
Lost data is probably better than incorrect data. Either use locks
or a copying mechanism. People will depend on the data returned
making sense.
But with per-backend data, there is only ever *one* writer to a given set
of counters. Everyone
* Philip Warner [EMAIL PROTECTED] [010315 16:46] wrote:
At 16:17 15/03/01 -0800, Alfred Perlstein wrote:
Lost data is probably better than incorrect data. Either use locks
or a copying mechanism. People will depend on the data returned
making sense.
But with per-backend data, there
* Philip Warner [EMAIL PROTECTED] [010315 17:08] wrote:
At 16:55 15/03/01 -0800, Alfred Perlstein wrote:
* Philip Warner [EMAIL PROTECTED] [010315 16:46] wrote:
At 16:17 15/03/01 -0800, Alfred Perlstein wrote:
Lost data is probably better than incorrect data. Either use locks
or a
At 16:55 15/03/01 -0800, Alfred Perlstein wrote:
* Philip Warner [EMAIL PROTECTED] [010315 16:46] wrote:
At 16:17 15/03/01 -0800, Alfred Perlstein wrote:
Lost data is probably better than incorrect data. Either use locks
or a copying mechanism. People will depend on the data returned
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
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
* Philip Warner [EMAIL PROTECTED] [010312 18:56] wrote:
At 13:34 12/03/01 -0800, Alfred Perlstein wrote:
Is it possible
to have a spinlock over it so that an external utility can take a snapshot
of it with the spinlock held?
I'd suggest that locking the stats area might be a bad idea;
This ought to always give a consistant snapshot of the file to
whomever opens it.
I think Tom has previously stated that there are technical reasons not to
do IO in signal handlers, and I have philosophical problems with
performance monitors that ask 50 backends to do file IO. I really
This ought to always give a consistant snapshot of the file to
whomever opens it.
I think Tom has previously stated that there are technical reasons not to
do IO in signal handlers, and I have philosophical problems with
performance monitors that ask 50 backends to do file IO. I really do
* Philip Warner [EMAIL PROTECTED] [010313 06:42] wrote:
This ought to always give a consistant snapshot of the file to
whomever opens it.
I think Tom has previously stated that there are technical reasons not to
do IO in signal handlers, and I have philosophical problems with
At 13:34 12/03/01 -0800, Alfred Perlstein wrote:
Is it possible
to have a spinlock over it so that an external utility can take a snapshot
of it with the spinlock held?
I'd suggest that locking the stats area might be a bad idea; there is only
one writer for each backend-specific chunk,
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
[ 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
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()
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
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
Bruce Momjian [EMAIL PROTECTED] 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
I was going to implement the signal handler like we do with Cancel,
where the signal sets a flag and we check the status of the flag in
various _safe_ places.
Can anyone think of a better way to get information out of a backend?
--
Bruce Momjian|
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
[ 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
* Bruce Momjian [EMAIL PROTECTED] [010312 12:12] wrote:
I was going to implement the signal handler like we do with Cancel,
where the signal sets a flag and we check the status of the flag in
various _safe_ places.
Can anyone think of a better way to get information out of a backend?
Why
At 13:34 12/03/01 -0800, Alfred Perlstein wrote:
Is it possible
to have a spinlock over it so that an external utility can take a snapshot
of it with the spinlock held?
I'd suggest that locking the stats area might be a bad idea; there is only
one writer for each backend-specific chunk, and it
I have renamed pgtop.tcl to pgmonitor. I think the new name is clearer.
ftp://candle.pha.pa.us/pub/postgresql/pgmonitor
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, |
In article [EMAIL PROTECTED], "Bruce Momjian"
[EMAIL PROTECTED] 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
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
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
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 [EMAIL PROTECTED] wrote regarding
Re: [HACKERS] Performance monitor :
Tom Lane writes:
How many of our supported platforms actually have working ps
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
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 Hermit Hacker [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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
Bruce Momjian [EMAIL PROTECTED] 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
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.
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
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
.
FWIW,
Mike Mascari
[EMAIL PROTECTED]
-Original Message-
From: Philip Warner [SMTP:[EMAIL PROTECTED]]
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 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
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
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
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,
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
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|
77 matches
Mail list logo