PostgreSQL 9.6 等待事件的詳細分析

背景

PostgreSQL 9.6動態視圖pg_stat_activity新增了wait_event_type, wait_event的等待事件展示。

當會話處於等待狀態時，wait_event與wait_event_type非空，表示會話正在等待的類型。

根據等待資訊，可以瞭解當前會話的狀態。

將來也可以通過外掛程式的形式，掌握資料庫在某個時間段內的等待事件統計，更好的診斷資料庫的健康狀態。

已有的外掛程式如下

https://github.com/postgrespro/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
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.