GaussDB 数据库架构师修炼(十九)-性能调优-长事务分析

1 问题描述

长事务在数据库的表现是session持续时间长，期间可能伴随CPU、内存升高，严重可导致数据库整体响应 缓慢，业务无法正常运行，所以在业务系统中应该尽量避免长事务的发生。

2 问题现象

用户执行事务耗时过长

3 对业务影响

• 1 可能造成大量业务等锁超时
• 2 执行时间长，容易造成主备复制时延过高
• 3 回滚所需要的时间比较长

4 主要原因

1 大量的锁竞争

2 执行了比较耗时的SQL

5 处理过程

步骤1：通过pg_stat_activity视图，查询当前数据库中存在的长事务：

select  pid, sessionid, query_id,substring(query,0,100) as query, state, usename, now()-xact_start as runtime    
from pg_stat_activity 
where state!='idle' 
and datname in('postgres') 
and usename in  ('root') 
and extract(epoch from current_timestamp-xact_start)/60 > 0.5;

如上查询结果返回数据库中执行时间超过30s的长事务

步骤2：结合pg_thread_wait_status视图通过如下语句查看长事务会话的阻塞情况，排查是否因为锁阻塞导致。

gaussdb=> select 
a.query_id,
a.query,
b.wait_status, 
b.wait_event, 
b.block_sessionid,
c.pid,
block_pid,
c.query as block_query
from pg_stat_activity a,pg_thread_wait_status b,pg_stat_activity c
where a.query_id= b.query_id 
and b.block_sessionid=c.sessionid 
and a.state!='idle' 
and a.query_id=1962725012603471261;

步骤3：通过如下结果可以定位到当前语句的wait_status状态为acquire_lock，以及阻塞当前语句的会话id和query信息。如果wait_status状态为none，说明该长事务非锁阻塞导致，可能是事务本身长时间执行未结束。

步骤4：如果是锁阻塞导致或事务自身原因长时间执行未结束， kill当前会话：

pg_terminate_session($pid, $sessionid);