PostgreSQL 等待事件 及 等待采样统计(pg_wait_sampling)

5 minute read

背景

PostgreSQL 9.6动态视图pg_stat_activity新增了wait_event_type, wait_event的等待事件展示。

当会话处于等待状态时,wait_event与wait_event_type非空,表示会话正在等待的类型。

根据等待信息,可以了解当前会话的状态。

将来也可以通过插件的形式,掌握数据库在某个时间段内的等待事件统计,更好的诊断数据库的健康状态。

已有的插件如下

https://github.com/postgrespro/pg_wait_sampling

https://postgrespro.com/docs/enterprise/10/pg-wait-sampling

用于对等待事件进行采样。

例子

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 profile sampling in milliseconds 10
pg_wait_sampling.profile_pid bool Whether profile should be per pid true

pg_wait_sampling_current 视图 列出了当前所有进程(包括background process)的等待事件。

pg_wait_sampling_get_current(pid int4) 用于查看指定PID当前的等待事件。

pg_wait_sampling_history 视图,列出了每次采样时捕获到的每个进程的等待事件信息。

pg_wait_sampling_profile 视图,按PID进行等待事件的统计,统计每个PID的每个等待事件的次数。

pg_wait_sampling.profile_pid配置为FALSE时,则将所有的PID的统计信息合起来,不单独算。

PostgreSQL 9.6 等待事件

详见
https://www.postgresql.org/docs/9.6/static/monitoring-stats.html

pg_stat_activity 视图新增等待事件列,可以观察到会话当前的等待。

1. wait_event_type
表示等待时间的类别,如果backend处于等待状态则有内容,否则为空。

类别如下

1.1 LWLockNamed:

命名的轻量锁,这种锁的目的是用于保护内存中的数据结构,防止并发的问题。      
    
The backend is waiting for a specific named lightweight lock.     
    
Each such lock protects a particular data structure in shared memory.     

1.2 LWLockTranche:

分组轻量锁,没有细分名字,只是笼统的分类。      
  
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:

重量级锁,用于保护SQL可见对象,例如表。  也可以用于保护存储,例如扩展表时。      
  
见 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 extension.     

1.4 BufferPin:

bufferpin用于保护数据库data buffer中的数据,例如保护游标访问的数据。      
  
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.    

2. wait_event

表示wait_event_type中对应的详细的等待事件。      

如果当前backend处于等待状态,则有值,否则为空    
  
Wait event name if backend is currently waiting, otherwise NULL.     

2.1 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       
    通常出现在高并发的请求事务号,并且开启了old_snapshot_threshold时    
    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        
    wal刷盘较慢时,可以提高wal writer频率,或者加大BUFFER,或者提高目标盘的IOPS,降低目标盘的RT。      
    Waiting for WAL buffers to be written to disk.    
  
ControlFileLock     
    如果产生XLOG确实很频繁,并且没有办法降低,可以使用较大的XLOG文件,最大64MB。      
    通常这个很少见。    
    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        
    检查点分三步(write, sync_file_range, fsync),表示fsync请求出现等待,需要提高IO,或者减少fsync时的dirty page。      
    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 B-tree 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      
    说明autovacuum单表比较慢,看看是否可以关闭autovacuum的SLEEP调度。    
    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.    
  
OldSnapshotTimeMapLock      
    Waiting to read or update old snapshot control information.    

2.2 LWLockTranche

clog        
    通常很少见,可能出现在在非常高并发的极小写事务时,文件IO出现等待,使用cgroup可以很容易复现。      
    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      
    指 数据库 shared buffer    
    Waiting to read or write a data page in memory.    
  
buffer_io           
    指 数据库 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 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.    

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. 获取当指定PID当前的等待信息。

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

Flag Counter

digoal’s 大量PostgreSQL文章入口