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(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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

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