Greenplum Database Memory Overview


Greenplum Database Memory Overview



For best query throughput, the memory configuration should be managed carefully. There are memory configuration options at every level in Greenplum Database, from operating system parameters, to managing resources with resource queues and resource groups, to setting the amount of memory allocated to an individual query.

Segment Host Memory


Figure 1. Greenplum Database Segment Host Memory




Within a segment, the currently active resource management scheme, Resource Queues or Resource Groups, governs how memory is allocated to execute a SQL statement. These constructs allow you to translate business requirements into execution policies in your Greenplum Database system and to guard against queries that could degrade performance. For an overview of resource groups and resource queues, refer to Managing Resources.

Options for Configuring Segment Host Memory

Host memory is the total memory shared by all applications on the segment host. You can configure the amount of host memory using any of the following methods:
  • Allocate swap space to increase the size of virtual memory.




The vm.overcommit_ratio kernel parameter sets the percentage of RAM that is used for application processes, the remainder reserved for the operating system. The default for Red Hat is 50 (50%). Setting this parameter too high may result in insufficient memory reserved for the operating system, which can cause segment host failure or database failure. Leaving the setting at the default of 50 is generally safe, but conservative. Setting the value too low reduces the amount of concurrency and the complexity of queries you can run at the same time by reducing the amount of memory available to Greenplum Database. When increasing vm.overcommit_ratio, it is important to remember to always reserve some memory for operating system activities.

To calculate a safe value for vm.overcommit_ratio when resource queue-based resource management is active, first determine the total memory available to Greenplum Database processes, called gp_vmem_rq, with this formula:
where SWAP is the swap space on the host in GB, and RAM is the number of GB of RAM installed on the host. When resource queue-based resource management is active, use gp_vmem_rq to calculate the vm.overcommit_ratio value with this formula:
vm.overcommit_ratio = (RAM - 0.026 * gp_vmem_rq) / RAM

When resource group-based resource management is active, the operating system vm.overcommit_ratio default value is a good starting point. Tune as necessary. If your memory utilization is too low, increase the value; if your memory or swap usage is too high, decrease the setting.

Configuring Greenplum Database Memory



When resource queue-based resource management is active, the gp_vmem_protect_limit server configuration parameter value identifies the amount of memory to allocate to each segment. This value is estimated by calculating the memory available for all Greenplum Database processes and dividing by the maximum number of primary segments during a failure. If gp_vmem_protect_limit is set too high, queries can fail. Use the following formula to calculate a safe value for gp_vmem_protect_limit; provide the gp_vmem_rq value you calculated earlier.

gp_vmem_protect_limit = gp_vmem_rq / max_acting_primary_segments


gp_vmem_protect_limit does not affect segment memory when using resource groups for Greenplum Database resource management.

When resource group-based resource management is active, the amount of memory allocated to each segment on a segment host is the memory available to Greenplum Database multiplied by the gp_resource_group_memory_limit server configuration parameter and divided by the number of active primary segments on the host. Use the following formula to calculate a safe value for segment memory when using resource groups for resource management.

rg_perseg_mem = ((RAM * (vm.overcommit_ratio / 100) + SWAP) * gp_resource_group_memory_limit) / num_active_primary_segments

Resource queues and resource groups support additional configuration parameters that enable you to further control and refine the amount of memory allocated for queries.