【云小课】EI第34课 运筹帷幄-GaussDB(DWS)教你分析阻塞SQL的几个妙招

查看SQL运行信息

• 获取当前用户执行SQL信息：

  SELECT usename,state,query FROM PG_STAT_ACTIVITY WHERE DATNAME='数据库名称';

  回显如下，如果state为active，则query列表示当前执行的SQL语句，其他情况则表示为上一个查询语句。

  usename | state | query
  
  ---------+--------+---------------------------------------------------------------------------
  
  leo | idle | select * from joe.mytable;
  
  dbadmin | active | SELECT usename,state,query FROM PG_STAT_ACTIVITY WHERE DATNAME='gaussdb';
  
  joe | idle | GRANT SELECT ON TABLE mytable to leo;
  
  (3 rows)

查看耗时较长的语句

• 查看当前运行中的耗时较长的SQL语句

  SELECT current_timestamp - query_start as runtime, datname, usename, query FROM PG_STAT_ACTIVITY WHERE state != 'idle' order by 1 desc;

  查询会返回按执行时间长短从大到小排列的查询语句列表。第一条结果就是当前系统中执行时间最长的查询语句。

  runtime | datname | usename | query
  
  -----------------+----------+---------+-----------------------------------------------------------------------------------------------------------------------------------------
  
  00:04:47.054958 | gaussdb | leo | insert into mytable1 select generate_series(1, 10000000);
  
  00:00:01.72789 | gaussdb | dbadmin | SELECT current_timestamp - query_start as runtime, datname, usename, query FROM PG_STAT_ACTIVITY WHERE state != 'idle' order by 1 desc;
  
  (2 rows)

• 若当前系统较为繁忙，可以通过限制current_timestamp - query_start大于某一阈值来查看执行时间超过此阈值的查询语句。

  SELECT query from PG_STAT_ACTIVITY WHERE current_timestamp - query_start > 2;

查看处于阻塞状态的语句

• 查看当前处于阻塞状态的查询语句：

  SELECT pid, datname, usename, state, query FROM PG_STAT_ACTIVITY WHERE state <> 'idle' and waiting=true;

  执行以下语句结束到阻塞的SQL会话。

  SELECT PG_TERMINATE_BACKEND(pid);

  

  • 大部分场景下，阻塞是因为系统内部锁而导致的，waiting字段才显示为true，此阻塞可在视图pg_stat_activity中体现。

  • 在一些少数场景下，例如写文件、定时器等情况的查询阻塞，不会在视图pg_stat_activity中体现。

  
  

• 查看阻塞的查询语句及阻塞查询的表、模式信息

  SELECT w.query as waiting_query,
  
  w.pid as w_pid,
  
  w.usename as w_user,
  
  l.query as locking_query,
  
  l.pid as l_pid,
  
  l.usename as l_user,
  
  t.schemaname || '.' || t.relname as tablename
  
  from pg_stat_activity w join pg_locks l1 on w.pid = l1.pid
  
  and not l1.granted join pg_locks l2 on l1.relation = l2.relation
  
  and l2.granted join pg_stat_activity l on l2.pid = l.pid join pg_stat_user_tables t on l1.relation = t.relid
  
  where w.waiting;

  该查询返回会话ID、用户信息、查询状态，以及导致阻塞的表、模式信息。

  查询到阻塞的表及模式信息后请根据会话ID结束会话。

  SELECT PG_TERMINATE_BACKEND(pid);