作者
digoal
日期
2021-12-21
标签
PostgreSQL , 热门问题
- 问题说明(现象、环境)
- 分析原因
- 结论和解决办法
13、为什么长时间等待业务处理的情况不建议封装在一个长事务中进行处理?
https://www.bilibili.com/video/BV1mD4y1c7go/
begin;
sql1;
... 等待业务处理, 长时间
sql2;
... 等待业务处理, 长时间
sql3;
... , 长时间
end;
长事务有什么问题?
问题1:
从最老的backend_xid, backend_xmin事务号之后产生的垃圾, 无法被回收、影响freeze xid.
- 1、导致膨胀, 存储成本增加, 内存消耗增加, 性能变差, 备份时间变长, 恢复时间变长.
- 2、freeze xid极端影响: 由于数据库的xid是32位的, 需要重复使用, 所以如果xid长时间不freeze, 极端情况将需要停库进入单用户数据库模式手工执行freeze, 把年龄降低.
- 3、freeze 另一个影响: 积累久了, 可能出现大量table同时需要freeze的情况.
- 导致IO暴增: 数据文件的读写IO、wal日志的写IO.
- 产生大量WAL日志, 可能导致standby延迟甚至中断.
- 导致归档压力暴增. 等等一系列问题.
postgres=# \d pg_stat_activity
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
pid | integer | | |
leader_pid | integer | | |
usesysid | oid | | |
usename | name | | |
application_name | text | | |
client_addr | inet | | |
client_hostname | text | | |
client_port | integer | | |
backend_start | timestamp with time zone | | |
xact_start | timestamp with time zone | | |
query_start | timestamp with time zone | | |
state_change | timestamp with time zone | | |
wait_event_type | text | | |
wait_event | text | | |
state | text | | |
backend_xid | xid | | |
backend_xmin | xid | | |
query_id | bigint | | |
query | text | | |
backend_type | text | | |
引申: 长时间不结束的 2PC 的问题, 危害同上面一样.
postgres=# \d pg_prepared_xacts
View "pg_catalog.pg_prepared_xacts"
Column | Type | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------
transaction | xid | | |
gid | text | | |
prepared | timestamp with time zone | | |
owner | name | | |
database | name | | |
问题2:
- 长时间持有锁, 可能堵塞未来的SQL锁请求
- 增加死锁隐患
解决办法:
- 事后: 监控, 发现之后人为干预.
- 事前: 业务层建议找到根源解决.
- 例如大的原子操作是不是可以拆成几个小的原子操作, 并且把回退逻辑做好, 如果大原子的中间步骤遇到问题, 可以回退已经完成的小原子操作.
- 数据库设置: 语句超时, 空闲事务超时, 空闲会话超时, 锁超时, snapshot too old
- statement_timeout
- lock_timeout
- idle_in_transaction_session_timeout
- idle_session_timeout
- old_snapshot_threshold
- 超过后强制回收和freeze, 做好膨胀和最危险的xid wrapped保护.
参考:
《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第3期 - 为什么会有大量的idle in transaction|idle事务? 有什么危害?》
期望 PostgreSQL 增加什么功能?
PolarDB for PostgreSQL云原生分布式开源数据库
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
