Facing memory allocation issue in Redshift.

Question

Our Redshift WLM is setup on Auto configuration, But when we are executing the same query directly on redshift, it is executing.

java.sql.SQLException: [Amazon](500310) Invalid operation: Cannot allocate memory
Details: -----------------------------------------------
  error:  Cannot allocate memory
  code:      12
  context:   local_compile_pid != -1 Failed to fork process.
  query:     729086732
  location:  cg_codecompiler.cpp:1263
  process:   padbmaster [pid=1073832368]
  -----------------------------------------------;
        at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.handleErrorResponse(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.handleMessage(Unknown Source)
        at com.amazon.jdbc.communications.InboundMessagesPipeline.getNextMessageOfClass(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.doMoveToNextClass(Unknown Source)
        at com.amazon.redshift.client.PGMessagingContext.getErrorResponse(Unknown Source)
        at com.amazon.redshift.client.PGClient.handleErrorsScenario2ForPrepareExecution(Unknown Source)
        at com.amazon.redshift.client.PGClient.handleErrorsPrepareExecute(Unknown Source)
        at com.amazon.redshift.dataengine.CallablePreparedOrAtomicExecuteTask.call(Unknown Source)
        at com.amazon.redshift.dataengine.CallablePreparedOrAtomicExecuteTask.call(Unknown Source)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
Caused by: com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: Cannot allocate memory
Details: -----------------------------------------------
  error:  Cannot allocate memory
  code:      12
  context:   local_compile_pid != -1 Failed to fork process.
  query:     729086732
  location:  cg_codecompiler.cpp:1263
  process:   padbmaster [pid=1073832368]
  -----------------------------------------------;
        ... 13 common frames omitted

Accepted Answer

The error you're encountering is related to memory allocation in Amazon Redshift. The error message "**Cannot allocate memory**" indicates that Redshift is unable to allocate the required memory for the query execution. This can happen for a variety of reasons, and the error details provide some context about the issue.

Here are some steps and considerations to troubleshoot and resolve the memory allocation issue in Redshift:

**Query Complexity:**
- Ensure that the query you're running isn't overly complex. Complex queries with multiple joins, subqueries, or large datasets can consume a significant amount of memory.
- Try simplifying the query or breaking it into smaller parts to see if it runs successfully.

**WLM (Workload Management) Configuration:**
- Even though you mentioned that your WLM is set to Auto configuration, it's worth checking the memory settings. Auto WLM dynamically manages memory and concurrency, but in some cases, manual tuning might be beneficial.
- Consider increasing the memory percentage for the specific queue where the query is running.
- Adjust the concurrency level if needed. A higher concurrency might lead to memory contention among queries.

**Concurrent Queries:**
- Check if there are other heavy queries running concurrently that might be consuming a significant portion of the available memory.
- Consider scheduling heavy queries during off-peak hours or using reserved WLM queues for them.

**Cluster Size and Node Type:**
- Ensure that your Redshift cluster has adequate resources (CPU, memory) for the workload. You might need to resize the cluster or choose a different node type with more memory.
- Consider using dense compute (DC) node types if memory is a frequent bottleneck.

**Disk Space:**
- Ensure that there's enough disk space available. When the disk gets full, it can impact the performance and memory allocation.

**Vacuum and Analyze:**
- Regularly run the VACUUM command to reclaim space from deleted rows and ensure that data is sorted efficiently.
- Run the ANALYZE command to update statistics, which helps the query planner make optimization decisions.

**Review Error Details:**
- The error details mention cg_codecompiler.cpp and Failed to fork process. This suggests an issue at the compilation phase of the query. It might be beneficial to review the specific query and see if any functions or operations can be optimized.

Lastly, always monitor the performance and health of your Redshift cluster using Amazon CloudWatch metrics. This can give you insights into memory usage, query performance, and potential bottlenecks.

Facing memory allocation issue in Redshift.

Relevant content