Simplify your question efficiency diagnostics in Amazon Redshift with Question profiler

Amazon Redshift is a quick, scalable, safe, and absolutely managed cloud information warehouse that permits you to analyze your information at scale. Amazon Redshift Serverless enables you to entry and analyze information with out the standard configurations of a provisioned information warehouse. Sources are mechanically 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 choose to handle your Amazon Redshift assets manually, you may create provisioned clusters on your information querying wants. For extra info, seek advice from 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 features of your cluster or serverless, akin 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 known as the Question profiler. The Question profiler is a graphical instrument 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 numerous statistics. The Question profiler makes it simpler for customers to grasp and troubleshoot their queries.

On this submit, we cowl two frequent use circumstances for troubleshooting question efficiency. We present you step-by-step easy methods to analyze and troubleshoot long-running queries utilizing the Question profiler.

Overview

For Amazon Redshift Serverless, the Question profiler may 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 on the market, you’ll observe a listing of kid queries. Select a question to view it in Question profiler.

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

Stipulations

• You need to use the next pattern AWS Identification and Entry Administration (IAM) coverage to configure your IAM consumer or position with minimal privileges to entry Question profiler from the AWS console. In case your IAM consumer or position already has entry to Question and masses 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:<your-namespace>",
                "arn:aws:redshift-serverless:<your-workgroupname>",
                "arn:aws:redshift:<your-clustername>"
            ]
        }
    ]
}

• You possibly can select to make use of Question profiler in your account with an present Amazon Redshift information warehouse and queries. Nevertheless, if you want to implement this demo in your present Amazon Redshift information warehouse, obtain Redshift question editor v2 pocket book, Redshift Question profiler demo, and seek advice from the Knowledge Loading part later on this submit.
• You should hook up with the cluster utilizing database credentials and grant the sys:operator or sys:monitor position to the database consumer 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 broaden the database sample_data_dev.
2. Select the icon related to the tpcds.
3. The question editor v2 then masses the info right into a schema tpcds within the database sample_data_dev.

The next screenshot exhibits these steps.

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

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

Use circumstances

On this submit, we describe two frequent makes use of circumstances round question efficiency and easy methods to use Question profiler to troubleshoot the efficiency points:

1. Nested loop joins – This be a part of sort is the slowest of the potential be a part of varieties. Nested loop joins are the cross-joins with out a be a part of situation that outcome 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 knowledge 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 concern associated to nested loop joins.
   

4. Run cell #5 to seize the question id from the SYS_QUERY_HISTORY system view filtering based mostly on the question label you set within the previous step.
5. On the Amazon Redshift console, within the navigation pane, choose Question and masses and select the cluster identify the place the question was initially executed, as proven within the following screenshot.
   
6. It will open the brand new Question profiler. Underneath the Question historical past part, select Connect with database.After profitable connection to the database, you’ll observe the Standing exhibiting as Related and displaying the question historical past, as proven within the following screenshot.
   
7. You 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. Underneath the Question plan part, select Baby question 1, as proven within the following screenshot. If there are a number of baby 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, seek advice from 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 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 give attention to 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 concerning the nested loop be a part of.
12. By taking a look at Step particulars – nestloop, we are able to examine the Enter rows and evaluate that with the Output rows, as proven within the following screenshot. On this case, because of the 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 a 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 display 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. Observe 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 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. Underneath ID, choose 6 from the Streams facet panel for additional evaluation.
   

Stream 6 exhibits a step of hash be a part of, which includes a hash be a part of of two tables which can be each redistributed. This may be inferred from Hash Proper Be a 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 proper 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 vary 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 spot 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 must be redistributed on the question runtime, leading to optimized efficiency.
   

Issues

Think about 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 lately 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 sudden 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 submit, we mentioned easy methods to use Amazon Redshift Question profiler to observe and troubleshoot long-running queries. We demonstrated a step-by-step method to investigate question efficiency by analyzing the question execution plan and statistics and figuring out the foundation reason behind question slowness. Do this characteristic in your surroundings and share your suggestions with us.

In regards to the Authors

Raks Khare is a Senior Analytics Specialist Options Architect at AWS based mostly out of Pennsylvania. He helps prospects throughout various industries and areas architect information analytics options at scale on the AWS platform. Exterior 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 workforce. She works with prospects to assist discover the usage of 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 keen about serving to prospects construct scalable and sturdy information and analytics options. Earlier than AWS, she labored in a number of completely different information engineering and analytics roles. Exterior of labor, she enjoys panorama images, touring, and board video games.