Re: [DOCS] Stats views and functions not in order?

On Wed, Nov 23, 2022 at 1:36 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:

> On Thu, Nov 17, 2022 at 8:46 AM David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> > Also, make it so each view ends up being its own separate page.
> >
>
> I did not do this. AFAIK those views of chapter 54 get rendered to
> separate pages only because they are top-level <sect1>. So I do not
> know how to put all these stats views onto different pages without
> radically changing the document structure. Anyway – doing this would
> be incompatible with my <sect3> changes of patch 0006 (see above).
>
>
I did some experimentation and reading on this today. Short answer - turn
each view into a refentry under a dedicated sect2 where the table resides.

David J.

<chapter>
[...]
<sect1> <!--The Cumulative Statistics System -->
[...]
<sect2>
<title>Statistics Views</title>
<para>Table of Statistics Views...</para>

<refentry id="monitoring-pg-stat-activity-view">
<refnamediv><refname>pg_stat_activity</refname><refpurpose>Purpose</refpurpose></refnamediv>
<refsect1>
<title><structname>pg_stat_activity</structname></title>

<indexterm>
<primary>pg_stat_activity</primary>
</indexterm>

</refsect1></refentry>

</sect2> <!-- Statistics Views -->

</sect1>
</chapter>

I was doing quite a bit of experimentation and basically gutted the actual
page to make that easier. The end result looked basically like below.

Chapter 28. Monitoring Database Activity

Table of Contents

28.1. Standard Unix Tools
28.2. The Cumulative Statistics System

28.2.1. Statistics Collection Configuration
28.2.2. Viewing Statistics
28.2.3. Statistics Views

A database administrator frequently wonders, “What is the system doing
right now?” This chapter discusses how to find that out.

Several tools are available for monitoring database activity and analyzing
performance. Most of this chapter is devoted to describing PostgreSQL's
cumulative statistics system, but one should not neglect regular Unix
monitoring programs such as ps, top, iostat, and vmstat. Also, once one has
identified a poorly-performing query, further investigation might be needed
using PostgreSQL's EXPLAIN command. Section 14.1 discusses EXPLAIN and
other methods for understanding the behavior of an individual query.

============== Page for 28.2 (sect1) ==============
28.2. The Cumulative Statistics System

28.2.1. Statistics Collection Configuration
28.2.2. Viewing Statistics
28.2.3. Statistics Views

PostgreSQL's cumulative statistics system supports collection and reporting
of information about server activity. Presently, accesses to tables and
indexes in both disk-block and individual-row terms are counted. The total
number of rows in each table, and information about vacuum and analyze
actions for each table are also counted. If enabled, calls to user-defined
functions and the total time spent in each one are counted as well.

PostgreSQL also supports reporting dynamic information about exactly what
is going on in the system right now, such as the exact command currently
being executed by other server processes, and which other connections exist
in the system. This facility is independent of the cumulative statistics
system.
28.2.1. Statistics Collection Configuration

Since collection of statistics adds some overhead to query execution, the
system can be configured to collect or not collect information. This is
controlled by configuration parameters that are normally set in
postgresql.conf. (See Chapter 20 for details about setting configuration
parameters.)

The parameter track_activities enables monitoring of the current command
being executed by any server process.

The parameter track_counts controls whether cumulative statistics are
collected about table and index accesses.

The parameter track_functions enables tracking of usage of user-defined
functions.

The parameter track_io_timing enables monitoring of block read and write
times.

The parameter track_wal_io_timing enables monitoring of WAL write times.

Normally these parameters are set in postgresql.conf so that they apply to
all server processes, but it is possible to turn them on or off in
individual sessions using the SET command. (To prevent ordinary users from
hiding their activity from the administrator, only superusers are allowed
to change these parameters with SET.)

Cumulative statistics are collected in shared memory. Every PostgreSQL
process collects statistics locally, then updates the shared data at
appropriate intervals. When a server, including a physical replica, shuts
down cleanly, a permanent copy of the statistics data is stored in the
pg_stat subdirectory, so that statistics can be retained across server
restarts. In contrast, when starting from an unclean shutdown (e.g., after
an immediate shutdown, a server crash, starting from a base backup, and
point-in-time recovery), all statistics counters are reset.
28.2.2. Viewing Statistics

test
28.2.3. Statistics Views

Table of Statistics Views...

===============
file:///usr/local/pgsql/share/doc/html/monitoring-pg-stat-activity-view.html
=============
(no ToC entry but the Next link in our footer does point to here)

pg_stat_activity

pg_stat_activity — Purpose
pg_stat_activity

The pg_stat_activity view will have one row per server process, showing
information related to the current activity of that process.

Here is an example of how wait events can be viewed:

SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE
wait_event is NOT NULL;
pid | wait_event_type | wait_event
------+-----------------+------------
2540 | Lock | relation
6644 | LWLock | ProcArray
(2 rows)