The queries in Amazon Redshift vary significantly in their execution cost, time, and size of the result set, which is different from Online Transaction Processing (OLTP), where most queries are uniform in size and cost for all jobs. Once you start utilizing Amazon Redshift in a production environment, you may run into situations where an Ad-hoc query that takes too long to complete might block some critical reports.
What do you do in these situations? A considerable advantage of Amazon Redshift is that it implements a mechanism that allows you to manage the resources of your cluster by creating priorities queues with specific resources associated with them and then attaching queries to the priorities queues. In this manner, you can prioritize the execution of your queries based on their importance and our available resources. This can be achieved using the special Amazon Redshift Workload Management.
This post walkthroughs the Redshift Workload Management, its working, and steps. You will also gain a basic understanding of different Queues and how to configure them.
Table of Contents
- Understanding Amazon Redshift Workload Management
- Understanding Working of Redshift Workload Management
- Configure Amazon Redshift Workload Management
- Customize the Redshift Workload Management Settings
- Points to Consider for Amazon Redshift Workload Management
Understanding Amazon Redshift Workload Management
Amazon Redshift Workload Management (WLM) provides users with a flexible way to adjust the priority of workloads so short queries don’t get stuck behind longer queries.
Querying Amazon Redshift more often will slow it down. You can define queues in Amazon Redshift Workload Management, lists of queries waiting to run.
You can control the maximum number of concurrently running queries (the default number is five). Jobs that run for an extended time in the background can be queued separately from shorter, latency-sensitive jobs. To avoid the queue from performing slowly, long-running jobs can be run at intervals (such as one at a time).
The default WLM configurations are attached to a redshift cluster when it is created. There are two queues by default. There is a concurrency of 1 for superusers in the first queue, and in the second queue, there is a concurrency of 5 for all other users. In other words, users can run five queries at once.
Thus, if, for example, this queue contains five long-running queries, short queries will have to wait until these queries are finished. To address this issue, we use WLM, which allows us to create separate queues for short and long queries. Furthermore, WLM will divide the cluster’s overall memory between queues.
Basically, the Amazon Redshift Workload Management interface can be used to configure Queue Priority, Memory Allocation, and Concurrency.
Understanding Working of Redshift Workload Management
Every cluster that you create in Amazon Redshift has an associated parameter group. Parameter groups are groups of parameters applied to all the databases in the cluster you make. Such parameters define settings like query timeouts and database date style. A parameter group is configured by Redshift Workload Management (WLM).
Understanding Query Queues
Queries in Amazon Redshift are routed into Query Queues. They are placed into one or more query queues. The memory allocated to each query queue is a portion of the cluster’s total memory. This memory is split among the queries in a queue. Each query queue can be configured with Redshift Workload Management properties that determine how memory is allocated, how queries are routed at runtime, and when to cancel long-running queries. There is also a parameter, wlm_query_slot_count, which you can use to temporarily enable queries to use more memory by allocating multiple slots.
A Redshift Query Queue is configured by default as follows:
● One Superuser Queue. This queue can be used to cancel a user’s long-running query or add users to the database. Only superusers can use the Superuser queue, which cannot be configured. This queue should only be used for queries that affect the system or troubleshooting. It should not be used for routine queries.
● One Default User Queue. A Default User queue can initially handle five concurrent queries. The Default queue enables you to customize concurrency, timeout, and memory allocation properties. However, the Default User queue does not declare any user groups or query groups. Default queues must be at the end of your WLM configuration. In the default queue, queries that aren’t routed to any other queue are processed.
In addition to the default Redshift Workload Management configuration, you can add 8 additional Query Queues.
Simplify Redshift ETL and Data Analysis with Hevo’s No-code Data Pipeline
Hevo Data, a No-code Data Pipeline, helps load data from any data source such as Databases, SaaS applications, Cloud Storage, SDK,s, and Streaming Services and simplifies the ETL process. It supports 100+ Data Sources including 40+ Free Sources. It loads the data onto the desired Data Warehouse such as Amazon Redshift and transforms it into an analysis-ready form without having to write a single line of code.
Hevo’s fault-tolerant and scalable architecture ensures that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data.GET STARTED WITH HEVO FOR FREE
Check out why Hevo is the Best:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled securely and consistently with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Simplify your ETL & Data Analysis with Hevo today!SIGN UP HERE FOR A 14-DAY FREE TRIAL!
Configure Amazon Redshift Workload Management
The user has complete control over all the parameters above. You can do this by:
- The Amazon Redshift Management Console
- The Amazon Redshift CLI
Understanding how Redshift allocates queries to queues is essential before configuring Workload Management. This flowchart illustrates how queries are assigned to queues very clearly. As you work with a large number of queries on your cluster, you will need to understand the rules well to troubleshoot your workload.
Queries are assigned to specific queues by either setting up a user group or a query group for your query. To assign something, you need to use the SET command, and you can see some examples of queries here.
In Redshift Workload Management Configuration, there are two properties: static and dynamic. Static properties require you to reboot your cluster. There is one main difference between the dynamic and static properties.
- Static Properties
- User Groups
- User Group wildcard
- Query Groups
- Query group wildcard
- Dynamic Properties
- Percent of memory used
● User Groups: Queries run by user group members are routed to the queue associated with their group when they run queries in the database.
● Query Groups: Commas should separate the query groups. Each query group’s members run queries in the database using a queue assigned to their query group.
● Concurrency Level: The queue’s WLM query slots count, or concurrency level, is reached when the number of queries in the queue reaches the maximum number of slots defined for that queue. The subsequent queries then wait in line.
It is possible to configure each queue with up to 50 query slots. There is a maximum of 50 WLM query slots per queue for all user-defined queues. While the limit includes the default queue, it does not have the reserved Superuser queue. There is a difference between the WLM concurrency level and the number of concurrent connections each user can make to a cluster. Users can connect to a cluster concurrently up to 500 times.
● WLM Memory Percent to Use: Approximately how much memory should be allocated to the queue. The percentage of memory you specified for at least one queue must also be set for all other queues, up to 100 percent. As long as you have less than 100 percent memory allocation across all queues, the service manages the unallocated memory. This unallocated memory can be temporarily allocated to a queue requesting more memory to process requests.
● WLM Timeout: WLM timeouts are not applied to queries that have reached a returning state. The STV_WLM_QUERY_STATE system table shows the current state of a query. ANALYZE and VACUUM is not subject to WLM timeout when copying statements or performing maintenance operations.
As with the statement_timeout configuration parameter, WLM timeout applies to a specific queue in the WLM configuration instead of the entire cluster as with the statement_timeout parameter. In addition to statement_timeout, WLM timeout (max_execution_time) is used if statement_timeout is also specified.
● WLM Query Queue Hopping: Queries can be hopped by a WLM timeout or a query monitoring rule (QMR) hop action. According to the rules for WLM queue assignment, when a query is hopped, it tries to route it to the next matching queue. The query is canceled if it does not match any other queue definition. The query is not assigned to the default queue.
Based on the WLM timeout, the following table summarizes the behavior of different types of queries.
Using a QMR hop action, the following table summarizes the behavior of different types of queries.
- Query Monitoring Rules: A query monitoring rule specifies metrics-based performance boundaries for WLM queues and what action should be taken if a query crosses those boundaries. In light of the above, it is recommended to set your query and user groups up initially and not change them frequently. Having to restart your cluster will cause downtime for your services, taking time.
Customize the Redshift Workload Management Settings
There are two basic modes in the Redshift Workload Management, automatic and manual. Redshift’s automatic mode allows some tuning functionality, like changing the priority for different queues, but it tries to automate the processing characteristics as much as possible for workloads. Using manual mode, you can control workloads in great detail.
To fine-tune workload settings, you need a good understanding of Redshift performance characteristics and the factors affecting them and a knowledge of the specific workloads that need to be tuned.
It is also essential to keep in mind that there are two types of Redshift Workload Management configuration changes, dynamic and static. The AWS documentation explains which changes are dynamic and which are static. To take effect, static changes, such as switching between automatic and manual WLM modes, require a cluster reboot.
Redshift Workload Management console, command-line interface, or API can be used to reboot clusters. Additionally, it might occur due to scheduled maintenance for Redshift during the maintenance window.
Points to Consider for Amazon Redshift Workload Management
When configuring the Amazon Redshift Workload Management interface, consider the following guidelines and rules:
- Ensure that the jobs have the resources to complete the queries and separate the data import jobs from the analysis queries.
- Default concurrency for non-ETL queries should be set at five, and concurrent analytic query jobs should be allocated sufficient memory.
- The queues for batch jobs, ad-hoc, and interactive queries must be separated.
- ETL jobs must be queued separately.
- ETL jobs that are short-lived and long-lived need to be queued separately. When working on long-running ETL jobs, specify a smaller memory size, and when working on short-running ETL jobs, smaller memory size can be specified.
This post helped you gain a basic understanding of Query Queues, working of Redshift Workload Management, and how you can configure it. However, you might have seen that extracting data from a diverse set of sources and loading it to your Amazon Redshift can become complex and challenging. To help you simplify these tasks, a simple alternative like Hevo can save your day.
Hevo Data is a No-Code Data Pipeline that offers a faster way to move data from 100+ Data Sources including 40+ Free Sources, into your Amazon Redshift to be visualized in a BI tool. You can use Hevo Pipelines to replicate the data from your desired source to the Destination system. Hevo is fully automated and hence does not require you to code.VISIT OUR WEBSITE TO EXPLORE HEVO
Want to take Hevo for a spin?
Share your experience of working with Redshift Workload Management with us in the comment sections below.