Pivotal Knowledge Base

Follow

"WARNING: database xxx must be vacuumed within XXXX transactions" message seen in Pivotal Greenplum

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x
OS RHEL 6.x

Symptom

All the commands executed on the cluster give lots of WARNING messages as shown below. This alert will cause some OBDC/JDBC connected applications to fail as well.

HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 2145744400 transactions (seg36 sdw7:20358 pid=19142)

Cause

In GPDB, all transactions get a transaction ID to start with. This value increases with every query or transaction. Database administrators should be doing a full database vacuum periodically to avoid this situation. Check routine maintenance tasks for GPDB for more information.

When the limit reaches a certain value, then WARNING messages will be seen for all queries. The value after which the warnings start coming in can be calculated as shown below:

(2^31 - xid_warn_limit - xid_stop_limit)

By default, xid_warn_limit is set to 500M, xid_stop_limit is set to 1000M. So, with the default settings, the database will start to generate this alert when the age of the database has reached 500M - 600M. You can check the limits using show commands:

gpadmin=# SHOW xid_stop_limit ; 
 xid_stop_limit 
----------------
 1000000000
(1 row)

gpadmin=# SHOW xid_warn_limit; 
 xid_warn_limit 
----------------
 500000000
(1 row)

Use the following queries to check the current database age:

-- check db age for master instance
SELECT datname, datfrozenxid ,age(datfrozenxid) FROM pg_database ORDER BY 1 DESC ;
--check db age for all segments. Sometimes the WARNINGS gets generated if one segment is at high age
SELECT gp_segment_id,datname, age(datfrozenxid) FROM gp_dist_random('pg_database') ORDER BY 3 DESC; 

Resolution

Run VACUUM FREEZE for all the databases OR for a specific database showing high age. You can connect to the database and issue a VACUUM FREEZE. It would be better to run through nohup so that the process can go through, even if the connection gets disconnected. The following example shows a VACUUM FREEZE getting executed on the postgres database :

nohup psql -c " VACUUM FREEZE " postgres 2>&1 & 

You can also run VACUUM FREEZE for specific objects that have high age. Refer to this article for further information.

If the age is not reducing even after running VACUUM FREEZE, then please open a severity 2 support case with support.pivotal.io.

Additional Information

Please refer routine maintenance tasks for GPDB at www.pivotal.io for more information. Related resolved issues number 23647 at 4.3.2.1 release notes .

 

Comments

  • Avatar
    Brendan Stephens

    Context of the Article suggests Internal Use.

  • Avatar
    Shawn Yan

    Hi Brendan, I do agree, but looks like many customer still need such an article (to understand this issue better). So maybe it's better in a public status for now?

Powered by Zendesk