KingbaseES V8R6 最老事务阻止vacuum freeze

前言

最近生产环境发生几次由于长事务导致表、库年龄没法回收的情况。我们要规避这种情况的发生，不要等发生了再去强制中断会话连接。

当数据库中存在最老事务版本xmin，那么早于他的快照可以被标记为frozen，如果在最老事务之后产生的快照版本，不被标记为frozen。

这个最老事务通常被认为是数据库中的长事务，长事务不结束，其后产生的版本都不能被vacuum。那么如果这段时间数据库产生大量事务，就会消耗表，数据库的age。age达到21亿就会强行进入单用户模式vacuum freeze整个数据库才能保证数据库正常运行。

最老事务不仅阻止age的frozen，还阻止表的死亡行回收，所以尽量要避免数据库中产生长事务。

查看长事务的sql：

1、查询长事务语句比较灵活，我们可以加入order by ，limit限定结果集，有很多时候可以加入查询条件 query like‘’ 查到可能阻止vacuum的表涉及的sql语句，但有的时候，其他表的长事务也会阻止不相关的表进行vacuum freeze：

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 extract(epoch from (now() - xact_start)) > 3; <时间阈值，单位秒> ;

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 datname,usename,query,xact_start,now()-xact_start xact_duration,query_start,now()-query_start query_duration,state from sys_stat_activity where state<>$$idle$$ and (backend_xid is not null or backend_xmin is not null) and now()-xact_start > interval $$30 min$$ order by xact_start;

2、prepare预备语句视图也需要查看：

select * from sys_prepared_statements where now()-prepare_time > interval $$30 min$$ order by prepare_time;

测试

1、需要准备一个产生事务的压测脚本
vi test.sql  
select txid_current();  
2、新建一个事务，不结束事务

test=# create table t1(id int);  
CREATE TABLE  
test=# begin;  
BEGIN  
test=# insert into t1 values (1);  
INSERT 0 1  
test=# select txid_current();  
 txid_current   
--------------  
    1668525  
(1 row)  
3、新建t2表，写入一条记录，注意这条记录的版本是在最老事务之后产生的。

test=# create table t2 (id int);  
CREATE TABLE  
test=# insert into t2 values (100);  
INSERT 0 1  
4、执行压测脚本，目的是消耗大量事务号

kbbench -U SYSTEM -d test -M prepared -n -r -P 1 -f ./test.sql -c 16 -j 16 -T 10  

5、然后，再新建另一个事务，不结束事务

test=# begin;  
BEGIN  
test=# insert into t1 values (2);  
INSERT 0 1  
test=# select txid_current();  
 txid_current   
--------------  
    1788896  
(1 row)  

6、下面继续消耗大量事务

kbbench -U SYSTEM -d test -M prepared -n -r -P 1 -f ./test.sql -c 16 -j 16 -T 10  


7、freeze t2这个表，我们看到年龄始终降不下来。因为前面说过：表的tuple如果是在最老事务之前产生的，它可以被标记为frozen，而在最老事务之后产生的tuple，必须保留版本，不能被标记为frozen。

test=# vacuum (freeze,verbose) t2;  
INFO:  aggressively vacuuming "public.t2"  
INFO:  "t2": found 0 removable, 1 nonremovable row versions in 1 out of 1 pages  
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1668525  
There were 0 unused item identifiers.  
Skipped 0 pages due to buffer pins, 0 frozen pages.  
0 pages are entirely empty.  
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.  
VACUUM  

  查看表年龄没有下降
test=# select age(relfrozenxid),relname,pg_size_pretty(pg_total_relation_size(oid)) from sys_class where relname='t2';  
   age    | relname | pg_size_pretty   
----------+---------+----------------  
 244389   | t2      | 40 kB  
(1 row)  

8、释放第一个事务，再次freeze t2。注意这个时候最老的事务是在t2的所有记录版本之后产生的。所以理论上执行vacuum freeze后，这个表的年龄应该可以降到0.

test=# vacuum (freeze,verbose) t2;  
INFO:  aggressively vacuuming "public.t2"  
INFO:  "t2": found 0 removable, 1 nonremovable row versions in 1 out of 1 pages  
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1788896  
There were 0 unused item identifiers.  
Skipped 0 pages due to buffer pins, 0 frozen pages.  
0 pages are entirely empty.  
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.  
VACUUM  
  表年龄有下降，但是没有降到0
test=# select age(relfrozenxid),relname,pg_size_pretty(pg_total_relation_size(oid)) from sys_class where relname='t2';  
   age   | relname | pg_size_pretty   
---------+---------+----------------  
 124048  | t2      | 40 kB  
(1 row)  


第二次vacuum t2，oldest xmin: 1788896，因为第一个事务版本1668525被释放了。

释放第二个事务，再次freeze t2，这时候查看t2年龄已经降到0

TEST=# select age(relfrozenxid),relname,pg_size_pretty(pg_total_relation_size(oid)) from sys_class where relname='t2';
 age | relname | pg_size_pretty
-----+---------+----------------
   0 | t2      | 40 kB
(1 row)

总结

在实际生产环境中，无论主库备库，应尽量避免长事务，作为DBA我们要充分和客户沟通，长事务可能对数据库系统带来的隐患。由于KingbaseES数据库中过去的MVCC版本和表放在一起，而不像oracle，mysql数据库那样利用undo表空间单独存储回滚段，所以面对vacuum问题，我们需要注意把vacuum dead tuple，以及vacuum freeze可能遇到的问题。参考文档《KingbaseESV8R6 垃圾回收原理以及如何预防膨胀》

本案例中，在结束第一个事务后，t2表的事务之后产生的是t1表有关的事务，也就是说t1表的事务不释放也会阻止t2表的vacuum freeze进行。