网站安装步骤页面/seo网络推广招聘

PostgreSQL 9.6 统计信息收集进程pgstat，增加了等待事件信息的收集，并且用户可以获得backend的等待事件信息。

目前支持的等待事件分类如下
src/include/pgstat.h

/* ----------* Wait Classes* ----------*/
typedef enum WaitClass
{WAIT_UNDEFINED,WAIT_LWLOCK_NAMED,WAIT_LWLOCK_TRANCHE,WAIT_LOCK,WAIT_BUFFER_PIN
}       WaitClass;

支持的获取等待事件类别和等待事件信息的函数
src/backend/postmaster/pgstat.c

/* ----------* pgstat_get_wait_event_type() -**      Return a string representing the current wait event type, backend is*      waiting on.*/
const char *
pgstat_get_wait_event_type(uint32 wait_event_info)
{uint8           classId;const char *event_type;/* report process as not waiting. */if (wait_event_info == 0)return NULL;wait_event_info = wait_event_info >> 24;classId = wait_event_info & 0XFF;switch (classId){case WAIT_LWLOCK_NAMED:event_type = "LWLockNamed";break;case WAIT_LWLOCK_TRANCHE:event_type = "LWLockTranche";break;case WAIT_LOCK:event_type = "Lock";break;case WAIT_BUFFER_PIN:event_type = "BufferPin";break;default:event_type = "???";break;}return event_type;
}/* ----------* pgstat_get_wait_event() -**      Return a string representing the current wait event, backend is*      waiting on.*/
const char *
pgstat_get_wait_event(uint32 wait_event_info)
{uint8           classId;uint16          eventId;const char *event_name;/* report process as not waiting. */if (wait_event_info == 0)return NULL;eventId = wait_event_info & ((1 << 24) - 1);wait_event_info = wait_event_info >> 24;classId = wait_event_info & 0XFF;switch (classId){case WAIT_LWLOCK_NAMED:case WAIT_LWLOCK_TRANCHE:event_name = GetLWLockIdentifier(classId, eventId);break;case WAIT_LOCK:event_name = GetLockNameFromTagType(eventId);break;case WAIT_BUFFER_PIN:event_name = "BufferPin";break;default:event_name = "unknown wait event";break;}return event_name;
}

详细的等待信息归类和信息见手册
https://www.postgresql.org/docs/9.6/static/monitoring-stats.html

在pg_stat_activity动态视图中支持的等待事件字段信息如下
wait_event_type

The type of event for which the backend is waiting, if any; otherwise NULL. 
Possible values are:LWLockNamed: 
The backend is waiting for a specific named lightweight lock. Each such lock protects a particular data structure in shared memory. wait_event will contain the name of the lightweight lock.LWLockTranche: 
The backend is waiting for one of a group of related lightweight locks. All locks in the group perform a similar function; wait_event will identify the general purpose of locks in that group.Lock: 
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 extension. wait_event will identify the type of lock awaited.BufferPin: 
The server process is waiting to access to a data buffer during a period when no other process can be examining that buffer. Buffer pin waits can be protracted if another process holds an open cursor which last read data from the buffer in question.

wait_event

Wait event name if backend is currently waiting, otherwise NULL. 
See wait_event for details.

等待事件的归类以及对应的等待信息解释
LWLockNamed

ShmemIndexLock    Waiting to find or allocate space in shared memory.
OidGenLock    Waiting to allocate or assign an OID.
XidGenLock    Waiting to allocate or assign a transaction id.
ProcArrayLock    Waiting to get a snapshot or clearing a transaction id at transaction end.
SInvalReadLock    Waiting to retrieve or remove messages from shared invalidation queue.
SInvalWriteLock    Waiting to add a message in shared invalidation queue.
WALBufMappingLock    Waiting to replace a page in WAL buffers.
WALWriteLock    Waiting for WAL buffers to be written to disk.
ControlFileLock    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    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    Waiting to read or update vacuum-related information for a Btree index.
AddinShmemInitLock    Waiting to manage space allocation in shared memory.
AutovacuumLock    Autovacuum worker or launcher waiting to update or read the current state of autovacuum workers.
AutovacuumScheduleLock    Waiting to ensure that the table it has selected for a vacuum still needs vacuuming.
SyncScanLock    Waiting to get the start location of 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 an 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 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.

LWLockTranche

clog    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 an async (notify) buffer.
oldserxid    Waiting to I/O on an oldserxid buffer.
wal_insert    Waiting to insert WAL into a memory buffer.
buffer_content    Waiting to read or write a data page in memory.
buffer_io    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 block 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.

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.

BufferPin

BufferPin    Waiting to acquire a pin on a buffer.