StringBoot-druid连接池-pgsql-超时取消-超时参数-canceling statement due to statement timeout

1. 观察日志

出现很多
Caused by: org.postgresql.util.PSQLException: ERROR: canceling statement due to user request。可以理解是客户端主动取消，此时还没到数据库服务端statement_timeout的时间。实测相关的sql查询需要59秒，还是很耗时的。

假如客户端的超时设置60秒，服务端statement_timeout是120秒，sql实际执行了70秒，那这个时候，会被客户端主动取消，表现为 canceling statement due to user request（先达到到客户端的超时时间）。

假如客户端的超时设置60秒，服务端statement_timeout是50秒，sql实际执行了65秒，那这个时候，sql实际执行超时时间超过了statement_timeout，表现为 canceling statement due to statement timeout（先达到到数据库服务端的超时时间）。

所以
数据库服务端 statement_timeout（110s） < 客户端timetout相关 （120s） , 如果真的超时了，那应该报canceling statement due to statement timeout

服务端和客户端分别设置如下,见2处 "看我"。

补充说明：
Caused by: org.postgresql.util.PSQLException: ERROR: canceling statement due to user request。
之前线上也出现过，不过换了更高配置的pg数据库，就没出现了，本质上还是数据库的性能太差，每次查询都超时失败，造成了待消费日志挤压，并且是恶性循坏。

2. psql数据库端设置超时时间

2.1 设置超时参数

psql中执行：

SHOW statement_timeout;
SHOW idle_in_transaction_session_timeout;
-- 默认都是0;
-- 查询验证
SELECT name, setting, unit FROM pg_settings WHERE name in ('statement_timeout','idle_in_transaction_session_timeout');

看我(1/2)

确保服务端 statement_timeout > Druid 的 socket-timeout（当前是60秒）
设置合理的超时值，限制查询的最大执行时间和事务在空闲状态下的最长持续时间

#注意这个会导致在navicat中执行也会受到这个超时配置的影响，所以也需要临时放大，或者设置成0？

ALTER SYSTEM SET statement_timeout = '110s';- - 比120 小才会出现canceling statement due to statement timeout。
ALTER SYSTEM SET idle_in_transaction_session_timeout = '300s';
SELECT pg_reload_conf();  -- 或者 执行命令 pg_ctl reload

异常会由due to user request变成 Caused by: org.postgresql.util.PSQLException: ERROR: canceling statement due to statement timeout。

说明问题已从客户端主动取消转变为服务端主动超时。

也就是：

1、PostgreSQL 服务端的 statement_timeout 参数已生效。

2、当前 SQL 执行时间超过了 statement_timeout 阈值。

2.2 处理当前的超时会话

1. 查询超过1分钟的sql会话

   SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration, 
    query
   FROM
   pg_stat_activity
   WHERE
   state = 'active' AND 
   now() - pg_stat_activity.query_start > interval '1 minute'
   ORDER BY 
   now() - pg_stat_activity.query_start DESC;
   

2. 定期清理旧会话

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state IN ('idle in transaction','active') AND now() - query_start > interval '1 day'; -- interval '1 minute';

1. 实测业务sql在Navicat中执行需要59秒

SELECT  id,pol_id,qn,st,cn,mn,p_flag,cp,data_time,receive_time,status,msg,remark  FROM bcs_log_receive_3020 WHERE (cn = '3020' AND status = '0') ORDER BY receive_time ASC limit 500
-- 设置更合适的索引，执行了960s，所以上面的statement_timeout 要先设置到1100s。

CREATE INDEX  "idx_bcs_log_receive_3020_cn_status_receive_time " ON "public"."bcs_log_receive_3020" (
    "cn",
    "receive_time",
    "status"
);

3. 代码

// Spring Retry 示例，如果有必要，未验证
@Retryable(
    value = {DataAccessResourceFailureException.class},
    maxAttempts = 3,
    backoff = @Backoff(delay = 2000, multiplier = 2)
)
public List<BcsLogReceive3020> queryData(String cn, String status) {
    return receive3020Mapper.selectByCnAndStatus(cn, status);
}

4. 代码配置

druid连接池的超时相关的配置

看我(2/2)

#客服端：

#获取连接时的最大等待时间（毫秒），如果超过这个时间则抛出异常。设置为 -1 表示无限等待。
spring.datasource.druid.maxWait=120000
# 查询超时时间（单位：秒）体现在SHOW statement_timeout;不设置会有 Caused by: org.postgresql.util.PSQLException: ERROR: canceling statement due to user request
spring.datasource.druid.query-timeout=120
# 事务内查询超时时间
spring.datasource.druid.transaction-query-timeout=120
#jar包没有这些配资 connectTimeout/socketTimeout
spring.datasource.druid.connection-properties=connectTimeout=120000;socketTimeout=120000