每天5分钟PG聊通透第3期,为什么会有大量的`idle in transaction|idle`事务? 有什么危害?

背景

• 问题说明(现象、环境)
• 分析原因
• 结论和解决办法

链接、驱动、SQL

3、为什么会有大量的idle in transaction|idle事务? 有什么危害? (事务abort未处理, 框架自动开启事务. 危害之一: 观察事务开启时间以及是否保有backend xmin xid)

https://www.bilibili.com/video/BV1644y1E7CL/

begin;  
begin; select 1;
begin; insert into x values (xxx);
begin isolation level repeatable read; select 1;
postgres=# select * from pg_stat_activity where state ~ 'idle';
-[ RECORD 1 ]----+--------------------------------
datid            | 13236
datname          | postgres
pid              | 6283
leader_pid       | 
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2021-12-11 11:18:10.51215+08
xact_start       | 2021-12-11 11:18:11.821312+08
query_start      | 2021-12-11 11:18:13.079184+08
state_change     | 2021-12-11 11:18:13.079559+08
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 
backend_xmin     | 
query_id         | 
query            | select 1;
backend_type     | client backend
-[ RECORD 2 ]----+--------------------------------
datid            | 13236
datname          | postgres
pid              | 6269
leader_pid       | 
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2021-12-11 11:17:35.315226+08
xact_start       | 2021-12-11 11:17:47.699981+08
query_start      | 2021-12-11 11:17:49.76987+08
state_change     | 2021-12-11 11:17:49.77028+08
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 
backend_xmin     | 29753153
query_id         | 
query            | select 1;
backend_type     | client backend
-[ RECORD 3 ]----+--------------------------------
datid            | 13236
datname          | postgres
pid              | 6260
leader_pid       | 
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2021-12-11 11:17:02.027604+08
xact_start       | 2021-12-11 11:17:09.68111+08
query_start      | 2021-12-11 11:17:15.363823+08
state_change     | 2021-12-11 11:17:15.374042+08
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 29753153
backend_xmin     | 
query_id         | 
query            | insert into t_age values (1,1);
backend_type     | client backend
-[ RECORD 4 ]----+--------------------------------
datid            | 13236
datname          | postgres
pid              | 6250
leader_pid       | 
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2021-12-11 11:15:53.414038+08
xact_start       | 2021-12-11 11:15:55.50149+08
query_start      | 2021-12-11 11:15:55.50149+08
state_change     | 2021-12-11 11:15:55.506008+08
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 
backend_xmin     | 
query_id         | 
query            | begin;
backend_type     | client backend

在backend_xid或backend_xmin有值的事务中, 输入一条错误sql导致事务abort;
abort事务会自动释放snapshot.
-[ RECORD 2 ]----+------------------------------
datid            | 13236
datname          | postgres
pid              | 6269
leader_pid       | 
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2021-12-11 11:17:35.315226+08
xact_start       | 
query_start      | 2021-12-11 11:20:24.013488+08
state_change     | 2021-12-11 11:20:24.013661+08
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction (aborted)
backend_xid      | 
backend_xmin     | 
query_id         | 
query            | s;
backend_type     | client backend

有backend_xid,backend_xmin的会话(除了vacuum), 不管它处于什么状态, 超出这个value之后新启动的事务所产生的垃圾tuple都不能被vacuum回收. 将会有什么影响呢?

• 如果系统中还有大量的update,delete操作, 时间久了就可能导致表、索引膨胀, 影响性能、浪费空间.
• 如果时间非常非常久, 还可能导致事务回卷警告, 甚至需要停库进入数据库单用户模式执行freeze处理后才能使用.
• 解决方案:
• 业务层处理, 避免框架自动开启事务
• 数据库参数设置, idle_in_transaction_session_timeout 自动释放长时间空闲的事务
• 设置old_snapshot_threshold数据库参数, 避免vacuum长时间做不下去.

大量idle状态的连接又是怎么回事?

• 可能DB的性能出现过抖动, 导致业务请求拥塞, 业务端通过新建更多的连接来处理拥塞的请求. 然后业务端又没有配置自动释放空闲连接或者没有到达自动释放空闲连接的超时时间.
• 影响1: 每个会话都有自己的私有内存, 会缓存访问过的对象元数据(例如结构定义), 特别是长连接的影响尤为更大, 因为访问过的对象可能比较多(特别是有分区表时, 每个分区都有独立的元数据), 导致每个会话占用的内存都比较多, 这样的连接多了可能导致系统可自由支配的内存不足, 从而触发OOM.
• 影响2: 可能占满连接导致别的业务需要连接时报连接不足的错误.
• 解决方案:
• 首先, 是从根上排查和解决拥塞的问题.
• 其次, 可以在业务端设置降级保护(例如丢弃请求或者将请求放入队列排队处理, 控制到DB端的最大并发, 确保DB不会被打死导致雪崩的全面业务受损).
• 也可以设置数据库idle_session_timeout参数, 让数据库来进行自动释放长时间空闲的会话.
• 最后, 在业务端可以调低最大连接数, 同样也是防止过多连接把DB打死.