Re: Performance monitor signal handler

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