本文将为您介绍如何对实例中的Query进行诊断和管理。
Query管理概述
- 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视图信息
select * from pg_stat_activity ;
pg_stat_activity视图的参数说明如下所示:字段 | 描述 |
---|---|
datid | Hologres后端连接到的数据库的OID。 |
datname | Hologres后端连接到的数据库的名称。 |
pid | Hologres后端的进程ID。 |
usesysid | 登录到Hologres后端的用户OID。 |
usename | 当前连接的用户名。
|
application_name | 客户端的应用类型。 |
client_addr | 客户端的IP地址。
显示的IP地址可能是被解析过的,不保证一定是源端IP。 |
client_hostname | 客户端的主机名。 |
client_port | 客户端的端口。 |
backend_start | 后台进程开始的时间。 |
xact_start | 该进程的当前事务被启动的时间。
|
query_start | 当前活动查询开始的时间,如果当前连接状态不是active,取值为上一个查询开始的时间。 |
state_change | 连接的状态(state)上一次被改变的时间。 |
wait_event_type | 后端正在等待的事件类型,如果不存在则为NULL。可能的取值有:
|
wait_event | 如果后端当前正在等待,则是等待事件的名称,否则为 NULL。 |
state | 表示连接的状态。常见的状态如下:
|
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控制台,详情请参见连接HoloWeb。
- 单击顶部导航栏的诊断与优化。
- 在左侧导航栏单击活跃Query。
- 进入活跃Query页面,查看当前实例的活跃Query及对活跃Query进行管理,例如执行取消操作。
- (可选)单击目标查询操作列的详情,查看当前查询的详细信息。您还可以单击详情页面的复制,复制当前查询执行的SQL语句。
查看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。
修改活跃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;
- 设置超时时间为5000min,其中具体时间带单位,5000min需要整体添加单引号。
修改空闲Query超时时间
参数idle_in_transaction_session_timeout描述了事务进入idle状态后的超时行为,如果不设置参数值,默认不会做事务超时的释放,容易发生事务不释放,导致查询被锁死的情况。Hologres支持您通过如下方式修改空闲Query运行超时时间。
- 应用场景
当Query执行产生死锁时,需要设置超时时间。例如如下代码,未执行
commit
,开启了一个事务,但是没有提交,会造成事务泄漏,进而引发数据库级别的死锁,影响服务正常使用。
当出现这种死锁场景时,可以通过设置idle_in_transaction_session_timeout超时时间来解决。当一个带事务的空闲连接超过idle_in_transaction_session_timeout设置的时间还未提交或者回滚事务,系统将自动根据超时时间回滚事务,并关闭连接。begin; select * from t;
- 语法示例
--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;
- 客户端或应用设置的超时时间,需要业务排查客户端的设置。如果是客户端设置的超时时间导致,可以根据业务情况重新设置合理的超时时间。
- 通过数据服务生成API,数据服务的超时时间为
- 原因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';
- 原因1:客户端或Hologres实例设置了超时时间,常见的超时时间如下。