Re: [DOCS] Stats views and functions not in order? - Mailing list pgsql-hackers

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)