We have migrated to new Google sites!
Post date: Jan 04, 2015 3:5:22 PM
Will adding more hosts provide relief for an OOM error?
No. The memory used by each query is determined by the statement_mem parameter and it is set at query invocation time. However, if adding more hosts allows decreasing the number of segments per host, then an improvement in the amount memory allocated in gp_vmem_protect_limit can be raised.
What is the impact of not setting gp_vmem_protect_limt and linux sysctl settings correctly?
If the gp_vmem_protect_limit is too high, it is possible for memory to become exhausted on the system and normal operations may fail, causing segment failures. If the gp_vmem_protect_limit is set to a safer low value, then we prevent true memory exhaustion on the system, only queries may fail for hitting the limit.
Can I increase gp_vmem_protect_limit?
Refer to the formula below to determine the max size of vmprotect parameter. Increasing it beyond the calculated limit may cause undesired failures. gp_vmem_protect is a setting per segment database (postgres instance) which indicates the maximum amount of memory that can be allocated
That GUC controls the MAX memory available for a query. It is not allocated at startup. It could have a negative affect on performance but could allow some large queries to run. Refer GPDB reference guide for further details.
LINUX SYSCTL: vm.overcommit_memory
This should be always set to 2. It determines the method the OS uses for determining how much memory can be allocated to processes and 2 is the only safe settings for the database.
LINUX SYSCTL vm.overcommit_ratio
This is the % of RAM that is used for application processes. The default on the system is 50, and we recommend to maintain this setting at the default 50.
GPDB vmprotect
This is the amount of memory per segment to allow all queries to use in total at any given time. If queries exceed this amount, they will fail. The setting should be calculated:
(SWAP + (RAM * vm.overcommit_ratio)) * .9 / number_segments_per_server
(8 + (128 * .5) * .9 / 8 = 8 GB
GPDB statement_mem
This is the amount of memory to be allocated to a query on a segdb. If additional memory is required it will spill. Calculated as following:
( vmprotect * .9 ) / max_expected_concurrent_queries
So for 40 concurrent queries the calc would like this:
(8 GB * .9) / 40 = 184 MB
Can I control the statement memory for queries using resource queries?
Yes. Setting a memory limit on a resource queue sets the maximum amount of memory that all queries submitted through the queue can consume on a segment. The amount of memory allotted to a particular query is based on the queue memory limit divided by the active statement limit. For example, if a queue has a memory limit of 2000MB and an active statement limit of 10, each query submitted through the queue is allotted 200MB of memory by default. The default memory allotment can be overridden on a perquery basis using the statement_mem server configuration parameter (up to the queue memory limit). Once a query has started executing, it holds its allotted memory in the queue until it completes (even if during execution it actually consumes less than its allotted amount of memory).
Shared memory settings
GPDB uses shared memory to communicate between postgres processes that are part of the same postgres instance. The following shared memory settings should be set in sysctl and are rarely modified.
kernel.shmmax = 500000000
kernel.shmmni = 4096
kernel.shmall = 4000000000