Pivotal Knowledge Base

Follow

How to Reduce the Age of a Database by Vacuuming Tables?

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.0 and higher
HAWQ 1.2.0 and higher

Purpose

How to detect which tables are keeping the age of a database high?

Resolution

There are a number of objects, which can keep the age of a database high in addition to base relations due to the fact that UAO tables are now counted. These objects include UAO visimaps, segments and block objects in addition to base objects and TOAST objects.

However only top level relation objects are valid targets for VACUUM and finding the relation associated with a sub-object can be problematic.

To that end, the script attached can be used to generate a list of VACUUM FREEZE commands which can then be run against the database to bring down the age. The script will scan for objects in the database who's age is greater than xid_cutoff which can be configured (defaults to 100,000,000 transactions).

One note, this script will use the age of the objects as found on the master. It is possible that the age on individual segments will remain high. In order to address this issue, run the attached script against the databases on the individual segments and the resultant commands against the same.

Note:

Some table won't be vacuumed using the default VACUUM command in psql. Under this category falls the orphan temporary tables. To find more about orphan temporary schemas, see the following article: Orphan Temporary Schemas .

Comments

  • Avatar
    Brian Honohan

    Hi Mike,

    Would it be possible to amend the 2nd line of the script, to the following:

    SELECT 'VACUUM FREEZE ' || nspname || '.' || relname || ';'

    So that the output produced is:

                         ?column?
    --------------------------------------------------
     VACUUM FREEZE information_schema.sql_languages;
     VACUUM FREEZE information_schema.sql_packages;
     VACUUM FREEZE information_schema.sql_parts;
     VACUUM FREEZE information_schema.sql_sizing;
     VACUUM FREEZE information_schema.sql_sizing_profiles;
    

    Instead of the current:

                         ?column?
    --------------------------------------------------
     VACUUM TABLE information_schema.sql_languages;
     VACUUM TABLE information_schema.sql_packages;
     VACUUM TABLE information_schema.sql_parts;
     VACUUM TABLE information_schema.sql_sizing;
     VACUUM TABLE information_schema.sql_sizing_profiles;
    
  • Avatar
    Michael Roth

    Script has been updated to use VACUUM FREEZE

  • Avatar
    Krzysztof Byszewski

    Hi Mike, now it says vacuum freeze table, should be vacuum freeze

  • Avatar
    Brian Honohan

    Hi Mike,

    The script still creates statements that VACUUM FREEZE TABLE, instead of the desired VACUUM FREEZE.

  • Avatar
    Brian Honohan

    Script updated to remove VACUUM FREEZE TABLE. Replaced with VACUUM FREEZE.

Powered by Zendesk