Re: Performance monitor signal handler

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.



> > > 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
> >     and a 1 byte (even if it'd be anoter 32-bit value), how  many
> >     messages do you want to generate to get these statistics:
> > 
> >     -   Number of sequential scans done per table.
> >     -   Number of tuples returned via sequential scans per table.
> >     -   Number of buffer cache lookups  done  through  sequential
> >         scans per table.
> >     -   Number  of  buffer  cache  hits  for sequential scans per
> >         table.
> >     -   Number of tuples inserted per table.
> >     -   Number of tuples updated per table.
> >     -   Number of tuples deleted per table.
> >     -   Number of index scans done per index.
> >     -   Number of index tuples returned per index.
> >     -   Number of buffer cache lookups  done  due  to  scans  per
> >         index.
> >     -   Number of buffer cache hits per index.
> >     -   Number  of  valid heap tuples returned via index scan per
> >         index.
> >     -   Number of buffer cache lookups done for heap fetches  via
> >         index scan per index.
> >     -   Number  of  buffer  cache hits for heap fetches via index
> >         scan per index.
> >     -   Number of buffer cache lookups not accountable for any of
> >         the above.
> >     -   Number  of  buffer  cache hits not accountable for any of
> >         the above.
> > 
> >     What I see is that there's a difference in what we  two  want
> >     to see in the statistics. You're talking about looking at the
> >     actual querystring and such. That's  information  useful  for
> >     someone   actually  looking  at  a  server,  to  see  what  a
> >     particular backend  is  doing.  On  my  notebook  a  parallel
> >     regression  test  (containing >4,000 queries) passes by under
> >     1:30, that's more than 40 queries per second. So that doesn't
> >     tell me much.
> > 
> >     What I'm after is to collect the above data over a week or so
> >     and then generate a report to identify the hot spots  of  the
> >     schema.  Which tables/indices cause the most disk I/O, what's
> >     the average percentage of tuples returned in scans (not  from
> >     the  query, I mean from the single scan inside of the joins).
> >     That's the information I need  to  know  where  to  look  for
> >     possibly  better  qualifications, useless indices that aren't
> >     worth to maintain and the like.
> > 
> 
> I was going to have the per-table stats insert a stat record every time
> it does a sequential scan, so it sould be [oid][sequential_scan_value]
> and allow the collector to gather that and aggregate it.
> 
> I didn't think we wanted each backend to do the aggregation per oid. 
> Seems expensive. Maybe we would need a count for things like "number of
> rows returned" so it would be [oid][stat_type][value].
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 


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