KingbaseES-v8r6-vacuum失效原因

vacuum失效原因

KingbaesES数据库中遇到表膨胀，有时碰到vacuum失效的场景。最常见的原因如下。

• autovacuum是否运行，通过查看 sys_stat_user_tables 查看autovacuum_count字段次数

select relname,autovacuum_count,last_autovacuum from sys_stat_all_tables;

• 是否有长时间运行的语句

SELECT pid, datname, usename, state, backend_xmin,query,backend_type,query_start,client_addr
FROM sys_stat_activity
WHERE backend_xmin IS NOT NULL and sys_backend_pid() != pid
ORDER BY age(backend_xmin) DESC;

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 datname,application_name,client_addr,backend_start,xact_start,query,state,backend_xid,backend_xmin
from sys_stat_activity
where (state = 'idle in transaction')
and xact_start is not null;

• 两段式提交中，prepare操作操作由于某些原因迟迟不能commit的sql

SELECT gid, prepared, owner, database, transaction AS xmin
FROM sys_prepared_xacts
ORDER BY age(transaction) DESC;

select * from sys_prepared_statements;

• 复制槽视图中xmin字段值如果一直不变，说明备库有长事务获取过去的快照版本，长事务没有结束

select * from sys_replication_slots ORDER BY age(xmin) DESC;

如果以上sql没有查到有用信息，那么我们看last_autovacuum字段信息来判断最近一次表的autovacuum的时间。

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM sys_stat_all_tables
ORDER BY n_dead_tup
/ (n_live_tup
 * current_setting('autovacuum_vacuum_scale_factor')::float8
 * current_setting('autovacuum_vacuum_threshold')::float8)
DESC
LIMIT 10;

• 如果遇到某个对象一直没有触发autovacuum进程。
  可采用以下两种方法

1.可以设置参数log_autovacuum_min_duration，为了在数据库日志中查看autovacuum记录。
2.通过手工vacuum verbose tablename 查看有关vacuum的详细信息以及vauucm不成功的原因，另外手工vaucum成功后，观察会不会触发自动autovacuum进程。