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;)].
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.
Use https://greenplum.org/calculator/ to set appropriate value for gp_vmem_protect_limit
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
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
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