Query管理 - 实时数仓Hologres

Query管理概述

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

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

查询pg_stat_activity视图信息

pg_stat_activity是一个非常有用的视图，可以分析排查当前运行的SQL任务以及一些异常问题。您可以执行如下命令查看Query的运行信息。pg_stat_activity不表示所有的网络连接，部分后台管理进程不占用连接，也会出现在pg_stat_activity表中。

select * from pg_stat_activity ;

pg_stat_activity视图的参数说明如下所示：


字段	描述
datid	Hologres后端连接到的数据库的OID。
datname	Hologres后端连接到的数据库的名称。
pid	Hologres后端的进程ID。
usesysid	登录到Hologres后端的用户OID。
usename	当前连接的用户名。 `holo_admin`是Hologres内置的服务账号，运行结果显示为PSQL，该连接是必要的。如果您的连接数未超出当前实例的最大限度，不需要针对此连接进行优化。连接数查询方式，请参见通过SQL查询连接信息。
application_name	客户端的应用类型。
client_addr	客户端的IP地址。显示的IP地址可能是被解析过的，不保证一定是源端IP。
client_hostname	客户端的主机名。
client_port	客户端的端口。
backend_start	后台进程开始的时间。
xact_start	该进程的当前事务被启动的时间。如果没有活动事务，则为空。如果当前查询是该进程的第一个事务，这一列等于query_start。
query_start	当前活动查询开始的时间，如果当前连接状态不是active，取值为上一个查询开始的时间。
state_change	连接的状态（state）上一次被改变的时间。
wait_event_type	后端正在等待的事件类型，如果不存在则为NULL。可能的取值有： LWLock：后端正在等待一个轻量级锁。 Lock：后端正在等待一个重量级锁。`wait_event`将标识等待的锁的类型。 BufferPin：服务器进程正在等待访问一个数据缓冲区，而此时没有其他进程正在检查该缓冲区。 Activity：服务器进程处于闲置状态。被用于在其主处理循环中等待活动的系统进程。 Extension：服务器进程正在一个扩展模块中等待活动。 Client：服务器进程正在等待来自用户应用的某种查询，并且该服务器预期某种与其内部处理无关的事情发生。 PC：服务器进程正在等待来自服务器中另一个进程的某种活动。 Timeout：服务器进程正在等待一次超时发生。 IO：服务器进程正在等待一次IO完成。
wait_event	如果后端当前正在等待，则是等待事件的名称，否则为 NULL。
state	表示连接的状态。常见的状态如下： active：活跃。 idle：空闲。 idle in transaction：长事务中的空闲状态。 idle in transaction（Aborted）：已失败事务中的空闲状态。 \N: 状态为空，表示非用户连接的进程，一般属于系统后台的维护进程，可以忽略。
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等。

HoloWeb可视化活跃Query管理

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

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

查看SQL运行信息

Superuser可以查看所有用户的SQL运行信息，RAM用户只能查看自己的SQL运行信息。更多关于参数的说明，请参见参数说明。

您可以通过如下语句查看当前实例内用户的SQL运行信息。
```
SELECT datname::text,usename,query,pid::text,state FROM pg_stat_activity;
```

您可以执行如下语句查看当前正在运行的SQL信息。

SELECT datname::text,usename,query,pid::text,state
   FROM pg_stat_activity
   WHERE state != 'idle' ;

您可以执行以下语句查看当前实例正在运行且耗时较长的SQL信息。

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

示例执行结果如下所示，该示例中可以看到耗时较久的语句为UPDATE。

runtime          | datname   | usename  |       current_query           | pid
-----------------+-----------+----------+-------------------------------+-------------
 00:00:24.258388 | holotest  | 123xxx   |  UPDATE holo_order            |1267xx
                                              : set gmt = now(),
                                              : trade_id = $1,
                                              : trade_create_time = $2;
 00:00:1.186394  | testdb    | 156xx    |  SELECT * FROM oder;          |1783xx 
(2 rows)

终止Query

如果当前存在不符合预期的Query，您可以根据实际情况通过如下命令进行终止。

取消当前连接上的Query。
```
select pg_cancel_backend(<pid>);
```

批量取消Query。

SELECT pg_cancel_backend(pid)
        ,query
        ,datname::text
        ,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'
AND     query not like '%pg_cancel_backend%';

修改活跃Query超时时间

Hologres支持您通过如下方式修改活跃Query运行超时时间。

语法示例
```
set statement_timeout = <time>;
```
参数说明
time：超时时间取值范围为0~2147483647ms，单位默认为ms（当time后加单位时需要使用单引号，否则会报错）。当前默认超时时间为8小时，该设置针对session级别生效。

说明 set statement_timeout = <time> 和要修改超时时间的SQL语句一起执行方可生效。
使用示例
- 设置超时时间为5000min，其中具体时间带单位，5000min需要整体添加单引号。
```
set statement_timeout = '5000min' ; 
select * from tablename;
```
- 设置超时时间为5000ms。
```
set statement_timeout = 5000 ; 
select * from tablename;
```

修改空闲Query超时时间

参数idle_in_transaction_session_timeout描述了事务进入idle状态后的超时行为，如果不设置参数值，默认不会做事务超时的释放，容易发生事务不释放，导致查询被锁死的情况。Hologres支持您通过如下方式修改空闲Query运行超时时间。

应用场景
当Query执行产生死锁时，需要设置超时时间。例如如下代码，未执行commit，开启了一个事务，但是没有提交，会造成事务泄漏，进而引发数据库级别的死锁，影响服务正常使用。
```
begin; 
select * from t;
```
当出现这种死锁场景时，可以通过设置idle_in_transaction_session_timeout超时时间来解决。当一个带事务的空闲连接超过idle_in_transaction_session_timeout设置的时间还未提交或者回滚事务，系统将自动根据超时时间回滚事务，并关闭连接。

语法示例

--session修改空闲事务超时时间
set idle_in_transaction_session_timeout=<time>;

--DB级别修改空闲事务超时时间
alter database db_name set idle_in_transaction_session_timeout=<time>;

参数说明
time：超时时间取值范围为0~2147483647ms，单位默认为ms（当time后加单位时需要使用单引号，否则会报错）。在Hologres V0.10及以下版本，默认值为0，即不会自动清理；在Hologres V1.1版本，默认值为10分钟，超过10分钟后将会回滚事务。

说明不建议超时时间设置过短，如果过短容易错误回滚正在使用中的事务。

使用示例

设置超时时间为300000ms。

--session修改空闲事务超时时间
set idle_in_transaction_session_timeout=300000;

--DB级别修改空闲事务超时时间
alter database db_name set idle_in_transaction_session_timeout=300000;

查询慢Query日志

从Hologres V0.10版本开始，支持进行慢query日志查询，详情请参见慢Query日志查看与分析。

常见问题

问题现象
执行SQL后出现报错：ERROR: canceling statement due to statement timeout。
问题原因及解决方法
- 原因1：客户端或Hologres实例设置了超时时间，常见的超时时间如下。
  - 通过数据服务生成API，数据服务的超时时间为10s，不可以修改，建议优化SQL以降低执行时间。
  - HoloWeb或DataWorks的Hologres SQL模块执行的Query，超时时间为1h，不可以修改，建议优化SQL以降低执行时间。
  - 为Hologres实例设置的超时时间，可以通过以下SQL查看设置的实例超时时间，如果是实例超时时间导致，可以根据业务情况重新设置合理的超时时间。
```
show statement_timeout;
```
  - 客户端或应用设置的超时时间，需要业务排查客户端的设置。如果是客户端设置的超时时间导致，可以根据业务情况重新设置合理的超时时间。
- 原因2：执行DML SQL时对表执行了DROP或者Truncate操作导致超时。
  Truncate的原理是drop+create即先删除表再创建表。当执行DML SQL时，会获取行锁或表锁，锁相关内容请参见锁以及排查锁。此时再对这个表同时执行DROP或者Truncate，DROP或者Truncate操作会抢DML的锁，然后系统会将DML SQL取消，即出现statement timeout的报错。
  解决方法：通过慢Query日志排查是否同一时间对该表执行drop或truncate操作，示例如下，需避免此类操作。
```
--示例查询过去一天某张表执行的drop/truncate记录
select * from hologres.hg_query_log 
where command_tag in ('DROP TABLE','TRUNCATE TABLE') 
and query like '%xxx%' and query_start >= now() - interval '1 day';
```