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(at)Yahoo(dot)com #