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

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 &