Pivotal Greenplum Database (GPDB) 4 and 5
This article explains how to check the database age in the Greenplum.
What is database age
When we talk about database age in Greenplum, we are referring to the same concept as database age in PostgreSQL. Database age is dependent on the age of the oldest object in the database and can be calculated using the
It is important to check the age in all segments and master when working in Greenplum. The age could differ between segments or between segments and the master.
There are two potential ways of looking at age:
- At a database level which is dependent on the age of the oldest objects in the database
- At a table level which just tests a specific relation xid and determines its age
How to check database age
This section will provide a few different ways of checking database and individual relations age:
At a database level
To determine the age of all databases and have a visual hint of where we are with respect to the wraparound limit, the following query can be used:
WITH cluster AS ( SELECT gp_segment_id, datname, age(datfrozenxid) age FROM pg_database UNION ALL SELECT gp_segment_id, datname, age(datfrozenxid) age FROM gp_dist_random('pg_database') ) SELECT gp_segment_id, datname, age, CASE WHEN age < (2^31-1 - current_setting('xid_stop_limit')::int - current_setting('xid_warn_limit')::int) THEN 'BELOW WARN LIMIT' WHEN ((2^31-1 - current_setting('xid_stop_limit')::int - current_setting('xid_warn_limit')::int) < age) AND (age < (2^31-1 - current_setting('xid_stop_limit')::int)) THEN 'OVER WARN LIMIT and UNDER STOP LIMIT' WHEN age > (2^31-1 - current_setting('xid_stop_limit')::int ) THEN 'OVER STOP LIMIT' WHEN age < 0 THEN 'OVER WRAPAROUND' END FROM cluster ORDER BY datname, gp_segment_id;
At a table level
Running the following SQL will generate an ordered list of tables in a database that is old. Using a LIMIT clause might be useful to see only a specific number of them or a WHERE clause to show only ages greater than some constant. This will need to run in utility mode in all segments where the database age has been identified as high.
SELECT coalesce(n.nspname, ''), relname, relkind, relstorage, age(relfrozenxid) FROM pg_class c LEFT JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind = 'r' AND relstorage NOT IN ('x') ORDER BY 5 DESC
This article shows a procedure to scan the database for relations that are keeping the database age high and automatically generate "VACUUM FREEZE" commands to be used in those tables.