注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL9.6:新增等待事件(Wait event)性能监控  

2016-05-22 15:14:35|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
 等待事件(Wait event) 是Oracle性能分析一项重要的参考指标,PostgreSQL 9.6 版本之前不支持 Wait event,9.6 版本之后终于支持 Wait event,这在锁性能监控方面前进了一大步。
 
 之前版本 PostgreSQL 的 pg_stat_activity 视图的 waiting 字段判断会话是否等待锁资源(通俗地讲, waiting 值为true表示申请不到锁资源处于等待状态),但是并不会给出具体的锁的信息,9.6 版本之后 pg_stat_activity 视图的 waiting 字段被 wait_event_type 和 wait_event 字段取代,这两个字段分别代表等待事件的类型、等待事件名称,我们先来看以下示例。
 
一 模拟等待事件的测试 
--创建测试表

francs=> drop table test_wait;
DROP TABLE

francs=> create table test_wait(id int4 ,name character varying(32));
CREATE TABLE

francs=> insert into test_wait values(1,'a'),(2,'b'),(3,'c');
INSERT 0 3

francs=> select * from test_wait;
 id | name 
----+------
  1 | a
  2 | b
  3 | c
(3 rows)

--会话一:事务中执行一条ID=3的 UPDATE 语句,不提交

francs=> begin;
BEGIN
francs=> select pg_backend_pid();
 pg_backend_pid 
----------------
          11261
(1 row)

francs=> update test_wait set name='ccc' where id=3;
UPDATE 1

注:注意此时不提交事务。

--会话二:删除ID=3的记录
francs=> select pg_backend_pid();
 pg_backend_pid 
----------------
          11273
(1 row)

francs=> delete from test_wait where id=3;

 备注:此时这条 DELETE 语句处于等待状态。

--会话三:查看 pg_stat_activity 视图
PostgreSQL9.6:新增等待事件(Wait event)性能监控 - francs - PostgreSQL DBA
 备注:这时 pg_stat_activity 视图的 wait_event_type 字段值为 Lock,wait_event 值为 transactionid,表示等待事务完成。

--回到会话二:执行 ALTER TABLE DDL 
终止之前的 DELETE 语句,执行一条 ALTER TABLE 加字段的 DDL,看看情况

francs=> alter table test_wait add column create_time date;

备注:此时这条 alter table DDL处于等待状态

--回到会话三:查看 pg_stat_activity 视图
PostgreSQL9.6:新增等待事件(Wait event)性能监控 - francs - PostgreSQL DBA
 备注:这时 wait_event_type 值仍然为 Lock,wait_event 值变成了 relation,表示申请 relation 级别的锁,这里是指表级锁;通过以上两个测试大概对等待事件有了基本的了解,等待事件种类很多,不同的等待事件具有不同的含义, PostgreSQL9.6 手册提供详细的等待事件表,如下:

--wait_event 表
Wait Event TypeWait Event NameDescription
LWLockNamedShmemIndexLockWaiting to find or allocate space in shared memory.
OidGenLockWaiting to allocate or assign an OID.
XidGenLockWaiting to allocate or assign a transaction id.
ProcArrayLockWaiting to get a snapshot or clearing a transaction id at transaction end.
SInvalReadLockWaiting to retrieve or remove messages from shared invalidation queue.
SInvalWriteLockWaiting to add a message in shared invalidation queue.
WALBufMappingLockWaiting to replace a page in WAL buffers.
WALWriteLockWaiting for WAL buffers to be written to disk.
ControlFileLockWaiting to read or update the control file or creation of a new WAL file.
CheckpointLockWaiting to perform checkpoint.
CLogControlLockWaiting to read or update transaction status.
SubtransControlLockWaiting to read or update sub-transaction information.
MultiXactGenLockWaiting to read or update shared multixact state.
MultiXactOffsetControlLockWaiting to read or update multixact offset mappings.
MultiXactMemberControlLockWaiting to read or update multixact member mappings.
RelCacheInitLockWaiting to read or write relation cache initialization file.
CheckpointerCommLockWaiting to manage fsync requests.
TwoPhaseStateLockWaiting to read or update the state of prepared transactions.
TablespaceCreateLockWaiting to create or drop the tablespace.
BtreeVacuumLockWaiting to read or update vacuum-related information for a Btree index.
AddinShmemInitLockWaiting to manage space allocation in shared memory.
AutovacuumLockAutovacuum worker or launcher waiting to update or read the current state of autovacuum workers.
AutovacuumScheduleLockWaiting to ensure that the table it has selected for a vacuum still needs vacuuming.
SyncScanLockWaiting to get the start location of a scan on a table for synchronized scans.
RelationMappingLockWaiting to update the relation map file used to store catalog to filenode mapping.
AsyncCtlLockWaiting to read or update shared notification state.
AsyncQueueLockWaiting to read or update notification messages.
SerializableXactHashLockWaiting to retrieve or store information about serializable transactions.
SerializableFinishedListLockWaiting to access the list of finished serializable transactions.
SerializablePredicateLockListLockWaiting to perform an operation on a list of locks held by serializable transactions.
OldSerXidLockWaiting to read or record conflicting serializable transactions.
SyncRepLockWaiting to read or update information about synchronous replicas.
BackgroundWorkerLockWaiting to read or update background worker state.
DynamicSharedMemoryControlLockWaiting to read or update dynamic shared memory state.
AutoFileLockWaiting to update the postgresql.auto.conf file.
ReplicationSlotAllocationLockWaiting to allocate or free a replication slot.
ReplicationSlotControlLockWaiting to read or update replication slot state.
CommitTsControlLockWaiting to read or update transaction commit timestamps.
CommitTsLockWaiting to read or update the last value set for the transaction timestamp.
ReplicationOriginLockWaiting to setup, drop or use replication origin.
MultiXactTruncationLockWaiting to read or truncate multixact information.
LWLockTrancheclogWaiting for I/O on a clog (transaction status) buffer.
commit_timestampWaiting for I/O on commit timestamp buffer.
subtransWaiting for I/O a subtransaction buffer.
multixact_offsetWaiting for I/O on a multixact offset buffer.
multixact_memberWaiting for I/O on a multixact_member buffer.
asyncWaiting for I/O on an async (notify) buffer.
oldserxidWaiting to I/O on an oldserxid buffer.
wal_insertWaiting to insert WAL into a memory buffer.
buffer_contentWaiting to read or write a data page in memory.
buffer_ioWaiting for I/O on a data page.
replication_originWaiting to read or update the replication progress.
replication_slot_ioWaiting for I/O on a replication slot.
procWaiting to read or update the fast-path lock information.
buffer_mappingWaiting to associate a data block with a buffer in the buffer pool.
lock_managerWaiting to add or examine locks for backends, or waiting to join or exit a locking group (used by parallel query).
predicate_lock_managerWaiting to add or examine predicate lock information.
LockrelationWaiting to acquire a lock on a relation.
extendWaiting to extend a relation.
pageWaiting to acquire a lock on page of a relation.
tupleWaiting to acquire a lock on a tuple.
transactionidWaiting for a transaction to finish.
virtualxidWaiting to acquire a virtual xid lock.
speculative tokenWaiting to acquire a speculative insertion lock.
objectWaiting to acquire a lock on a non-relation database object.
userlockWaiting to acquire a userlock.
advisoryWaiting to acquire an advisory user lock.
BufferPinBufferPinWaiting to acquire a pin on a buffer.

备注:通过以上表格, wait_event_type 主要分类四类:
  • LWLockNamed:表示backend后台进程等待某种特定的轻量级锁;
  • LWLockTranche:表示backend后台进程等待一组相关轻量级锁;
  • Lock:表示backend后台进程等待重量级的锁,通常是指 relation、tuple、page、transactionid 等子类型锁;
  • BufferPin:表示server process 后台进程等待 buffer pin,手册上解释为 Waiting to acquire a pin on a buffer,比较难理解,以后想想如何模拟此场景。


二 通过 pgbench 实验,监控等待事件
--监控等待事件的 SQL 

SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE  pid <> pg_backend_pid() and wait_event is NOT NULL;
 pid  | wait_event_type |  wait_event
------+-----------------+---------------
 2540 | Lock            | relation
 11273| Lock            | transactionid
(2 rows)   

备注:这条SQL用户监控数据等待事件情况,下面通过 pgbench 压力测试示例监控等待事件情况。

--创建测试表

francs=> create table test_wait_big(id int4 primary key, name character varying(32));
CREATE TABLE

francs=> insert into test_wait_big(id)select n from generate_series(1,3000000) n;
INSERT 0 3000000


--编写 update_1.sql ,用于 pgbench

[pg96@db1 load_test]$ cat update_1.sql 
\set v_id random(1,1000000)

update test_wait_big set name=hashtext('random()*1000000') where id=:v_id;

 备注: 9.6 版本的 random 函数用法不同,使用内置 random 函数替换 \setrandom。

--压力测试

[pg96@db1 load_test]$ nohup pgbench -c 10 -T 90 -d francs -U francs -n N -M prepared -f update_1.sql   >  update_1.out &
[1] 11856   



--查询等待事件情况,每隔1秒运行以下监控SQL
图一
PostgreSQL9.6:新增等待事件(Wait event)性能监控 - francs - PostgreSQL DBA
 
图二
PostgreSQL9.6:新增等待事件(Wait event)性能监控 - francs - PostgreSQL DBA
 
图三
PostgreSQL9.6:新增等待事件(Wait event)性能监控 - francs - PostgreSQL DBA
 备注: 压力测试过程中,监控到了 buffer_content、extend、XidGenLock 等待事件。
  评论这张
 
阅读(545)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018