What are Query Groups in Amazon Redshift and Integrate.io?

Amazon Redshift is a robust, enterprise-class cloud data warehouse—but that doesn’t mean it’s always the most user-friendly solution. Getting the most out of your Redshift deployment can be a challenge at the best of times (which is one reason why we wrote our article “15 Performance Tuning Techniques for Amazon Redshift”).

In particular, Redshift queries can cause performance issues and mysterious slowdowns if you don’t know how to optimize them. But without a dedicated way to analyze Redshift query performance, how can you hope to isolate the problem?

That’s why we’ve introduced the Query Groups feature to Integrate.io, our performance monitoring solution for Amazon Redshift. Query Groups is a powerful asset that intelligently classifies and ranks query workloads on your cluster. Using Query Groups, you can answer questions like:

  • My cluster just experienced a sudden increase in latency. Which queries are causing this?
  • Which queries are consuming the most amount of cluster resources?
  • Which queries are slowly increasing in latency, or have the highest queue time?

How Do Query Groups Work in Amazon Redshift and Integrate.io?

Using Integrate.io, the queries in a Query Group are grouped together using a proprietary algorithm, and ranked by volume, execution time, and queue time. More metrics might be added in the future depending on user demand and necessity. All of the queries in a Query Group share a SQL structure and operate on the same tables.

Example: Find the Queries Causing a Query Spike

At 8:17 a.m. on August 7, the below cluster experienced an eightfold spike in queries—wow! Typically, this type of event is caused by a handful of new queries that suddenly increased in volume. But how do you find which queries, and who ran them?

Integrate.io’s Query Groups feature can quickly determine which of your queries are responsible.

First, click on the new Query Groups page in the left navigation panel. By default, Query Groups are sorted by Rank. In this case, we want to re-sort by “Rank Change,” which will order the list of Query Groups by the “fastest movers.” In other words, this will help us quickly see the groups which have been moving up the ranks in the past week.

Sure enough, we see a handful of Query Groups which suddenly started running. Clicking into the first one, we can isolate the exact queries that are causing the problem.

You could also use the same procedure to determine the queries that underwent a spike in latency or queue time.

What’s Next for Query Groups?

Query Groups in Redshift and Integrate.io offer the potential to make your Redshift performance tuning process radically more efficient—and we’re just getting started. We plan to expand the “grouping” concept in the future to add:

  • Grouping by application, e.g. ranking queries by Looker user, Chartio dashboard, or Airflow task.
  • MetricStream support for setting up notifications and email alerts.
  • New rank metrics such as data transfer, disk-based queries, and aborted queries.

Sound like what you’ve been looking for,? We’re here to assist. Sign up today for a free trial of the Integrate.io platform. Query Groups are just one of the ways that we’ve helped Redshift customers get the most from their cloud data warehouse.