Query failing with "ERROR: Canceling query because of high VMEM usage"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

A query fails with the error message shown below:

ERROR: Canceling query because of high VMEM usage. Used: 7296MB, available 816MB, red zone: 7372MB (runaway_cleaner.c:135)  (seg15 slice6 sdw2:1032 pid=745469) (cdbdisp.c:1528)(File cdbdisp.c;Line 1528;Routine cdbdisp_finishCommand;)].

Environment

Cause

This error is occurs when queries running on the cluster use 90% of the gp_vmem_protect_limit value. This helps in preventing segments crashing because of out-of-memory (OOM) issues which would cause all running queries to fail. The runaway detector will cancel the query that is using the largest amount of memory to free up RAM and allow all the other queries to run to completion.

As indicated in the sample output above, the query that was executed using 7296MB out of 8GB (90%) of the gp_vmem_protect_limit.

Exceeding the memory limits may also be caused by poor plans due to out-of-date statistics for the tables.

Resolution

Verify the gp_vmem_protect_limit

Use https://greenplum.org/calculator/ to set appropriate value for gp_vmem_protect_limit

Ensure all tables involve in the query are VACUUM'ed and ANALYZE'd

Find all the tables involved in the query and run the following against each table:
VACUUM <schema>.<tablename>
ANALYZE <schema>.<tablename>
REINDEX table <schema>.<tablename>

For more information: Routine System Maintenance Tasks

Ensure the main catalog tables are VACUUM'ed and ANALYZE'd

VACUUM pg_catalog.pg_class
VACUUM pg_catalog.pg_attribute
VACUUM pg_catalog.pg_type
ANALYZE pg_catalog.pg_class
ANALYZE pg_catalog.pg_attribute
ANALYZE pg_catalog.pg_type
REINDEX TABLE pg_catalog.pg_class
REINDEX TABLE pg_catalog.pg_attribute
REINDEX TABLE pg_catalog.pg_type

Check the guidelines

Memory and Resource Management with Resource Queues

To monitor the usage of memory by the session, you may check the view session_state.session_level_memory_consumption, which lists the memory consumption for sessions that are running SQL queries. For more information on this view, please refer to Monitoring a Greenplum System