Simplify your question efficiency diagnostics in Amazon Redshift with Question profiler

Amazon Redshift is a quick, scalable, safe, and totally managed cloud information warehouse that allows you to analyze your information at scale. Amazon Redshift Serverless helps you to entry and analyze information with out the same old configurations of a provisioned information warehouse. Assets are routinely provisioned and information warehouse capability is intelligently scaled to ship quick efficiency for even essentially the most demanding and unpredictable workloads. In the event you want to handle your Amazon Redshift assets manually, you may create provisioned clusters on your information querying wants. For extra info, consult with Amazon Redshift clusters.

Amazon Redshift gives efficiency metrics and information so you may monitor the well being and efficiency of your provisioned clusters, serverless workgroups, and databases. The efficiency information you need to use on the Amazon Redshift console falls into two classes:

• Amazon CloudWatch metrics – Helps you monitor the bodily facets of your cluster or serverless, comparable to useful resource utilization, latency, and throughput.
• Question and cargo efficiency information – Helps you monitor database exercise, examine and diagnose question efficiency issues.

Amazon Redshift has launched a brand new characteristic referred to as the Question profiler. The Question profiler is a graphical software that helps customers analyze the elements and efficiency of a question. This characteristic is a part of the Amazon Redshift console and gives a visible and graphical illustration of the question’s run order, execution plan, and varied statistics. The Question profiler makes it simpler for customers to know and troubleshoot their queries.

On this put up, we cowl two widespread use instances for troubleshooting question efficiency. We present you step-by-step the way to analyze and troubleshoot long-running queries utilizing the Question profiler.

Overview

For Amazon Redshift Serverless, the Question profiler might be accessed by going to the Serverless console. Select Question and database monitoring, choose a question, after which navigate to the Question plan tab. If a question plan is out there, you’ll observe a listing of kid queries. Select a question to view it in Question profiler.

For Amazon Redshift provisioned, the Question profiler might be accessed by going to the provisioned clusters dashboard. Select Question and hundreds, and select a question. Navigate to the Question plan tab. If a question plan is out there, you’ll observe a listing of kid queries. Select a question to view it in Question profiler.

Stipulations

• You should utilize the next pattern AWS Id and Entry Administration (IAM) coverage to configure your IAM person or function with minimal privileges to entry Question profiler from the AWS console. In case your IAM person or function already has entry to Question and hundreds part of Redshift provisioned cluster dashboard or Question and database monitoring part of Redshift serverless dashboard, then no further permissions are wanted:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Effect": "Allow",
            "Action": [
                "redshift:DescribeClusters",
                "redshift-serverless:ListNamespaces",
                "redshift-serverless:ListWorkgroups",
                "redshift-data:ExecuteStatement",
                "redshift-data:DescribeStatement",
                "redshift-data:GetStatementResult"
            ],
            "Useful resource": [
                "arn:aws:redshift-serverless:",
                "arn:aws:redshift-serverless:",
                "arn:aws:redshift:"
            ]
        }
    ]
}

• You possibly can select to make use of Question profiler in your account with an current Amazon Redshift information warehouse and queries. Nevertheless, if you need to implement this demo in your current Amazon Redshift information warehouse, obtain Redshift question editor v2 pocket book, Redshift Question profiler demo, and consult with the Knowledge Loading part later on this put up.
• It’s essential to connect with the cluster utilizing database credentials and grant the sys:operator or sys:monitor function to the database person to view queries run by customers.

Knowledge loading

Amazon Redshift Question Editor v2 comes with pattern information that may be loaded right into a pattern database and corresponding schema. To check Question profiler towards the pattern information, load the tpcds pattern information and run queries.

1. To load the tpcds pattern information, launch Redshift question editor v2 and develop the database sample_data_dev.
2. Select the icon related to the tpcds.
3. The question editor v2 then hundreds the information right into a schema tpcds within the database sample_data_dev.

The next screenshot exhibits these steps.

4. Confirm the information by working the next pattern question, as proven within the following screenshot.

choose depend(*) from sample_data_dev.tpcds.buyer;

Use instances

On this put up, we describe two widespread makes use of instances round question efficiency and the way to use Question profiler to troubleshoot the efficiency points:

1. Nested loop joins – This be part of kind is the slowest of the potential be part of sorts. Nested loop joins are the cross-joins and not using a be part of situation that consequence within the Cartesian product of two tables.
2. Suboptimal information distribution – If information distribution is suboptimal, you may discover a big broadcast or redistribution of information throughout compute nodes when two giant tables are joined collectively.

Use case 1: Nested loop joins

To troubleshoot efficiency points with nest loop joins utilizing Question profiler, comply with these steps:

1. Import pocket book downloaded beforehand in conditions part of the weblog into Redshift question editor v2.
2. Set the context of database to sample_data_dev in Question Editor v2, as proven within the following screenshot.
   
3. Run cell #3 from demo pocket book to diagnose a question efficiency situation associated to nested loop joins.
   

4. Run cell #5 to seize the question id from the SYS_QUERY_HISTORY system view filtering primarily based on the question label you set within the previous step.
5. On the Amazon Redshift console, within the navigation pane, choose Question and hundreds and select the cluster title the place the question was initially executed, as proven within the following screenshot.
   
6. It will open the brand new Question profiler. Beneath the Question historical past part, select Hook up with database.After profitable connection to the database, you’ll observe the Standing exhibiting as Linked and displaying the question historical past, as proven within the following screenshot.
   
7. Yow will discover your queries both by Question ID or Course of ID. Enter the Question ID captured within the previous step to filter the long-running question for additional evaluation and select the corresponding Question ID, as proven within the following screenshot.
   
8. Beneath the Question plan part, select Baby question 1, as proven within the following screenshot. If there are a number of youngster queries, you’ll have to examine each for efficiency points.
   It will open the question plan in a tree view together with further metrics on the facet panel. This lets you rapidly analyze the question streams, segments and steps. For extra details about streams, segments, and steps, consult with Question planning and execution workflow within the Amazon Redshift Database Developer Information.
9. Activate View streams and, within the Streams facet panel, examine and establish which stream has the very best execution time. On this case, Streams ID 5 is the place the question spends nearly all of time, as proven within the following screenshot
   
10. Within the Streams facet panel, beneath ID, choose 5 to deal with Stream 5 for additional evaluation. Stream 5 exhibits a step of Nestloop, as proven within the following screenshot.
    
11. Select the Nestloop step to additional analyze. The facet panel will change with step particulars and extra metrics in regards to the nested loop be part of.
12. By Step particulars – nestloop, we are able to examine the Enter rows and examine that with the Output rows, as proven within the following screenshot. On this case, as a result of cross-joining with the Store_returns desk, 287,514 enter rows explodes to 950,233,770 rows, thus inflicting our question to run slower.
    
13. Repair the question by introducing a be part of situation between the store_sales and store_returns. Run cell #7 from Question editor v2 demo pocket book.The re-written question runs in simply 307 milliseconds.

Use case 2: Suboptimal information distribution

1. To exhibit suboptimal information distribution, change the distribution fashion of tables web_sales and web_returns to EVEN by working cell #10 of Question editor v2 demo pocket book.

2. Run cell #12. The question takes 409 milliseconds to run, as proven by the elapsed time within the following screenshot of the Question editor v2.
3. Comply with steps 3–10 from use case 1 to find the query_id and to open the Question profiler view for the previous question.
4. On the Question profiler web page for the previous question, activate View streams. Within the Streams facet panel, examine and establish which stream has the very best execution time. On this case, Stream ID 6 is the place the question spends a majority of the time, as proven within the following screenshot.
   
5. Beneath ID, choose 6 from the Streams facet panel for additional evaluation.
   

Stream 6 exhibits a step of hash be part of, which entails a hash be part of of two tables which can be each redistributed. This may be inferred from Hash Proper Be part of DS_DIST_BOTH beneath Clarify plan node info within the following screenshot. Often, these redistributions happen as a result of the tables aren’t joined on their distribution keys, or they don’t have the right distribution fashion. Within the case of enormous tables, these redistributions can result in vital efficiency degradation and, therefore, it is very important establish and repair such steps to optimize question efficiency.

6. Repair this suboptimal information distribution sample by selecting the suitable distribution keys on the tables concerned: web_sales and web_returns. To alter the distribution types, run cell #14 of demo pocket book to change desk instructions.
   
7. After the previous instructions end working, run cell #16 to re-execute the choose question. As proven within the Question Editor within the following screenshot, now the identical question completed in 244 milliseconds after updating the distribution fashion to key for tables web_sales and web_returns.
   
8. Within the Question profiler view, activate View streams and see that Streams 5 now took essentially the most time. It took 8 milliseconds to complete, as in comparison with 13 milliseconds within the previous step.
   
9. Within the Streams facet panel, beneath ID, choose 5 to drill down additional, then select the Hashjoin As the next screenshot exhibits, after altering the distribution fashion to key for each web_sales and web_return tables, not one of the tables should be redistributed on the question runtime, leading to optimized efficiency.
   

Concerns

Take into account the next particulars whereas utilizing Question profiler:

1. Question profiler shows info returned by the SYS_QUERY_HISTORY, SYS_QUERY_EXPLAIN, SYS_QUERY_DETAIL, and SYS_CHILD_QUERY_TEXT views.
2. Question profiler solely shows question info for queries which have not too long ago run on the database. If a question completes utilizing a prepopulated resultset cache, Question profiler received’t have details about it as a result of Amazon Redshift doesn’t generate a question plan for such queries.
3. Queries run by Question profiler to return the question info run on the identical information warehouse because the user-defined queries.

Clear Up

To keep away from surprising prices, full the next motion to delete the assets you created:

Drop all of the tables within the sample_data_dev beneath tpcds schema.

Conclusion

On this put up, we mentioned the way to use Amazon Redshift Question profiler to watch and troubleshoot long-running queries. We demonstrated a step-by-step method to research question efficiency by analyzing the question execution plan and statistics and figuring out the foundation reason for question slowness. Do this characteristic in your atmosphere and share your suggestions with us.

In regards to the Authors

Raks Khare is a Senior Analytics Specialist Options Architect at AWS primarily based out of Pennsylvania. He helps clients throughout various industries and areas architect information analytics options at scale on the AWS platform. Outdoors of labor, he likes exploring new journey and meals locations and spending high quality time along with his household.

Blessing Bamiduro is a part of the Amazon Redshift Product Administration crew. She works with clients to assist discover using Amazon Redshift ML of their information warehouse. In her spare time, Blessing loves travels and adventures.

Ekta Ahuja is an Amazon Redshift Specialist Options Architect at AWS. She is captivated with serving to clients construct scalable and strong information and analytics options. Earlier than AWS, she labored in a number of totally different information engineering and analytics roles. Outdoors of labor, she enjoys panorama images, touring, and board video games.