LightDB 23.1 EM新特性支持顶级活动监控、索引监控等

原创姚崇 2023-03-20

231

LightDB 23.1 开始EM支持顶级活动监控，一眼便可以看出数据库在等待什么，哪个SQL耗时最多

如上图听过等待时间DataFileWrite@IO占比最高，说明数据库在等待数据文件IO写方面可能成为瓶颈

LightDB 的常见等待事件

以下是 LightDB PostgreSQL 的常见等待事件。要了解有关等待事件和调整 LightDB PostgreSQL 数据库集群的更多信息，请参阅使用 LightDB PostgreSQL 的等待事件进行优化。
大多数并没有经过实际案例的验证，因此目前只能用“可能”两个字，也希望对此有兴趣的朋友们在实践中加以纠正。

分类	名称	描述	关联根因
LWLock	ShmemIndexLock	等待在共享内存中分配内存	共享内存操作，并发
LWLock	OidGenLock	等待分配OID	并发DDL
LWLock	XidGenLock	等待生成事务XID	并发事务
LWLock	ProcArrayLock	等待获得snapshot或者在会话结束时清理XID	并发事务
LWLock	SInvalReadLock	等待从共享缓冲失效队列中检索或删除消息	shared buffers，并发SQL
LWLock	SInvalWriteLock	等待在共享缓冲失效队列中添加消息	shared buffers，并发SQL
LWLock	WALBufMappingLock	等待替换 WAL 缓冲区中的页面	WAL BUFFER,DML，并发写入
LWLock	SInvalReadLock	等待从共享缓冲失效队列中检索或删除消息	shared buffers，并发SQL
LWlock	WALWriteLock	等待从WAL缓冲区中写数据到磁盘	DML,并发写入，磁盘IO性能
LWlock	ControlFileLock	等待读取或者修改控制文件，或者创建一个新的WAL文件	DML,并发写入，磁盘IO性能
LWlock	CheckpointLock	等待执行CKPT	并发事务
LWlock	SubtransControlLock	等待读取或者修改子事务信息	并发事务，子事务，SAVEPOINT
LWlock	MultiXactGenLock	等待读取或者修改共享组合事务( multixact)状态	并发事务，共享组，SAVEPOINT
LWlock	MultiXactOffsetControlLock	等待读取或者修改组合事务(multixact) 偏移映射信息	并发事务
LWlock	MultiXactMemberControlLock	等待读取或者修改组合事务(multixact) 成员映射信息	并发事务
LWlock	RelCacheInitLock	等待读写 relation cache初始化文件（pg_internal.init）	磁盘IO性能，数据库中表的数量过多
LWlock	CheckpointerCommLock	等待管理fsync 请求	磁盘IO性能，并发写入
LWlock	TwoPhaseStateLock	等待读取或者修改prepared transaction的状态	分布式事务
LWlock	TablespaceCreateLock	等待创建或者删除表空间	表空间操作，磁盘IO性能，文件系统
LWlock	BtreeVacuumLock	等待读取或者修改vacuum相关的B树索引信息	VACUUM,索引
LWlock	AddinShmemInitLock	等待共享内存中的内存空间管理	共享内存初始化
LWlock	AutovacuumLock	等待Autovacuum worker 或者launcher等待读取或者修改 autovacuum worker的当前状态	VACUUM
LWlock	AutovacuumScheduleLock	等待被选择做vacuum 的表仍然需要 vacuuming的确认信息	VACUUM
LWlock	SyncScanLock	等待获取表上扫描的开始位置以便于进行同步扫描	表或索引扫描操作
LWlock	RelationMappingLock	等待更新用于存储目录到文件节点映射的关系映射文件	DDL操作
LWlock	AsyncCtlLock	等待读取或者修改共享通知状态	会话数，并发执行，并发事务
LWlock	AsyncQueueLock	等待读取或者修改通知消息	会话数，并发执行，并发事务
LWlock	SerializableXactHashLock	等待检索或者存储serializable事务相关的信息	事务隔离级别，并发事务
LWlock	SerializableFinishedListLock	等待访问serializable 事务完成清单	事务隔离级别，并发事务
LWlock	SerializablePredicateLockListLock	等待在一个被serializable事务锁锁定的清单上做操作	事务隔离级别，并发事务
LWlock	OldSerXidLock	等待读取或记录冲突的可序列化事务	事务隔离级别，并发事务
LWlock	SyncRepLock	等待读取或更新有关同步复制的信息	流复制，同步复制
LWlock	BackgroundWorkerLock	等待读取后者修改后台worker进程的状态	并行执行，后台进程启动，后台进程关闭
LWlock	DynamicSharedMemoryControlLock	等待读取或者修改动态共享内存状态	动态共享内存分配、释放
LWlock	AutoFileLock	等待修改postgresql.auto.conf 文件	参数文件修改
LWlock	ReplicationSlotAllocationLock	等待分配或者始放一个复制槽	流复制，复制槽
LWlock	ReplicationSlotControlLock	等待读取或者修改复制槽状态	流复制，复制槽
LWlock	CommitTsControlLock	等待读取或者修改事务提交时间戳	事务提交，页控制相关，DB CACHE,并发事务，
LWlock	CommitTsLock	等待读取或者修改事务时间戳的最后值集合	事务提交，并发事务，
LWlock	ReplicationOriginLock	等待设置、删除或使用复制源	流复制
LWlock	MultiXactTruncationLock	等待读取或者截断 multixact 信息	事务并发，大事务
LWlock	OldSnapshotTimeMapLock	等待读取或者修改旧的snapshot控制信息	事务并发，SAVEPOINT
LWlock	BackendRandomLock	等待生成随机数	随机数生成
LWlock	LogicalRepWorkerLock	等待逻辑复制的WORKER结束任务	流复制
LWlock	CLogTruncationLock	等待执行txid_status 或者将可获得的最老的transaction id赋给它	事务并发、磁盘IO性能、检查点配置
LWlock	WrapLimitsVacuumLock	等待修改multixact消耗和transaction id的限制	事务并发，磁盘IO性能，VACUUM、维护WORKER配置
LWlock	NotifyQueueTailLock	等待修改通知消息存储限制
LWlock	clog	等待CLOG缓冲区的IO操作	事务并发、磁盘IO性能
LWlock	commit_timestamp	等待 commit timestamp buffer IO操作完成	事务并发、参数配置、磁盘IO性能
LWlock	subtrans	等待 subtransaction buffer IO操作完成	事务并发，磁盘IO性能
LWlock	multixact_offset	等待 multixact offset buffer IO操作完成	事务并发，磁盘IO性能
LWlock	multixact_member	等待 multixact_member buffer IO操作完成	事务并发，磁盘IO性能
LWlock	async	等待async (notify) buffer IO完成	活跃会话、磁盘IO性能
LWlock	oldserxid	等待oldserxid buffer IO完成	磁盘IO性能，事务并发
LWlock	wal_insert	等待将WAL插入缓冲区	事务并发、WALBUFFER
LWlock	buffer_content	等待在DB CACHE中读写数据页	磁盘IO性能、热块、DBCACHE
LWlock	buffer_io	等待数据页IO完成	磁盘IO性能、检查点、热块
LWlock	replication_origin	等待读取或者修改复制进度	数据库复制
LWlock	replication_slot_io	等待复制槽上的IO	数据库复制、磁盘IO性能
LWlock	proc	等待读取或者修改快速路径锁的信息
LWlock	buffer_mapping	等待将数据块与缓冲池中的缓冲区关联	DBCACHE，热块冲突
LWlock		lock_manager	在并行执行中，等待为后端添加或检查锁，或者等待加入或退出锁组
LWlock	predicate_lock_manager	等待添加或检查谓词锁信息	并发执行
LWlock	parallel_query_dsa	等待并行查询动态共享内存分配锁
LWlock	tbm	等待 TBM 共享迭代器锁，一般发生在并行bitmap扫描中，等待TID BITMAP	并发执行、索引扫描
lock	relation	等待获取关系上的锁	并发执行
lock	extend	等待扩展 relation结束
lock	frozenid	等待修改 pg_database.datfrozenxid和 pg_database.datminmxid.	VACUUM、磁盘IO性能、数据库配置
lock	page	等待获取relation中的一个页面的锁	热块、DBCACHE
lock	tuple	等待获取元组（tuple）锁	热块、事务并发、DBCACHE
lock	transactionid	等待一个事务结束	事务并发、长事务
lock	virtualxid	等待获取虚拟XID锁	并发执行、活跃会话
lock	speculative token	等待获取推测插入锁	热块、事务并发、热表
lock	object	等待一个非关系数据库锁
lock	userlock	等待获取用户锁
lock	advisory	等待获取建议用户锁
BufferPin	BufferPin	等待获得BUFFER的PIN锁	热块、DBCACHE
activity	ArchiverMain	归档进程的主循环等待	后台进程，一般可忽略
activity	AutoVacuumMain	autovacuum启动进程的主循环等待	后台进程，一般可忽略
activity	BgWriterHibernate	后台写入进程等待，正在休眠	后台进程，一般可忽略
activity	BgWriterMain	bgwriter进程的主循环等待	后台进程，一般可忽略
activity	CheckpointerMain	CKPT进程主循环等待	后台进程，一般可忽略
activity	LogicalApplyMain	逻辑应用进程主循环等待	后台进程，一般可忽略
activity	LogicalLauncherMain	逻辑启动进程主循环等待	后台进程，一般可忽略
activity	PgStatMain	统计信息采集进程主循环等待	后台进程，一般可忽略
activity	RecoveryWalAll	实例恢复时等待WAL数据流到达	等待新的WAL数据
activity	RecoveryWalStream	在恢复时再次尝试检索 WAL 数据之前，等待任何类型的源（本地、存档或流）中的 WAL 数据不可用时	等待新的WAL数据
activity	SysLoggerMain	syslogger进程主循环等待	后台进程，一般可忽略
activity	WalReceiverMain	WAL接收进程主循环等待	后台进程，一般可忽略
activity	WalSenderMain	WAL发送进程主循环等待	后台进程，一般可忽略
activity	WalWriterMain	WAL写进程主循环等待	后台进程，一般可忽略
client		clientRead	等待读取客户端输入
client		clientWrite	等待向客户端发送数据
client	LibPQWalReceiverConnect	在 WAL 接收器中等待建立与远程服务器的连接。
client	LibPQWalReceiverReceive	等待 WAL 接收器接收来自远程服务器的数据。
client	SSLOpenServer	等待SSL连接
client	WalReceiverWaitStart	等待启动进程发送初始化复制数据流
client	WalSenderWaitForWAL	在WAL发送进程中等待WAL刷新
client	WalSenderWriteData	在 WAL 发送者进程中处理来自 WAL 接收者的回复时等待任何活动
IPC	BgWorkerShutdown	等待后台worker关闭
IPC	BgWorkerStartup	等待后台worker启动
IPC	BtreePage	等待继续并行 B 树扫描所需的页可用（并行索引扫描）	并行执行
IPC	ExecuteGather	执行Gather时等待子进程的活动	表分析
IPC	LogicalSyncData	等待逻辑复制远程服务发送数据，用于初始表同步	逻辑复制
IPC	LogicalSyncStateChange	等待逻辑复制远程服务改变状态	逻辑复制
IPC	MessageQueueInternal	等待其他进程连接到共享消息队列中
IPC	MessageQueuePutMessage	等待写一条协议消息到共享消息队列中
IPC	MessageQueueReceive	等待从共享消息队列中接收字节
IPC	MessageQueueSend	等待向共享消息队列发送字节
IPC	ParallelBitmapScan	等待并行位图索引扫描初始化	并行执行
IPC	ParallelFinish	等待并行查询worker结束计算	并行执行
IPC	ProcArrayGroupUpdate	当事务结束时等待组leader清除transaction id	长事务
IPC	ReplicationOriginDrop	等待复制源变为非活动状态以被删除	复制槽
IPC	ReplicationSlotDrop	等待复制槽变为非活动状态以被删除	复制槽
IPC	SafeSnapshot	一个READ ONLY DEFERRABLE 事务等待snapshot	事务快照
IPC	SyncRep	同步复制时等待远程服务确认	同步复制
IO	BufFileRead	bffered文件读等待	磁盘IO，热块，DBCACHE
IO	BufFileWrite	buffered文件写等待	DBCACHE,磁盘IO
IO	ControlFileRead	等待控制文件读	磁盘IO
IO	ControlFileSync	等待控制文件写入持久化存储	磁盘IO
IO	ControlFileSyncUpdate	等待控制文件修改到达持久化存储	磁盘IO
IO	ControlFileWrite	等待写入控制文件	磁盘IO
IO	ControlFileWriteUpdate	等待一个修改控制文件的写操作	磁盘IO
IO	CopyFileRead	COPY命令中的读等待	磁盘IO
IO	CopyFileWrite	COPY命令中的写等待	磁盘IO
IO	DataFileExtend	等待 relation数据文件扩展	磁盘IO，磁盘容量
IO	DataFileFlush	等待 relation数据文件写入持久存储	磁盘IO
IO	DataFileImmediateSync	等待一个立即同步 relation 数据文件写入持久存储	磁盘IO
IO	DataFilePrefetch	等待从Relation数据文件异步预读数据	磁盘IO
IO	DataFileRead	等待从relation数据文件读数据	磁盘IO
IO	DataFileSync	等待 relation 数据文件的变化写入持久存储	磁盘IO
IO	DataFileTruncate	等待relation 数据文件截断	磁盘IO
IO	DataFileWrite	等待 relation数据文件写	磁盘IO
IO	DSMFillZeroWrite	等待向一个动态共享内存文件写入字节0
IO	lockFileAddToDataDirRead	向数据字典锁文件添加一行时等待读操作	磁盘IO，并发DDL
IO	lockFileAddToDataDirSync	向数据字典锁文件添加一行时等待数据写入持久存储	磁盘IO，并发DDL
IO	lockFileAddToDataDirWrite	向数据字典锁文件添加一行时等待写操作	磁盘IO，并发DDL
IO	lockFileCreateRead	创建数据字典锁文件时等待读操作	磁盘IO
IO	lockFileCreateSync	创建数据字典锁文件时等待数据写入持久存储	磁盘IO
IO	lockFileCreateWrite	创建数据字典锁文件时等待写操作	磁盘IO
IO	LogicalRewriteCheckpointSync	CKPT时等待逻辑重写映射到达持久化存储	磁盘IO，检查点,逻辑复制
IO	LogicalRewriteMappingSync	逻辑重写时等待映射数据达到持久化存储	磁盘IO、逻辑复制
IO	LogicalRewriteMappingWrite	逻辑重写时等待写映射数据达到持久化存储	磁盘IO、逻辑复制
IO	LogicalRewriteSync	等待逻辑重写映射到达持久化存储	磁盘IO、逻辑复制
IO	LogicalRewriteTruncate	等待映射数据截断到达持久化存储	磁盘IO、逻辑复制
IO	LogicalRewriteWrite	等待一个逻辑重写映射写操作	磁盘IO、逻辑复制
IO	RelationMapRead	等待Relation Map文件读	磁盘IO、逻辑复制
IO	RelationMapSync	等待Relation Map文件写入持久存储	磁盘IO
IO	ReorderBufferRead	RecorderBuffer管理中等待读操作（逻辑复制）	磁盘IO、逻辑复制
IO	ReorderBufferWrite	RecorderBuffer管理中等待写操作（逻辑复制）	磁盘IO、逻辑复制
IO	ReorderLogicalMappingRead	RecorderBuffer管理中等待逻辑映射文件读操作	磁盘IO、逻辑复制
IO	ReplicationSlotRestoreSync	当复制槽控制文件从内存中复制时等待该文件写入持久存储	磁盘IO、复制
IO	TimelineHistoryRead	等待时间线历史文件上的读操作	磁盘IO
IO	WALCopyRead	当使用拷贝一个现有的WAL 段创建一个新WAL段的时候等待读操作	磁盘IO、复制
IO	WALCopyWrite	当使用拷贝一个现有的WAL 段创建一个新WAL段的时候等待写操作	磁盘IO、复制
IO	WALInitWrite	初始化新的WAL文件的时候等待写操作	磁盘IO、检查点
IO	WALWrite	等待WAL文件写	磁盘IO、WAL量

等待事件的关联根因都是相对笼统的，因为从等待事件我们还无法直接定位直接原因，只能初步明确一个大体的方向。如果我们想进一步定位问题，可以通过存在较严重问题的等待事件在pg_stat_activity中对应的会话的其他字段，比如SQL语句等来进一步定位，或者根据等待事件关联的问题领域，再使用其他分析工具去做进一步定位。比如如果我们发现IO问题比较多，那么通过OS的iostat就可以进一步分析IO是否真正存在问题，IO问题到底在哪。
这张表格可以在你遇到PG性能问题的时候，通过等待事件来初步定位问题。不过这张表需要放在一个知识体系中，和其他的运维工具配合，才能发挥更大的作用。在D-SMART中，它已经构建为一个知识，通过PG等待事件分析工具，帮助我们发现系统当前存在的问题。并通过发现的问题自动推荐相关的诊断工具，指导DBA进一步进行精确诊断。

下钻SQL ID

点击SQL ID后可以看到具体的SQL文本，同事可以看SQL的执行计划

索引监控

被扫描次数：pg_stat_all_indexes.idx_scan
扫描记录数 pg_stat_all_indexes.idx_tup_read
回表扫描页记录数 pg_stat_all_indexes.idx_tup_fetch

在 PostgreSQL 中，pg_stat_all_indexes 是一个系统视图，用于提供有关所有索引的统计信息。该视图中包含的一些常用列及其含义如下：

idx_scan：索引被扫描的次数，包括索引扫描和索引只扫描。
idx_tup_read：已从索引读取的行数，包括使用索引扫描和索引只扫描的行数。
idx_tup_fetch：通过回表扫描获取的行数，回表扫描是指使用索引返回行指针，然后在堆表上获取相应的行。
这些统计信息可以用于优化查询计划和识别索引效率低下的情况。