Redshift out of memory when running query


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]

  1. 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


asked 2 years ago4265 views
2 Answers
Accepted Answer

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

answered 2 years ago

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- where memory management is dynamic
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions