实时数仓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视图的字段说明如下所示：

若您更倾向于使用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进行管理。

   查询结果列表包含如下信息：

   参数	说明
   Query Start	查询开始时间。
   Runtime	查询运行时间。
   PID	查询服务进程ID。
   Query	查询执行的SQL语句。
   State	当前连接的状态。常见的状态如下： active：活跃。 idle：空闲。 idle in transaction：长事务中的空闲状态。 idle in transaction（Aborted）：已失败事务中的空闲状态。 \N: 状态为空，表示非用户连接的进程，一般属于系统后台的维护进程，可以忽略。
   User Name	当前连接的用户名。
   Application	查询应用类型。
   Client Address	查询客户端的IP地址。

   如果Query长时间运行不结束，您可以单击对应Query操作列的取消，终结Query。也可以多选Query，执行批量取消操作。

5. （可选）单击目标查询操作列的详情，查看当前查询的详细信息。

   您可在详情页面，执行如下操作：

   • 复制：复制当前查询执行的SQL语句。

   • 格式化：格式化当前查询执行的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';