Pivotal Knowledge Base

Follow

How to check database age in Greenplum

Environment

Pivotal Greenplum Database (GPDB) 4 and 5

Purpose

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 age() function.

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:

  1. At a database level which is dependent on the age of the oldest objects in the database
  2. At a table level which just tests a specific relation xid and determines its age

Procedure

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.

 

Comments

Powered by Zendesk