Re: Performance monitor signal handler

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 collector poll the shared memory
> area.  It also has the auto-discard option.  He will make logging
> configurable on a per-database level, so it can be turned off when not
> in use.
>
> He has a trial UDP implementation that he will post soon.  Also, I asked
> him to try DGRAM Unix-domain sockets for performance reasons.  My
> Steven's book says it they should be supported.  He can put the socket
> file in /data.

"Trial" implementation attached :-)

    First  attachment  is  a patch for various backend files plus
    generating two new source files. If your patch(1) doesn't put
    'em   automatically,  they  go  to  src/include/pgstat.h  and
    src/backend/postmaster/pgstat.c.

    BTW:  tgl  on  2/99  was  right,  the  hash_destroy()  really
    crashes.  Maybe  we  want  to  pull  out  the  fix  I've done
    (includes some new feature for hash table memory  allocation)
    and apply that to 7.1?

    Second   attachment  is  a  tarfile  that  should  unpack  to
    contrib/pgstat_tmp.  I've placed the SQL level functions into
    a shared module for now. The sql script also creates a couple
    of views.

    -   pgstat_all_tables shows scan- and tuple based  statistics
        for all tables.  pgstat_sys_tables and pgstat_user_tables
        filter out (you guess what) system or user tables.

    -   pgstatio_all_tables,       pgstatio_sys_tables        and
        pgstatio_user_tables   show   buffer  IO  statistics  for
        tables.

    -   pgstat_*_indexes and pgstatio_*_indexes are similar  like
        the  above,  just that they give detailed info about each
        single index.

    -   pgstatio_*_sequences shows buffer IO statistics  about  -
        right,   sequences.    Since   sequences  aren't  scanned
        regularely, they have no scan- and tuple related view.

    -   pgstat_activity shows informations  about  all  currently
        running  backends  of the entire instance. The underlying
        function for displaying the  actual  query  returns  NULL
        allways for non-superusers.

    -   pgstat_database shows transaction commit/abort counts and
        cumulated  buffer  IO   statistics   for   all   existing
        databases.

    The  collector  writes  frequently  a  file  data/pgstat.stat
    (approx. every 500 milliseconds as long as there is something
    to  tell,  so  nothing  is  done  if  the entire installation
    sleeps). He also reads this file  on  startup,  so  collected
    statistics survive postmaster restarts.

    TODO:

    -   Are  PF_UNIX  SOCK_DGRAM  sockets  supported  on  all the
        platforms we do?  If not, what's wrong with  the  current
        implementation?

    -   There  is  no way yet to tell the collector about objects
        (relations and  databases)  removed  from  the  database.
        Basically  that  could be done with messages too, but who
        will send them and how can we guarantee that  they'll  be
        generated  even if somebody never queries the statistics?
        Thus, the current collector will grow, and grow, and grow
        until   you   remove   the  pgstat.stat  file  while  the
        postmaster is down.

    -   Also there aren't functions or  messages  implemented  to
        explicitly reset statistics.

    -   Possible additions would be to remember when the backends
        started and collect resource usage (rstat(2)) information
        as well.

    -   The   entire  thing  needs  an  additional  attribute  in
        pg_database that tells the  backends  what  to  tell  the
        collector at all. Just to make them quiet again.

    So far for an actual snapshot. Comments?


Jan

--

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