Pivotal Knowledge Base

Follow

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

Environment

Product Version
Pivotal Greenplum (GPDB) >4.3.4.0
OS RHEL 6.x

Symptom

The query failed 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.gphd.local:1032 pid=745469) (cdbdisp.c:1528)(File cdbdisp.c;Line 1528;Routine cdbdisp_finishCommand;)].

Cause

This is caused due to a new feature on the GPDB version 4.3.4.0 which cancels runaway queries that use 90% of the gp_vmem_protect_limit value; this also helps in preventing segments crashing due to out-of-memory issues.

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

Resolution

This is controlled by the parameter "runaway_detector_activation_percent". Please refer to the documentation here for more information.

flightdata=# show runaway_detector_activation_percent;
 runaway_detector_activation_percent
-------------------------------------
 90
(1 row)

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

Comments

Powered by Zendesk