A detailed analysis of PostgreSQL 9.6 waiting events

Background

PostgreSQL 9.6 Dynamic View pg_stat_activity new Wait_event_type, wait_event wait event display.

When the session is in a waiting state, wait_event and Wait_event_type are Non-null, indicating the type the session is waiting for.

Depending on the wait information, you can understand the status of the current session.

In the future can also be the form of Plug-ins, the database in a certain period of time waiting for event statistics, better diagnosis of the health of the database.

The existing plug-ins are as follows

Https://github.com/postgrespro/pg_wait_sampling

Used to sample a wait event.

Example

Pg_wait_sampling_history view–history of wait events obtained by sampling into
In-memory ring buffer.

Column Name column type Description
PID Int4 Id of Process
TS TIMESTAMPTZ Sample Timestamp
Event_type text Name of wait event type
Event text Name of Wait event
Pg_wait_sampling_profile View–profile of wait events obtained by sampling into
In-memory hash table.

Column Name column type Description
PID Int4 Id of Process
Event_type text Name of wait event type
Event text Name of Wait event
Count text Count of samples
Pg_wait_sampling_reset_profile () function resets the profile.

The work of Wait event statistics collector worker is controlled by following
Gucs.

Parameter name Data type Description Default value
Pg_wait_sampling.history_size int4 size of history in-memory ring buffer 5000
Pg_wait_sampling.history_period Int4 period for history sampling in milliseconds 10
Pg_wait_sampling.profile_period Int4 period for profiles sampling in milliseconds 10
pg_wait_sampling.profile_pid bool Whether profile should is per PID true
PostgreSQL 9.6 Waiting events

See
Https://www.postgresql.org/docs/9.6/static/monitoring-stats.html

The pg_stat_activity view adds a wait event column to observe the current wait for the session.

1. Wait_event_type
The category that represents the wait time, or null if the backend is in the waiting state.

Categories are as follows

1.1 Lwlocknamed:

Named lightweight locks, which are designed to protect the data structure in memory and prevent concurrent problems.

The backend is waiting for a specific named lightweight lock.

Each such lock protects a particular the data structure in shared memory.
1.2 Lwlocktranche:

Group lightweight locks, no subdivision of names, just general classification.

The backend is waiting for one of a group of related lightweight locks.

All locks in the group perform a similar function;
1.3 Lock:

A heavyweight lock used to protect a SQL-visible object, such as a table. can also be used to protect storage, for example, when extending tables.

See Src/include/storage/lock.h

The backend is waiting for a heavyweight lock.

Heavyweight locks, also known as lock manager locks or simply locks, primarily-protect sql-visible objects such as tables.

However, they are also used to ensure mutual exclusion for certain internal operations such as relation.
1.4 Bufferpin:

Bufferpin is used to protect data in a database, such as data buffer access.

The server process is waiting to access to a data buffer during a period while no other process can being examining that Buffe R.

Buffer pin waits can be protracted if another process holds a open cursor which last read data from the buffer in Questio N.
2. Wait_event

Represents a detailed wait event in the Wait_event_type.

If the current backend is in a wait state, there is a value, otherwise null

Wait event name if backend is currently waiting, otherwise NULL.
2.1 lwlocknamed

Shmemindexlock
Waiting to find or allocate spaces in shared memory.

Oidgenlock
Waiting to allocate or assign an OID.

Xidgenlock
Typically occurs when a high concurrent request transaction number is encountered
Waiting to allocate or assign a transaction ID.

Procarraylock
Usually occurs in a high concurrency request transaction number, and when Old_snapshot_threshold is opened
Waiting to get a snapshot or clearing a transaction ID in transaction end.

Sinvalreadlock
Waiting to retrieve or remove messages from shared invalidation queue.

Sinvalwritelock
Waiting to add a is in the shared invalidation queue.

Walbufmappinglock
Waiting to replace a page in WAL buffers.

Walwritelock
Wal brush disk slower, you can improve the Wal writer frequency, or increase the buffer, or increase the IOPS of the target disk, reduce the target of Rt.
Waiting for WAL buffers to is written to disk.

Controlfilelock
If the generation of Xlog is indeed very frequent and there is no way to reduce it, you can use the larger Xlog file, Max 64MB.
Usually this is very rare.
Waiting to read or update the control file or creation of a new WAL file.

Checkpointlock
Waiting to perform checkpoint.

Clogcontrollock
Waiting to read or update transaction status.

Subtranscontrollock
Waiting to read or update sub-transaction information.

Multixactgenlock
Waiting to read or update shared multixact state.

Multixactoffsetcontrollock
Waiting to read or update multixact offset mappings.

Multixactmembercontrollock
Waiting to read or update Multixact member mappings.

Relcacheinitlock
Waiting to read or write relation cache initialization file.

Checkpointercommlock
The checkpoint is divided into three steps (write, Sync_file_range, Fsync), indicating that the Fsync request is waiting, needs to be increased IO, or the dirty page when the Fsync is reduced.
Waiting to manage fsync requests.

Twophasestatelock
Waiting to read or update the state of prepared transactions.

Tablespacecreatelock
Waiting to create or drop the tablespace.

Btreevacuumlock
Frequently, indicating that the index fields are frequently updated.
Waiting to read or update vacuum-related information for a b-tree index.

Addinshmeminitlock
Waiting to manage spaces allocation in shared memory.

Autovacuumlock
Autovacuum worker or launcher waiting to update or read the current state of autovacuum workers.

Autovacuumschedulelock
Note that the Autovacuum single table is slow to see if you can turn off the autovacuum sleep schedule.
Waiting to ensure this table it has selected for a vacuum still needs.

Syncscanlock
Waiting to get the "start location" a scan on a table for synchronized scans.

Relationmappinglock
Waiting to update the relation map file used to store catalog to Filenode mapping.

Asyncctllock
Waiting to read or update shared notification state.

Asyncqueuelock
Waiting to read or update notification messages.

Serializablexacthashlock
Waiting to retrieve or store information about serializable transactions.

Serializablefinishedlistlock
Waiting to access the list of finished serializable transactions.

Serializablepredicatelocklistlock
Waiting to perform a operation on a list of locks held by serializable transactions.

Oldserxidlock
Waiting to read or record conflicting serializable transactions.

Syncreplock
Waiting to read or update information about synchronous replicas.

Backgroundworkerlock
Waiting to read or update background the worker state.

Dynamicsharedmemorycontrollock
Waiting to read or update dynamic shared memory state.

Autofilelock
Waiting to update the postgresql.auto.conf file.

Replicationslotallocationlock
Waiting to allocate or free a replication slot.

Replicationslotcontrollock
Waiting to read or update replication slot state.

Committscontrollock
Waiting to read or update transaction commit timestamps.

Committslock
Waiting to read or update the last value set for the transaction timestamp.

Replicationoriginlock
Waiting to setup, drop or use replication origin.

Multixacttruncationlock
Waiting to read or truncate multixact information.

Oldsnapshottimemaplock
Waiting to read or update old snapshot control information.
2.2 Lwlocktranche

Clog
Often rare, file IO waits and can be easily reproduced using cgroup when very high concurrent minimal write transactions occur.
Waiting for I/O on a clog (transaction status) buffer.

Commit_timestamp
Waiting for I/O on commit timestamp buffer.

Subtrans
Waiting for I/O a subtransaction buffer.

Multixact_offset
Waiting for I/O on a multixact offset buffer.

Multixact_member
Waiting for I/O on a multixact_member buffer.

Async
Waiting for I/O on async (notify) buffer.

Oldserxid
Waiting to I/O on a oldserxid buffer.

Wal_insert
Waiting to insert WAL into a memory buffer.

Buffer_content
Refers to the database shared buffer
Waiting to read or write a data page in memory.

Buffer_io
Refers to the database shared buffer
Waiting for I/O on a data page.

Replication_origin
Waiting to read or update the replication progress.

Replication_slot_io
Waiting for I/O on a replication slot.

Proc
Waiting to read or update the Fast-path lock information.

Buffer_mapping
Waiting to associate a data blocks with a buffer in the buffer pool.

Lock_manager
Waiting to add or examine locks for backends, or waiting to join or exit a locking group (used by parallel query).

Predicate_lock_manager
Waiting to add or examine predicate lock information.
2.3 Lock

Relation
Waiting to acquire a lock on a relation.

Extend
Waiting to extend a relation.

Page
Waiting to acquire a lock on page of a relation.

Tuple
Waiting to acquire a lock on a tuple.

TransactionID
Waiting for a transaction to finish.

Virtualxid
Waiting to acquire a virtual XID lock.

Speculative token
Waiting to acquire a speculative insertion lock.

Object
Waiting to acquire a lock on a Non-relation database object.

UserLock
Waiting to acquire a userlock.

Advisory
Waiting to acquire an advisory user lock.
2.4 Bufferpin

Bufferpin
Waiting to acquire a pin on a buffer.
3. Gets the current wait information for the specified PID.

Pg_stat_get_backend_wait_event_type (integer)
Wait event type name if backend is currently waiting, otherwise NULL. The Table 28-4 for details.

Pg_stat_get_backend_wait_event (integer)
Wait event name if backend is currently waiting, otherwise NULL. The Table 28-4 for details.