How are we out of memory if disk spill is enabled?

[Brandon Dean]

We are utilizing resource groups to manage workload on our Greenplum cluster but have had instances where queries fail with errors like this:

WARN tool.EvalSqlTool: SQL exception executing statement: org.postgresql.util.PSQLException: ERROR: Out of memory  (seg7 xxx.xxx.xxx.xxx:16002 pid=65712)

  Detail: Resource group memory limit reached

I'm confused why this would occur rather than just spilling to disk.

OS is Redhat 7.5 and Greenplum database version is 5.12

gp_resource_manager=group 

gp_resgroup_memory_policy=eager_free

memory_spill_ratio=20

gp_workfile_limit_per_query=0

gp_workfile_limit_per_segment=0

Is there someone who can help me understand this behavior better?

[Jesse Krizenesky]

We are utilizing resource groups to manage workload on our Greenplum cluster but have had instances where queries fail with errors like this:

WARN tool.EvalSqlTool: SQL exception executing statement: org.postgresql.util.PSQLException: ERROR: Out of memory  (seg7 xxx.xxx.xxx.xxx:16002 pid=65712)

  Detail: Resource group memory limit reached

Sounds/Reads like a resource queue memory limit has been reached, not a physical limit.

--
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 post to this group, send email to gpdb-...@greenplum.org.
Visit this group at https://groups.google.com/a/greenplum.org/group/gpdb-users/.
For more options, visit https://groups.google.com/a/greenplum.org/d/optout.

--

Jesse Krizenesky

920-378-2717

zData inc.

[Brandon Dean]

Jesse, thanks for the quick response, but I'm confused.  If I don't spill to disk when my resource group memory limits are reached for my transaction, when do I?  The memory_spill_ratio is a configurable parameter for each resource group.  I'm not talking about using swap memory at the OS level.

Just to be clear, I am using resource groups and not resource queues.

[Jon Roberts]

Are your stats up to date?  Is it possible that you have no or stale statistics?

Jon Roberts

[Ivan Novick]

According to how i understand it:

not every operation can just spill if there is not enough memory available.  Things like SORTING can spill but there is a fixed amount of memory needed for all parts of the query and if there is not enough memory available in the resource group it should error out.....

--

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 post to this group, send email to gpdb-...@greenplum.org.
Visit this group at https://groups.google.com/a/greenplum.org/group/gpdb-users/.
For more options, visit https://groups.google.com/a/greenplum.org/d/optout.

--

Ivan Novick, Product Manager Pivotal Greenplum

ino...@pivotal.io --  (Mobile) 408-230-6491

https://www.youtube.com/GreenplumDatabase

[Simon Gao]

you can find some logs in master or the seg7 which will indicate the consumed memory amount by the query. If the memory usage is normal, you can just tune memory setting in the group, e.g. leave some memory unallocated for global share, or increase memory limit for this group, or increase memory_spill_ratio. If the memory usage is super high, there might be some memory leak. in this case, please help to open an issue with some reproduce steps, e.g. your table schema, query, and all resource group configurations, and we can have a look. 

[Luis Macedo]

Brandon,

Are you running as a superuser o gpadmin or as a regular user? Also there is only one query running or there are many?

Thanks,

Luis Macedo | Sr Platform Architect | Pivotal Inc 

Call Me @ +55 11 97616-6438

Take care of the customers and the rest takes care of itself

---

Pivotal POV Blog

Pivotal Mission: Help Developers Do What They Love

Big Data Problems? Go here!

Latency Issues? No more!

---

[Brandon Dean]

Jon, my stats were up to date but I'm not trying to tune for this specific query.  My question is really about why the disk spill is not occurring when I get an out of memory condition for any query.

[Brandon Dean]

Thanks Ivan, that is really helpful information!  Do you know if there is any documentation that provides more information about what will spill to disk and what won't?

[Brandon Dean]

Thanks Simon, this is a relatively simple query so I don't have any immediate reason to suspect a memory leak.  I did find these messages in the seg7 logs:

2019-02-07 10:51:54.525774 EST,"myuser","mydb",p65712,th493721728,"xxx.xxx.xxx.xxx","8057",2019-02-07 10:51:53 EST,0,con1892,cmd3,seg7,,dx4594,,sx1,"LOG","00000","Resource group memory information: current group id is 133463, memLimit cap is 10, memSharedQuota cap is 20, memSpillRatio cap is 20, group expected memory limit is 677 MB, memory quota granted in currenct group is 540 MB, shared quota granted in current group is 137 MB, memory assigned to all running slots is 108 MB, memory usage in current group is 172 MB, memory shared usage in current group is 136 MB, memory quota in current slot is 36 MB, memory usage in current slot is 172 MB, memory usage in current proc is 168 MB",,,,,,,0,,,,

2019-02-07 10:51:54.525905 EST,"myuser","mydb",p65712,th493721728,"xxx.xxx.xxx.xxx","8057",2019-02-07 10:51:53 EST,0,con1892,cmd3,seg7,,dx4594,,sx1,"LOG","00000","Logging memory usage for reaching resource group limit",,,,,,"delete from my_schema.""my_table"" using ""my_table_s"" where my_schema.""my_table"".col1=""my_table_s"".col1",0,,"memprot.c",268,

There's then what looks like a very detailed breakdown of the internal memory allocations that's a little hard to follow, but I can post if that would be helpful.  After that are the messages:

2019-02-07 10:51:54.528119 EST,"myuser","mydb",p65712,th493721728,"xxx.xxx.xxx.xxx","8057",2019-02-07 10:51:53 EST,0,con1892,cmd3,seg7,,dx4594,,sx1,"ERROR","53400","Out of memory","Resource group memory limit reached",,,,,"delete from my_schema.""my_table"" using ""my_table_s"" where my_schema.""my_table"".col1=""my_table_s"".col1",0,,"memprot.c",320,

2019-02-07 10:51:54.528147 EST,"myuser","mydb",p65712,th493721728,"xxx.xxx.xxx.xxx","8057",2019-02-07 10:51:53 EST,0,con1892,cmd3,seg7,,dx4594,,sx1,"LOG","00000","An exception was encountered during the execution of statement: delete from my_schema.""my_table"" using ""my_table_s"" where my_schema.""my_table"".col1=""my_table_s"".col1",,,,,,,0,,,,

Based on Ivan't comment, I am assuming that this particular query ran out of memory on an operation that wasn't able to spill to disk.  We are still learning how to best tune our resource groups for our workload, but what I really wanted to understand with this post was how the spill to disk worked and what the limitations are so that we can take that into account when tuning.  This particular type of query is part of our standard load process so we definitely want to tune the workload group for our load utilities with this type of query in mind.

[Ivan Novick]

This is where I would start Brandon:

http://docs.greenplum.org/5160/admin_guide/workload_mgmt_resgroups.html

But i think more information is needed in these documents.  I will also try to follow up to see if someone can help to put some more clarfication in the docs on this.

Cheers,

Ivan

[Melanie Plageman]

Hi Brandon,

I may have missed it, but do you mind sharing the explain plan and query?

Thanks

Melanie Plageman

[Brandon Dean]

Luis, this particular query was ran as a regular user.  There are multiple users on the system so it is quite possible other queries were running at the same time.

[Brandon Dean]

Thanks Ivan, I have spent a lot of time looking at those docs but I agree that they are a little short on details when it comes to memory spill situations.

[Brandon Dean]

Sure Melanie, that information is below.  As I mentioned before though, I'm not trying to troubleshoot this specific query but understand how spill to disk works for greenplum in general.  This query is just an example of where I had expected it would spill to disk but it just failed with OOM instead.

explain delete from my_schema.my_table using my_schema_s.my_table_s where my_db.my_table.col1=my_table_s.col1;

                                                               QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------

 Delete  (cost=0.00..10914.33 rows=6818 width=1)

   ->  Redistribute Motion 135:135  (slice3; segments: 135)  (cost=0.00..954.58 rows=6818 width=374)

         Hash Key: my_db.my_table.key1

         ->  Result  (cost=0.00..946.60 rows=6818 width=374)

               ->  Hash Join  (cost=0.00..944.05 rows=6818 width=589)

                     Hash Cond: my_db.my_table.col1::text = my_table_s.col1::text

                     ->  Redistribute Motion 135:135  (slice1; segments: 135)  (cost=0.00..471.24 rows=12291 width=589)

                           Hash Key: my_db.my_table.col1

                           ->  Table Scan on my_table  (cost=0.00..435.12 rows=12291 width=589)

                     ->  Hash  (cost=436.52..436.52 rows=6818 width=97)

                           ->  Redistribute Motion 135:135  (slice2; segments: 135)  (cost=0.00..436.52 rows=6818 width=97)

                                 Hash Key: my_table_s.col1::text

                                 ->  Table Scan on my_table_s  (cost=0.00..433.22 rows=6818 width=97)

 Optimizer status: PQO version 3.8.0

(14 rows)

[Luis Macedo]

Brandon,

I am a little green with RG but you might have reached the memory limit for all quarries in the group so GPDB canceled one.

Statement_mem tells how much memory a single query can consume, after that it spills. I am not sure if this still true on RG scenario...

You might want to run it again when the cluster is quieter and see if it finishes...

BTW, try to avoid delete when possible, specially on large volumes. If it's ETL I can show you a few strategies to avoid the delete.

Rgda

--- Sent from my Google Pixel

[Simon Gao]

To answer your question about spill and out of memory, let me explain what I know about greenplum memory allocation and accounting internally. Welcome other experts to share more insights.

when a query is started, it will get allocated some initial quota memory and the quota will also be assigned to each operator in the query execution plan. For non-memory intensive operators,  it will get fixed memory 100k or so. For all memory intensive opetators, e.g. hash join or sort, they will equally share the rest of the quota. memory intensive operators are able to spill to disk. 

when the query is running, each operator will work on their responsibility and request memory as need. All the memory requests are tracked so GP knows the exact memory usage by a query and all queries in a group. For memory intensive operators, if the memory quota is enough to hold all data, they don't need to spill to disk.  If the quota is smaller than the requirement, e.g. cannot sort all the tuples in memory, they will spill to disk but still leverage memory.

So far we can see two different concepts:  initial quota and actual usage. Usually they are different. In your example, the initial quota is 36M,  actual usage is 170+M and the query is still requesting more.  The gap seems big but real.  Specially when query is starting to spill, its memory usage can still grow.

If we want to avoid out of memory at all, one option is to make actual max memory usage predictable, and assign a limited memory for operators so that their overall usage can be less than max. I am not sure if this is feasible or better

It is also easy to avoid single query out of memory, e.g. we can just allow any query to continue to get memory without out of memory error until the cluster has no availble free memory. But that is unfair and bad to other concurrent queries. That is why we suggest defining multiple resource groups to isolate your workloads,  so that similar trouble queries can fail with out of memory and have minimal impact to other groups or other queries in the same group, as you saw.

Back to your specific query and problem, I suggest you leave a few memory unallocated for global shared memory.  

Brandon Dean <engr...@gmail.com> 于 2019年2月16日周六 上午12:00写道：

[Brandon Dean]

Simon, thank you for sharing those details with me.  It's very helpful to have that breakdown.  I came to the same conclusion that we need to make sure that we have a reserve of unallocated memory for the global shared pool.