Thread

Performance monitor signal handler

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

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                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Performance monitor signal handler

Alfred Perlstein <bright@wintelcom.net> — 2001-03-12T21:34:00Z

* Bruce Momjian <pgman@candle.pha.pa.us> [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 not use a static area of the shared memory segment?  Is it possible
to have a spinlock over it so that an external utility can take a snapshot
of it with the spinlock held?

Also, this could work for other stuff as well, instead of overloading
a lot of signal handlers one could just periodically poll a region of
the shared segment.

just some ideas..

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
Daemon News Magazine in your snail-mail! http://magazine.daemonnews.org/

Re: Performance monitor signal handler

Philip Warner <pjw@rhyme.com.au> — 2001-03-13T02:22:37Z

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 won't matter a hell of a
lot if a reader gets inconsistent views (since I assume they will be
re-reading every second or so). All the stats area should contain would be
a bunch of counters with timestamps, I think, and the cost up writing to it
should be kept to an absolute minimum.

>
>just some ideas..
>

Unfortunatley, based on prior discussions, Bruce seems quite opposed to a
shared memory solution.

----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: Performance monitor signal handler

Alfred Perlstein <bright@wintelcom.net> — 2001-03-13T14:38:19Z

* Philip Warner <pjw@rhyme.com.au> [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; there is only
> one writer for each backend-specific chunk, and it won't matter a hell of a
> lot if a reader gets inconsistent views (since I assume they will be
> re-reading every second or so). All the stats area should contain would be
> a bunch of counters with timestamps, I think, and the cost up writing to it
> should be kept to an absolute minimum.
> 
> 
> >
> >just some ideas..
> >
> 
> Unfortunatley, based on prior discussions, Bruce seems quite opposed to a
> shared memory solution.

Ok, here's another nifty idea.

On reciept of the info signal, the backends collaborate to piece
together a status file.  The status file is given a temporay name.
When complete the status file is rename(2)'d over a well known
file.

This ought to always give a consistant snapshot of the file to
whomever opens it.

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
Daemon News Magazine in your snail-mail! http://magazine.daemonnews.org/

Re: Performance monitor signal handler

Philip Warner <pjw@rhyme.com.au> — 2001-03-13T14:42:18Z

>
>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 think
shared memory is TWTG.




----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: Performance monitor signal handler

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

> 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 won't matter a hell of a
> lot if a reader gets inconsistent views (since I assume they will be
> re-reading every second or so). All the stats area should contain would be
> a bunch of counters with timestamps, I think, and the cost up writing to it
> should be kept to an absolute minimum.
> 
> 
> >
> >just some ideas..
> >
> 
> Unfortunatley, based on prior discussions, Bruce seems quite opposed to a
> shared memory solution.

No, I like the shared memory idea.  Such an idea will have to wait for
7.2, and second, there are limits to how much shared memory I can use. 

Eventually, I think shared memory will be the way to go.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Performance monitor signal handler

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

> >
> >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 think
> shared memory is TWTG.

The good news is that right now pgmonitor gets all its information from
'ps', and only shows the query when the user asks for it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Performance monitor signal handler

Alfred Perlstein <bright@wintelcom.net> — 2001-03-13T14:59:48Z

* Philip Warner <pjw@rhyme.com.au> [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
> performance monitors that ask 50 backends to do file IO. I really do think
> shared memory is TWTG.

I wasn't really suggesting any of those courses of action, all I
suggested was using rename(2) to give a seperate appilcation a
consistant snapshot of the stats.

Actually, what makes the most sense (although it may be a performance
killer) is to have the backends update a system table that the external
app can query.

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
Daemon News Magazine in your snail-mail! http://magazine.daemonnews.org/

Re: Performance monitor signal handler

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

> > 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 think
> > shared memory is TWTG.
> 
> I wasn't really suggesting any of those courses of action, all I
> suggested was using rename(2) to give a seperate appilcation a
> consistant snapshot of the stats.
> 
> Actually, what makes the most sense (although it may be a performance
> killer) is to have the backends update a system table that the external
> app can query.

Yes, it seems storing info in shared memory and having a system table to
access it is the way to go.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Performance monitor signal handler

Thomas Swan <tswan-lst@ics.olemiss.edu> — 2001-03-13T21:36:59Z

>On reciept of the info signal, the backends collaborate to piece
>together a status file.  The status file is given a temporay name.
>When complete the status file is rename(2)'d over a well known
>file.

Reporting to files, particularly well known ones, could lead to race 
conditions.

All in all, I think your better off passing messages through pipes or a 
similar communication method.

I really liked the idea of a "server" that could parse/analyze data from 
multiple backends.

My 2/100 worth...

Re: Performance monitor signal handler

Alfred Perlstein <bright@wintelcom.net> — 2001-03-13T21:46:25Z

* Thomas Swan <tswan-lst@ics.olemiss.edu> [010313 13:37] wrote:
> 
> >On reciept of the info signal, the backends collaborate to piece
> >together a status file.  The status file is given a temporay name.
> >When complete the status file is rename(2)'d over a well known
> >file.
> 
> Reporting to files, particularly well known ones, could lead to race 
> conditions.
> 
> All in all, I think your better off passing messages through pipes or a 
> similar communication method.
> 
> I really liked the idea of a "server" that could parse/analyze data from 
> multiple backends.
> 
> My 2/100 worth...

Take a few moments to think about the semantics of rename(2).

Yes, you would still need syncronization between the backend
processes to do this correctly, but not any external app.

The external app can just open the file, assuming it exists it
will always have a complete and consistant snapshot of whatever
the backends agreed on.

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
Daemon News Magazine in your snail-mail! http://magazine.daemonnews.org/

Re: Performance monitor signal handler

Jan Wieck <janwieck@yahoo.com> — 2001-03-15T11:57:49Z

Bruce Momjian wrote:
>
> Yes, it seems storing info in shared memory and having a system table to
> access it is the way to go.

    Depends,

    first  of all we need to know WHAT we want to collect.  If we
    talk about block read/write statistics  and  such  on  a  per
    table  base, which is IMHO the most accurate thing for tuning
    purposes, then we're talking about an infinite size of shared
    memory perhaps.

    And  shared  memory has all the interlocking problems we want
    to avoid.

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

    The  postmaster  could already provide the UDP socket for the
    backends, so the collector can know  the  peer  address  from
    which  to  accept  statistics messages only. Any message from
    another peer address is  simply  ignored.   For  getting  the
    statistics  out  of  it,  the  collector  has  his own server
    socket, using TCP and providing some lookup protocol.

    Now whatever the backend has to tell the collector, it simply
    throws  a UDP packet into his direction. If the collector can
    catch it or not, not the backends problem.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Re: Performance monitor signal handler

Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-15T15:47:59Z

Jan Wieck <janwieck@Yahoo.com> 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 performance.

Interesting thought, but we don't want UDP I think; that just opens
up a whole can of worms about checking access permissions and so forth.
Why not a simple pipe?  The postmaster creates the pipe and the
collector daemon inherits one end, while all the backends inherit the
other end.

			regards, tom lane

Re: Performance monitor signal handler

Philip Warner <pjw@rhyme.com.au> — 2001-03-16T00:13:40Z

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 view, the same as if you drop a few UDP packets.

>    What about a collector deamon, fired up by the postmaster and
>    receiving UDP packets from the backends. 

This does sound appealing; it means that individual backend data (IO etc)
will survive past the termination of the backend. I'd like to see the stats
survive the death of the collector if possible, possibly even survive a
stop/start of the postmaster.

>    Now whatever the backend has to tell the collector, it simply
>    throws  a UDP packet into his direction. If the collector can
>    catch it or not, not the backends problem.

If we get the backends to keep the stats they are sending in local counters
as well, then they can send the counter value (not delta) each time, which
would mean that the collector would not 'miss' anything - just it's
operations/sec might see a hiccough. This could have a sidebenefit that(if
wewanted to?) we could allow a client to query their own counters to get an
idea of the costs of their queries.

When we need to reset the counters that should be done explicitly, I think.

----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: Performance monitor signal handler

Alfred Perlstein <bright@wintelcom.net> — 2001-03-16T00:17:10Z

* Philip Warner <pjw@rhyme.com.au> [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 one writer. It does not matter if a
> reader gets an inconsistent view, the same as if you drop a few UDP packets.

No, this is completely different.

Lost data is probably better than incorrect data.  Either use locks
or a copying mechanism.  People will depend on the data returned
making sense.

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]

Re: Performance monitor signal handler

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

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 else is a reader.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: Performance monitor signal handler

Alfred Perlstein <bright@wintelcom.net> — 2001-03-16T00:55:19Z

* Philip Warner <pjw@rhyme.com.au> [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 is only ever *one* writer to a given set
> of counters. Everyone else is a reader.

This doesn't prevent a reader from getting an inconsistant view.

Think about a 64bit counter on a 32bit machine.  If you charged per
megabyte, wouldn't it upset you to have a small chance of loosing
4 billion units of sale?

(ie, doing a read after an addition that wraps the low 32 bits
but before the carry is done to the top most signifigant 32bits?)

Ok, what what if everything can be read atomically by itself?

You're still busted the minute you need to export any sort of
compound stat.

If A, B and C need to add up to 100 you have a read race.

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]

Re: Performance monitor signal handler

Philip Warner <pjw@rhyme.com.au> — 2001-03-16T01:08:01Z

At 16:55 15/03/01 -0800, Alfred Perlstein wrote:
>* Philip Warner <pjw@rhyme.com.au> [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 is only ever *one* writer to a given set
>> of counters. Everyone else is a reader.
>
>This doesn't prevent a reader from getting an inconsistant view.
>
>Think about a 64bit counter on a 32bit machine.  If you charged per
>megabyte, wouldn't it upset you to have a small chance of loosing
>4 billion units of sale?
>
>(ie, doing a read after an addition that wraps the low 32 bits
>but before the carry is done to the top most signifigant 32bits?)

I assume this means we can not rely on the existence of any kind of
interlocked add on 64 bit machines?


>Ok, what what if everything can be read atomically by itself?
>
>You're still busted the minute you need to export any sort of
>compound stat.

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.




----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: Performance monitor signal handler

Alfred Perlstein <bright@wintelcom.net> — 2001-03-16T01:10:47Z

* Philip Warner <pjw@rhyme.com.au> [010315 17:08] wrote:
> At 16:55 15/03/01 -0800, Alfred Perlstein wrote:
> >* Philip Warner <pjw@rhyme.com.au> [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 is only ever *one* writer to a given set
> >> of counters. Everyone else is a reader.
> >
> >This doesn't prevent a reader from getting an inconsistant view.
> >
> >Think about a 64bit counter on a 32bit machine.  If you charged per
> >megabyte, wouldn't it upset you to have a small chance of loosing
> >4 billion units of sale?
> >
> >(ie, doing a read after an addition that wraps the low 32 bits
> >but before the carry is done to the top most signifigant 32bits?)
> 
> I assume this means we can not rely on the existence of any kind of
> interlocked add on 64 bit machines?
> 
> 
> >Ok, what what if everything can be read atomically by itself?
> >
> >You're still busted the minute you need to export any sort of
> >compound stat.
> 
> 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 the data.
If you have dependancies, you fit a consistant snapshot into a single
packet.

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]

Re: Performance monitor signal handler

Philip Warner <pjw@rhyme.com.au> — 2001-03-16T01:20:04Z

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 the data.
>If you have dependancies, you fit a consistant snapshot into a single
>packet.

If we were going to go the shared memory way, then yes, as soon as we start
collecting dependant data we would need locking, but IOs, locking stats,
flushes, cache hits/misses are not really in this category.

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 requirements from the backends.

----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: Performance monitor signal handler

Jan Wieck <janwieck@yahoo.com> — 2001-03-16T16:12:47Z

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 requirements from the backends.

    OK, did some tests...

    The  postmaster can create a SOCK_DGRAM socket at startup and
    bind(2) it to "127.0.0.1:0", what causes the kernel to assign
    a  non-privileged  port  number  that  then  can be read with
    getsockname(2). No other process can have a socket  with  the
    same port number for the lifetime of the postmaster.

    If  the  socket  get's  ready, it'll read one backend message
    from   it   with   recvfrom(2).   The   fromaddr   must    be
    "127.0.0.1:xxx"  where  xxx  is  the  port  number the kernel
    assigned to the above socket.  Yes,  this  is  his  own  one,
    shared  with  postmaster  and  all  backends.  So  both,  the
    postmaster and the backends can  use  this  one  UDP  socket,
    which  the  backends  inherit on fork(2), to send messages to
    the collector. If such  a  UDP  packet  really  came  from  a
    process other than the postmaster or a backend, well then the
    sysadmin has  a  more  severe  problem  than  manipulated  DB
    runtime statistics :-)

    Running  a 500MHz P-III, 192MB, RedHat 6.1 Linux 2.2.17 here,
    I've been able to loose no single message during the parallel
    regression  test,  if each backend sends one 1K sized message
    per query executed, and the collector simply sucks  them  out
    of  the  socket. Message losses start if the collector does a
    per message idle loop like this:

        for (i=0,sum=0;i<250000;i++,sum+=1);

    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 drawing board. Maybe a SYS-V message queue can serve?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Re: Performance monitor signal handler

Alfred Perlstein <bright@wintelcom.net> — 2001-03-16T16:31:49Z

* Jan Wieck <JanWieck@yahoo.com> [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 requirements from the backends.
> 
>     OK, did some tests...
> 
>     The  postmaster can create a SOCK_DGRAM socket at startup and
>     bind(2) it to "127.0.0.1:0", what causes the kernel to assign
>     a  non-privileged  port  number  that  then  can be read with
>     getsockname(2). No other process can have a socket  with  the
>     same port number for the lifetime of the postmaster.
> 
>     If  the  socket  get's  ready, it'll read one backend message
>     from   it   with   recvfrom(2).   The   fromaddr   must    be
>     "127.0.0.1:xxx"  where  xxx  is  the  port  number the kernel
>     assigned to the above socket.  Yes,  this  is  his  own  one,
>     shared  with  postmaster  and  all  backends.  So  both,  the
>     postmaster and the backends can  use  this  one  UDP  socket,
>     which  the  backends  inherit on fork(2), to send messages to
>     the collector. If such  a  UDP  packet  really  came  from  a
>     process other than the postmaster or a backend, well then the
>     sysadmin has  a  more  severe  problem  than  manipulated  DB
>     runtime statistics :-)

Doing this is a bad idea:

a) it allows any program to start spamming localhost:randport with
messages and screw with the postmaster.

b) it may even allow remote people to mess with it, (see recent
bugtraq articles about this)

You should use a unix domain socket (at least when possible).

>     Running  a 500MHz P-III, 192MB, RedHat 6.1 Linux 2.2.17 here,
>     I've been able to loose no single message during the parallel
>     regression  test,  if each backend sends one 1K sized message
>     per query executed, and the collector simply sucks  them  out
>     of  the  socket. Message losses start if the collector does a
>     per message idle loop like this:
> 
>         for (i=0,sum=0;i<250000;i++,sum+=1);
> 
>     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 drawing board. Maybe a SYS-V message queue can serve?

I wouldn't say back to the drawing board, I would say two steps back.

What about instead of sending deltas, you send totals?  This would
allow you to loose messages and still maintain accurate stats.

You can also enable SIGIO on the socket, then have a signal handler
buffer packets that arrive when not actively select()ing on the
UDP socket.  You can then use sigsetmask(2) to provide mutual
exclusion with your SIGIO handler and general select()ing on the
socket.

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]

Re: Performance monitor signal handler

Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-16T16:53:01Z

Jan Wieck <JanWieck@Yahoo.com> 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 drawing board. Maybe a SYS-V message queue can serve?

That would be the same as a pipe: backends would block if the collector
stopped accepting data.  I do like the "auto discard" aspect of this
UDP-socket approach.

I think Philip had the right idea: each backend should send totals,
not deltas, in its messages.  Then, it doesn't matter (much) if the
collector loses some messages --- that just means that sometimes it
has a slightly out-of-date idea about how much work some backends have
done.  It should be easy to design the software so that that just makes
a small, transient error in the currently displayed statistics.

			regards, tom lane

Re: Performance monitor signal handler

Jan Wieck <janwieck@yahoo.com> — 2001-03-16T18:49:41Z

Alfred Perlstein wrote:
> * Jan Wieck <JanWieck@yahoo.com> [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 requirements from the backends.
> >
> >     OK, did some tests...
> >
> >     The  postmaster can create a SOCK_DGRAM socket at startup and
> >     bind(2) it to "127.0.0.1:0", what causes the kernel to assign
> >     a  non-privileged  port  number  that  then  can be read with
> >     getsockname(2). No other process can have a socket  with  the
> >     same port number for the lifetime of the postmaster.
> >
> >     If  the  socket  get's  ready, it'll read one backend message
> >     from   it   with   recvfrom(2).   The   fromaddr   must    be
> >     "127.0.0.1:xxx"  where  xxx  is  the  port  number the kernel
> >     assigned to the above socket.  Yes,  this  is  his  own  one,
> >     shared  with  postmaster  and  all  backends.  So  both,  the
> >     postmaster and the backends can  use  this  one  UDP  socket,
> >     which  the  backends  inherit on fork(2), to send messages to
> >     the collector. If such  a  UDP  packet  really  came  from  a
> >     process other than the postmaster or a backend, well then the
> >     sysadmin has  a  more  severe  problem  than  manipulated  DB
> >     runtime statistics :-)
>
> Doing this is a bad idea:
>
> a) it allows any program to start spamming localhost:randport with
> messages and screw with the postmaster.
>
> b) it may even allow remote people to mess with it, (see recent
> bugtraq articles about this)

    So  it's  possible  for  a  UDP socket to recvfrom(2) and get
    packets with  a  fromaddr  localhost:my_own_non_SO_REUSE_port
    that really came from somewhere else?

    If  that's  possible,  the  packets  must  be coming over the
    network.  Oterwise it's the local superuser sending them, and
    in  that case it's not worth any more discussion because root
    on your system has more powerful possibilities to muck around
    with  your  database. And if someone outside the local system
    is doing it, it's time for some filter rules, isn't it?

> You should use a unix domain socket (at least when possible).

    Unix domain UDP?

>
> >     Running  a 500MHz P-III, 192MB, RedHat 6.1 Linux 2.2.17 here,
> >     I've been able to loose no single message during the parallel
> >     regression  test,  if each backend sends one 1K sized message
> >     per query executed, and the collector simply sucks  them  out
> >     of  the  socket. Message losses start if the collector does a
> >     per message idle loop like this:
> >
> >         for (i=0,sum=0;i<250000;i++,sum+=1);
> >
> >     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 drawing board. Maybe a SYS-V message queue can serve?
>
> I wouldn't say back to the drawing board, I would say two steps back.
>
> What about instead of sending deltas, you send totals?  This would
> allow you to loose messages and still maintain accurate stats.

    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.

>
> You can also enable SIGIO on the socket, then have a signal handler
> buffer packets that arrive when not actively select()ing on the
> UDP socket.  You can then use sigsetmask(2) to provide mutual
> exclusion with your SIGIO handler and general select()ing on the
> socket.

    I  already thought that priorizing the socket-drain this way:
    there is a fairly big receive buffer. If the buffer is empty,
    it  does  a  blocking  select(2). If it's not, it does a non-
    blocking (0-timeout) one and only if the  non-blocking  tells
    that  there  aren't  new  messages waiting, it'll process one
    buffered message and try to receive again.

    Will give it a shot.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Re: Performance monitor signal handler

Alfred Perlstein <bright@wintelcom.net> — 2001-03-16T20:18:26Z

* Tom Lane <tgl@sss.pgh.pa.us> [010316 10:06] wrote:
> Jan Wieck <JanWieck@Yahoo.com> 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 drawing board. Maybe a SYS-V message queue can serve?
> 
> That would be the same as a pipe: backends would block if the collector
> stopped accepting data.  I do like the "auto discard" aspect of this
> UDP-socket approach.
> 
> I think Philip had the right idea: each backend should send totals,
> not deltas, in its messages.  Then, it doesn't matter (much) if the
> collector loses some messages --- that just means that sometimes it
> has a slightly out-of-date idea about how much work some backends have
> done.  It should be easy to design the software so that that just makes
> a small, transient error in the currently displayed statistics.

MSGSND(3)              FreeBSD Library Functions Manual              MSGSND(3)


ERRORS
     msgsnd() will fail if:

     [EAGAIN]           There was no space for this message either on the
                        queue, or in the whole system, and IPC_NOWAIT was set
                        in msgflg.

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]

Re: Performance monitor signal handler

Philip Warner <pjw@rhyme.com.au> — 2001-03-17T09:49:44Z

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 only send totals for individual counters when they change; some
stats may never be resynced, but for the most part it will work. Also, does
Unix allow interrupts to occur as a result of data arrivibg in a pipe? If
so, how about:

- All backends to do *blocking* IO to collector.

- Collector to receive an interrupt when a message arrives; while in the
interrupt it reads the buffer into a local queue, and returns from the
interrupt.

- Main line code processes the queue and writes it to a memory mapped file
for durability.

- If collector dies, postmaster starts another immediately, which slears
the backlog of data in the pipe and then remaps the file.

- Each backend has its own local copy of it's counters which *possibly* to
collector can ask for when it restarts.




----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Re: Performance monitor signal handler

Jan Wieck <janwieck@yahoo.com> — 2001-03-17T14:33:03Z

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 wasted bandwidth.
>
> Not if you only send totals for individual counters when they change; some
> stats may never be resynced, but for the most part it will work. Also, does
> Unix allow interrupts to occur as a result of data arrivibg in a pipe? If
> so, how about:
>
> - All backends to do *blocking* IO to collector.

    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  and  the  more  load  is  on  the
    system,  the  more interesting accurate statistics get.  Both
    factors are contraproductive. More complex schema means  more
    tables  and  thus  bigger  messages.  More  load  means  more
    messages.  Having good statistics on a toy system while  they
    get  worse  for  a  web  backend  server  that's really under
    pressure is braindead from the start.

    We don't want the backends to block,  so  that  they  can  do
    THEIR work. That's to process queries, nothing else.

    Pipes  seem  to  be  inappropriate  because  their  buffer is
    limited to 4K on Linux and most BSD flavours. Message  queues
    are too because they are limited to 2K on most BSD's. So only
    sockets remain.

    If we have multiple processes that try to  receive  from  the
    UDP  socket,  condense  the  received  packets  into  summary
    messages and send them to the central collector,  this  might
    solve the problem.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Re: Performance monitor signal handler

Samuel Sieb <samuel@sieb.net> — 2001-03-17T16:21:13Z

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  and  the  more  load  is  on  the
>     system,  the  more interesting accurate statistics get.  Both
>     factors are contraproductive. More complex schema means  more
>     tables  and  thus  bigger  messages.  More  load  means  more
>     messages.  Having good statistics on a toy system while  they
>     get  worse  for  a  web  backend  server  that's really under
>     pressure is braindead from the start.
> 
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 you can't automatically start/restart it from the
postmaster and it will put a little more load on the network, but it seems
to mostly solve the issues of blocked pipes and using too much cpu time
on the database server.

Re: Performance monitor signal handler

Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-17T16:48:22Z

Samuel Sieb <samuel@sieb.net> 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 you can't automatically start/restart it from the
> postmaster and it will put a little more load on the network,

... 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 lossy transmission method.  Let's try that first, and if it really
proves to be unworkable then we can get out the lily-gilding equipment.
But there is tons more stuff to do before we have useful stats at all,
and I don't think that this aspect is the most critical part of the
problem.

			regards, tom lane

Re: Performance monitor signal handler

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-17T17:10:37Z

> ... 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 lossy transmission method.  Let's try that first, and if it really
> proves to be unworkable then we can get out the lily-gilding equipment.
> But there is tons more stuff to do before we have useful stats at all,
> and I don't think that this aspect is the most critical part of the
> problem.

Agreed.  Sounds like overkill.

How about a per-backend shared memory area for stats, plus a global
shared memory area that each backend can add to when it exists.  That
meets most of our problem.

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.  Even better, have an SQL table
updated with the per-table stats periodically.  How about a collector
process that periodically reads though the shared memory and UPDATE's
SQL tables with the information.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Performance monitor signal handler

Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-17T17:38:36Z

Bruce Momjian <pgman@candle.pha.pa.us> 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 kernel to send stats messages --- regardless of the transport
mechanism chosen --- don't seem all that cheap either.

> Even better, have an SQL table updated with the per-table stats
> periodically.

That will be horribly expensive, if it's a real table.

I think you missed the point that somebody made a little while ago
about waiting for functions that can return tuple sets.  Once we have
that, the stats tables can be *virtual* tables, ie tables that are
computed on-demand by some function.  That will be a lot less overhead
than physically updating an actual table.

			regards, tom lane

Re: Performance monitor signal handler

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-17T17:43:25Z

> Bruce Momjian <pgman@candle.pha.pa.us> 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 kernel to send stats messages --- regardless of the transport
> mechanism chosen --- don't seem all that cheap either.

I am confused.  Reading/writing shared memory is not a kernel call,
right?

I agree on the locking contention problems of a circular buffer.

> 
> > 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 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
stats for all backends.

> I think you missed the point that somebody made a little while ago
> about waiting for functions that can return tuple sets.  Once we have
> that, the stats tables can be *virtual* tables, ie tables that are
> computed on-demand by some function.  That will be a lot less overhead
> than physically updating an actual table.

Yes, but do we want to keep these stats between postmaster restarts? 
And what about writing them to tables when our storage of table stats
gets too big?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Performance monitor signal handler

Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-17T19:11:51Z

Bruce Momjian <pgman@candle.pha.pa.us> 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 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
> stats for all backends.

>> I think you missed the point that somebody made a little while ago
>> about waiting for functions that can return tuple sets.  Once we have
>> that, the stats tables can be *virtual* tables, ie tables that are
>> computed on-demand by some function.  That will be a lot less overhead
>> than physically updating an actual table.

> Yes, but do we want to keep these stats between postmaster restarts? 
> And what about writing them to tables when our storage of table stats
> gets too big?

All those points seem to me to be arguments in *favor* of a virtual-
table approach, not arguments against it.

Or are you confusing the method of collecting stats with the method
of making the collected stats available for use?

			regards, tom lane

Re: Performance monitor signal handler

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-17T20:10:05Z

> > 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
> > stats for all backends.
> 
> >> I think you missed the point that somebody made a little while ago
> >> about waiting for functions that can return tuple sets.  Once we have
> >> that, the stats tables can be *virtual* tables, ie tables that are
> >> computed on-demand by some function.  That will be a lot less overhead
> >> than physically updating an actual table.
> 
> > Yes, but do we want to keep these stats between postmaster restarts? 
> > And what about writing them to tables when our storage of table stats
> > gets too big?
> 
> All those points seem to me to be arguments in *favor* of a virtual-
> table approach, not arguments against it.
> 
> Or are you confusing the method of collecting stats with the method
> of making the collected stats available for use?

Maybe I am confusing them.  I didn't see a distinction in the
discussion.

I assumed the UDP/message passing of information to the collector was
the way statistics were collected, and I don't understand why a
per-backend area and global area, with some kind of cicular buffer for
per-table stuff isn't the cheapest, cleanest solution.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Performance monitor signal handler

Jan Wieck <janwieck@yahoo.com> — 2001-03-18T02:18:39Z

Tom Lane wrote:
> Samuel Sieb <samuel@sieb.net> 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 you can't automatically start/restart it from the
> > postmaster and it will put a little more load on the network,
>
> ... 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 lossy transmission method.  Let's try that first, and if it really
> proves to be unworkable then we can get out the lily-gilding equipment.
> But there is tons more stuff to do before we have useful stats at all,
> and I don't think that this aspect is the most critical part of the
> problem.

    Well,

    back  to my initial approach with the UDP socket collector. I
    now have a collector simply reading  all  messages  from  the
    socket.  It  doesn't  do  anything useful except for counting
    their number.

    Every backend sends a couple  of  1K  junk  messages  at  the
    beginning  of  the  main loop. Up to 16 messages, there is no
    time(1) measurable  delay  in  the  execution  of  the  "make
    runcheck".

    The   dummy   collector  can  keep  up  during  the  parallel
    regression test until the  backends  send  64  messages  each
    time,  at  that number he lost 1.25% of the messages. That is
    an amount of statistics data of >256MB to be collected.  Most
    of  the  test  queries  will never generate 1K of message, so
    that there should be some space here.

    My plan  now  is  to  add  some  real  functionality  to  the
    collector and the backend, to see if that has an impact.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com