Greenplum:ARNING: database "xxxx" must be vacuumed within xxxx transactions,HINT: To avoid a databas

作者：Cpp五条 | 2024-03-17 13:43:50

踩

must be vacuumed within

Greenplum告警错误

检查master数据库年龄

gpadmin=# SELECT datname, datfrozenxid ,age(datfrozenxid) FROM pg_database ORDER BY 1 DESC ;

检查segment数据库年龄

gpadmin=# SELECT gp_segment_id,datname, age(datfrozenxid) FROM gp_dist_random('pg_database') ORDER BY 3 DESC;

WARNING: database "warehouse" must be vacuumed within 1498780009 transactions (seg59 slice1 192-168-102-83:40003 pid=42937)

HINT: To avoid a database shutdown, execute a full-database VACUUM in "warehouse".

---------------+-----------+-----------

10 | warehouse | 648853276

5 | warehouse | 648837230

7 | template0 | 648813926

47 | template0 | 648812301

14 | template0 | 648799717

7 | warehouse | 648789699

。。。。。。。。。。。。。。。。。。。。。。。。。。。省略

gpadmin=# SHOW xid_stop_limit ;

xid_stop_limit

----------------

1000000000

(1 row)

gpadmin=# SHOW xid_warn_limit;

xid_warn_limit

----------------

500000000

(1 row)

以上两个参数都是默认值

一旦年龄超过以下数值，那么就会报warning

(2^31 - xid_warn_limit - xid_stop_limit)

也就是年龄超过这个值

gpadmin=# select 2^31-1000000000-500000000;

?column?

-----------

647483648

处理步骤：

1.关闭greenplum

2.临时修改xid_stop_limit到10,000,000

3.启动gp

4.对有影响的库做vacuum freeze;

5.关闭gp，修改参数xid_stop_limit到原来值，启动gp

cat vacuum.sh

#!/bin/bash

source /home/gpadmin/.bash_pro file

psql -h 127.0.0.1 -p 1921 -d warehouse -c "vacuum freeze"

nohup sh vacuum.sh > vacuum.log 2>&1 &

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Cpp五条/article/detail/256323