One more feature for discussion :-)

    In  the  next  couple of hours (at least tomorrow) I would be
    ready to commit the backend changes  for  table-/index-access
    statistics and current backend activity views.

    Should  I  apply  the patches or provide a separate patch for
    review first?


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


Thu, 31 May 2001 12:20:38 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Access statistics
Jan Wieck <JanWieck@Yahoo.com> writes:
>     In  the  next  couple of hours (at least tomorrow) I would be
>     ready to commit the backend changes  for  table-/index-access
>     statistics and current backend activity views.
>     Should  I  apply  the patches or provide a separate patch for
>     review first?

Considering that you've not offered any detailed information about
what you plan to do (AFAIR), a patch for review first would be polite ...

			regards, tom lane

Thu, 31 May 2001 12:53:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Access statistics 
> One more feature for discussion :-)
> 
>     In  the  next  couple of hours (at least tomorrow) I would be
>     ready to commit the backend changes  for  table-/index-access
>     statistics and current backend activity views.
> 
>     Should  I  apply  the patches or provide a separate patch for
>     review first?

I like doing a cvs diff -c and throwing the patch to PATCHES just before
commit.  That way, people can see may changes easier and find problems. 
Of course, if I am at all unsure, I post to patches and wait 2 days.

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

Thu, 31 May 2001 13:00:03 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Access statistics
Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> >     In  the  next  couple of hours (at least tomorrow) I would be
> >     ready to commit the backend changes  for  table-/index-access
> >     statistics and current backend activity views.
> >     Should  I  apply  the patches or provide a separate patch for
> >     review first?
>
> Considering that you've not offered any detailed information about
> what you plan to do (AFAIR), a patch for review first would be polite ...

    We  had that discussion a couple of weeks ago down to if it's
    better to use UNIX or INET domain UDP sockets. But I expected
    it  not  to  be detailed enough for our current quality level
    :-)

    It's incomplete anyway since the per  database  configuration
    in  pg_database  is  missing and some other details I need to
    tidy up. So I'll wrap up a patch tomorrow.


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


Thu, 31 May 2001 15:10:44 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Access statistics
Jan Wieck writes:

>     In  the  next  couple of hours (at least tomorrow) I would be
>     ready to commit the backend changes  for  table-/index-access
>     statistics and current backend activity views.
>
>     Should  I  apply  the patches or provide a separate patch for
>     review first?

Maybe you could describe what it's going to do and how it's going to work.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Thu, 31 May 2001 23:05:24 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Access statistics
Peter Eisentraut wrote:
> Jan Wieck writes:
>
> >     In  the  next  couple of hours (at least tomorrow) I would be
> >     ready to commit the backend changes  for  table-/index-access
> >     statistics and current backend activity views.
> >
> >     Should  I  apply  the patches or provide a separate patch for
> >     review first?
>
> Maybe you could describe what it's going to do and how it's going to work.

    Real  programmers  don't comment - if it was hard to write it
    should be hard to read :-)

    So outing myself not beeing a *real programmer*, this is what
    I have so far:

    * On startup the postmaster creates an INET domain UDP socket
      and bind(2)'s it to localhost:0, meaning  the  kernel  will
      assign  a  yet unused, unprivileged port that could be seen
      with getsockaddr(2).

      It then starts two background processes  of  which  one  is
      simply a wraparound buffer doing recvfrom(2) on the socket,
      checking that the source address of the received packets is
      the  sockets  own  address (!) and forwarding approved ones
      over a pipe to the second one, discribed later.

    * Backends call some collector functions  at  various  places
      now  (these  will  finally  be macros), that count up table
      scans, tuples returned by scans,  buffer  fetches/hits  and
      the like. At the beginning of a statement the backends send
      a message telling the first couple of hundred bytes of  the
      querystring  and  after  the statement is done (just before
      getting ready for the next command) they send the collected
      access numbers.

      Tables,  indexes etc. in these statistics are identified by
      OID, so the data doesn't tell much so far.

    * The second  background  process  fired  by  the  postmaster
      collects  these  numbers  into hashtables and as long as it
      receives messages, it'll write out a summary file every 500
      or  so  milliseconds,  telling a snapshot of current stats.
      Using select(2) with timeouts ensures that a complete  idle
      DB  instance not to waste a single CPU cycle or IO to write
      these snapshots.

      On startup it tries to read the last snapshot file  in,  so
      the collected statistics survive a postmaster restart.

      Vacuum  reads  the  file too and sends bulk delete messages
      for  objects  that  are  gone.  So  the  stats  don't  grow
      infinitely.

    * A  bunch  of  new  builtin  functions  gain  access  to the
      snapshot file. At first call  of  one  of  these  functions
      during  a  transaction,  the  backend will read the current
      file and return the numbers from in memory then.

      Based on these functions a couple of views can  tell  these
      collected  stats.  Information  from  the  databases system
      catalog is of course required to identify  the  objects  in
      the  stats,  but  I think those informations should only be
      visible to someone who identified herself  as  a  valid  DB
      user anyway.

      The  visibility  of  querystrings  (this  info is available
      cross DB) is restricted to DB superusers.

    There has been discussion already about  using  an  INET  vs.
    UNIX  UDP  socket for the communication. At least for Linux I
    found INET to be the most effective way of communication. And
    for  security  concerns: If someone else than root can really
    send packets to that  socket  that  show  up  with  a  source
    address  of  127.0.0.1:n,  where  n  is a portnumber actually
    occupied by your own socket, be sure you'll have more  severe
    problems than modified access statistics.

    The views should be considered examples. The final naming and
    layout is subject for discussion.


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


Fri, 1 Jun 2001 09:31:05 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Access statistics
Jan Wieck <JanWieck@Yahoo.com> writes:
>     So outing myself not beeing a *real programmer*, this is what
>     I have so far:

Hmm ... what is the performance of all this like?  Seems like a lot
of overhead.  Can it be turned off?

>     * Backends call some collector functions  at  various  places
>       now  (these  will  finally  be macros), that count up table
>       scans, tuples returned by scans,  buffer  fetches/hits  and
>       the like.

Have you removed the existing stats-gathering support
(backend/access/heap/stats.c and so on)?   That would buy back
at least a few of the cycles involved ...

			regards, tom lane

Fri, 01 Jun 2001 10:08:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Access statistics 
Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> >     So outing myself not beeing a *real programmer*, this is what
> >     I have so far:
>
> Hmm ... what is the performance of all this like?  Seems like a lot
> of overhead.  Can it be turned off?

    Current  performance loss is about 2-4% wallclock measured. I
    expect it to become better when turning some of the functions
    into macros.

    The  plan is to add another column to pg_database that can be
    used to turn it on/off on a per database level. Backends just
    decide  at startup if they collect and send for their session
    lifetime.

> >     * Backends call some collector functions  at  various  places
> >       now  (these  will  finally  be macros), that count up table
> >       scans, tuples returned by scans,  buffer  fetches/hits  and
> >       the like.
>
> Have you removed the existing stats-gathering support
> (backend/access/heap/stats.c and so on)?   That would buy back
> at least a few of the cycles involved ...

    Not sure if we really should.  Let's  later  decide  if  it's
    really obsolete.

--

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


Fri, 1 Jun 2001 10:34:36 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Access statistics
Jan Wieck <JanWieck@yahoo.com> writes:
> Tom Lane wrote:
>> Have you removed the existing stats-gathering support
>> (backend/access/heap/stats.c and so on)?   That would buy back
>> at least a few of the cycles involved ...

>     Not sure if we really should.  Let's  later  decide  if  it's
>     really obsolete.

Considering that Bruce long ago ifdef'd out all the code that could
actually *do* anything with those stats (like print them), I'd say
it's obsolete.  In any case, it's too confusing to have two sets of
stats-gathering code in there.  I vote for getting rid of the old
stuff.

			regards, tom lane

Fri, 01 Jun 2001 10:42:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Access statistics 
> Tom Lane wrote:
> > Jan Wieck <JanWieck@Yahoo.com> writes:
> > >     So outing myself not beeing a *real programmer*, this is what
> > >     I have so far:
> >
> > Hmm ... what is the performance of all this like?  Seems like a lot
> > of overhead.  Can it be turned off?
> 
>     Current  performance loss is about 2-4% wallclock measured. I
>     expect it to become better when turning some of the functions
>     into macros.

At 2-4%, I assume it is not enabled by default.  I can see the query
string part being enabled by default though.

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

Fri, 1 Jun 2001 12:20:36 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Access statistics
> Jan Wieck <JanWieck@yahoo.com> writes:
> > Tom Lane wrote:
> >> Have you removed the existing stats-gathering support
> >> (backend/access/heap/stats.c and so on)?   That would buy back
> >> at least a few of the cycles involved ...
> 
> >     Not sure if we really should.  Let's  later  decide  if  it's
> >     really obsolete.
> 
> Considering that Bruce long ago ifdef'd out all the code that could
> actually *do* anything with those stats (like print them), I'd say
> it's obsolete.  In any case, it's too confusing to have two sets of
> stats-gathering code in there.  I vote for getting rid of the old
> stuff.

I agree.  Rip away.

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

Fri, 1 Jun 2001 12:23:44 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Access statistics