Post Syndicated from Adam Gatt original https://aws.amazon.com/blogs/big-data/automate-your-amazon-redshift-performance-tuning-with-automatic-table-optimization/

Amazon Redshift is a cloud data warehouse database that provides fast, consistent performance running complex analytical queries on huge datasets scaling into petabytes and even exabytes with Amazon Redshift Spectrum. Although Amazon Redshift has excellent query performance out of the box, with up to three times better price performance than other cloud data warehouses, you can further improve its performance by physically tuning tables in a data model. You do so by sorting table rows and rearranging rows across a cluster’s nodes. In Amazon Redshift, you implement this by setting sort and distribution key table attributes.

In the past, setting sort and distribution keys was an involved manual process that required a skilled resource to analyze a cluster’s workload and choose and implement the right keys for every table in the data model. More recently, Amazon Redshift Advisor provided suggestions, but these still had to be manually implemented. At AWS re:Invent 2020, Amazon Redshift announced a new feature to automate this process: automatic table optimization (ATO). ATO automatically monitors a cluster’s workload and table metadata, runs artificial intelligence algorithms over the observations, and implements sort and distribution keys online in the background, without requiring any manual intervention, and without interrupting any running queries.

In this post, I explain what sort and distribution keys are and how they improve query performance. I also explain how ATO works and how to enable and disable it. Then I outline the steps to set up and run a test of ATO on the Cloud DW benchmark derived from TPC-H using a 30 TB dataset. Finally, I present the results of a test that show ATO improved performance on this benchmark, without requiring any manual tuning.

Distribution and sort keys

In this section, I give a high-level overview of distribution and sort keys, then I explain how they’re automatically set by ATO.

Distribution keys

Amazon Redshift has a massively parallel processing (MPP) architecture, where data is distributed across multiple compute nodes (see the following diagram). This allows Amazon Redshift to run queries against each compute node in parallel, dramatically increasing query performance.

To achieve the best possible query performance, data needs to be distributed across the compute nodes in a way that is optimal to the specific workload that is being run on the cluster. For example, the optimal way to distribute data for tables that are commonly joined is to store rows with matching join keys on the same nodes. This enables Amazon Redshift to join the rows locally on each node without having to move data around the nodes. Data distribution also affects the performance of GROUP BY operations.

In Amazon Redshift, the data distribution pattern is determined by two physical table settings: distribution style (DISTSTYLE) and distribution key (DISTKEY).

Amazon Redshift has three distribution styles:

• All – A copy of the entire table is replicated to every node
• Even – The data in the table is spread evenly across the nodes in a cluster in a round-robin distribution
• Key – The data is distributed across the nodes by the values in the column defined as the DISTKEY

If a table’s distribution style is key, then a single column in the table can be set as the DISTKEY.

Sort keys

Sort keys determine how rows are physically sorted in a table. Having table rows sorted improves the performance of queries with range-bound filters. Amazon Redshift stores the minimum and maximum values of each of its data blocks in metadata. When a query filters on a column (or multiple columns), the execution engine can use the metadata to skip blocks that are out of the filter’s range. For example, if a table has a sort key on the column created_date and a query has a filter WHERE created_date BETWEEN '2020-02-01' AND '2020-02-02', the execution engine can identify which blocks don’t contain data for February 1 and 2 given their metadata. The execution engine can then skip over these blocks, reducing the amount of data read and the number of rows that need to be materialized and processed, which improves the query performance.

Sort keys can be set on a single column in a table, or multiple columns (known as a compound sort key). They can also be interleaved.

Manually set distribution and sort keys

When you create a table in Amazon Redshift, you can manually set the distribution style and key, and the sort key in the CREATE TABLE DDL.

The following code shows two simplified example DDL statements for creating a dimension and fact table in a typical star schema model that manually set distribution and sort keys:

CREATE TABLE customer_dim (
   customer_key INT  
  ,customer_id INT
  ,first_name VARCHAR(100)
  ,last_name VARCHAR(100)
  ,join_date DATE) 
DISTKEY ( customer_key );

CREATE TABLE sale_fact (
   date_key DATE SORTKEY
  ,customer_key INT  
  ,product_key INT
  ,sale_amount DECIMAL(7,2))
DISTKEY ( customer_key );

Both tables have the customer_key column set as the distribution key (DISTKEY). When rows are inserted into these tables, Amazon Redshift distributes them across the cluster based on the values of the customer_key column. For example, all rows with a customer_key of 100 are moved to the same node, and likewise all rows with a customer_key of 101 are also moved to the same node (this may not be the same node as the key of 100), and so on for every row in the table.

In the sale_fact table, the date_key column has been set as the sort key (SORTKEY). When rows are inserted into this table, they’re physically sorted on the disk in the order of dates in the date_key column.

After data has been loaded into the tables, we can use the svv_table_info system view to see what keys have been set:

INSERT INTO customer_dim (customer_key, customer_id, first_name, last_name, join_date)
VALUES (100, 1001, 'John', 'Smith', SYSDATE);

INSERT INTO sale_fact (date_key, customer_key, product_key, sale_amount)
VALUES (SYSDATE, 100, 203, 98.76);

SELECT ti."table"      
      ,ti.diststyle      
      ,ti.sortkey1
FROM svv_table_info ti
WHERE ti.database = 'dev'
AND ti.schema = 'public'
AND ti."table" IN ('customer_dim', 'sale_fact')
ORDER BY 1;

The following table shows our results.

The sort key for customer_dim has been set to AUTO(SORTKEY), which I explain in the next section.

Now, if we populate these tables and run a business analytics query on them, like the following simplified query, we can see how the distribution and sort keys improve the performance of the query:

SELECT cd.customer_id      
     ,SUM(sf.sale_amount) AS sale_amount
FROM sale_fact sf
INNER JOIN customer_dim cd   
   ON sf.customer_key = cd.customer_key
WHERE sf.date_key BETWEEN '2021-03-01' AND '2021-03-07'
GROUP BY cd.customer_id;

The customer_dim table is joined to the sale_fact table on the customer_key column. Because both the table’s rows are distributed on the customer_key column, this means the related rows (such as customer_key of 100) are co-located on the same node, so when Amazon Redshift runs the query on this node, it doesn’t need to move related rows across the cluster from other nodes (a process known as redistribution) for the join. Also, there is a range bound filter on the date_key column (WHERE sf.date_key BETWEEN '2021-03-01' AND '2021-03-07'). Because there is a sort key on this column, the Amazon Redshift execution engine can more efficiently skip blocks that are out of the filter’s range.

Automatically set distribution and sort keys

Amazon Redshift ATO is enabled by default. When you create a table and don’t explicitly set distribution or sort keys in the CREATE TABLE DDL (as in the previous example), the following happens:

• The distribution style is set to AUTO(ALL) for small tables and AUTO(EVEN) for large tables.
• The sort key is set to AUTO(SORTKEY). This means no sort key is currently set on the table.

The AUTO keyword indicates the style and key are being managed by ATO.

For example, we can create a table with no keys explicitly defined:

CREATE TABLE customer_dim (
   customer_key INT  
  ,customer_id INT  
  ,first_name VARCHAR(100)
  ,last_name VARCHAR(100)
  ,join_date DATE);

Then we insert some data and look at the results from svv_table_info:

INSERT INTO customer_dim (customer_key, customer_id, first_name, last_name, join_date)
VALUES (100, 1001, 'John', 'Smith', SYSDATE);

SELECT ti."table"      
      ,ti.diststyle      
      ,ti.sortkey1
FROM svv_table_info ti
WHERE ti.database = 'dev'
AND ti.schema = 'public'
AND ti."table" IN ('customer_dim', 'sale_fact')
ORDER BY 1;

The following table shows our results.

The distribution and sort key are being managed by ATO, and the distribution style has already been set to ALL.

ATO now monitors queries that access customer_dim and analyzes the table’s metadata, and makes several observations. An example of an observation is the amount of data that is moved across nodes to perform a join, or the number of times a column was used in a range-scan filter that would have benefited from sorted data.

ATO then analyzes the observations using AI algorithms to determine if the introduction or change of a distribution or sort key will improve the workload’s performance.

For distribution keys, Amazon Redshift constructs a graph representation of the SQL join history, and uses this graph to calculate the optimal table distribution to reduce data transfer across nodes when joining tables (see the following diagram). You can find more details of this process in the scientific paper Fast and Effective Distribution-Key Recommendation for Amazon Redshift.

For sort keys, a table’s queries are monitored for columns that are frequently used in filter and join predicates. A column is then chosen based on the frequency and selectivity of those predicates.

When an optimal configuration is found, ATO implements the new keys in the background, redistributing rows across the cluster and sorting tables. For sort keys, another Amazon Redshift feature, automatic table sort, handles physically sorting the rows in the table, and maintains the sort order over time.

This whole process, from monitoring to implementation, completes in hours to days, depending on the number of queries that are run.

Convert existing tables to automatic optimization

You can enable ATO on existing tables by setting the distribution style and sort key to AUTO with the ALTER TABLE statement. For example:

ALTER TABLE customer_dim ALTER DISTSTYLE AUTO;

ALTER TABLE customer_dim ALTER SORTKEY AUTO;

If the table has existing sort and/or distribution keys that were explicitly set, then currently they will be preserved and won’t be changed by ATO.

Disable automatic table optimization

To disable ATO on a table, you can explicitly set a distribution style or key. For example:

ALTER TABLE customer_dim ALTER DISTSTYLE ALL;

ALTER TABLE customer_dim ALTER DISTSTYLE EVEN;

ALTER TABLE customer_dim ALTER DISTKEY customer_id;

You can then explicitly set a sort key or set the sort key to NONE:

ALTER TABLE customer_dim ALTER SORTKEY (join_date);

ALTER TABLE customer_dim ALTER SORTKEY NONE;

Performance test and results

Cloud DW benchmark derived from TPC-H

TPC-H is an industry standard benchmark designed to measure ad hoc query performance for business analytics workloads. It consists of 8 tables and 22 queries designed to simulate a real-world decision support system. For full details of TPC-H, see TPC BENCHMARK H.

The following diagram shows the eight tables in the TPC-H data model.

The Cloud DW Benchmark is derived from TPC-H and uses the same set of tables, queries, and a 30 TB dataset in Amazon Simple Storage Service (Amazon S3), which was generated using the official TPC-H data generator. This provides an easy way to set up and run the test on your own Amazon Redshift cluster.

Because the Cloud DW benchmark is derived from the TPC-H benchmark, it isn’t comparable to published TPC-H results, because the results of our tests don’t fully comply with the specification. This post uses the Cloud DW benchmark.

Set up the test

The following steps outline how to set up the TPC-H tables on an Amazon Redshift cluster, import the data, and set up the Amazon Redshift scheduler to run the queries.

Create an Amazon Redshift cluster

We recommend running this test on a five-node ra3.16xlarge cluster. You can run the test on a smaller cluster, but the query run times will be slower, and you may need to adjust the frequency the test queries are run at, such as every 4 hours instead of every 3 hours. For more information about creating a cluster, see Step 2: Create a sample Amazon Redshift cluster. We also recommend creating the cluster in the us-east-1 Region to reduce the amount of time required to copy the test data.

Set up permissions

For this test, you require permissions to run the COPY command on Amazon Redshift to load the test data, and permissions on the Amazon Redshift scheduler to run the test queries.

AWS Identity and Access Management (IAM) roles grant permissions to AWS services. The following steps describe how to create and set up two separate roles with the required permissions.

The RedshiftCopyRole role grants Amazon Redshift read-only access on Amazon S3 so it can copy the test data.

1. On the IAM console, on the Role page, create a new role called RedshiftCopyRole using Redshift – Customizable as the trusted entity use case.
2. Attach the policyamazonS3ReadOnlyAccess.

The RedshiftATOTestingRole role grants the required permissions for setting up and running the Amazon Redshift scheduler.

3. On the IAM console, create a new role called RedshiftATOTestingRole using Redshift – Customizable as the trusted entity use case.
4. Attach the following policies:
   1. amazonRedshiftDataFullAccess
   2. amazonEventBridgeFullAccess
5. Create a new policy called RedshiftTempCredPolicy with the following JSON and attach it to the role. Replace {DB_USER_NAME} with the name of the Amazon Redshift database user that runs the query.

{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Sid": "UseTemporaryCredentials",
			"Effect": "Allow",
			"Action": "redshift:GetClusterCredentials",
			"Resource": [
			"arn:aws:redshift:*:*:dbuser:*/{DB_USER_NAME}"
			]
		}
	]
}

This policy assigns temporary credentials to allow the scheduler to log in to Amazon Redshift.

6. Check the role has the policies RedshiftTempCredPolicy, amazonEventBridgeFullAccess, and amazonEventBridgeFullAccess attached.
   
7. Now edit the role’s trust relationships and add the following policy:

   {
      "Sid": "S1",
      "Effect": "Allow",
      "Principal": {
        "Service": "events.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }

The role should now have the trusted entities as shown in the following screenshot.

Now you can attach the role to your cluster.

8. On the Amazon Redshift console, choose your test cluster.
9. On the Properties tab, choose Cluster permissions.
10. Select Manage IAM roles and attach RedshiftCopyRole.

It may take a few minutes for the roles to be applied.

The IAM user that sets up and runs the Amazon Redshift scheduler also needs to have the right permissions. The following steps describe how to assign the required permissions to the user.

11. On the IAM console, on the Users page, choose the user to define the schedule.
12. Attach the policy amazonEventBridgeFullAccess directly to the user.
13. Create a new policy called AssumeATOTestingRolePolicy with the following JSON and attach it to the user. Replace {AWS ACCOUNT_NUMBER} with your AWS account number.

{
	"Version": "2012-10-17",
	"Statement": [
		{
		"Sid": "AssumeIAMRole",
		"Effect": "Allow",
		"Action": "sts:AssumeRole",
		"Resource": "arn:aws:iam::{AWS ACCOUNT_NUMBER}:role/RedshiftATOTestingRole"
		}
	]
}

This policy allows the user to assume the role RedshiftATOTestingRole, which has required permissions for the scheduler.

The user should now have the AssumeATOTestingRolePolicy and amazonEventBridgeFullAccess policies directly attached.

Create the database tables and copy the test data

The following script is an untuned version of the TPC-H ddl.sql file that creates all required tables for the test and loads them with the COPY command. To untune the tables, all the sort and distribution keys have been removed. The original tuned version is available on the amazon-redshift-utils GitHub repo.

Copy the script into your Amazon Redshift SQL client of choice, replace the <AWS ACCOUNT_NUMBER> string with your AWS account number, and run the script. On a five-node ra3.16xlarge cluster in the us-east-1 Region, the copy should take approximately 3 hours.

The script creates tables in the default schema public. For this test, I have created a database called tpch_30tb, which the script is run on.

ddl_untuned.sql

When the copy commands have finished, run the following queries to see the table’s physical settings:

SELECT ti."table"      
      ,ti.tbl_rows      
      ,ti.size      
      ,ti.diststyle      
      ,ti.sortkey1      
      ,ti.sortkey_num      
      ,ti.encoded
FROM svv_table_info ti
WHERE ti.database = 'tpch_30tb'
AND ti.schema = 'public'
ORDER BY ti.size;

The output of this query shows some optimizations have already been implemented by the COPY command. The smaller tables have the diststyle set to ALL (replicating all rows across all data nodes), and the larger tables are set to EVEN (a round-robin distribution of rows across the data nodes). Also, the encoding (compression) has been set for all the tables.

SELECT td.tablename      
      ,td."column"      
      ,td.encoding
FROM pg_table_def td
WHERE td.tablename = 'customer'
AND   td.schemaname = 'public';

This output shows the encoding type set for each column of the customer table. Because Amazon Redshift is a columnar database, the compression can be set differently for each column, as opposed to a row-based database, which can only set compression at the row level.

Schedule the test queries

The TPC-H benchmark uses a set of 22 queries that have a wide variation of complexity, amount of data scanned, answer set size, and elapsed time. For this test, the queries are run in serial from a single script file query0.sql. Because Query 11 (Q11) returns a large number of rows and the goal of this test was to measure execution time, this benchmark included a limit 1000 statement on the query to ensure the time being measured was predominantly execution time, rather than return time. This script is available on the amazon-redshift-utils GitHub repo under the src/BlogContent/ATO directory.

At the top of the script, enable_result_cache_for_session is set to off. This forces Amazon Redshift to rerun the queries on each test run, and not just return the query results from the results cache.

Use the following steps to schedule the test script:

1. Download query0.sql from GitHub.
2. Make sure the IAM user has been granted the necessary permissions (set in a previous step).
3. On the Amazon Redshift console, open the query editor.
4. If the current tab is blank, enter any text to enable the Schedule button.
5. Choose Schedule.
   
6. Under Scheduler permissions for IAM role, specify the IAM role you created in a previous step (RedshiftATOTestingRole).
7. For Cluster, choose your cluster.
8. Enter values for Database name and Database user.
   
9. Under Query information, for Scheduled query name, enter tpch-30tb-test.
10. Choose Upload Query and choose the query0.sql file you downloaded in a previous step.
    
11. Under Scheduling options, change Repeat every: to 3 hours.

If you have a smaller cluster, each test run requires more time. To determine the amount of time needed, run query0.sql once to determine the runtime and add an hour for ATO to perform its processing.

12. For Repeat on:, select Everyday.
    
13. Choose Save changes.

The test queries are now set to run in the background at the given schedule. Leave the schedule running for approximately 48 hours to run a full test.

Check the table changes

Amazon Redshift automatically monitors the workload on the cluster and uses AI algorithms to calculate the optimal sort and distribution keys. Then ATO implements the table changes online, without disrupting running queries.

The following queries show the changes ATO has made and when they were made:

SELECT ti."table"      
      ,ti.diststyle      
      ,ti.sortkey1
FROM svv_table_info ti
WHERE ti.database = 'tpch_30tb'
AND ti.schema = 'public'
ORDER BY ti."table";

The following table shows our output.

Now we can see all the distribution and sort keys that have been set by ATO.

svl_auto_worker_action is an Amazon Redshift system view that allows us to see a log of the changes made by ATO:

SELECT st."table"      
      ,wa.type      
      ,wa.status      
      ,wa.eventtime         
      ,wa.previous_state
FROM svl_auto_worker_action wa
INNER JOIN svv_table_info st   
   ON wa.table_id = st.table_id
ORDER BY eventtime;

Data in the following table has been abbreviated to save space.

We can see when each change was implemented by ATO. First, a DISTKEY was added to the supplier table at 4:17 AM on June 25. Because this table is relatively small (300 million rows), ATO was able to apply the DISTKEY in one step.

Next, ATO started implementing a DISTKEY on the part table at 4:20 AM. For DISTKEY changes on large tables (part has 6 billion rows), ATO creates a copy of the source table in the background (a shadow table) and then copies data from the source table into the shadow table, redistributing the data according to the new DISTKEY. This process is done in batches known as checkpoints. After all the data has been copied, the metadata is updated, swapping the shadow table with the source table. There were seven checkpoints for part, and the full conversion was completed at 4:24 AM.

Then from 4:27 AM on June 25 until 4:55 AM on June 26, distribution keys were implemented on orders, customer, and partsupp. The last DISTKEY was then implemented on largest table in the model lineitem (179 billion rows), finishing at 8:41 AM. Because this table was so large, there were 261 checkpoints.

Finally, sort keys on supplier, part, orders and lineitem were created at 4:17 PM on June 26.

View the test results

Now we look at the output of the test and see what impact ATO’s changes had on the overall performance.

The following line graph shows the runtime of the query0.sql script over time. The vertical reference lines show when ATO changed a DISTKEY or SORTKEY. The first distribution keys were created before the test had actually started. This is because ATO was able to use table properties such as constraints to determine appropriate distribution keys. Over time, ATO may change these distribution keys based on workload observations.

The rest of the distribution keys were then added, and lastly the sort keys, reducing the runtime from approximately 4,750 seconds to 3,597.

The data for this graph is taken from the following query:

SELECT q.pid     
      ,COUNT(DISTINCT q.query) num_queries     
      ,MIN(q.starttime) starttime     
      ,MAX(q.endtime) endtime     
      ,DATEDIFF(SECOND, MIN(q.starttime), MAX(q.endtime)) elapsed_sec     
      ,SUM(DATEDIFF(SECOND, c.starttime, c.endtime)) compile_time     
      ,DATEDIFF(SECOND, MIN(q.starttime), MAX(q.endtime)) - SUM(DATEDIFF(SECOND, c.starttime, c.endtime)) exec_time
FROM stl_query q
INNER JOIN svl_compile c   
   ON q.query = c.query
WHERE q.userid > 1  
AND q.label LIKE 'RSPERF TPC-H%'
GROUP BY q.pid
ORDER BY starttime ASC;

The following table shows our results.

We take a baseline measurement of the runtime on the third run, which ensures any additional compile time is excluded from the test. When we look at the test runs from midnight on June 26 (after ATO had made its changes), we can see the performance improvement.

Clean up

After you have viewed the test results and ATO changes, be sure to decommission your cluster to avoid having to pay for unused resources. Also, delete the IAM policy RedshiftTempCredPolicy and the IAM roles RedshiftCopyRole and RedshiftATOTestingRole.

Cloud DW benchmark derived from TPC-H 3 TB results

We ran the same test using the 3 TB version of the Cloud DW benchmark derived from TPC-H. It ran on a 10-node ra3.4xlarge cluster with query0.sql run every 30 minutes. The results of the test showed ATO achieved a significant increase in performance of up to 25%.

Summary

With automatic table optimization, Amazon Redshift has further increased its automation capabilities to cover query performance tuning in addition to administration tasks.

In this post, I explained how distribution and sort keys improve performance and how they’re automatically set by ATO. I showed how ATO increased performance by up to 24% on a 30 TB industry-standard benchmark with no manual tuning required. I also outlined the steps for setting up the same test yourself.

I encourage you to try out ATO by setting up an Amazon Redshift cluster and running the test, or enabling ATO on existing and new tables on your current cluster and monitoring the results.

About the Author

Adam Gatt is a Senior Specialist Solution Architect for Analytics at AWS. He has over 20 years of experience in data and data warehousing and helps customers build robust, scalable and high-performance analytics solutions in the cloud.

Post Syndicated from David Zhang original https://aws.amazon.com/blogs/big-data/get-started-with-the-amazon-redshift-data-api/

Amazon Redshift is a fast, scalable, secure, and fully managed cloud data warehouse that enables you to analyze your data at scale. Tens of thousands of customers use Amazon Redshift to process exabytes of data to power their analytical workloads.

The Amazon Redshift Data API is an Amazon Redshift feature that simplifies access to your Amazon Redshift data warehouse by removing the need to manage database drivers, connections, network configurations, data buffering, credentials, and more. You can run SQL statements using the AWS Software Development Kit (AWS SDK), which supports different languages such as C++, Go, Java, JavaScript, .Net, Node.js, PHP, Python, and Ruby.

With the Data API, you can programmatically access data in your Amazon Redshift cluster from different AWS services such as AWS Lambda, Amazon SageMaker notebooks, AWS Cloud9, and also your on-premises applications using the AWS SDK. This allows you to build cloud-native, containerized, serverless, web-based, and event-driven applications on the AWS Cloud.

The Data API also enables you to run analytical queries on Amazon Redshift’s native tables, external tables in your data lake via Amazon Redshift Spectrum, and also across Amazon Redshift clusters, which is known as data sharing. You can also perform federated queries with external data sources such as Amazon Aurora.

In an earlier, post, we shared in great detail on how you can use the Data API to interact with your Amazon Redshift data warehouse. In this post, we learn how to get started with the Data API in different languages and also discuss various use cases in which customers are using this to build modern applications combining modular, serverless, and event-driven architectures. The Data API was launched in September 2020, and thousands of our customers are already using it for a variety of use cases:

• Extract, transform, and load (ETL) orchestration with AWS Step Functions
• Access Amazon Redshift from applications
• Access Amazon Redshift from SageMaker Jupyter notebooks
• Access Amazon Redshift with REST endpoints
• Event-driven extract, load, transformation
• Event-driven web application design
• Serverless data processing workflows

Key features of the Data API

In this section, we discuss the key features of the Data API.

Use different programming language of your choice

The Data API integrates with the AWS SDK to run queries. Therefore, you can use any language supported by the AWS SDK to build your application with it, such as C++, Go, Java, JavaScript, .NET, Node.js, PHP, Python, and Ruby.

Run individual or batch SQL statements

With the Data API, you can run individual queries from your application or submit a batch of SQL statements within a transaction, which is useful to simplify your workload.

Run SQL statements with parameters

With the Data API, you can run parameterized SQL queries, which brings the ability to write reusable code when developing ETL code by passing parameters into a SQL template instead of concatenating parameters into each query on their own. This also makes it easier to migrate code from existing applications that needs parameterization. In addition, parameterization also makes code secure by eliminating malicious SQL injection.

No drivers needed

With the Data API, you can interact with Amazon Redshift without having to configure JDBC or ODBC drivers. The Data API eliminates the need for configuring drivers and managing database connections. You can run SQL commands to your Amazon Redshift cluster by calling a Data API secured API endpoint.

No network or security group setup

The Data API doesn’t need a persistent connection with Amazon Redshift. Instead, it provides a secure HTTP endpoint, which you can use to run SQL statements. Therefore, you don’t need to set up and manage a VPC, security groups, and related infrastructure to access Amazon Redshift with the Data API.

No password management

The Data API provides two options to provide credentials:

• IAM temporary credentials – With this approach, you only need to provide the username and AWS Identity and Access Management (IAM) GetClusterCredentials permission to access Amazon Redshift with no password. The Data API automatically handles retrieving your credentials using IAM temporary credentials.
• AWS Secrets Manager secret – With this approach, you store your username and password credentials in an AWS Secrets Manager secret and allow the Data API to access those credentials from that secret.

You can also use the Data API when working with federated logins through IAM credentials. You don’t have to pass database credentials via API calls when using identity providers such as Okta, Azure Active Directory, or database credentials stored in Secrets Manager. If you’re using Lambda, the Data API provides a secure way to access your database without the additional overhead of launching Lambda functions in Amazon Virtual Private Cloud (Amazon VPC).

Asynchronous

The Data API is asynchronous. You can run long-running queries without having to wait for it to complete, which is key in developing a serverless, microservices-based architecture. Each query results in a query ID, and you can use this ID to check the status and response of the query. In addition, query results are stored for 24 hours.

Event notifications

You can monitor Data API events in Amazon EventBridge, which delivers a stream of real-time data from your source application to targets such as Lambda. This option is available when you’re running your SQL statements in the Data API using the WithEvent parameter set to true. When a query is complete, the Data API can automatically send event notifications to EventBridge, which you may use to take further actions. This helps you design event-driven applications with Amazon Redshift. For more information, see Monitoring events for the Amazon Redshift Data API in Amazon EventBridge.

Get started with the Data API

It’s easy to get started with the Data API using the AWS SDK. You can use the Data API to run your queries on Amazon Redshift using different languages such as C++, Go, Java, JavaScript, .Net, Node.js, PHP, Python and Ruby.

All API calls from different programming languages follow similar parameter signatures. For instance, you can run the ExecuteStatement API to run individual SQL statements in the AWS Command Line Interface (AWS CLI) or different languages such as Python and JavaScript (NodeJS).

The following code is an example using the AWS CLI:

aws redshift-data execute-statement
--cluster-identifier my-redshift-cluster
--database dev
--db-user awsuser
--sql 'select data from redshift_table’;

The following example code uses Python:

boto3.client("redshift-data").execute_statement(
	ClusterIdentifier = ‘my-redshift-cluster’,
	Database = ‘dev’,
	DbUser = ‘awsuser’,
	Sql = 'select data from redshift_table’)

The following code uses JavaScript (NodeJS):

new AWS.RedshiftData().executeStatement({
	ClusterIdentifier: 'my-redshift-cluster',
	Database: 'dev',
	DbUser: 'awsuser',
	Sql: 'select data from redshift_table'
}).promise();

We have also published a GitHub repository showcasing how to get started with the Data API in different languages such as Go, Java, JavaScript, Python, and TypeScript. You may go through the step-by-step process explained in the repository to build your custom application in all these languages using the Data API.

Use cases with the Data API

You can use the Data API to modernize and simplify your application architectures by creating modular, serverless, event-driven applications with Amazon Redshift. In this section, we discuss some common use cases.

ETL orchestration with Step Functions

When performing ETL workflows, you have to complete a number of steps. As your business scales, the steps and dependencies often become complex and difficult to manage. With the Data API and Step Functions, you can easily orchestrate complex ETL workflows. You can explore the following example use case and AWS CloudFormation template demonstrating ETL orchestration using the Data API and Step Functions.

Access Amazon Redshift from custom applications

If you’re designing your custom application in any programming language that is supported by the AWS SDK, the Data API simplifies data access from your applications, which may be an application hosted on Amazon Elastic Compute Cloud (Amazon EC2) or Amazon Elastic Container Service (Amazon ECS) and other compute services or a serverless application built with Lambda. You can explore an example use case and CloudFormation template showcasing how to easily work with the Data API from Amazon EC2 based applications.

Access Amazon Redshift from SageMaker Jupyter notebooks

SageMaker notebooks are very popular among the data science community to analyze and solve machine learning problems. The Data API makes it easy to access and visualize data from your Amazon Redshift data warehouse without troubleshooting issues on password management or VPC or network issues. You can learn more about this use case along with a CloudFormation template showcasing how to use the Data API to interact from a SageMaker Jupyter notebook.

Access Amazon Redshift with REST endpoints

With the AWS SDK, you can use the Data APIs to directly invoke them as REST API calls such as GET or POST methods. For more information, see REST for Redshift Data API.

Event-driven ELT

Event–driven applications are popular with many customers, where applications run in response to events. A primary benefit of this architecture is the decoupling of producer and consumer processes, which allows greater flexibility in application design and building decoupled processes. For more information, see Building an event-driven application with AWS Lambda and the Amazon Redshift Data API. The following CloudFormation template demonstrates the same.

Event-driven web application design

Similar to event-driven ELT applications, event-driven web applications are also becoming popular, especially if you want to avoid long-running database queries, which create bottlenecks for the application servers. For example, you may be running a web application that has a long-running database query taking a minute to complete. Instead of designing that web application with long-running API calls, you can use the Data API and Amazon API Gateway WebSockets, which creates a lightweight websocket connection with the browser and submits the query to Amazon Redshift using the Data API. When the query is finished, the Data API sends a notification to EventBridge about its completion. When the data is available in the Data API, it’s pushed back to this browser session and the end-user can view the dataset. You can explore an example use case along with a CloudFormation template showcasing how to build an event-driven web application using the Data API and API Gateway WebSockets.

Serverless data processing workflows

With the Data API, you can design a serverless data processing workflow, where you can design an end-to-end data processing pipeline orchestrated using serverless AWS components such as Lambda, EventBridge, and the Data API client. Typically, a data pipeline involves multiple steps, for example:

1. Load raw sales and customer data to a data warehouse.
2. Integrate and transform the raw data.
3. Build summary tables or unload this data to a data lake so subsequent steps can consume this data.

The example use case Serverless Data Processing Workflow using Amazon Redshift Data Api demonstrates how to chain multiple Lambda functions in a decoupled fashion and build an end-to-end data pipeline. In that code sample, a Lambda function is run through a scheduled event that loads raw data from Amazon Simple Storage Service (Amazon S3) to Amazon Redshift. On its completion, the Data API generates an event that triggers an event rule in EventBridge to invoke another Lambda function that prepares and transforms raw data. When that process is complete, it generates another event triggering a third EventBridge rule to invoke another Lambda function and unloads the data to Amazon S3. The Data API enables you to chain this multi-step data pipeline in a decoupled fashion.

Conclusion

The Data API offers many additional benefits when integrating Amazon Redshift into your analytical workload. The Data API simplifies and modernizes current analytical workflows and custom applications. You can perform long-running queries without having to pause your application for the queries to complete. This enables you to build event-driven applications as well as fully serverless ETL pipelines.

The Data API functionalities are available in many different programming languages to suit your environment. As mentioned earlier, there are a wide variety of use cases and possibilities where you can use the Data API to improve your analytical workflow. To learn more, see Using the Amazon Redshift Data API.

About the Authors

David Zhang is an AWS Solutions Architect who helps customers design robust, scalable, and data-driven solutions across multiple industries. With a background in software engineering, David is an active leader and contributor to AWS open-source initiatives. He is passionate about solving real-world business problems and continuously strives to work from the customer’s perspective.

Bipin Pandey is a Data Architect at AWS. He loves to build data lake and analytics platform for his customers. He is passionate about automating and simplifying customer problems with the use of cloud solutions.

Manash Deb is a Senior Analytics Specialist Solutions Architect at AWS. He has worked on building end-to-end data-driven solutions in different database and data warehousing technologies for over 15 years. He loves to learn new technologies and solving, automating, and simplifying customer problems with easy-to-use cloud data solutions on AWS.

Bhanu Pittampally is Analytics Specialist Solutions Architect based out of Dallas. He specializes in building analytical solutions. His background is in data warehouse – architecture, development and administration. He is in data and analytical field for over 13 years. His Linkedin profile is here.

Chao Duan is a software development manager at Amazon Redshift, where he leads the development team focusing on enabling self-maintenance and self-tuning with comprehensive monitoring for Redshift. Chao is passionate about building high-availability, high-performance, and cost-effective database to empower customers with data-driven decision making.

Debu Panda, a Principal Product Manager at AWS, is an industry leader in analytics, application platform, and database technologies, and has more than 25 years of experience in the IT world.

Post Syndicated from Raj Sett original https://aws.amazon.com/blogs/big-data/benchmarking-the-performance-of-the-new-auto-wlm-with-adaptive-concurrency-in-amazon-redshift/

With Amazon Redshift, you can run a complex mix of workloads on your data warehouse clusters. For example, frequent data loads run alongside business-critical dashboard queries and complex transformation jobs. We also see more and more data science and machine learning (ML) workloads. Each workload type has different resource needs and different service level agreements. How does Amazon Redshift give you a consistent experience for each of your workloads? Amazon Redshift workload management (WLM) helps you maximize query throughput and get consistent performance for the most demanding analytics workloads, all while optimally using the resources of your existing cluster.

Amazon Redshift has recently made significant improvements to automatic WLM (Auto WLM) to optimize performance for the most demanding analytics workloads. With the release of Amazon Redshift Auto WLM with adaptive concurrency, Amazon Redshift can now dynamically predict and allocate the amount of memory to queries needed to run optimally. Amazon Redshift dynamically schedules queries for best performance based on their run characteristics to maximize cluster resource utilization.

In this post, we discuss what’s new with WLM and the benefits of adaptive concurrency in a typical environment. We synthesized a mixed read/write workload based on TPC-H to show the performance characteristics of a workload with a highly tuned manual WLM configuration versus one with Auto WLM. In this experiment, Auto WLM configuration outperformed manual configuration by a great margin. From a throughput standpoint (queries per hour), Auto WLM was 15% better than the manual workload configuration. Overall, we observed 26% lower average response times (runtime + queue wait) with Auto WLM.

What’s new with Amazon Redshift WLM?

Workload management allows you to route queries to a set of defined queues to manage the concurrency and resource utilization of the cluster. Today, Amazon Redshift has both automatic and manual configuration types. With manual WLM configurations, you’re responsible for defining the amount of memory allocated to each queue and the maximum number of queries, each of which gets a fraction of that memory, which can run in each of their queues. Manual WLM configurations don’t adapt to changes in your workload and require an intimate knowledge of your queries’ resource utilization to get right. Amazon Redshift Auto WLM doesn’t require you to define the memory utilization or concurrency for queues. Auto WLM adjusts the concurrency dynamically to optimize for throughput. Optionally, you can define queue priorities in order to provide queries preferential resource allocation based on your business priority.

Auto WLM also provides powerful tools to let you manage your workload. Query priorities lets you define priorities for workloads so they can get preferential treatment in Amazon Redshift, including more resources during busy times for consistent query performance, and query monitoring rules offer ways to manage unexpected situations like detecting and preventing runaway or expensive queries from consuming system resources.

Our initial release of Auto WLM in 2019 greatly improved the out-of-the-box experience and throughput for the majority of customers. However, in a small number of situations, some customers with highly demanding workloads had developed highly tuned manual WLM configurations for which Auto WLM didn’t demonstrate a significant improvement. Over the past 12 months, we worked closely with those customers to enhance Auto WLM technology with the goal of improving performance beyond the highly tuned manual configuration. One of our main innovations is adaptive concurrency. With adaptive concurrency, Amazon Redshift uses ML to predict and assign memory to the queries on demand, which improves the overall throughput of the system by maximizing resource utilization and reducing waste.

Electronic Arts, Inc. is a global leader in digital interactive entertainment. EA develops and delivers games, content, and online services for internet-connected consoles, mobile devices, and personal computers. EA has more than 300 million registered players around the world. Electronic Arts uses Amazon Redshift to gather player insights and has immediately benefited from the new Amazon Redshift Auto WLM.

“By adopting Auto WLM, our Amazon Redshift cluster throughput increased by at least 15% on the same hardware footprint. Our average concurrency increased by 20%, allowing approximately 15,000 more queries per week now. All this with marginal impact to the rest of the query buckets or customers. Because Auto WLM removed hard walled resource partitions, we realized higher throughput during peak periods, delivering data sooner to our game studios.”

– Alex Ignatius, Director of Analytics Engineering and Architecture for the EA Digital Platform.

Benefits of Amazon Redshift Auto WLM with adaptive concurrency

Amazon Redshift has implemented an advanced ML predictor to predict the resource utilization and runtime for each query. The model continuously receives feedback about prediction accuracy and adapts for future runs. Higher prediction accuracy means resources are allocated based on query needs. This allows for higher concurrency of light queries and more resources for intensive queries. The latter leads to improved query and cluster performance because less temporary data is written to storage during a complex query’s processing. A unit of concurrency (slot) is created on the fly by the predictor with the estimated amount of memory required, and the query is scheduled to run. If you have a backlog of queued queries, you can reorder them across queues to minimize the queue time of short, less resource-intensive queries while also ensuring that long-running queries aren’t being starved. We also make sure that queries across WLM queues are scheduled to run both fairly and based on their priorities.

The following are key areas of Auto WLM with adaptive concurrency performance improvements:

• Proper allocation of memory – Reduction of over-allocation of memory creates more room for other queries to run and increases concurrency. Additionally, reduction of under-allocation reduces spill to disk and therefore improves query performance.
• Elimination of static partitioning of memory between queues – This frees up the entire available memory, which is then available for queries.
• Improved throughput – You can pack more queries into the system due to more efficient memory utilization.

The following diagram shows how a query moves through the Amazon Redshift query run path to take advantage of the improvements of Auto WLM with adaptive concurrency.

Benchmark test

To assess the efficiency of Auto WLM, we designed the following benchmark test. It’s a synthetic read/write mixed workload using TPC-H 3T and TPC-H 100 GB datasets to mimic real-world workloads like ad hoc queries for business analysis.

In this modified benchmark test, the set of 22 TPC-H queries was broken down into three categories based on the run timings. The shortest queries were categorized as DASHBOARD, medium ones as REPORT, and longest-running queries were marked as the DATASCIENCE group. The DASHBOARD queries were pointed to a smaller TPC-H 100 GB dataset to mimic a datamart set of tables. The COPY jobs were to load a TPC-H 100 GB dataset on top of the existing TPC-H 3 T dataset tables. The REPORT and DATASCIENCE queries were ran against the larger TPC-H 3 T dataset as if those were ad hoc and analyst-generated workloads against a larger dataset. Also, the TPC-H 3 T dataset was constantly getting larger through the hourly COPY jobs as if extract, transform, and load (ETL) was running against this dataset.

The following table summarizes the synthesized workload components.

The following table summarizes the manual and Auto WLM configurations we used.

We ran the benchmark test using two 8-node ra3.4xlarge instances, one for each configuration. The same exact workload ran on both clusters for 12 hours.

Summary of results

We noted that manual and Auto WLM had similar response times for COPY, but Auto WLM made a significant boost to the DATASCIENCE, REPORT, and DASHBOARD query response times, which resulted in a high throughput for DASHBOARD queries (frequent short queries).

Given the same controlled environment (cluster, dataset, queries, concurrency), Auto WLM with adaptive concurrency managed the workload more efficiently and provided higher throughput than the manual WLM configuration. Better and efficient memory management enabled Auto WLM with adaptive concurrency to improve the overall throughput. Elimination of the static memory partition created an opportunity for higher parallelism. More short queries were processed though Auto WLM, whereas longer-running queries had similar throughput. To optimize the overall throughput, adaptive concurrency control kept the number of longer-running queries at the same level but allowed more short-running queries to run in parallel.

Detailed results

In this section, we review the results in more detail.

Throughput and average response times

The following table summarizes the throughput and average response times, over a runtime of 12 hours. Response time is runtime + queue wait time.

The following chart shows the throughput (queries per hour) gain (automatic throughput) over manual (higher is better).

The following chart shows the average response time of each query (lower is better).

Bucket by query completion times

The following results data shows a clear shift towards left for Auto WLM. More and more queries completed in a shorter amount of time with Auto WLM.

The following chart visualizes these results.

Query latency and count over time

As we can see from the following charts, Auto WLM significantly reduces the queue wait times on the cluster.

The following chart shows the count of queries processed per hour (higher is better).

The following chart shows the count of queued queries (lower is better).

The following chart shows the total queue wait time per hour (lower is better).

Temporary data spill to disk

Because it correctly estimated the query runtime memory requirements, Auto WLM configuration was able to reduce the runtime spill of temporary blocks to disk. Basically, a larger portion of the queries had enough memory while running that those queries didn’t have to write temporary blocks to disk, which is good thing. This in turn improves query performance.

The following chart shows that DASHBOARD queries had no spill, and COPY queries had a little spill.

Auto WLM outperforms the manual configuration

Based on these tests, Auto WLM was a better choice than manual configuration. If we look at the three main aspects where Auto WLM provides greater benefits, a mixed workload (manual WLM with multiple queues) reaps the most benefits using Auto WLM. The majority of the large data warehouse workloads consists of a well-defined mixture of short, medium, and long queries, with some ETL process on top of it. So large data warehouse systems have multiple queues to streamline the resources for those specific workloads. Also, overlap of these workloads can occur throughout a typical day. If the Amazon Redshift cluster has a good mixture of workloads and they don’t overlap with each other 100% of the time, Auto WLM can use those underutilized resources and provide better performance for other queues.

Conclusion

Our test demonstrated that Auto WLM with adaptive concurrency outperforms well-tuned manual WLM for mixed workloads. If you’re using manual WLM with your Amazon Redshift clusters, we recommend using Auto WLM to take advantage of its benefits. Moreover, Auto WLM provides the query priorities feature, which aligns the workload schedule with your business-critical needs.

For more information about Auto WLM, see Implementing automatic WLM and the definition and workload scripts for the benchmark.

About the Authors

Raj Sett is a Database Engineer at Amazon Redshift. He is passionate about optimizing workload and collaborating with customers to get the best out of Redshift. Outside of work, he loves to drive and explore new places.

Paul Lappas is a Principal Product Manager at Amazon Redshift. Paul is passionate about helping customers leverage their data to gain insights and make critical business decisions. In his spare time Paul enjoys playing tennis, cooking, and spending time with his wife and two boys.

Gaurav Saxena is a software engineer on the Amazon Redshift query processing team. He works on several aspects of workload management and performance improvements for Amazon Redshift. In his spare time, he loves to play games on his PlayStation.

Mohammad Rezaur Rahman is a software engineer on the Amazon Redshift query processing team. He focuses on workload management and query scheduling. In his spare time, he loves to spend time outdoor with family.