Redshift Queues

Question

I'm brand new to RedShift and am trying to debug why our systems are running very slowly. I've noticed that we have around 50 users and hundreds of daily reports running, all of which take substantial execution time. I believe that part of the problem my reside in the problem with too many concurrent queries running and slowing the system.

I would like to optimize the queuing system so that certain users/applications are give priority, while low priority reports that are less seldom run will go into a different queue.

How can I view the current queues and setup new ones? Further, is it possible to restrict individual users to a particular queue so that every query that they run will be in that particular queue? Thanks.

Answer 1

Amazon provides WLM (Work Load Management) specifically for this task.

This allows you to allocate memory and other resources like setting concurrency, setting timeout values etc. If you have access to AWS Redshift Console, you can easily assign a parameter group to a cluster and then browse through Parameter Groups > WLM and set below WLM parameters for that particular cluster -

Concurrency - Max number of queries which can run concurrently.

User Groups - You need to create user group like (report_gr, etl_gr, default_gr etc) and assign users to those groups accordingly.

Timeout - Timeout value for that user group's queries

Memory - Percentage of memory allocated for that user group's queries.

Answer 2

The original question comes from 2016, and meanwhile AWS has added a lot more knobs to fine-tune your workloads.

Three components:

1. Workload Management
2. Short Query Acceleration
3. Concurrency Scaling

Workload Management Workload Management allows to separate your different users from each other. See image below. We recommend separating your users by the type of SQL command they run, as they share similar memory and workload patterns. We recommend 4 queues:

• default
• loads
• transforms
• ad-hoc

I've written a detailed post on how to configure your WLM in 4 steps. The docs recommend not going over 15 slots total, but reality is that you can go all the way to 50. That assumes you have sufficient memory though so queries don't fall back to disk.

Short Query Acceleration AWS turns on SQA by default now. They predict the length of a query and route the short ones to a special queue.

Concurrency Scaling Concurrency Scaling for Amazon Redshift gives Redshift clusters additional capacity to handle bursts in query load. It works by off-loading queries to new, “parallel” clusters in the background. Queries are routed based on WLM configuration and rules.

You have to turn Concurrency Scaling on in the console, and AWS claims that it's free for 97% of Redshift users. We ran an internal test for Concurrency Scaling, and found that scaling may mitigate queue times during bursts in queries.