Memory and Resource Management with Resource Queues

    Note: Resource groups are a newer resource management scheme that enforces memory, CPU, and concurrent transaction limits in Greenplum Database. The Managing Resources topic provides a comparison of the resource queue and the resource group management schemes. Refer to for configuration and usage information for this resource management scheme.

    Memory management has a significant impact on performance in a Greenplum Database cluster. The default settings are suitable for most environments. Do not change the default settings until you understand the memory characteristics and usage on your system.

    An out of memory error message identifies the Greenplum segment, host, and process that experienced the out of memory error. For example:

    Some common causes of out-of-memory conditions in Greenplum Database are:

    • Insufficient system memory (RAM) available on the cluster
    • Improperly configured memory parameters
    • Data skew at the segment level

    Following are possible solutions to out of memory conditions:

    • Tune the query to require less memory
    • Reduce query concurrency using a resource queue
    • Validate the configuration parameter at the database level. See calculations for the maximum safe setting in .
    • Set the memory quota on a resource queue to limit the memory used by queries executed within the resource queue
    • Use a session setting to reduce the statement_mem used by specific queries
    • Decrease statement_mem at the database level
    • Decrease the number of segments per host in the Greenplum Database cluster. This solution requires a re-initializing Greenplum Database and reloading your data.
    • Increase memory on the host, if possible. (Additional hardware may be required.)

    Adding segment hosts to the cluster will not in itself alleviate out of memory problems. The memory used by each query is determined by the statement_mem parameter and it is set when the query is invoked. However, if adding more hosts allows decreasing the number of segments per host, then the amount of memory allocated in gp_vmem_protect_limit can be raised.

    A low statement_mem setting (for example, in the 1-3MB range) has been shown to increase the performance of queries with low memory requirements. Use the statement_mem server configuration parameter to override the setting on a per-query basis. For example:

    Most out of memory conditions can be avoided if memory is thoughtfully managed.

    It is not always possible to increase system memory, but you can prevent out-of-memory conditions by configuring memory use correctly and setting up resource queues to manage expected workloads.

    It is important to include memory requirements for mirror segments that become primary segments during a failure to ensure that the cluster can continue when primary segments or segment hosts fail.

    The following are recommended operating system and Greenplum Database memory settings:

    • Do not configure the OS to use huge pages.

    • vm.overcommit_memory

    • vm.overcommit_ratio

      This is a Linux kernel parameter, set in /etc/sysctl.conf. It is the percentage of RAM that is used for application processes. The remainder is reserved for the operating system. The default on Red Hat is 50.

      Setting vm.overcommit_ratio too high may result in not enough memory being reserved for the operating system, which can result in segment host failure or database failure. Setting the value too low reduces the amount of concurrency and query complexity that can be run by reducing the amount of memory available to Greenplum Database. When increasing the setting it is important to remember to always reserve some memory for operating system activities.

      See Resource Queue Segment Memory Configuration for instructions to calculate a value for vm.overcommit_ratio.

    • runaway_detector_activation_percent

      Runaway Query Termination, introduced in Greenplum Database 4.3.4, prevents out of memory conditions. The runaway_detector_activation_percent system parameter controls the percentage of gp_vmem_protect_limit memory utilized that triggers termination of queries. It is set on by default at 90%. If the percentage of gp_vmem_protect_limit memory that is utilized for a segment exceeds the specified value, Greenplum Database terminates queries based on memory usage, beginning with the query consuming the largest amount of memory. Queries are terminated until the utilized percentage of gp_vmem_protect_limit is below the specified percentage.

    • statement_mem

      Use statement_mem to allocate memory used for a query per segment database. If additional memory is required it will spill to disk. Set the optimal value for statement_mem as follows:

      The default value of statement_mem is 125MB. For example, a query running on a Dell EMC DCA system using the default statement_mem value will use 1GB of memory on each segment server (8 segments ⨉ 125MB). Set statement_mem at the session level for specific queries that require additional memory to complete. This setting works well to manage query memory on clusters with low concurrency. For clusters with high concurrency also use resource queues to provide additional control on what and how much is running on the system.

    • gp_workfile_limit_files_per_query

    • gp_workfile_compress_algorithm

      If there are numerous spill files then set gp_workfile_compress_algorithm to compress the spill files. Compressing spill files may help to avoid overloading the disk subsystem with IO operations.

    Greenplum Database resource queues provide a powerful mechanism for managing the workload of the cluster. Queues can be used to limit both the numbers of active queries and the amount of memory that can be used by queries in the queue. When a query is submitted to Greenplum Database, it is added to a resource queue, which determines if the query should be accepted and when the resources are available to execute it.

    • Associate all roles with an administrator-defined resource queue.

      Each login user (role) is associated with a single resource queue; any query the user submits is handled by the associated resource queue. If a queue is not explicitly assigned the user’s queries are handed by the default queue, .

    • Do not run queries with the gpadmin role or other superuser roles.

      Superusers are exempt from resource queue limits, therefore superuser queries always run regardless of the limits set on their assigned queue.

    • Use the ACTIVE_STATEMENTS resource queue parameter to limit the number of active queries that members of a particular queue can run concurrently.

    • Use the MEMORY_LIMIT parameter to control the total amount of memory that queries running through the queue can utilize. By combining the ACTIVE_STATEMENTS and MEMORY_LIMIT attributes an administrator can fully control the activity emitted from a given resource queue.

      The allocation works as follows: Suppose a resource queue, sample_queue, has ACTIVE_STATEMENTS set to 10 and MEMORY_LIMIT set to 2000MB. This limits the queue to approximately 2 gigabytes of memory per segment. For a cluster with 8 segments per server, the total usage per server is 16 GB for sample_queue (2GB * 8 segments/server). If a segment server has 64GB of RAM, there could be no more than four of this type of resource queue on the system before there is a chance of running out of memory (4 queues * 16GB per queue).

      Note that by using STATEMENT_MEM, individual queries running in the queue can allocate more than their “share” of memory, thus reducing the memory available for other queries in the queue.

    • Resource queue priorities can be used to align workloads with desired outcomes. Queues with MAX priority throttle activity in all other queues until the MAX queue completes running all queries.

    • Alter resource queues dynamically to match the real requirements of the queue for the workload and time of day. You can script an operational flow that changes based on the time of day and type of usage of the system and add crontab entries to execute the scripts.

    Parent topic: Greenplum Database Best Practices