datname:用户所连接数据库的名称。 wait_event:进程正在等待的事件的名称(如果有)。如果进程处于活动状态并且存在wait_event,则表示查询当前被系统的其他部分阻塞。 wait_event_type:进程正在等待的事件的类别。 pid:进程的进程号。 query_start:对于活动查询,当前查询开始的时间戳。 xact_start:如果进程正在执行事务,...
application_name|ksql client_addr|client_hostname|client_port|-1backend_start|2022-04-2210:20:29.124634+08xact_start|2022-04-2210:20:30.962902+08query_start|2022-04-2210:20:30.962902+08state_change|2022-04-2210:20:30.962905+08wait_event_type|Lockwait_event|relation state|active backend_xid|12...
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted |fastpath---+---+---+---+---+---+---+---+---+---+---+---+---+---+--- advisory | 16393 |...
pg_stat_activity快照:在问题时段内(例如14:10, 14:30, 14:50)多次执行SELECT * FROM pg_stat_activity WHERE state = 'active' OR wait_event_type IS NOT NULL;并记录结果。 pg_locks快照:同样在问题时段内多次执行SELECT locktype, relation::regclass, pid, mode, granted FROM pg_locks WHERE NOT gr...
Wait Event Type Wait Event Name Description LWLock 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. Pr...
Wait eventDefinition Client:ClientRead This event occurs when Aurora PostgreSQL is waiting to receive data from the client. Client:ClientWrite This event occurs when Aurora PostgreSQL is waiting to write data to the client. CPU This event occurs when a thread is active in CPU or is waiting...
wait_event, usename, locktype, mode, granted FROM pg_stat_activity LEFT JOIN pg_locks ON pg_stat_activity.pid = pg_locks.pid WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_%' AND query NOT ILIKE '%application_name%' AND query NOT ILIKE '%inet%' ...
select pid,now()-query_start as "执行时间",wait_event_type,wait_event,query from pg_stat_activity; 上面两个SQL执行的效果是一致的,直接查看整个视图信息会较全,但是比较多,可使用第二个视图,其中,wait_event wait_event_type 字段代表等待事件。不同的等待事件代表不同的含义。
pg_stat_activity的最后一部分包含连接的实际状态。如果查询正在等待另一个释放锁,那么wait_event_type包含关于它是哪种等待事件的一些信息,而wait_event列将显示等待事件的名称。 state”显示当前连接的状态,如活动、空闲、事务中的空闲,查询列将显示正在运行的实际查询,或最近运行的查询。
wait_event_type : 会话状态,Lock表示被阻塞无法获取锁资源 wait_event : 等待事件 query : 会话执行相关查询 事务一事务二事务三 begin; begin - update t1 set info ='aaaaaaaaabbbbbbbbb' where id=2; - - - update t1 set info ='aaaaaaaaa' where id=2;//被夯住 - - - select pid,pg_blocki...