Re: Performance monitor signal handler - Mailing list pgsql-hackers

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