- Pivotal Greenplum (GPDB) above 18.104.22.168
- Operating System- RHEL 6.x
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;)].
This is caused due to a new feature on the GPDB version 22.214.171.124 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.
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.