等待事件分析
为了了解数据库当前的运行时状态,管理员可以查看系统视图 sys_stat_activity 来进行查看。该视图能够知道数据库目前正在发生写什么:比如有多少个连接,客户端的情况,每个连接的状态,每个连接上的等待事件等。
注意
当前执行的查询语句和等待事件状态需要在开启 track_activities 参数的情况下才可以查看。开启该参数会带来一定的性能损耗。
1. 活跃会话连接
sys_stat_activity 视图结构如下:
字段名 | 类型 | 说明 |
---|---|---|
datid | oid | 数据库OID |
datname | name | 数据库名称 |
pid | integer | 服务于这个连接的进程ID |
usesysid | oid | 用户ID |
usename | name | 用户名 |
application_name | text | 应用名称 |
client_addr | inet | 客户端地址 |
client_hostname | text | 客户端主机名 |
client_port | integer | 客户端端口号 |
backend_start | timestamp with time zone | 该连接的启动时间 |
xact_start | timestamp with time zone | 当前事务开始时间 |
query_start | timestamp with time zone | 当前查询开始时间 |
state_change | timestamp with time zone | 当前状态改变时间 |
wait_event_type | text | 当前等待事件的类型 |
wait_event | text | 当前等待事件 |
state | text | 当前的状态 |
backend_xid | xid | 这个后端的顶层事务标识符 |
backend_xmin | xid | 这个进程的当前事务被启动的时间 |
query | text | 当前查询语句 |
backend_type | text | 后端进程的类型 |
其中,state为当前连接的状态,其可能的值主要有:
-
active:后端正在执行一个查询。
-
idle:后端正在等待一个新的客户端命令。
-
idle in transaction:后端在一个事务中,但是当前没有正在执行一个查询。
-
idle in transaction (aborted):这个状态与 idle in transaction 相似,不过在该事务中的一个语句导致了一个错误。
-
fastpath function call:后端正在执行一个 fast-path 函数。
-
disabled:如果在这个后端中 track_activities 被禁用,则报告这个状态。
注意
如果一个查询或者事务执行的时间过长而一直没有结束,很有可能会拖累整个系统的性能表现,需要具体分析问题的原因并采取合适的办法。
2. 等待事件¶
等待事件是数据库内部记录的一种统计信息,该信息出现表明数据库的服务进程必须在等待事件完成后才能继续处理。等待事件揭示了可能影响性能的各种问题症状,比如缓冲区中争用、锁争用等。
sys_stat_activity 里记录的等待事件是瞬时信息,没有对等待事件的时间进行累计,所以量化等待事件上存在一些问题。用户可以考虑多次人工采样来收集等待事件信息,如果在等待的连接较多,则需要等待事件对系统性能造成了较大影响,需要做具体分析。
注意
当前执行的查询语句和等待事件状态需要在开启 track_activities 参数的情况下才可以查看。开启该参数会带来一定的性能损耗。
sys_stat_activity 视图中,wait_event_type 和 wait_event 字段记录了等待时间相关的内容。当一个会话处于等待状态时,wait_event 与 wait_event_type 两列非空,表示会话正在等待的事件和等待事件类型。
wait_event_type的可能值有:
-
LWLock:轻量级锁
-
Lock:重量级锁
-
BufferPin:数据缓冲区等待
-
Activity:后台辅助进程活动等待
-
Client:客户端等待
-
Extension:扩展插件等待
-
IPC:进程间通讯等待
-
TimeOut:超时等待
-
IO:IO等待
wait_event 为等待事件名称,包括:
等待事件类型 | 等待事件名称 | 描述 |
---|---|---|
LWLock | ShmemIndexLock | 正等待在共享内存中查找或者分配空间。 |
OidGenLock | 正等待分配或者赋予一个 OID。 | |
XidGenLock | 正等待分配或者赋予一个事务 ID。 | |
ProcArrayLock | 正等待在事务结尾得到一个快照或者清除事务 ID。 | |
SInvalReadLock | 正等待从共享无效消息队列中检索或者移除消息。 | |
SInvalWriteLock | 正等待在共享无效消息队列中增加一个消息。 | |
WALBufMappingLock | 正等待在 WAL 缓冲区中替换一个页面。 | |
WALWriteLock | 正等待 WAL 缓冲区被写入到磁盘。 | |
ControlFileLock | 正等待读取或者更新控制文件或创建一个新的 WAL 文件。 | |
CheckpointLock | 正等待执行检查点。 | |
CLogControlLock | 正等待读取或者更新事务状态。 | |
SubtransControlLock | 正等待读取或者更新子事务信息。 | |
MultiXactGenLock | 正等待读取或者更新共享多事务状态。 | |
MultiXactOffsetControlLock | 正等待读取或者更新多事务偏移映射。 | |
MultiXactMemberControlLock | 正等待读取或者更新多事务成员映射。 | |
RelCacheInitLock | 正等待读取或者写入关系缓冲区初始化文件。 | |
CheckpointerCommLock | 正等待管理 fsync 请求。 | |
TwoPhaseStateLock | 正等待读取或者更新预备事务的状态。 | |
TablespaceCreateLock | 正等待创建或者删除表空间。 | |
BtreeVacuumLock | 正等待读取或者更新一个 B-树索引的 vacuum 相关的信息。 | |
AddinShmemInitLock | 正等待管理共享内存中的空间分配。 | |
AutovacuumLock | 自动清理工作者或者启动器正等待更新或者读取自动清理工作者的当前状态。 | |
AutovacuumScheduleLock | 正等待确认选中进行清理的表仍需要清理。 | |
SyncScanLock | 正等待为同步扫描得到一个表上扫描的开始位置。 | |
RelationMappingLock | 正等待更新用来存储目录到文件节点映射的关系映射文件。 | |
AsyncCtlLock | 正等待读取或者更新共享通知状态。 | |
AsyncQueueLock | 正等待读取或者更新通知消息。 | |
SerializableXactHashLock | 正等待检索或者存储有关可序列化事务的信息。 | |
SerializableFinishedListLock | 正等待访问已结束可序列化事务的列表。 | |
SerializablePredicateLockListLock | 正等待在由可序列化事务持有的所列表上执行一个操作。 | |
OldSerXidLock | 正等待读取或者记录冲突的可序列化事务。 | |
SyncRepLock | 正等待读取或者更新有关同步复制的信息。 | |
BackgroundWorkerLock | 正等待读取或者更新后台工作者状态。 | |
DynamicSharedMemoryControlLock | 正等待读取或者更新动态共享内存状态。 | |
AutoFileLock | 正等待更新kingbase.auto.conf文件。 | |
ReplicationSlotAllocationLock | 正等待分配或者释放一个复制槽。 | |
ReplicationSlotControlLock | 正等待读取或者更新复制槽状态。 | |
CommitTsControlLock | 正等待读取或者更新事务提交时间戳。 | |
CommitTsLock | 正等待读取或者更新事务时间戳的最新设置值。 | |
ReplicationOriginLock | 正等待设置、删除或者使用复制源头。 | |
MultiXactTruncationLock | 正等待读取或者阶段多事务信息。 | |
OldSnapshotTimeMapLock | 正等待读取或者更新旧的快照控制信息。 | |
LogicalRepWorkerLock | 正等待逻辑复制工作者上的动作完成。 | |
CLogTruncationLock | 等待执行txid_st atus或更新可用的最新事务id。 | |
clog | 正在等地clog (事务状态)缓冲区上的I/O。 | |
commit_timestamp | 正等待提交时间戳缓冲区上的 I/O。 | |
subtrans | 正等待子事务缓冲区上的 I/O。 | |
multixact_offset | 正等待多事务偏移缓冲区上的 I/O。 | |
multixact_member | 正等待多事务成员缓冲区上的 I/O。 | |
async | 正等待 async(通知)缓冲区上的 I/O。 | |
oldserxid | 正等待 oldserxid 缓冲区上的 I/O。 | |
wal_insert | 正等待把 WAL 插入到一个内存缓冲区。 | |
buffer_content | 正等待读取或者写入内存中的一个数据页。 | |
buffer_io | 正等待一个数据页面上的 I/O。 | |
replication_origin | 正等待读取或者更新复制进度。 | |
replication_slot_io | 正等待一个复制槽上的 I/O。 | |
proc | 正等待读取或者更新 fast-path 锁信息。 | |
buffer_mapping | 正等待把一个数据块与缓冲池中的一个缓冲区关联。 | |
lock_manager | 正等待增加或者检查用于后端的锁,或者正等待加入或者退出一个锁定组(并行查询使用)。 | |
predicate_lock_manager | 正等待增加或者检查谓词锁信息。 | |
serializable_xact | 等待在并行查询中对一个可序列化事务执行操作。 | |
parallel_query_dsa | 正等待并行查询动态共享内存分配锁。 | |
tbm | 正等待TBM共享迭代器锁。 | |
parallel_append | 在Parallel Append计划执行期间等待选择下一个子计划。 | |
parallel_hash_join | 在Parallel Hash计划执行期间等待分配或交换一块内存或者更新计数器。 | |
Lock | relation | 正等待获得一个关系上的锁。 |
extend | 正等待扩展一个关系。 | |
page | 正等待获得一个关系上的页面的锁。 | |
tuple | 正等待获得一个元组上的锁。 | |
transactionid | 正等待一个事务结束。 | |
virtualxid | 正等待获得一个虚拟 xid 锁。 | |
speculative token | 正等待获取一个 speculative insertion lock。 | |
object | 正等待获得一个非关系数据库对象上的锁。 | |
userlock | 正等待获得一个用户锁。 | |
advisory | 正等待获得一个咨询用户锁。 | |
BufferPin | BufferPin | 正等待在一个缓冲区上加 pin。 |
Activity | ArchiverMain | 正在归档进程的主循环中等待。 |
AutoVacuumMain | 正在autovacu um启动器进程的主循环中等待。 | |
BgWriterHibernate | 正在后台写入器进程中等待,休眠中。 | |
BgWriterMain | 正在后台写入器进程的后台工作者的主循环中等待。 | |
CheckpointerMain | 正在检查点进程的主循环中等待。 | |
LogicalApplyMain | 正在逻辑应用进程的主循环中等待。 | |
LogicalLauncherMain | 正在逻辑启动器进程的主循环中等待。 | |
PgStatMain | 正在统计收集器进程的主循环中等待。 | |
RecoveryWalAll | 在恢复时等待来自于任意类型来源(本地、归档或流)的WAL。 | |
RecoveryWalStream | 在恢复时等待来自于一个流的WAL。 | |
SysLoggerMain | 正在系统日志进程的主循环中等待。 | |
WalReceiverMain | 正在W AL接收器进程的主循环中等待。 | |
WalSenderMain | 正在W AL发送器进程的主循环中等待。 | |
WalWriterMain | 正在W AL写入器进程的主循环中等待。 | |
Client | ClientRead | 正等待从客户端读取数据。 |
ClientWrite | 正等待向客户端写入数据。 | |
LibPQWalReceiverConnect | 正在WAL接收器中等待建立与远程服务器的连接。 | |
LibPQWalReceiverReceive | 正在WAL接收器中等待从远程服务器接收数据。 | |
SSLOpenServer | 正在尝试连接期间等待SSL。 | |
WalReceiverWaitStart | 正等待start up进程发送流复制的初始数据。 | |
WalSenderWaitForWAL | 正在WA L发送器进程中等待WAL被刷写。 | |
WalSenderWriteData | 在WAL发送器进程中处理来自WAL 接收器的回复时等待任意活动。 | |
Extension | Extension | 正在一个扩展中等待。 |
IPC | BgWorkerShutdown | 正等待后台工作者关闭。 |
BgWorkerStartup | 正等待后台工作者启动。 | |
BtreePage | 正等待继续并行 B-树扫描所需的页号变得可用。 | |
CheckpointDone | 等待检查点完成。 | |
CheckpointStart | 等待检查点开始。 | |
ClogGroupUpdate | 正等待组领袖在事务结束时更新事务状态。 | |
ExecuteGather | 在执行Gather 节点时等待来自子进程的活动。 | |
Hash/Batch/Allocating | 正等待一个选出的Parallel Hash参与者分配哈希表。 | |
Hash/Batch/Electing | 正在选出一个Parallel Hash参与者来分配一个哈希表。 | |
Hash/Batch/Loading | 正等待其他Parallel Hash参与者完成装载哈希表。 | |
Hash/Build/Allocating | 正等待一个选出的Parallel Hash参与者分配初始哈希表。 | |
Hash/Build/Electing | 正在选出一个Parallel Hash参与者以分配初始哈希表。 | |
Hash/Build/HashingInner | 正等待其他Parallel Hash参与者完成对内关系的哈希操作。 | |
Hash/Build/HashingOuter | 正等待其他Parallel Hash参与者完成对外关系的哈希操作。 | |
Hash/GrowBatches/Allocating | 正等待一个选出的Parallel Hash参与者分配更多批次。 | |
Hash/GrowBatches/Deciding | 正在选出一个Parallel Ha sh参与者决定未来的批次增长。 | |
Hash/GrowBatches/Electing | 正在选出一个Parallel Hash参与者分配更多批次。 | |
Hash/GrowBatches/Finishing | 正在等待一个选出的Parallel Ha sh参与者决定未来的批次增长。 | |
Hash/GrowBatches/Repartitioning | 正等待其他Parallel Hash参与者完成重新分区。 | |
Hash/GrowBuckets/Allocating | 正等待一个选出的Parallel Hash参与者完成更多桶的分配。 | |
Hash/GrowBuckets/Electing | 正在选出一个Parallel Hash参与者分配更多桶。 | |
Hash/GrowBuckets/Reinserting | 正等待其他Parallel Hash参与者完成将元组插入到新桶的操作。 | |
LogicalSyncData | 正等待逻辑复制的远程服务器发送用于初始表同步的数据。 | |
LogicalSyncStateChange | 正等待逻辑复制的远程服务器更改状态。 | |
MessageQueueInternal | 正等待其他进程被挂接到共享消息队列。 | |
MessageQueuePutMessage | 正等待把一个协议消息写到一个共享消息队列。 | |
MessageQueueReceive | 正等待从一个共享消息队列接收字节。 | |
MessageQueueSend | 正等待向一个共享消息队列中发送字节。 | |
ParallelBitmapScan | 正等待并行位图扫描被初始化。 | |
ParallelCreateIndexScan | 正等待并行CREATE INDEX工作者完成堆扫描。 | |
ParallelFinish | 正等待并行工作者完成计算。 | |
ProcArrayGroupUpdate | 正等待组领袖在事务结束时清除事务ID。 | |
Promote | 等待备用节点升级。 | |
ReplicationOriginDrop | 正等待一个复制源头变得不活跃以便被删除。 | |
ReplicationSlotDrop | 正等待一个复制槽变得不活跃以便被删除。 | |
SafeSnapshot | 正等待一个用于READ ONLY DEFERRABLE事务的快照。 | |
SyncRep | 正在同步复制期间等待来自远程服务器的确认。 | |
Timeout | BaseBackupThrottle | 当有限流活动时在基础备份期间等待。 |
PgSleep | 正在调用sys_sleep的进程中等待。 | |
RecoveryApplyDelay | 在恢复时等待应用WAL,因为它被延迟了。 | |
IO | BufFileRead | 正等待从一个缓存的文件中读取。 |
BufFileWrite | 正等待向一个缓存的文件中写入。 | |
ControlFileRead | 正等待从控制文件中读取。 | |
ControlFileSync | 正等待控制文件到达稳定存储。 | |
ControlFileSyncUpdate | 正等待对控制文件的更新到达稳定存储。 | |
ControlFileWrite | 正等待一个对控制文件的写入。 | |
ControlFileWriteUpdate | 正等待一个写操作更新控制文件。 | |
CopyFileRead | 正在文件拷贝操作期间等待一个读操作。 | |
CopyFileWrite | 正在文件拷贝操作期间等待一个写操作。 | |
DataFileExtend | 正等待一个关系数据文件被扩充。 | |
DataFileFlush | 正等待一个关系数据文件到达稳定存储。 | |
DataFileImmediateSync | 正等待一个关系数据文件的立即同步到达稳定存储。 | |
DataFilePrefetch | 正等待从一个关系数据文件中的一次异步预取。 | |
DataFileRead | 正等待一次对一个关系数据文件的读操作。 | |
DataFileSync | 正等待对一个关系数据文件的更改到达稳定存储。 | |
DataFileTruncate | 正等待一个关系数据文件被截断。 | |
DataFileWrite | 正等待一次对一个关系数据文件的写操作。 | |
DSMFillZeroWrite | 等待向一个动态共享内存备份文件中写零字节。 | |
LockFileAddToDataDirRead | 在向数据目录锁文件中增加一行时等待一个读操作。 | |
LockFileAddToDataDirSync | 在向数据目录锁文件中增加一行时等待数据到达稳定存储。 | |
LockFileAddToDataDirWrite | 在向数据目录锁文件中增加一行时等待一个写操作。 | |
LockFileCreateRead | 在创建数据目录锁文件期间等待读取。 | |
LockFileCreateSync | 在创建数据目录锁文件期间等待数据到达稳定存储。 | |
LockFileCreateWrite | 在创建数据目录锁文件期间等待一个写操作。 | |
LockFileReCheckDataDirRead | 在重新检查数据目录锁文件的过程中等待一个读操作。 | |
LogicalRewriteCheckpointSync | 在一个检查点期间等待逻辑重写映射到达稳定存储。 | |
LogicalRewriteMappingSync | 在一次逻辑重写期间等待映射数据到达稳定存储。 | |
LogicalRewriteMappingWrite | 在一次逻辑重写期间等待对映射数据的写操作。 | |
LogicalRewriteSync | 正等待逻辑重写映射到达稳定存储。 | |
LogicalRewriteWrite | 正等待对逻辑重写映射的写操作。 | |
RelationMapRead | 正等待对关系映射文件的读操作。 | |
RelationMapSync | 正等待关系映射文件到达稳定存储。 | |
RelationMapWrite | 正等待对关系映射文件的写操作。 | |
ReorderBufferRead | 在重排序缓冲区管理期间等待一个读操作。 | |
ReorderBufferWrite | 在重排序缓冲区管理期间等待一个写操作。 | |
ReorderLogicalMappingRead | 在重排序缓冲区管理期间等待对一个逻辑映射的读操作。 | |
ReplicationSlotRead | 正等待对一个复制槽控制文件的读操作。 | |
ReplicationSlotRestoreSync | 在把一个复制槽控制文件恢复到内存的过程中等待它到达稳定存储。 | |
ReplicationSlotSync | 正等待一个复制槽控制文件到达稳定存储。 | |
ReplicationSlotWrite | 正等待对一个复制槽控制文件的写操作。 | |
SLRUFlushSync | 在检查点或者数据库关闭期间等待SLRU数据到达稳定存储。 | |
SLRURead | 正等待对一个SLRU页面的读操作。 | |
SLRUSync | 正等待SLRU数据在一个页面写之后到达稳定存储。 | |
SLRUWrite | 正等待一个SLRU页面上的写操作。 | |
SnapbuildRead | 正等待一个序列化历史目录快照的读操作。 | |
SnapbuildSync | 正等待一个序列化历史目录快照到达稳定存储。 | |
SnapbuildWrite | 正等待一个序列化历史目录快照的写操作。 | |
TimelineHistoryFileSync | 正等待一个通过流复制接收到的时间线历史文件到达稳定存储。 | |
TimelineHistoryFileWrite | 正等待一个通过流复制接收到的时间线历史文件的读操作。 | |
TimelineHistoryRead | 正等待一个时间线历史文件上的读操作。 | |
TimelineHistorySync | 正等待一个新创建的时间线历史文件达到稳定存储。 | |
TimelineHistoryWrite | 正等待一个新创建的时间线历史文件上的写操作。 | |
TwophaseFileRead | 正等待一个两阶段状态文件的读操作。 | |
TwophaseFileSync | 正等待一个两阶段状态文件到达稳定存储。 | |
TwophaseFileWrite | 正等待一个两阶段状态文件的写操作。 | |
WALBootstrapSync | 在自举期间等待WAL到达稳定存储。 | |
WALBootstrapWrite | 在自举期间等待一个WAL页面的写操作。 | |
WALCopyRead | 在通过拷贝一个已有WAL段创建一个新的WAL段时等待一个读操作。 | |
WALCopySync | 正等待一个通过拷贝已有WAL段创建的新WAL段到达稳定存储。 | |
WALCopyWrite | 在通过拷贝一个已有WAL段创建一个新的WAL段时等待一个写操作。 | |
WALInitSync | 正等待一个新初始化的WAL文件到达稳定存储。 | |
WALInitWrite | 在初始化一个新的 WAL文件期间等待一个写操作。 | |
WALRead | 正等待一次对一个WAL文件的读操作。 | |
WALSenderTimelineHistoryRead | 在walsender的时间线命令期间等待对一个时间线历史文件的读操作。 | |
WALSync | 等待 WAL文件到达持久的存储空间。 | |
WALSyncMethodAssign | 在指派WAL同步方法时等待数据到达稳定存储。 | |
WALWrite | 正等待一次对一个WAL文件的写操作。 |
关于等待事件,可以使用下面例子中的SQL语句对其进行查询:
SELECT wait_event_type, wait_event, state, count(*) FROM sys_stat_activity group by wait_event_type, wait_event, state;
如果某个活跃连接经常出现长时间的等待事件,则需要对其进行进一步的分析。
例如: 我们在跑高并发的tpcc时,可能看到如下等待事件:
SELECT wait_event_type, wait_event, state, count(*) FROM sys_stat_activity group by wait_event_type, wait_event, state order by 4 desc;
采样几次以后,发现等待事件DataFileRead,DataFileWrite一直过高。
DataFileRead、DataFileWrite是等待读、写一个关系表的等待事件,说明IO很频繁,可以通过调大 shared_buffers 让更多的数据页缓存在共享内存里来减少IO。