Announcing Query Insights – Helping customers with growing data & query volume
Table of Contents
The number one driver of change for our customers is that they are experiencing huge growth in data and query volume.
We observe three main drivers behind that growth:
- Growth in the number of data sources connected to a Redshift cluster and the volume of data coming from those sources. We observe a data growth factor of ~2x every year, meaning data volume grows by a factor of 10x every 5 years.
- Growth in the number of workflows that are running once data resides inside a cluster. The reason is simple – the more data and sources you have, the more ways you have to join and combine that data.
- Growth in the number of data consumers that want more data in more combinations at a higher frequency. Much of that is driven by how easy dashboard tools make it to explore data.
When building data pipelines with Amazon Redshift, that growth makes it quite challenging to monitor and detect anomalies across all of the queries running on the system. For example, a lot of our customers use Looker as their tool for data exploration, and a frequent issue are slow Looker dashboards.
One way of approaching that problem is to use the best practices for setting up your workload management in Amazon Redshift the right way. You get a more granular view of your users, and the resources they use, and the WLM gives you the power to isolate them from each other.
But even after optimizing your WLM configuration, you’re still lacking critical insights, for example:
- what if you detect a large spike in query volume – how do you know what caused that?
- Or if you have a data pipeline that is experiencing an overall increase in latency. Which individual queries is contributing to the increase?
- Or if you detect a large spike in Looker PDT performance which query is causing that?
Until now it’s been quite hard to surface this information in a single place. So we built a way to visualize that information in a more intuitive and powerful way. It’s a new feature that makes your life as a data engineer easier when working with Amazon Redshift.
It’s called “Query Insights” and it’s available immediately in your dashboard.
Check out some example of using Query Insights.
Detect a huge increase in query volume
This example shows how you can use Query Insights to quickly identify that a huge spike in query volume happened and the specific queries that caused it.
- notice that the query count spike at 8:59pm on Jan 24
- group by the default ‘Query Group’ attribute to segment the chart by query group.
- click on the chart to reveal a slide-in list of query groups in descending order of Count
- click the “view queries” Action to jump to the query details page to view the individual queries
Identify which query group is causing my batch pipeline to slow down?
This example shows how you can use Query Insights to find the cause of an overall increase in latency.
- Detect there is an overall increase in latency, start investigating by grouping the data by “App”
- Notice the “intermix_collector” App is experiencing a largest increase in latency. Click on it and then group by a custom attribute
- The “intermix_collector” has custom attributes “DAG” and “Task” that appear on the left-pane. This is because these queries are tagged using the Intermix annotation format.
- Group by “DAG” and then “Task” to isolate the specific Task that is seeing a latency spike
- Finally, group by ‘Query Group’ to see the queries
Find the query that caused a Looker PDT latency spike
In this example, let’s monitor our Looker PDT queries to find the queries which are the slowest.
- Click on the “Looker PDT” so we’re only viewing data for Looker PDT queries
- The Looker PDT app has a few custom attributes. Let’s click on “model” to group by model name
- Notice the “supply chain” model is the slowest, click on it then group by “Query Group” to find the individual queries
Look for “Transfer Insights” soon. This will allow you to monitor the users & apps which are loading data and rows into your Amazon Redshift cluster.
Schedule a Personalized Demo
I’d appreciate the opportunity to give your team a personalized tour of Query Insights, as well as share our product roadmap and hear your feature requests and feedback.
Please find a time on my calendar at this link.
Join 11,000 of your peers.
Subscribe to our newsletter SF Data.
People at Facebook, Amazon and Uber read it every week.
Every Monday morning we'll send you a roundup of the best content from intermix.io and around the web. Make sure you're ready for the week! See all issues.