PostgreSQL 9.6 支持等待事件统计了

5 minute read

背景

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.  

Flag Counter

digoal’s 大量PostgreSQL文章入口