金仓数据库KingbaseES V8R6 集群环境备库不结束旧事务快照将影响主库的vacuum操作

背景

KingbaseES V8R6读写分离集群环境，一主多备。

版本：KingbaseES V008R006C004

问题现象

主库日常巡检发现日志大量记录：

waring ：oldest xmin is far in the past，close open transactions soon to avoid wraparound problems，you mignt also need to commit or roll back old prepared transactions，or drop state replication slots.

集群同步方式

synchronous_commit=on：保证主备不丢数据，备库接收的wal日志写入磁盘后，反馈给主库表示事务完成。

synchronous_standby_names=any 1(*)：优选同步(quorum), 所有备库都显示为quorum，最先同步完成的备库为同步备库; 任意备库故障不影响其他备库。

以上参数配置说明集群传输方式为同步模式。

分析

从日志看，提示“尽快结束掉尚未完成的事务，避免发生事务回卷的可能”。判断可能有尚未提交的事务，导致 autovacuum 进程不能 vacuum freeze 释放掉表年龄。

众所周知，如果有事务持有bachend_xmin，也就是存在快照不释放，那么其快照号之后产生的age是无法回收的。但是可以通过一个参数来规定快照在一定时间内有效，old_snapshot_threshold：设置在使用快照时，一个快照可以被使用而没有发生 snapshot too old 错误风险的最大时间。这个参数只能在服务器启动时设置。如果超过该阈值，旧数据将被清理掉。这可以有助于阻止长时间使用的快照造成的快照膨胀。为了阻止由于本来对该快照可见的数据被清理导致的不正确结果，当快照比这个阈值更旧并且该快照被用来读取一个该快照建立以来被修改过的页面时，将会产生一个错误。最大可以设置为60天。

主库查看：(部分涉及敏感数据不便展示)

查询结果显示数据库和表年龄均大于4亿

相关sql

数据库年龄：

SELECT datname, age(datfrozenxid) FROM sys_database order by age(datfrozenxid) desc;

表年龄：

SELECT n.nspname as "Schema", c.relname as "Name", c.relfrozenxid
FROM sys_catalog.sys_class c
LEFT JOIN sys_catalog.sys_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'
AND sys_catalog.sys_table_is_visible(c.oid)
ORDER BY c.relfrozenxid::text::bigint DESC;

下面进行vacuum verbose table 查看详情,

如图，这里只展示了重点信息：oldest xmin is far in the past 意思是xmin28亿还没有释放，这远远超过了目前表年龄的4亿

下面的close open transactions.....提示和主库日志里报错一样，重点是oldest xmin：2828976404表示有旧的事务快照没有释放。我们需要把它查出来：

相关长事务sql：

select * from sys_stat_activity where state<>'idle' and sys_backend_pid() != pid and (backend_xid is not null or backend_xmin is not null )

或：

select * from sys_stat_activity where state<>'idle' and sys_backend_pid() != pid and (backend_xid is not null or backend_xmin is not null ) and query like '%tablename%' ;

select * from sys_prepared_statements ;

这个sql在主库查看为空，但是备库查到了相关结果，如图所示：

图片只截取一部分，原图有大量的未结束的事务，我们明显可以看到backend_xmin 正好持有的是主库显示的2828976404快照.并且事务状态为：idle in transaction状态，表示在空闲事务中，但未结束事务。

最后，经和应用沟通，结束掉这些会话连接后，主库日志不再有相关报错，并且表年龄，数据库年龄在触发autovacuum时候被回收。

总结

在同步模式读写分离集群或者单机环境，应避免有关session长时间持有 xmin快照并不结束事务。这样在触发vauum时，相关表的年龄以及死亡元组不会释放。当然最老的快照号之前产生的年龄或死亡元组会释放。此案例并没有调整 autovacuum有关参数阈值，无论是vacuum_freeze_table_age，autovacuum_freeze_max_age，均保持默认。

最后，怎么彻底避免这类问题呢？

1，调整业务逻辑，应用控制长事务时间。或调整在备库的有关查询sql的执行时间。

2、设置参数old_snapshot_threshold，超时后，快照被移除，有关查询报错。

3、关闭备库的hot_standby_feedback，该参数指备库定时将最小活跃事务ID(xmin)告诉master主库，使得 主库在执行vacuum 时对备库还需要的tuple暂不回收，但这样容易导致主库膨胀，反馈消息的发送频度不会高于每个 wal_receiver_status_interval 周期发送一次。默认值是 off 。这个参数只能在 kingbase.conf文件中或在服务器命令行上设置。并且此设置不会覆盖在主数据库上的old_snapshot_threshold参数行为。 更多信息，参见https://help.kingbase.com.cn/v8/index.html