每天5分钟PG聊通透第13期,长事务的危害！

背景

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

链接、驱动、SQL

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
• 超过后强制回收和freeze, 做好膨胀和最危险的xid wrapped保护.
• statement_timeout
• lock_timeout
• idle_in_transaction_session_timeout
• idle_session_timeout
• old_snapshot_threshold

参考:
《每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第3期 - 为什么会有大量的idle in transaction|idle事务? 有什么危害?》

用好周边工具, 数据库管理水平战胜90%老司机

1、管控软件

云猿生开源的kubeblocks, 如果你要管理很多套并且种类很多的数据库产品, 推荐选择.

• https://github.com/apecloud/kubeblocks

乘数开源的clup, 专门用来管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 企业可以关注一下.

• https://www.csudata.com/

若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套数据库, 并且对插件有特别多的需求, 推荐选择.

• https://pigsty.cc/zh/

2、审计监控诊断优化

海信聚好看的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.

• https://www.dbdoctor.cn/

Bytebase 的目标非常远大, 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。

• https://bytebase.cc/docs/introduction/what-is-bytebase/

D-Smart, Oracle老前辈白老大他们搞的, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.

• https://www.modb.pro/db/567140

3、数据同步&迁移&备份恢复

NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.

• https://www.ninedata.cloud/home

DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.

• https://www.dsgdata.com/

通过信创并且开源的数据库:

PolarDB for PostgreSQL

• https://github.com/ApsaraDB/PolarDB-for-PostgreSQL