Pivotal Greenplum Database (GPDB) 4.3.x
Resource queue tuning is an important part of Pivotal Greenplum database administration.
The resource queues not only prioritize and allocate resources to queries according to business requirements, they also prevent queries from starting when resources are unavailable.
Overcommitting resource queues can cause downgraded query performance and "Out of Memory" errors. As such, it's important to understand the relationship between the Resource Queue settings and how memory is allocated to statements.
Linux supports virtual memory, that is, using a disk as an extension of RAM so that the effective size of usable memory grows correspondingly. The kernel will write the contents of a currently unused block of memory to the hard disk so that the memory can be used for another purpose. When the original contents are needed again, they are read back into memory. This is all made completely transparent to the user.
Following the Installation Guide, system administrators are instructed to configure a few key system parameters, including:
This recommended setting (2) instructs the OS not to overcommit memory.
The total address space for the system is not permitted to exceed swap + a configurable amount (default is 50%) of physical RAM.
This setting specifies the configurable amount of RAM to use.
The current recommendation, via the Pivotal Greenplum Memory Calculator, is 95%, which will be used as the basis for the examples provided in this article.
With these parameters in place, the Operating System Virtual Memory is calculated as:
( SWAP + ( RAM * vm.overcommit_ratio ) )
Example: 32 GB SWAP + 64 GB RAM
( 32 + ( 64 * .95 ) ) = 92.8 GB of Virtual Memory
This is the total amount of Virtual Memory available to the Operating System to run and manage ALL Applications and System Functions. If this amount is exceeded, the Operating System will run "Out of Memory". If an OS "Out of Memory" event occurs, the system will "PANIC" and attempt to reset.
This condition can lead to data loss events if the OS is unable to commit the memory to complete critical events, such as updating a transaction file correctly.
This sets the amount of Virtual Memory each segment can use.
The linux kernel is very good at optimizing its own memory, but we still have to ensure we do not over-allocate GPDB memory resulting in out of memory events.
Using the Pivotal Greenplum Memory Calculator, a value can be calculated that provides a safe margin for Operating System overhead, including a reasonable amount of spill files.
Example: 32 GB SWAP + 64 GB RAM + 8 Primary Segments per Host
Using the parameters above, the calculator generates a value of 6400 MB.
With 8 primary segments per host that's (6400 * 8) 50 GB of the 92 GB available to the operating system.
Allocates segment host memory per query.
The amount of memory allocated with this parameter cannot exceed MAX_STATEMENT_MEM or the memory limit on the resource queue through which the query was submitted.
Greenplum Database creates spill files, also known as work files, on disk if it does not have sufficient memory to execute an SQL query in memory.
The default value 125 MB (128000 K) is acceptable for most implementations as this value can be increased or decreased by SET statements or Resource Queue Memory Allocation.
Calculating Maximum Concurrency and Resource Queue Memory
The maximum number of concurrent queries can be calculated by:
gp_vmem_protect_limit / STATEMENT_MEM
This effectively divides the available virtual memory, by the default amount of memory assigned per query.
Example: gp_vmem_protect_limit (6400 MB) / STATEMENT_MEM (125 MB)
6400 MB / 125 MB = ~51 ACTIVE_STATEMENTS
This means, as a best practice measure, that the sum of the ACTIVE_STATEMENT for ALL resource queues should not exceed 51 and the sum of MEMORY_LIMIT should be equal to or less than 6400 MB.
STATEMENT_MEM is Calculated for Query Execution
There are (3) methods by which Statement Memory is calculated.
- The default STATEMENT_MEM value
-- or --
- The Resource Queue MEMORY_LIMIT divided by the ACTIVE_STATEMENTS
-- or --
- MEMORY_LIMIT * (query_cost / MAX_COST)
- Pivotal Greenplum will always use the LARGER of the calculated values.
- MAX_COST memory calculation only applies if ACTIVE_STATEMENTS is DISABLED (-1)
- Pivotal recommends that MEMORY_LIMIT be used in conjunction with ACTIVE_STATEMENTS rather than with MAX_COST.
Example: ACTIVE_STATEMENTS (10) | MEMORY_LIMIT (2000 MB)
2000 MB / 10 = 200 MB > 125 MB STATEMENT_MEM :: STATEMENT_MEM = 200 MB
In this example, as the calculated statement memory is greater than the default, the calculated value is used.
Example: ACTIVE_STATEMENTS (10) | MEMORY_LIMIT (1000 MB)
1000 MB / 10 = 100 MB < 125 MB STATEMENT_MEM :: STATEMENT_MEM = 125 MB
In this example, as the calculated statement memory is less than the default, and the default value is used.
The ACTIVE_STATEMENTS value would never reach 10 unless additional queries were running under a MIN_COST value rule.
A 1000 MB MEMORY_LIMIT divided by 125 MB, effectively caps this queue at 7 ACTIVE_STATEMENTS.
When a MEMORY_LIMIT is set on a resource queue, the number of queries that the queue can execute concurrently is limited by the queue's available memory.
Memory for this queue would be underutilized, and modifying the ACTIVE_STATMENTS to 7 would assign more memory per query ( 142.8 MB )
Resource Queues In Practice
The following examples assume:
- STATEMENT_MEM (125 MB) and
- gp_vmem_protect_limit (6250 MB)
RQname | ActiveStatment | MemoryLimit | Priority ------------+----------------+-------------+---------- pg_default | 50 | 6250 | medium
This is a simple implementation if there is only 1 resource queue:
50 ACTIVE_STATEMENTS into 6400 MB of memory equals a STATEMENT_MEM of 128 MB.
If the number of ACTIVE_STATEMENTS was REDUCED, each query would receive additional STATEMENT_MEM as (MEMORY_LIMIT / ACTIVE_STATEMENTS > STATEMENT_MEM).
If the number of ACTIVE_STATEMENTS were INCREASED:
- Default STATEMENT_MEM would need to be reduced accordingly (MEMORY_LIMIT/ACTIVE_STATEMENTS)
-- or --
- Roles assigned to the queue would need to have a reduced STATEMENT_MEM applied to their session defaults (ALTER ROLE [name] SET statement_mem...)
Failure to decrease the STATEMENT_MEM would force overallocation.
The resource queue calculation would be less than the STATEMENT_MEM and additional queries would continue to use 125 MB.
Note: While possible to ALTER DATABASE to SET a STATEMENT_MEM default, it may not be practical in application. A resource queue MEMORY_LIMIT will override a database level default.
RQname | ActiveStatment | MemoryLimit | Priority ------------+----------------+-------------+---------- pg_default | 20 | -1 | medium rq1 | 15 | -1 | min rq2 | 10 | -1 | low rq3 | 5 | -1 | medium
This resource queue configuration could work:
Effectively, this is 50 ACTIVE_STATEMENTS * STATEMENT_MEM (125 MB)
However, with a near capacity load, there is nothing preventing overcommitment with SET STATEMENT_MEMORY. With the default MAX_STATEMENT_MEMORY is set to 2000 MB, one query could easily cause virtual memory allocation errors for many other queries.
RQname | ActiveStatment | MemoryLimit | Priority ------------+----------------+-------------+---------- pg_default | 20 | -1 | medium rq1 | 15 | 1875 | min rq2 | 10 | 1250 | low rq3 | 5 | 625 | medium
This configuration is effectively identical to the one above --
The pg_default queue, with no MEMORY_LIMIT would run at capacity with (20 * 125 MB) 2500 MB of memory, but this queue still has the potential of using more than its expected share, through the use of SET STATEMENT_MEMORY and could still cause memory allocation errors for queries in ANY queue.
In order to prevent this, the MEMORY_LIMIT of 2500 MB should be applied to the pg_default queue.
RQname | ActiveStatment | MemoryLimit | Priority ------------+----------------+-------------+---------- pg_default | 20 | 2500 | medium rq1 | 15 | 1875 | min rq2 | 10 | 1250 | low rq3 | 5 | 625 | medium
This configuration is BALANCED --
The product of ACTIVE_STATEMENTS and STATEMENT_MEM is EQUAL to MEMORY_LIMIT.
And the sum of the MEMORY_LIMIT is equal to the gp_vmem_protect_limit.
Setting a MEMORY_LIMIT on a resource queue does not prevent individual queries or sessions from running with additional memory using SET STATEMENT_MEM up to the MAX_STATMENT_MEM, but the queue is limited.
For example, if 1 query runs with SET STATEMENT_MEM = 2000 MB, then 4 other queries can run simultaneously in that queue ( 4 x 125 = 500 MB ) and any remaining queries will queue until the memory is available.
RQname | ActiveStatment | MemoryLimit | Priority ------------+----------------+-------------+---------- pg_default | 10 | 2500 | medium (250 MB) rq1 | 5 | 1875 | high (375 MB) rq2 | 2 | 1250 | max (625 MB) rq3 | 5 | 625 | low (125 MB)
This configuration is optimized for higher STATEMENT_MEM --
In this example configuration, several queues are running with a lower number of ACTIVE_STATEMENTS, thereby increasing the amount of STATEMENT_MEMORY available to each query as the resource queue calculation will be greater than the default (MEMORY_LIMIT / ACTIVE_STATEMENTS > STATEMENT_MEMORY).
These settings safeguard your system from virtual memory crashes --
Set correctly, the resource queue will prevent queries that would exceed your gp_vmem_protect limit. Your gp_vmem_protect limit should protect your OS from running out of virtual memory. Your OS virtual memory settings should prevent your OS from running out of memory...
The tendency is to overcommit the resource queues --
Overallocation may be acceptable in a few circumstances.
For example, if resource queues are not active at the same time, then the same resources can be assigned.
However, overlaps and job overruns can lead to errors and overcommitting memory.
Further, it diminishes the resource queue's ability to utilize the queues memory limit.
It is generally a far better strategy to lower the number of concurrent queries, increasing statement_memory and prioritize the resource queues accordingly.
- With the gp_vmem_protect_limit calculated and the default STATMENT_MEM known the number of ACTIVE_STATEMENTS can be generated and divided among the queues.
- MEMORY_LIMIT for each queue can be calculated by multiplying the ACTIVE_STATEMENTS by the default STATEMENT_MEM.
- Resource queues requiring more memory for faster performance or fewer spill files (work files) can reduce the number of ACTIVE_STATEMENTS, thereby increasing the resource queue calculation.
For more information on Memory Calculations and typical "Out of Memory" errors, please follow the articles below: