"Greenplum5.0 Best Practices" memory and resource Queues (iv) _ Database

Click to have a surprise

Avoid memory errors and gpdb resource issues

Memory management has an important performance impact on the gpdb cluster. Most environments recommend using default settings. Don't change the default settings unless you really understand the needs of your system. Resolving Memory Overflow errors

Low Memory error mapping segment database, node, process information for gpdb that are experiencing out-of-memory errors
For example:

Out of Memeory (seg27 host. example.com pid = 47093)
VM Protecte failed to allocate 4096 bytes, 0MB available

Typically, a gpdb memory overflow can be attributed to the following conditions: The system itself is insufficient memory resources, not enough to use in the segment database level, the data skew in the query level, calculation tilt to solve the gpdb memory overflow, you can use the following methods to modify the query statement to minimize memory consumption using resource queues, Reduce the number of concurrent queries check the *gp_vmem_protect_limit* parameter configuration at the database level. Use the following example to calculate the value in the resource queue, setting a memory quota for each query is using session settings to reduce the *statement_mem* of the query at the database level reduce *statememt_mem* reduce the number of databases on the node in the cluster, But this requires reinstalling the database cluster and reloading the data adds hardware resources to the node, and increasing the number of segment databases if possible (added according to your requirements) does not mitigate the problem of insufficient memory. The memory used by each query is *statememt_mem* to determine.
Add nodes, reduce the number of segments, and adjust *gp_vmem_protect_limit* to increase memory allocations. # # Greenplum configuration of memory parameters if you carefully configure system parameters, you can avoid most memory overflow problems. If you cannot increase the system hardware resources, we can manage the task by configuring the resource queue to avoid memory overflow. When a segment database fails, the memory requirements are important to the mirror segment because it requires
The following system settings and database memory parameter settings are recommended not to vm.overcommit_memory system-level parameter settings with a system-level large storage page. The recommended setting is 2, which determines how much resources are allocated to the process for use by the system. Vm.overcommit_ratio the Linux system-level parameter settings. Percentage of memory usage. The default setting is 50. If this value is set too high, it will cause the system to have no resources available, which can also cause a segment failure or a database failure; If this setting is only too low, it will result in query concurrency and query complexity, by reducing the amount of storage in the database cluster to achieve. If you want to increase this value, be sure to keep some of your resources reserved for the operating system. *gp_vmem_protect_limit *. The maximum amount of memory that can be requested for all work on a segment database is controlled by that value. Never set this value greater than the physical memory size (RAM) that the operating system contains. If set too large, it is easy to cause problems, the operation failed, and so on. If the value you set is too small, there is a problem. For example, when a real system needs memory, it is stopped. The query is likely to fail because the hiting is restricted. But it can protect the problem of the failure of the library and the failure of the node. Runaway_detector_activation_percent Stop runaway query, introduce to GPDB4.3.4, avoid memory anomaly. This parameter *runaway_detector_activation_percent* the system parameter to control the memory using the *gp_vmem_protect_limit* to reach a certain percentage and then to trigger the abort of the runaway query. The default value for this value is 90%. If the amount of memory used on a segment database exceeds this percentage, gpdb will abort the query based on memory usage. Query memory usage is below the required percentage and will not be aborted. Statement_mem The amount of memory used by a query in a segment database. If the excess memory for this value is requested, an overflow file will be generated to disk. Use the following formula to determine the value of the *statement_mem*.

 
 (Vmprotect * 0.9)/max_expected_concurrent_queries

The default value for Statement_mem is 125MB. For example, a query runs on the Dell EMC DCA V2 system, and using default values will change to 1GB memory for each segment database (8 segments * 125MB). Set at session level
Statement_mem to complete the query task. This setup task will reduce the concurrency of the query. For cluster high concurrency using resource queues to achieve additional control, the cluster runs how many queries.
Gp_workfle_limit_files_per_query This parameter is used to control the maximum number of disk overflow files for each query task. An overflow file is a temporary disk file that is created when a query requires more memory resources. When a query requests more disk overflow files than this value, it causes the query to be aborted. This value defaults to 0, meaning that there is no limit to the number of overflow files in the query, knowing that the number of overflow files fills the entire file system. It's crazy to think about it.
Gp_workfile_compress_algorithm sets the compression algorithm for the overflow file to avoid the problem of overflow file too large, reduce the pressure of I/O operation.
Sample Analysis

The cluster contains physical memory of: 256GB
Swap Space SWAP:64GB
A total of 4 nodes, each node has 8 segments of the database, 8 mirror segments of the database;
The maximum number of nodes allowed to fail per master node is 11

 Gp_vmem = ((SWAP + RAM)-(7.5GB + 0.05 * RAM)  )/1.7 
                  =  ((+ + 256)-(7.5 + 0.05*256))/1.7
                  = 176
  vm.overcommit_ratio = (RAM-(0.026 * gp_vmeme))/ram
                                   = (256-(0.026 * 176))/256
                                   = 0.982

So set vm.overcommit_ratio = 98

Calculation

Gp_vmem_protect_limit = gp_vmem/maximum_acting_primary_segments
                                              = 176/11
                                              = GB
                                              = 16384 MB

Resource Queue Configuration

The GPDB Resource team provides a very powerful mechanism for managing the entire cluster load. The resource queue can limit the number of queries in the queue and the amount of memory used in the query statement. When a query is submitted to the GPDB cluster, it is appended to the resource queue. This determines whether the query is received by the resource queue and determines if sufficient resources are available for execution of the query.

Associates all user and administrator-defined resource queues, each logged-on user has its own resource queue, and any queries submitted by this user will be associated with the resource queues provided by the administrator. If the allocation queue is not displayed, the user's query will be converted to the default queue.
Do not use Gpadmin users or other superuser roles to run queries. Because the superuser is not restricted by the resource queue, the Superuser runs on its specified resource queue and is not restricted by the parameters previously set. Use the *active_statements* resource queue parameter to limit the number of active queries that a particular resource queue member can run concurrently use the *memory_limit* parameter to limit the amount of memory that can be used by all queries in the resource queue. By combining the *active_statements* and *memory_limit* properties, an administrator can fully control the activity that is emitted from a given resource queue. Assignment work provides a resource queue with the name: *sample_queue*, *active_statements* set to ten, *memory_limit* set to 2000MB. This resource queue has an approximate 2GB memory limit for executing queries on each segment of the database. For a cluster, there are 8 segments of the database on each node, so the memory required for the node to execute the query is 16GB for *sample_queue* this queue. (2gb* 8 segmnet/server). If a node has 64GB of memory, then a resource queue like *sample_queue* should not exceed 4 (4 queues GB per queue). Note that by using *statement_mem*, running individual queries in a queue can allocate more internal talent for their "*share*". Thus reducing the amount of memory available for other queries. You can use resource queues to control workloads to achieve the desired effect. Queues with the max priority block other lower-priority queues from running. Until the max queue is finished, the lower-priority team executes the priority of dynamically adjusting the resource queues to meet business requirements based on the load and time periods in the day. Depending on the time period and the usage of the system, the typical environment will have an operation flow that dynamically adjusts the queue priority. Workflows can be implemented through scripts and added to the crontab. Use the *gptoolkit* tool to view the usage of monitoring resource queues and understand how queues work

Click to have a surprise