With Amazon Redshift, you’ll be able to run a fancy mixture of workloads in your knowledge warehouse, equivalent to frequent knowledge hundreds operating alongside business-critical dashboard queries and complicated transformation jobs. We additionally see increasingly knowledge science and machine studying (ML) workloads. Every workload kind has completely different useful resource wants and completely different service-level agreements (SLAs).

Amazon Redshift workload administration (WLM) helps you maximize question throughput and get constant efficiency for essentially the most demanding analytics workloads by optimally utilizing the sources of your present knowledge warehouse.

In Amazon Redshift, you implement WLM to outline the variety of question queues which can be obtainable and the way queries are routed to these queues for processing. WLM queues are configured primarily based on Redshift person teams, person roles, or question teams. When customers belonging to a person group or position run queries within the database, their queries are routed to a queue as depicted within the following flowchart.

Function-based entry management (RBAC) is a brand new enhancement that helps you simplify the administration of safety privileges in Amazon Redshift. You need to use RBAC to manage end-user entry to knowledge at a broad or granular degree primarily based on their job position. Now we have launched assist for Redshift roles in WLM queues, you’ll now discover Person roles together with Person teams and Question teams as question routing mechanism.

This submit gives examples of analytics workloads for an enterprise, and shares frequent challenges and methods to mitigate these challenges utilizing WLM. We information you thru frequent WLM patterns and the way they are often related along with your knowledge warehouse configurations. We additionally present the best way to assign person roles to WLM queues and the best way to use WLM question insights to optimize configuration.

Use case overview

ExampleCorp is an enterprise utilizing Amazon Redshift to modernize its knowledge platform and analytics. They’ve number of workloads with customers from numerous departments and personas. The service-level efficiency necessities fluctuate by the character of the workload and person personas accessing the datasets. ExampleCorp want to handle sources and priorities on Amazon Redshift utilizing WLM queues. For this multitenant structure by division, ExampleCorp can obtain learn/write isolation utilizing the Amazon Redshift knowledge sharing characteristic and meet its unpredictable compute scaling necessities utilizing concurrency scaling.

The next determine illustrates the person personas and entry in ExampleCorp.

ExampleCorp has a number of Redshift clusters. For this submit, we deal with the next:

• Enterprise knowledge warehouse (EDW) platform – This has all write workloads, together with a number of the functions operating reads through the Redshift Information API. The enterprise standardized knowledge from the EDW cluster is accessed by a number of shopper clusters utilizing the Redshift knowledge sharing characteristic to run downstream studies, dashboards, and different analytics workloads.
• Advertising knowledge mart – This has predictable extract, rework, and cargo (ETL) and enterprise intelligence (BI) workloads at particular instances of day. The cluster admin understands the precise useful resource necessities by workload kind.
• Auditor knowledge mart – That is solely used for a couple of hours a day to run scheduled studies.

ExampleCorp want to higher handle their workloads utilizing WLM.

Answer overview

As we mentioned within the earlier part, ExampleCorp has a number of Redshift knowledge warehouses: one enterprise knowledge warehouse and two downstream Redshift knowledge warehouses. Every knowledge warehouse has completely different workloads, SLAs, and concurrency necessities.

A database administrator (DBA) will implement acceptable WLM methods on every Redshift knowledge warehouse primarily based on their use case. For this submit, we use the next examples:

• The enterprise knowledge warehouse demonstrates Auto WLM with question priorities
• The advertising and marketing knowledge mart cluster demonstrates guide WLM
• The auditors group makes use of their knowledge mart sometimes for sporadic workloads; they use Amazon Redshift Serverless, which doesn’t require workload administration

The next diagram illustrates the answer structure.

Stipulations

Earlier than starting this resolution, you want the next:

• An AWS account
• Administrative entry to Amazon Redshift

Let’s begin by understanding some foundational ideas earlier than fixing the issue assertion for ExampleCorp. First, how to decide on between auto vs. guide WLM.

Auto vs. guide WLM

Amazon Redshift WLM lets you flexibly handle priorities inside workloads to satisfy your SLAs. Amazon Redshift helps Auto WLM or guide WLM in your provisioned Redshift knowledge warehouse. The next diagram illustrates queues for every possibility.

Auto WLM determines the quantity of sources that queries want and adjusts the concurrency primarily based on the workload. When queries requiring massive quantities of sources are within the system (for instance, hash joins between massive tables), the concurrency is decrease. For added data, check with Implementing automated WLM. You need to use Auto WLM when your workload is very unpredictable.

With guide WLM, you handle question concurrency and reminiscence allocation, versus auto WLM, the place it’s managed by Amazon Redshift robotically. You configure separate WLM queues for various workloads like ETL, BI, and advert hoc and customise useful resource allocation. For added data, check with Tutorial: Configuring guide workload administration (WLM) queues.

Use guide when When your workload sample is predictable or if you have to throttle sure kinds of queries relying on the time of day, equivalent to throttle down ingestion throughout enterprise hours. If you have to assure a number of workloads are in a position to run on the similar time, you’ll be able to outline slots for every workload.

Now that you’ve got chosen automated or guide WLM, let’s discover WLM parameters and properties.

Static vs. dynamic properties

The WLM configuration for a Redshift knowledge warehouse is about utilizing a parameter group below the database configuration properties.

The parameter group WLM settings are both dynamic or static. You possibly can apply dynamic properties to the database with out a cluster reboot, however static properties require a cluster reboot for adjustments to take impact. The next desk summarizes the static vs. dynamic necessities for various WLM properties.

Observe the next:

• The parameter group parameters and WLM swap from guide to auto or vice versa are static properties, and subsequently require a cluster reboot.
• For the WLM properties Concurrency on most important, Share of reminiscence to make use of, and Timeout, that are dynamic for guide WLM, the change solely applies to new queries submitted after the worth has modified and never for at the moment operating queries.
• The question monitoring guidelines, which we talk about later on this submit, are dynamic and don’t require a cluster reboot.

Within the subsequent part, we talk about the idea of service class, that means which queue does the question get submitted to and why.

Service class

Whether or not you employ Auto or guide WLM, the person queries submitted go to the meant WLM queue through one of many following mechanisms:

• User_Groups – The WLM queue immediately maps to Redshift teams that would seem within the pg_group desk.
• Query_Groups – Queue task relies on the query_group label. For instance, a dashboard submitted from the identical reporting person can have separate priorities by designation or division.
• User_Roles (newest addition) – The queue is assigned primarily based on the Redshift roles.

WLM queues from a metadata perspective are outlined as service class configuration. The next desk lists frequent service class identifiers in your reference.

The WLM queues you outline primarily based on user_groups, query_groups, or user_roles fall in service class ID 6–13 for guide WLM and repair class id 100–107 for automated WLM.

Utilizing Query_group, you’ll be able to power a question to go to service class 5 and run within the superuser queue (supplied you’re a licensed superuser) as proven within the following code:

set query_group to 'superuser';
analyze table_xyz;
vacuum full table_xyz;
reset query_group;

For extra particulars on the best way to assign a question to a selected service class, check with Assigning queries to queues.

The quick question acceleration (SQA) queue (service class 14) prioritizes short-running queries forward of longer-running queries. Should you allow SQA, you’ll be able to cut back WLM queues which can be devoted to operating quick queries. As well as, long-running queries don’t have to take care of quick queries for slots in a queue, so you’ll be able to configure your WLM queues to make use of fewer question slots (a time period used for obtainable concurrency). Amazon Redshift makes use of an ML algorithm to research every eligible question and predict the question’s runtime. Auto WLM dynamically assigns a worth for the SQA most runtime primarily based on evaluation of your cluster’s workload. Alternatively, you’ll be able to specify a set worth of 1–20 seconds when utilizing guide WLM.

SQA is enabled by default within the default parameter group and for all new parameter teams. SQA can have a most concurrency of six queries.

Now that you just perceive how queries get submitted to a service class, it’s vital to grasp methods to keep away from runaway queries and provoke an motion for an unintended occasion.

Question monitoring guidelines

You need to use Amazon Redshift question monitoring guidelines (QMRs) to set metrics-based efficiency boundaries for WLM queues and specify what motion to take when a question goes past these boundaries.

The Redshift cluster robotically collects question monitoring metrics. You possibly can question the system view SVL_QUERY_METRICS_SUMMARY as an support to find out threshold values for outlining the QMR. Then create the QMR primarily based on following attributes:

• Question runtime, in seconds
• Question return row depend
• The CPU time for a SQL assertion

For an entire listing of QMRs, check with WLM question monitoring guidelines.

Create pattern parameter teams

For our ExampleCorp use case, we reveal automated and guide WLM for a provisioned Redshift knowledge warehouse and share a serverless perspective of WLM.

The next AWS CloudFormation template gives an automatic approach to create pattern parameter teams that you may connect to your Redshift knowledge warehouse for workload administration.

Enterprise knowledge warehouse Redshift cluster utilizing automated WLM

For the EDW cluster, we use Auto WLM. To configure the service class, we have a look at all three choices: user_roles, user_groups, and query_groups.

Right here’s a glimpse of how this may be arrange in WLM queues after which utilized in your queries.

On the Amazon Redshift console, below Configurations within the navigation pane, select Workload Administration. You possibly can create a brand new parameter group or modify an present one created by you. Choose the parameter group to edit its queues. There’s all the time a default queue (the final one in case of a number of queues outlined), which is a catch-all for queries that don’t get routed to any particular queue.

Person roles in WLM

With the introduction of person roles in WLM queues, now you’ll be able to handle your workload by including completely different roles to completely different queues. This will help you prioritize the queries primarily based on the roles a person has. When a person runs a question, WLM will examine if this person’s roles have been added in any workload queues and assign the question to the primary matching queue. So as to add roles into the WLM queue, you’ll be able to go to the WLM web page, create or modify an present workload queue, add a person’s roles within the queue, and choose Matching wildcards so as to add roles that get matched as wildcards.

For extra details about the best way to convert from teams to roles, check with Amazon Redshift Roles (RBAC), which walks you thru a saved process to transform teams to roles.

Within the following instance, now we have created the WLM queue EDW_Admins, which makes use of edw_admin_role created in Amazon Redshift to submit the workloads on this queue. The EDW_Admins queue is created with a excessive precedence and automated concurrency scaling mode.

Person teams

Teams are collections of customers who’re all granted permissions related to the group. You need to use teams to simplify permission administration by granting privileges only one time. If the members of a gaggle get added or eliminated, you don’t have to handle them at a person degree. For instance, you’ll be able to create completely different teams for gross sales, administration, and assist and provides the customers in every group the suitable entry to the information they want for his or her work.

You possibly can grant or revoke permissions on the person group degree, and people adjustments will apply to all members of the group.

ETL, knowledge analysts, or BI or resolution assist methods can use person teams to higher handle and isolate their workloads. For our instance, ETL WLM queue queries can be run with the person group etl. The info analyst group (BI) WLM queue queries will run utilizing the bi person group.

Select Add queue so as to add a brand new queue that you’ll use for user_groups, on this case ETL. If you want these to be matched as wildcards (strings containing these key phrases), choose Matching wildcards. You possibly can customise different choices like question precedence and concurrency scaling, defined earlier on this submit. Select Save to finish this queue setup.

Within the following instance, now we have created two completely different WLM queues for ETL and BI. The ETL queue has a excessive precedence and concurrency scaling mode is off, whereas the BI queue has a low precedence and concurrency scaling mode is off.

Use the next code to create a gaggle with a number of customers:

-- Instance of create group with a number of customers
create group ETL with person etl_user1, etl_user2;
Create group BI with person bi_user1, bi_user2;

Question teams

Query_Groups are labels used for queries which can be run inside the similar session. Consider these as tags that you could be need to use to determine queries for a uniquely identifiable use case. In our instance use case, the information analysts or BI or resolution assist methods can use query_groups to higher handle and isolate their workloads. For our instance, weekly enterprise studies can run with the query_group label wbr. Queries from the advertising and marketing division may be run with a query_group of selling.

The advantage of utilizing query_groups is that you should utilize it to constrain outcomes from the STL_QUERY and STV_INFLIGHT tables and the SVL_QLOG view. You possibly can apply a separate label to each question that you just run to uniquely determine queries with out having to lookup their IDs.

Select Add queue so as to add a brand new queue that you’ll use for query_groups, on this case wbr or weekly_business_report. If you want these to be matched as wildcards (strings containing these key phrases), choose Matching wildcards. You possibly can customise different choices like question precedence and concurrency scaling choices as defined earlier on this submit. Select Save to avoid wasting this queue setup.

Now let’s see how one can power a question to make use of the query_groups queue simply created.

You possibly can assign a question to a queue at runtime by assigning your question to the suitable question group. Use the SET command to start a question group:

SET query_group TO wbr;
-- or
SET query_group TO weekly_business_report;

Queries following the SET command would go to the WLM queue Query_Group_WBR till you both reset the question group or finish your present login session. For details about setting and resetting server configuration parameter, see SET and RESET, respectively.

The question group labels that you just specify should be included within the present WLM configuration; in any other case, the SET query_group command has no impact on question queues.

For extra query_groups examples, check with WLM queue task guidelines.

Advertising Redshift cluster utilizing guide WLM

Increasing on the advertising and marketing Redshift cluster use case of ExampleCorp, this cluster serves two kinds of workloads:

• Operating ETL for a interval of two hours between 7:00 AM to 9:00 AM
• Operating BI studies and dashboards for the remaining time in the course of the day

When you may have such a readability within the workloads, and your scope of utilization is customizable by design, you might need to think about using guide WLM, the place you’ll be able to management the reminiscence and concurrency useful resource allocation. Auto WLM will nonetheless be relevant, however guide WLM may also be a alternative.

Let’s arrange guide WLM on this case, with two WLM queues: ETL and BI.

To greatest make the most of the sources, we use an AWS Command Line Interface (AWS CLI) command initially of our ETL, which is able to make our WLM queues ETL-friendly, offering greater concurrency to the ETL queue. On the finish of our ETL, we use an AWS CLI command to vary the WLM queue to have BI-friendly useful resource settings. Modifying the WLM queues doesn’t require a reboot of your cluster; nevertheless, modifying the parameters or parameter group does.

Should you have been to make use of Auto WLM, this might have been achieved by dynamically altering the question precedence of the ETL and BI queues.

By default, once you select Create, the WLM created can be Auto WLM. You possibly can swap to guide WLM by selecting Change WLM mode. After switching WLM mode, select Edit workload queues.

This may open the Modify workload queues web page, the place you’ll be able to create your ETL and BI WLM queues.

After you add your ETL and BI queues, select Save. You need to have configured the next:

• An ETL queue with 60% reminiscence allocation and question concurrency of 9
• A BI queue with 30% reminiscence allocation and question concurrency of 4
• A default queue with 10% reminiscence allocation and question concurrency of two

Your WLM queues ought to seem with settings as proven within the following screenshot.

Enterprises might desire to finish these steps in an automatic approach. For the advertising and marketing knowledge mart use case, the ETL begins at 7:00 AM. An excellent begin to the ETL move can be to have a job that makes your WLM settings ETL queue pleasant. Right here’s how you’ll modify concurrency and reminiscence (each dynamic properties in guide WLM queues) to an ETL-friendly configuration:

aws redshift --region 'us-east-1' modify-cluster-parameter-group --parameter-group-name manual-wlm-demo --parameters '{"ParameterName": "wlm_json_configuration","ParameterValue": "[{"query_group": [], "user_group": ["etl"],"query_group_wild_card": 0,"user_group_wild_card": 0, "query_concurrency": 9, "max_execution_time": 0, "memory_percent_to_use": 60, "identify": "ETL" }, {"query_group": [], "user_group": ["bi"],"query_group_wild_card": 0,"user_group_wild_card": 0, "query_concurrency": 3, "max_execution_time": 0, "memory_percent_to_use": 20, "identify": "BI" }, { "query_group": [], "user_group": [], "query_group_wild_card": 0, "user_group_wild_card": 0, "query_concurrency": 3, "max_execution_time": 5400000, "memory_percent_to_use": 20, "identify": "Default queue", "guidelines": [ { "rule_name": "user_query_duration_threshold", "predicate": [ { "metric_name": "query_execution_time", "operator": ">", "value": 10800 } ], "motion": "abort" } ] }, { "short_query_queue": "true" } ]","Description": "ETL Begin, ETL Pleasant"}';

The previous AWS CLI command programmatically units the configuration of your WLM queues with out requiring a reboot of the cluster as a result of the queue settings modified have been all dynamic settings.

For the advertising and marketing knowledge mart use case, at 9:00 AM or when the ETL is completed, you’ll be able to have a job run an AWS CLI command to switch the WLM queue useful resource settings to a BI-friendly configuration as proven within the following code:

aws redshift --region 'us-east-1' modify-cluster-parameter-group --parameter-group-name manual-wlm-demo --parameters '{"ParameterName": "wlm_json_configuration","ParameterValue": "[{"query_group": [], "user_group": ["etl"],"query_group_wild_card": 0,"user_group_wild_card": 0, "query_concurrency": 1, "max_execution_time": 0, "memory_percent_to_use": 5, "identify": "ETL" }, {"query_group": [], "user_group": ["bi"],"query_group_wild_card": 0,"user_group_wild_card": 0, "query_concurrency": 12, "max_execution_time": 0, "memory_percent_to_use": 80, "identify": "BI" }, { "query_group": [], "user_group": [], "query_group_wild_card": 0, "user_group_wild_card": 0, "query_concurrency": 2, "max_execution_time": 5400000, "memory_percent_to_use": 15, "identify": "Default queue", "guidelines": [ { "rule_name": "user_query_duration_threshold", "predicate": [ { "metric_name": "query_execution_time", "operator": ">", "value": 10800 } ], "motion": "abort" } ] }, { "short_query_queue": "true" } ]","Description": "ETL Finish, BI Pleasant"}';

Observe that with reference to a guide WLM configuration, the utmost slots you’ll be able to allocate to a queue is 50. Nevertheless, this doesn’t imply that in an automated WLM configuration, a Redshift cluster all the time runs 50 queries concurrently. This could change primarily based on the reminiscence wants or different kinds of useful resource allocation on the cluster. We suggest configuring your guide WLM question queues with a complete of 15 or fewer question slots. For extra data, see Concurrency degree.

In case of WLM timeout or a QMR hop motion inside guide WLM, a question can try to hop to the subsequent matching queue primarily based on WLM queue task guidelines. This motion in guide WLM is named question queue hopping.

Auditor Redshift knowledge warehouse utilizing WLM in Redshift Serverless

The auditor knowledge warehouse workload runs on the month, and quarter finish. For this periodic workload, Redshift Serverless is properly suited, each from a value and ease of administration perspective. Redshift Serverless makes use of ML to be taught out of your workload to robotically handle workload and auto scaling of compute wanted in your workload.

In Redshift Serverless, you’ll be able to arrange utilization and question limits. The question limits allow you to arrange the QMR. You possibly can select Handle question limits to robotically set off the default abort motion when queries transcend efficiency boundaries. For extra data, check with Question monitoring metrics for Amazon Redshift Serverless.

For different detailed limits in Redshift Serverless, check with Configure monitoring, limits, and alarms in Amazon Redshift Serverless to maintain prices predictable.

Monitor utilizing system views for operational metrics

The system views in Amazon Redshift are used to observe the workload efficiency. You possibly can view the standing of queries, queues, and repair courses by utilizing WLM-specific system tables. You possibly can question system tables to discover the next particulars:

• View which queries are being tracked and what sources are allotted by the workload supervisor
• See which queue a question has been assigned to
• View the standing of a question that’s at the moment being tracked by the workload supervisor

You possibly can obtain the pattern SQL pocket book system queries. You possibly can import this in Question Editor V2.0. The queries within the pattern pocket book will help you discover your workloads being managed by WLM queues.

Conclusion

On this submit, we coated real-world examples for Auto WLM and guide WLM patterns. We launched person roles task to WLM queues, and shared queries on system views and tables to assemble operational insights in your WLM configuration. We encourage you to discover utilizing Redshift person roles with workload administration. Use the script supplied on AWS re:Publish to transform teams to roles, and begin utilizing person roles in your WLM queues.

Concerning the Authors

Rohit Vashishtha is a Senior Analytics Specialist Options Architect at AWS primarily based in Dallas, Texas. He has over 17 years of expertise architecting, constructing, main, and sustaining huge knowledge platforms. Rohit helps prospects modernize their analytic workloads utilizing the breadth of AWS companies and ensures that prospects get the very best value/efficiency with utmost safety and knowledge governance.

Harshida Patel is a Principal specialist SA with AWS.

Nita Shah is an Analytics Specialist Options Architect at AWS primarily based out of New York. She has been constructing knowledge warehouse options for over 20 years and makes a speciality of Amazon Redshift. She is concentrated on serving to prospects design and construct enterprise-scale well-architected analytics and resolution assist platforms.

Yanzhu Ji is a Product Supervisor within the Amazon Redshift group. She has expertise in product imaginative and prescient and technique in industry-leading knowledge merchandise and platforms. She has excellent ability in constructing substantial software program merchandise utilizing net growth, system design, database, and distributed programming methods. In her private life, Yanzhu likes portray, pictures, and taking part in tennis.