Efficient query performance in Amazon Redshift often comes down to how well you manage workload concurrency. Redshift's Workload Management (WLM) queues enable you to control how queries share resources, helping avoid bottlenecks during peak loads. Properly configuring WLM queues ensures critical queries get the resources they need while maximizing cluster throughput.
This post will guide you through the basics of WLM queue configuration and share actionable steps to optimize query execution and concurrency for your Redshift workloads.
Automatic WLM vs. Manual WLM
Before diving into manual configuration, it's important to note that Amazon recommends Automatic WLM as the default approach for most workloads. With Automatic WLM, Redshift dynamically determines resource utilization as queries arrive and adjusts concurrency and memory allocation on the fly — without requiring manual tuning. It maximizes total throughput and handles unpredictable or changing workloads more efficiently than static manual configuration.
Manual WLM is best suited for scenarios where your workload patterns are predictable, you need to guarantee multiple workload types run simultaneously with fixed resource allocations, or you need to throttle certain query types at specific times of day. If you proceed with manual WLM, be prepared to monitor and tune it regularly as workloads evolve.
Step-by-Step Guide (Manual WLM)
1. Understand Redshift WLM Queues and Slots
WLM enables you to define queues with resource slots. Each query consumes one or more slots depending on queue configuration. Slots allocate memory and concurrency allowing multiple queries to run simultaneously without resource contention.
Learn more about WLM fundamentals here: Redshift WLM Documentation.
2. Analyze Your Query Workload
Identify query types and their resource demands. Separate lengthy ETL or analytical queries from short, high-priority reporting queries. Segmenting workloads allows for assigning appropriate queue configurations matching query resource profiles.
3. Create or Modify Queues Based on Workload Types
For example, create a "high_priority" queue with fewer concurrency slots but higher memory allocation for fast-running reports, and a "default" queue for ad-hoc or batch queries running with more concurrency but less memory per query.
4. Adjust Slots and Memory Allocations
Use the AWS Console or Redshift parameter groups to configure slots via the wlm_json_configuration parameter. An example config snippet:
-- Example JSON for WLM queue definition in parameter group:
[
{
"query_group": "high_priority",
"concurrency": 5,
"memory_percent_to_use": 30
},
{
"query_group": "default",
"concurrency": 10,
"memory_percent_to_use": 20
}
]
This example balances memory and concurrency to prioritize faster queries effectively.
5. Monitor and Tune Using System Views
Query views like STV_WLM_QUERY_STATE and STL_WLM_QUERY to analyze queue usage, wait times, and query runtimes. Use this insight to iteratively adjust concurrency and memory settings.
Conclusion
Optimizing Redshift WLM queues is key to balancing concurrency and performance in mixed workloads. For most users, Automatic WLM is the recommended starting point. If manual WLM better fits your use case, classifying queries, assigning queues based on priority, and tuning memory and slot allocations will ensure efficient resource use and a better experience for high-priority queries.
Regular monitoring and tuning with Redshift system views complete the feedback loop for continuous improvement.
Official Documentation for more details: