Memory

The Greenplum Command Center Query Monitor reports the current total memory consumed by all processes executing a query. When there is insufficient memory available for a query to complete, the query has an error status in the query monitor and an out of memory error is logged.

If you have enabled resource groups in VMware Greenplum, you can manage the amount of memory available to queries by tuning resource group parameters, and by setting VMware Greenplum configuration parameters that affect resource group memory.

• For a detailed description of resource group memory management, see Using Resource Groups in the VMware Greenplum administration documentation.

• If you are using resource queues, see Memory and Resource Management with Resource Queues and Using Resource Queues for ways to troubleshoot memory problems with resource queues.

• See Tuning SQL Queries for help with query optimization.

The following summary describes the resource group parameters and related VMware Greenplum server configuration parameters that determine the amount of memory available to database queries and how configuration choices affect concurrency, spill file usage, and query performance.

Resource Group Memory Configuration Parameters

A resource group has parameters CONCURRENCY, MEMORY_LIMIT, MEMORY_SHARED_QUOTA, and MEMORY_SPILL_RATIO, which determine how much memory is allocated to execute a query. The CPU_LIMIT parameter has no effect on memory allocation. See the CREATE RESOURCE GROUP SQL reference for command syntax and information about these parameters.

MEMORY_LIMIT

This parameter sets the amount of memory the resource group manages as a percentage of the memory available to resource groups. The sum of all resource groups’ MEMORY_LIMITs must not exceed 100. If the sum of all resource groups’ MEMORY_LIMITs is less than 100, the remaining, unallocated memory is global resource group shared memory, available to queries from all resource groups on a first-come, first-served basis.

MEMORY_SHARED_QUOTA

A resource group divides the memory it manages into a fixed portion and a shared portion, called resource group shared memory. This parameter specifies the percentage of a resource group’s memory that is shared. The default is 20 and the value can range from 0 to 100.

CONCURRENCY

This parameter limits the number of concurrent transactions a resource group allows. The fixed portion of the memory the resource group manages is divided equally among CONCURRENCY transaction slots. Every transaction starts with this fixed portion of memory and, if needed, VMware Greenplum uses additional memory from the resource group shared memory and global resource group shared memory.

MEMORY_SPILL_RATIO

This parameter sets a limit for the amount of memory a query can use before it spills to disk. The parameter value is expressed as a percentage of the fixed memory allocation. The default is 20 and the value can range from 0 to 100. A higher value uses more memory, but can improve query performance. A transaction can override this value by setting the memory_spill_ratio configuration parameter in the session.

When a query executes, VMware Greenplum allocates memory to it from the fixed portion of the resource group’s memory. If the query needs more memory and the resource group has available shared memory, VMware Greenplum allocates additional memory. If insufficient shared memory is available, VMware Greenplum allocates additional memory from global shared memory, if available. If the required memory is not available the transaction fails.

VMware Greenplum Memory Configuration Parameters

The following VMware Greenplum configuration parameters affect resource group memory allocation and concurrency.

gp_resource_group_memory_limit

This VMware Greenplum server configuration parameter sets the percentage of each host’s system memory to be managed by resource groups. The default is 0.7 (70%). This memory is divided equally among the primary segments on each host, and further divided among resource groups with the MEMORY_LIMIT resource group parameter. Any memory not allocated to resource groups becomes global shared memory available to queries from all resource groups. See gp_resource_group_memory_limit for a complete reference for this parameter.

gp_resgroup_memory_policy

This parameter determines how VMware Greenplum allocates memory to query operators. The default value, eager_free, re-allocates memory from completed operators to operators later in the query plan. The alternative value for this parameter, auto, allocates a fixed amount of memory to operators that are not memory-intensive and the rest to the memory-intensive operators. The default value is usually the optimal setting. See gp_resgroup_memory_policy for a complete reference for this parameter.

memory_spill_ratio

A transaction can override the resource group’s MEMORY_SPILL_RATIO value by setting the memory_spill_ratio configuration parameter in the session. The value is a percentage of the fixed memory allocation for transactions in the resource group, expressed as an integer from 0 to 100. The performance of queries with low memory requirements can be improved by setting the memory_spill_ratio parameter in the session to a low setting, for example 0 to 2. See memory_spill_ratio for more information about this parameter.