Understanding memory utilization related parameters in Greenplum

sachi=# show max_statement_mem;

max_statement_mem

-------------------

2000MB

(1 row)

max_statement_mem parameter is set by database superusers to prevent regular database users from over-allocation.

sachi=# show statement_mem;

statement_mem

---------------

125MB

(1 row)

statement_mem parameter is used to allocate memory to a particular query at runtime (override the default allocation assigned by the resource queue).

sachi=# show gp_resqueue_memory_policy;

gp_resqueue_memory_policy

---------------------------

eager_free

(1 row)

gp_resqueue_memory_policy parameter enables Greenplum memory management features. In Greenplum Database 4.2 and later, the distribution algorithm eager_free takes advantage of the fact that not all operators execute at the same time. The query plan is divided into stages and Greenplum Database eagerly frees memory allocated to a previous stage at the end of that stage’s execution, then allocates the eagerly freed memory to the new stage.When set to none, memory management is the same as in Greenplum Database releases prior to 4.1. When set to auto, query memory usage is controlled bystatement_mem and resource queue memory limits.

sachi=# show gp_vmem_protect_limit;

gp_vmem_protect_limit

-----------------------

8192

(1 row)

gp_vmem_protect_limit parameter sets the upper boundary that all query processes can consume that should not exceed the amount of physical memory of a segment host. When a segment host reaches this limit during query execution, the queries that cause the limit to be exceeded will be cancelled.

sachi=# show gp_vmem_idle_resource_timeout;

gp_vmem_idle_resource_timeout

-------------------------------

18s

(1 row)

sachi=# show gp_vmem_protect_segworker_cache_limit;

gp_vmem_protect_segworker_cache_limit

---------------------------------------

500

(1 row)

gp_vmem_idle_resource_timeout and gp_vmem_protect_segworker_cache_limit parameter is used to free memory on segment hosts held by idle database processes. Administrators may want to adjust these settings on systems with lots of concurrency.