8 Monitoring Wait Stats

Source chapter 3.6 for PostgreSQL 9.6.

Beta 1Source pages: PDF 45Original PDF on GitHub

PDF page 45

The information of the wait events that are occurring in the PostgreSQL instance can be get now. Waiting column is deleted from pg_stat_activity catalog, wait_event_type columns and wait_event column are added. The wait_event_type column contains the following values.

Columns value	Description
LWLockNamed	Waiting by a particular lightweight lock
LWLockTranche	Waiting by the lightweight lock for the group
Lock	Waiting by weight lock (LOCK TABLE statement etc)
BufferPin	PIN waiting for the buffer

Table 26 The value of wait_event_type column

Please refer to the following URL for more information. http://www.postgresql.org/docs/9.6/static/monitoring-stats.html

Example 40 Waiting by two LOCK TABLE IN EXCLUSIVE statements

postgres=> SELECT pid, wait_event_type, wait_event
FROM pg_stat_activity WHERE pid=4070 ;
 pid  | wait_event_type | wait_event
------+-----------------+------------
 4070 | Lock            | relation
(1 row)

Example 41 Waiting by SELECT FOR UPDATE statement and UPDATE statement

postgres=> SELECT pid, wait_event_type, wait_event
FROM pg_stat_activity WHERE pid=4070 ;
pid   | wait_event_type |  wait_event
------+-----------------+---------------
 4070 | Lock            | transactionid
 (1 row)