Data Warehousing on AWS Training Data Warehousing on AWS: Workload Management 3 — Questions and Answers
Question 1: Which Redshift WLM feature automatically moves short-running queries out of a long-running queue to reduce wait times?
- Query hopping
- Short-query acceleration (SQA) (Correct answer)
- Concurrency scaling
- Query priority boosting
Correct answer: Short-query acceleration (SQA)
Short-query acceleration (SQA) uses machine learning to identify short-running queries and execute them in a dedicated fast lane, bypassing the main WLM queues.
Question 2: When configuring user group routing rules in Redshift WLM, what happens if a user belongs to two groups that map to different queues?
- The query fails with an error
- The query is routed to the first matching queue in the WLM configuration order (Correct answer)
- The query is routed to the queue with the most available slots
- The query is sent to the default queue
Correct answer: The query is routed to the first matching queue in the WLM configuration order
Redshift WLM evaluates queue routing rules in order and routes the query to the first queue whose user group or query group criteria match.
Question 3: What is the purpose of the 'concurrency scaling' feature in Redshift in relation to WLM?
- It reduces the number of WLM queues needed
- It automatically adds transient cluster capacity when queued queries exceed thresholds (Correct answer)
- It compresses data to free up memory for more concurrent queries
- It pauses idle queries to release concurrency slots
Correct answer: It automatically adds transient cluster capacity when queued queries exceed thresholds
Concurrency scaling adds temporary cluster capacity to handle bursts of queued queries, seamlessly routing overflow workloads to transient clusters.
Question 4: In Redshift WLM, which memory allocation strategy helps prevent a single large query from consuming all cluster memory?
- Assigning 100% memory to the default queue
- Setting per-queue memory percentages so no single queue can exceed its allocation (Correct answer)
- Using columnar compression on all tables
- Enabling enhanced VPC routing
Correct answer: Setting per-queue memory percentages so no single queue can exceed its allocation
By assigning specific memory percentages to each WLM queue, you ensure no single queue (or the queries within it) can consume more than its allocated share of cluster memory.
Question 5: Which action does a query 'hop' perform in the context of Redshift WLM query hopping?
- A query moves from one node to another for parallel execution
- A timed-out query is cancelled and resubmitted to a queue with a longer timeout (Correct answer)
- A query is split into subqueries across multiple queues
- A query's priority is elevated after waiting too long
Correct answer: A timed-out query is cancelled and resubmitted to a queue with a longer timeout
Query hopping occurs when a query exceeds its WLM queue timeout; it is cancelled and automatically restarted in a queue configured with a higher (or no) timeout limit.
Question 6: What does the STL_WLM_ERROR system table in Redshift record?
- Syntax errors in SQL queries processed through WLM queues
- Queries that were cancelled due to WLM timeout or other WLM-related errors (Correct answer)
- Failed WLM parameter changes applied to the parameter group
- Network errors during inter-node communication for WLM
Correct answer: Queries that were cancelled due to WLM timeout or other WLM-related errors
STL_WLM_ERROR logs queries that encountered WLM-related errors, including those cancelled because they exceeded the queue's timeout setting.
Question 7: In automatic WLM with priority settings, which priority level gets the most cluster resources by default?
- Normal
- High
- Highest
- Critical (Correct answer)
Correct answer: Critical
In automatic WLM, the 'Critical' priority level receives the greatest share of cluster resources, followed by Highest, High, Normal, and Low.
Data Warehousing on AWS Training Data Warehousing on AWS: Workload Management 3
Which Redshift WLM feature automatically moves short-running queries out of a long-running queue to reduce wait times?