Amazon Redshift - The difference between Query Slots, Concurrency and Queues?

Question

We are building a business intelligence system, and we have a huge PostgreSQL Database (DB) where we make all the info processing, and a Redshift Data Warehouse (DWH) where we store the data and execute queries.

The backend is built in Java Server Faces (JSF), and before, the queries were made lineal. Some views were taking more than a minute to finish queries and load the info in screen, so we decided to use threads in Java and make the queries asynchronous.

So we have prepared the necessary queries for each view to be run parallel from our EC2 app machine, to our Redshift DWH, and run the threads but views still take long to load, sometimes even longer.

We found in the documentation:

http://docs.aws.amazon.com/redshift/latest/dg/cm-c-executing-queries.html

http://docs.aws.amazon.com/redshift/latest/dg/c_troubleshooting_query_performance.html

http://docs.aws.amazon.com/redshift/latest/dg/r_wlm_query_slot_count.html

That redshift by default receive 5 queries at same time, but that is a setting we can change.

There are 3 main things to take into account: query slots, concurrency and queues. We have understood this:

A queue is like a thread in Java. A query arrives and is designated to the "less loaded" queue, and it waits for its turn to be resolved. We can have as many queues as we like. A queue has some memory allocated (we guess divided equally?) In a queue we can assign user groups or queries groups. But in short-term, that's a lot of classification work in our queries we can't do right now.

A concurrency is the amount of queries that a queue can run in parallel. By default is 5.

And a query slot is the amount of memory a query can use. It's related to concurrency as we understood it. The more concurrency a queue has, the less memory in each query slot it has.

We have tried to have 3 queues, each one with concurrecy 5, but performance still slow.

So, if we have understood correctly, having that some viewes make up to 25-28 queries, and the total amount of loading time is around 60s, how can we leave the settings the queries can be resolved faster?

Answer 1

I think your understanding of query queues is a little off.

A queue is like a thread in Java. A query arrives and is designated to the "less loaded" queue, and it waits for its turn to be resolved.

The decision of where to put a query is independent of how busy a queue is; queries are allocated based on the rules you've set up: http://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-queue-assignment-rules.html

We can have as many queues as we like.

Not quite, check out http://docs.aws.amazon.com/redshift/latest/dg/cm-c-defining-query-queues.html. The relevant line is

The maximum total concurrency level for all user-defined queues, not including the reserved Superuser queue, is 50.

So the concurrency is definitely limited, but that's a reasonable restriction because each concurrency slot reserves some of the cluster's resources.

A queue has some memory allocated (we guess divided equally?)

By default memory is split equally among queues, but you can assign memory at the granularity of 1% of cluster memory. See the above link about defining query queues.

In a queue we can assign user groups or queries groups. But in short-term, that's a lot of classification work in our queries we can't do right now.

Creating user groups and setting query groups is actually pretty simple, see http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_GROUP.html and http://docs.aws.amazon.com/redshift/latest/dg/r_query_group.html.

If you didn't set up user groups or query groups, then my guess is that you didn't see any improvement after adding the extra queues because all the queries still ran in a single query queue. (In fact, the extra queues would simply take resources from the queue running your queries.) Since all your queries are running from the same user and for the same purpose, it makes sense that they should all resolve to the same query queue. A better solution might be to have only a single queue, but increase the concurrency level. (Keep in mind that a query queue's resources are divided equally among all concurrency slots, even if they're not being used, so a concurrency level of 50 means no query gets more than 2% of the total resources.)

All that being said, redshift is a higher-latency system than many other database solutions. If you're only looking to run lots of small queries, then it might not be the best fit for your problem.

Answer 2

It sounds to me like you're using Redshift in an OLTP model, vs. its original purpose as an OLAP engine.

From your question above, I can spot two workloads (or "concerns"):

• data loads into the cluster from Postgres
• queries on the data in the Redshift cluster

You don't seem to be running any transformations in Redshift, i.e. once you load the data into the cluster, data stays the way it is and you don't manipulate it anymore.

I think you need to consider three things if you want faster queries:

1. Separate your concerns into two different schemas. One for your raw data loads, and one for your ad-hoc queries. If your users query the same tables as you load data into, you will run into table locks.
2. Set up your workload management so that you separate your loads from the ad-hoc queries. That way you can assign the correct amount of memory and concurrency to each queue. Otherwise you will over-provision your cluster with slots and your queries will starve for memory, fall back to disk, slowing the entire cluster down.
3. Rather than querying your cluster directly, consider using a caching layer with Amazon RDS and dblink. That way you can use Redshift in the OLTP model. See our post "Have you Postgres Cake and Eat it Too". You will see faster queries with that.

I've written a detailed post on how we configure Redshift for performance, with a picture that tells a thousand words.