实时数仓Hologres：Query管理

Hologres兼容PostgreSQL，可以通过查询hg_stat_activity（pg_stat_activity）视图信息来查看实例Query的运行信息，以达到分析和诊断运行SQL的目的。具体涉及的操作内容如下所示：

• 使用SQL查看活跃Query：查看SQL运行信息，更好的管理SQL语句。
• HoloWeb可视化活跃Query管理：通过HoloWeb可视化查看和管理活跃Query。
• 排查锁：通过活跃Query排查当前SQL是否有锁或者被锁。
• 终止Query：使用命令语句终止不符合预期的Query。
• 修改活跃Query超时时间：修改活跃Query运行超时时间，防止引发死锁。
• 修改空闲Query超时时间：修改空闲Query运行超时时间，防止引发死锁。
• 查询慢Query日志：通过慢Query的查询可以对慢Query或失败Query进行诊断、分析和采取优化措施。
• 常见问题：出现ERROR: canceling statement due to statement timeout报错的原因和解决方法。

从Hologres V2.0版本开始，pg_stat_activity升级为hg_stat_activity视图。hg_stat_activity是pg_stat_activity视图的扩展，可以通过hg_stat_activity查询到活跃SQL更详细的运行信息，包括query id、query engine以及资源消耗等信息，从而帮助更进一步诊断活跃Query。您可以执行如下命令查看Query的运行信息。其他Postgres兼容管理工具，依旧可以查询pg_stat_activity获取SQL运行态信息。

--2.0及以上推荐语法
select *  from hg_stat_activity;

--1.3及以下版本语法
select *  from pg_stat_activity;

hg_stat_activity视图的字段说明如下所示：

字段	描述	hg_stat_activity视图支持情况	pg_stat_activity视图支持情况
datid	Hologres后端连接到的数据库的OID。	支持	支持
datname	Hologres后端连接到的数据库的名称。	支持	支持
pid	Hologres后端的进程ID。	支持	支持
query_id	当前执行SQL的ID，一个SQL对应一个query_id。	支持	不支持
transaction_id	当前SQL所属的事务ID，一个事务中可能有多个SQL。	支持	不支持
usesysid	当前活跃会话用户的系统标识符。	支持	支持
usename	当前连接的用户名。 holo_admin是Hologres内置服务账号，系统会通过holo_admin去定期获取表的meta信息等。holo_admin不会占用过多资源，因此无需过多关注holo_admin运行的SQL。	支持	支持
application_name	客户端的应用类型。 其中常见的应用类型如下： 阿里云Flink（VVR版）：{client_version}_ververica-connector-hologres。 开源Flink：{client_version}_hologres-connector-flink。 DataWorks数据集成离线同步读取Hologres：datax_{jobId}。 DataWorks数据集成离线同步写入Hologres：{client_version}_datax_{jobId}。 DataWorks数据集成实时同步：{client_version}_streamx_{jobId}。 HoloWeb：holoweb。 其余应用建议业务上在连接Hologres时连接串上显式指定application_name。	支持	支持
running_info	包含SQL执行过程各种状态信息，格式为JSON格式。其子字段包括： current_stage stage_name<parsing|planning|queueing|starting|executing|finish>： parsing：SQL解析。 说明 如果SQL是PARSE阶段，则无法显示执行引擎engine_type。 planning：生成执行计划。 queueing：等待资源。 starting：Query开始阶段。 executing：Query执行阶段。 finish：Query完成。 stage_duration_ms: 当前Stage到现在消耗的时间（单位毫秒）。 engine_type：当前Query的执行引擎。 {HQE}：Hologres引擎。 {HQE,PQE}：走PQE的SQL。 {PG}：系统SQL，无需过多关注。 {SDK}：Fixed Plan SQL。 {HQE,SQE}：未使用直读的MaxCompute外部表SQL。 {HQE,HiveQE}： 未使用直读的OSS外部表SQL。 {HQE,SQE,HiveQE}：未直读的外部表SQL。 fe_id：FE节点对应的ID。 warehouse_id：如果实例是warehouse模式，代表warehouse的ID。	支持	不支持
extend_info	包含SQL执行过程资源消耗等的扩展信息，为JSON格式。其子字段包括： total_cpu_max_time_ms：SQL累积CPU消耗（单位毫秒）。 total_mem_max_bytes：Query中各算子内存最大值之和（单位byte）。 scanned_rows：Query已读取数据的总行数。 affected_rows：DML语句影响的行数。 be_lock_waiters：当前SQL拿锁情况，表示当前SQL拿着锁，谁（Query_id）在等这个SQL释放锁。 be_lock_converting_waiters：当前SQL被锁的情况，表示当前SQL被谁（Query_id）锁住，需要等其释放锁。 说明 be_lock_waiters和be_lock_converting_waiters采集的都是backend的锁，而不是frontend的锁，排查锁请参见锁以及排查锁。	支持	不支持
state	表示连接的状态。常见的状态如下： active：活跃。 idle：空闲。 idle in transaction：长事务中的空闲状态。 idle in transaction（Aborted）：已失败事务中的空闲状态。 \N: 状态为空，表示非用户连接的进程，一般属于系统后台的维护进程，可以忽略。	支持	支持
query_start	Query开始执行的时间，如果active不是active，取值为上一个查询开始的时间。	支持	支持
client_addr	客户端的IP地址。 显示的IP地址可能是被解析过的，不保证一定是源端IP。	支持	支持
client_hostname	客户端的主机名。	支持	支持
client_port	客户端的端口。	支持	支持
backend_start	后台进程开始的时间。 在Hologres中无需过多关注。	支持	支持
xact_start	该进程的当前事务被启动的时间。 如果没有活动事务，则为空。 如果当前查询是该进程的第一个事务，这一列等于query_start。 在Hologres中无需过多关注。	支持	支持
state_change	连接的状态（state）上一次被改变的时间。 在Hologres中无需过多关注。	支持	支持
wait_event_type	后端正在等待的事件类型，如果不存在则为NULL。可能的取值有： LWLock：后端正在等待一个轻量级锁。 Lock：后端正在等待一个重量级锁。wait_event将标识等待的锁的类型。 BufferPin：服务器进程正在等待访问一个数据缓冲区，而此时没有其他进程正在检查该缓冲区。 Activity：服务器进程处于闲置状态。被用于在其主处理循环中等待活动的系统进程。 Extension：服务器进程正在一个扩展模块中等待活动。 Client：服务器进程正在等待来自用户应用的某种查询，并且该服务器预期某种与其内部处理无关的事情发生。 PC：服务器进程正在等待来自服务器中另一个进程的某种活动。 Timeout：服务器进程正在等待一次超时发生。 IO：服务器进程正在等待一次IO完成。	支持	支持
wait_event	如果后端当前正在等待，则是等待事件的名称，否则为 NULL。	支持	支持
backend_xid	Hologres后端的顶层事务标识符。	支持	支持
backend_xmin	当前后端的xmin范围。	支持	支持
query	后端最近执行的查询。如果state为active，将会显示当前正在执行的查询。在其他状态下，显示上一个被执行的查询。	支持	支持
backend_type	当前后端的类型。可能的类型为autovacuum launcher、autovacuum worker、logical replication launcher、logical replication worker、parallel worker、background writer、client backend、checkpointer、startup、walreceiver、walsender以及 walwriter。除此之外还包括后端的执行组件，例如PQE等。 说明 需要关注client backend类型，代表应用的连接。	支持	支持

若您更倾向于使用SQL方式查询活跃Query，可以使用以下SQL进行操作：

1. 查看当前活跃Query以及对应的执行阶段和资源消耗：
   说明 Superuser可以查看所有用户的SQL运行信息，RAM用户只能查看自己的SQL运行信息。

   --2.0及以上版本语法
   SELECT query,state,query_id,transaction_id,running_info, extend_info 
   FROM hg_stat_activity 
   WHERE state = 'active' 
   AND     backend_type = 'client backend'
   AND     application_name != 'hologres'
   AND     usename != 'holo_admin';
   
   --1.3及以下版本语法
   SELECT query,state,pid
   FROM pg_stat_activity 
   WHERE state = 'active' 
   AND     backend_type = 'client backend'
   AND     application_name != 'hologres'
   AND     usename != 'holo_admin';

   示例返回结果：

   -------------------------------------------------------------------------------
   query          | insert into test_hg_stat_activity select i, (i % 7) :: text, (i % 1007) from generate_series(1, 10000000)i;
   state          | active
   query_id       | 100713xxxx
   transaction_id | 100713xxxx
   running_info   | {"current_stage" : {"stage_duration_ms" :5994,
                                         "stage_name" :"EXECUTE" },
                      "engine_type" :"{HQE,PQE}",
                      "fe_id" :1,
                      "warehouse_id" :0 }
   extend_info    | {"affected_rows" :9510912,
                     "scanned_rows" :9527296 }

2. 当前运行的Query按照CPU消耗排序：

   --2.0及以上语法
   select query,((extend_info::json)->'total_cpu_max_time_ms')::text::bigint as cpu_cost,state,query_id,transaction_id 
   FROM hg_stat_activity where state = 'active' order by 2 desc;

   示例返回结果：

   ---------------------------------------------------------------------------------
   query          | select xxxxx
   cpu_cost       | 523461
   state          | active
   query_id       | 10053xxxx
   transaction_id | 10053xxxx
   ---------------------------------------------------------------------------------
   query          | insert xxxx
   cpu_cost       | 4817
   state          | active
   query_id       | 1008305xxx
   transaction_id | 1008305xxx

3. 当前运行的Query按照内存消耗排序：

   --2.0及以上语法
   select query,((extend_info::json)->'total_mem_max_bytes')::text::bigint as mem_max_cost,state,query_id,transaction_id 
   FROM hg_stat_activity where state = 'active' order by 2 desc;

   示例返回结果：

   ---------------------------------------------------------------------------------
   query          | update xxxx;
   mem_max_cost   | 5727634542
   state          | active
   query_id       | 10053302784827629
   transaction_id | 10053302784827629
   ---------------------------------------------------------------------------------
   query          | select xxxx;
   mem_max_cost   | 19535640
   state          | active
   query_id       | 10083259096119559
   transaction_id | 10083259096119559

4. 查看当前实例正在运行且耗时较长的Query：

   --2.0及以上语法
   select current_timestamp - query_start as runtime, datname::text, usename, query, query_id
       from hg_stat_activity
       where state != 'idle'
       and backend_type = 'client backend'
       and application_name != 'hologres'
       and usename != 'holo_admin'
       order by 1 desc;
   
   --1.3及以下语法
   select current_timestamp - query_start as runtime, datname::text, usename, query, pid
       from pg_stat_activity
       where state != 'idle'
       and backend_type = 'client backend'
       and application_name != 'hologres'
       and usename != 'holo_admin'
       order by 1 desc;

   示例返回结果：

   runtime          |  datname  | usename  | query_id    |      current_query
   -----------------+----------------+----------+------------------------------------
    00:00:24.258388 | holotest  | 123xxx   | 1267xx | UPDATE xxx;
    00:00:1.186394  | testdb    | 156xx    | 1783xx | select xxxx;

   可以看到update耗时较久，已经运行了24s还没有结束。

您可以通过HoloWeb可视化查看活跃Query，并进行管理。

1. 登录HoloWeb控制台，详情请参见连接HoloWeb。
2. 单击顶部导航栏的诊断与优化。
3. 在左侧导航栏单击活跃Query。
4. 进入活跃Query页面，查看当前实例的活跃Query及对活跃Query进行管理，例如执行取消操作。
5. （可选）单击目标查询操作列的详情，查看当前查询的详细信息。您还可以单击详情页面的复制，复制当前查询执行的SQL语句。

不符合预期的Query需要终止，可以通过以下命令语句操作。

• 终止单个Query：

  select pg_cancel_backend(<pid>);

• 批量终止Query：

  SELECT pg_cancel_backend(pid)
          ,query
          ,datname
          ,usename
          ,application_name
          ,client_addr
          ,client_port
          ,backend_start
          ,state
  FROM    pg_stat_activity
  WHERE   length(query) > 0
  AND     pid != pg_backend_pid()
  AND     backend_type = 'client backend'
  AND     application_name != 'hologres'
  AND     usename != 'holo_admin';