Pivotal Knowledge Base

Follow

Catalog Queries or Commands Experience Performance Degradation

Environment

 Product  Version
 Pivotal Greenplum  4.2.x
 OS  All Supported OS

Symptom

Catalog queries or commands experience performance degradation.

Simple PSQL metadata commands such as "\d" or "\dt", which should complete in less than one second, begin to take far longer, sometimes over 60 seconds to complete. Subsequently, overall system performance begins to degrade.

Cause

This is due to catalog bloat, refer to this document for more information about catalog bloat.

Resolution

If you frequently drop and create objects in your database, it is recommended that you periodically clean up your system catalog to maintain performance.

The system catalog has the potential to grow larger and larger unless a VACUUM command is performed periodically to open up space occupied by deleted objects. This is referred to as a catalog bloat and can cause extreme degradation in the overall performance of the system.

The following script is designed to assist with this process: Replace databasename with the database name to be cleaned:

#!/bin/bash
DBNAME="databasename"  # Replace databasename with the database name to be cleaned.
VCOMMAND="VACUUM ANALYZE"
#VCOMMAND="VACUUM FULL ANALYZE"
psql -tc "select '$VCOMMAND' || ' pg_catalog.' || relname || ';' from
pg_class
a,pg_namespace b where a.relnamespace=b.oid and b.nspname= 'pg_catalog'
and
a.relkind='r'" $DBNAME | psql -a $DBNAME

Risks/Impact
  • Although this is an online operation, this function runs best during a period with no interfering database activity.
  • If your system catalog is bloated due to a high object churn over a sustained period of time, consider VACUUM FULL ANALYZE on the System Catalog. Run VACUUM FULL ANALYZE only during a normal maintenance window since VACUUM FULL locks each catalog table it accesses and is generally not interruptible. Check this document for more information on VACUUM and VACUUM FULL.
  • Although offered in PostgreSQL, Greenplum does not recommend the use of Auto-Vacuum.

Comments

Powered by Zendesk