VMEM Errors

[Kurtis Walker]

Good morning,

  I have a Greenplum cluster with three resource queues configured.  Together they are configured to use 90% of the gp_vmem_protect_limit.  I’m still seeing occasional errors like:

 

“ERROR: Canceling query because of high VMEM usage. Used: 204MB, available 813MB, red zone: 7372MB”

 

So I have a couple questions.

 

1. Are there some kinds of statements that bypass the rules of the resource queues?  The statements that fail most frequently are “Copy table from STDIN”.  How about Vacuum statements?
2. How should I interpret the 3 numbers given in that error message.  The resource queue that I expect this query to use has 1024MB of memory and 8 active statements.  My reading of the error message suggests that there is plenty of space available.  i.e. 204MB currently in use before trying to execute the failed query.  813MB was available to try and use.  Each statement should try and take 1024/8 = 128MB of new memory (less than 813MB), any extra should spill over to files on disk.  Seems my understanding is not correct.
3. I know there is a global setting for statement_mem and max_statement_mem.  I assumed with the use of resource queues, that those settings do not have any effect, as the parameters from the queue would supersede them.  Is that correct?

 

Thanks for any help you can provide!

 

Kurt

 

[Kevin Huang]

This message generally gets triggered if runaway_detector_activation_percent setting is exceeded. Either a query used up a large amount of VMEM or there are many queries running and this query happened to exceed the threshold.

1.) From documentation, it mentions superusers are exempt from resource queue rules:

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

2.) The numbers do seem low compared to the red zone. Would be good if you could share complete details of the resource queue setting as well as gp_vmem_protect_limit value, RAM, number of segments, etc.

3.) statement_mem can be set and if it exceeds memory_limit/active_statements then the higher statement_mem value will be used for the query. The effect from my understanding is it would lower the number of active_statements that are able to run in the queue.

Hope that's of some help.

Kevin

[Kurtis Walker]

Thanks Kevin!  The superuser being exempt from resource queues could be my answer.  We’re running some maintenance tasks with a super user.  I altered pg_default, but I will try making a regular user to do those tasks.

 

gp_vmem_protect_limit is set to 8192MB.

A resource queue for queries is set to 5836MB and 50 statements

A resource queue for ingesting data is set to 1024MB and 8 statements

Pg_default is set to 512MB and 4 statements

 

4 segment nodes, 128GB memory each,  8 segments + 8 mirrors on each node.

 

From: Kevin Huang <kchua...@gmail.com>
Date: Tuesday, May 9, 2023 at 3:15 PM
To: Greenplum Users <gpdb-...@greenplum.org>
Cc: Kurtis Walker <kurtis...@sugarcrm.com>
Subject: Re: VMEM Errors

EXTERNAL EMAIL

This message generally gets triggered if runaway_detector_activation_percent setting is exceeded. Either a query used up a large amount of VMEM or there are many queries running and this query happened to exceed the threshold.

 

1.) From documentation, it mentions superusers are exempt from resource queue rules:

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

2.) The numbers do seem low compared to the red zone. Would be good if you could share complete details of the resource queue 8 as well as gp_vmem_protect_limit value, RAM, number of segments, etc.

[Kurtis Walker]

It turns out that COPY statements do not respect the memory rules set up with resource queues.  I am exploring using resource groups instead.  Two questions:

 

1. Will COPY abide by the rules of a resource group?  The doc for COPY says:  “Greenplum Database does not apply a cost value or memory value to a COPY command, resource queues with only cost or memory limits do not affect the running of COPY commands.”.  But it does not say anything about resource groups.
2. I’m failing to understand exactly how MEMORY_SPILL_RATIO is used.  The doc says “The memory usage threshold for memory-intensive transactions. When a transaction reaches this threshold, it spills to disk.”.  I guess the numerator for the ratio would be the memory needed for the transaction.  I’m unclear what is the denominator.  Is it the fixed amount of memory for the transaction in a resource group, or does it also include the shared memory from the group and the global shared memory?  If the denominator does not count the shared memory, then I guess having shared memory is not useful when using MEMORY_SPILL_RATIO?

 

Thanks again for the help!

 

Kurt

--
You received this message because you are subscribed to the Google Groups "Greenplum Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+...@greenplum.org.
To view this discussion on the web visit https://groups.google.com/a/greenplum.org/d/msgid/gpdb-users/CY4PR0101MB3080E6489CBA8B2C9FA9C63591779%40CY4PR0101MB3080.prod.exchangelabs.com.