Redshift out of memory when running query

Question

Hello there:
We have a development RedShift cluster (Version 1.0.37758) with on node (dc2.large). Recently, we keep getting out of memory errors when running ONE query. This select has UNPIVOT in it... the two errors we keep getting are:

1.ERROR: Out Of Memory: Detail: ----------------------------------------------- error: Out Of Memory: code: 1004 context: alloc(169776,MtRowset) query: 3422739 location: alloc.cpp:493 process: padbmaster [pid=15449] ----------------------------------------------- 
[ErrorId: 1-626a8968-5f716e5b710716e62422db2e]

2. ERROR: Out of Memory Detail: ----------------------------------------------- error: Out of Memory code: 1020 context: From OomGuard query: 0 location: oom_guard.cpp:392 process: oom_guard [pid=14039]

The first error with code 1004 are more often than the second one..

Any suggestions on what the root causes are  and how to fix them?

Thanks  alot in advance

Li

Accepted Answer

Thanks, yes, we rewrote our query and worked around this... thanks for your information.

Answer

How is your WLM set up? If it is Manual WLM set up, you need to see how much memory allocation is given for queue in which your query will be running 
If you have set up a higher concurrency per WLM queue each slot gets less memory.
You can allocate higher memory for specific query by  Allocating a higher query_slot_count to your query: 
For example  set wlm_query_slot_count to 3; to allocate 3 query slots

Alternatively you can simplify your query with additional interim processing tables and select columns which are required.  
              You can also refer Auto WLM- https://docs.aws.amazon.com/redshift/latest/dg/automatic-wlm.html where memory management is dynamic

Redshift out of memory when running query

Relevant content