Find out how to consider the advantages of AQUA to your Amazon Redshift workloads

Amazon Redshift is the cloud information warehouse of selection for tens of 1000’s of shoppers, who use it to investigate exabytes of information to achieve enterprise insights. Clients have been asking us for higher efficiency at scale as the quantity, selection, velocity, and veracity of their information grows. We now have added a number of options to Amazon Redshift that allow you to stand up to thrice higher value efficiency with Amazon Redshift than different cloud information warehouses.

As well as, we launched AQUA (Superior Question Accelerator) for Amazon Redshift that can assist you cost-effectively run analytics on the new scale of information. AQUA is a distributed and hardware-accelerated cache that allows Amazon Redshift to run an order of magnitude sooner than different enterprise cloud information warehouses by mechanically boosting sure sorts of queries. AQUA makes use of AWS-designed processors with AWS Nitro chips tailored to hurry up information encryption and compression, and customized analytics processors, carried out in FPGAs, to speed up operations resembling scans, filtering, and aggregation. AQUA is on the market with the RA3.16xlarge, RA3.4xlarge, and RA3.xlplus nodes at no further cost and requires no code modifications. You’ll be able to allow AQUA to your current Amazon Redshift RA3 clusters or launch a brand new AQUA-enabled RA3 cluster by way of the AWS Administration Console, API, or AWS Command Line Interface (AWS CLI). To study extra about AQUA, see Working with AQUA (Superior Question Accelerator).

This submit walks you thru steps to quantify anticipated advantages from AQUA for Amazon Redshift to your workloads. We cowl the methodology used for testing AQUA and share the scripts, a pattern dataset, and queries so you possibly can take a look at AQUA in your individual surroundings. We now have printed scripts on GitHub (together with a README file), which you want as you observe the steps on this submit. Obtain all scripts to your working listing from the place you plan to hook up with your Redshift clusters.

Answer overview

At a high-level, the AQUA take a look at course of entails the next steps:

1. Create a take a look at cluster to judge AQUA and guarantee that AQUA is turned on. For directions, see Working with AQUA (Superior Question Accelerator).
2. Analyze your workload for AQUA and seize eligible queries. To study extra in regards to the sorts of queries accelerated by AQUA, discuss with When does Amazon Redshift use AQUA to run queries?
3. Run the workload with AQUA activated and deactivated in your take a look at cluster.
4. Evaluate efficiency outcomes.

This method is a simple but sensible option to measure the efficiency achieve you possibly can count on to get to your workload by turning on AQUA.

Except one workload parsing and analyzing script (aqua_capture_query.sh), you possibly can run the remainder of the scripts on a take a look at cluster. We suggest utilizing a take a look at cluster to attenuate the affect to your manufacturing cluster.

Create a take a look at cluster

Create a snapshot from a manufacturing cluster that has read-heavy workloads and restore it as take a look at cluster.

Analyze your workload and seize eligible queries

Run aqua_capture_query.sh on the manufacturing cluster to find out the queries appropriate for acceleration by AQUA. We suggest selecting workloads with SELECT queries that use LIKE or SIMILAR TO features within the WHERE clause and scan, filter, and mixture giant datasets. aqua_capture_query.sh scans the question historical past in your manufacturing cluster and captures queries that AQUA can speed up.

The script runtime might exceed a number of minutes relying on the chosen time interval (analyze_starttime and analyze_endtime), measurement of the cluster, and workload complexity. We propose proscribing the script runtime by limiting the time interval to the lesser of your workload runtime or 3 hours. Alternately, you possibly can consider AQUA utilizing the Amazon Critiques pattern dataset, which we reveal later on this submit.

aqua_capture_query.sh saves an output file named aqua_eligible_queries to your working listing and accommodates a subset of best suited AQUA-eligible queries.

The next is a pattern output by the script:

choose rely(*) from amazon_reviews the place product_title SIMILAR TO '%lap%' group by star_rating ORDER BY star_rating desc;
choose rely(*) from amazon_reviews the place product_title ilike '%e%|%E%' or customer_ID like '3percent__percent45__3';

In case your workload historical past doesn’t have sufficient AQUA-eligible queries, the script experiences no eligible queries discovered. If this occurs, run the script with totally different date/time parameters. For those who nonetheless don’t see any queries, you possibly can strive utilizing the pattern dataset and queries offered on this submit. For extra details about AQUA-eligible queries, discuss with When does Amazon Redshift use AQUA to run queries?

Run the workload in your take a look at cluster

Run aqua_execute_query.sh in your take a look at cluster. The script runs the captured queries in your cluster repeatedly to get constant efficiency by lowering the affect of runtime distinction resulting from environmental elements with AQUA activated and deactivated. The script data the beginning date/time and finish date/time to a file named workload_datetime.txt in your working listing.

Evaluate efficiency outcomes

When aqua_execute_query.sh script is full, run aqua_perf_compare.sh, which generates a CSV file named aqua_benefit in your working listing. The next desk summarizes the pattern output.

This desk reveals the question identifiers and runtime of the queries with AQUA activated and deactivated. You’ll be able to examine the advantages supplied by AQUA by reviewing the speedup column.

Instance use case with the Amazon Critiques dataset

To check AQUA with the Amazon Critiques pattern dataset, carry out the next steps:

1. Create a two-node RA3.4xlarge cluster by issuing the next command:

   aws redshift create-cluster --cluster-identifier test-amazon-reviews --node-type ra3.4xlarge --number-of-nodes 2 --master-username adminuser --master-user-password <xxpasswordxx> --aqua-configuration-status enabled

2. Create a take a look at database on our Amazon Redshift cluster by issuing the next command:
3. Load the desk with the Amazon Critiques dataset by operating the script load_amazon_sentiments_data.sql.
4. Run a number of AQUA-eligible queries (much like the next) a number of instances with and with out AQUA activated utilizing the script execute_test_queries.sh:

   choose rely(*) from amazon_reviews WHERE product_title SIMILAR TO '%lap%' or product_title SIMILAR TO '%hope%' or product_title SIMILAR TO '%good%' or product_title SIMILAR TO '%soa%';

Extra analysis queries can be found on the GitHub repo.

5. Run aqua_perf_compare.sh on the take a look at cluster.

The next desk reveals that AQUA accelerated the queries 5–22 instances sooner.

Abstract

This submit gives an easy but sensible option to measure the efficiency achieve you possibly can count on to get to your workload by turning on AQUA. You should utilize the scripts offered and take a look at with your individual dataset and queries, or use the pattern dataset and queries to find out AQUA’s affect.

We proceed to speculate and launch new capabilities like AQUA for Amazon Redshift to verify Amazon Redshift continues to enhance as your information warehouse wants develop. For AQUA, we proceed so as to add {hardware} acceleration for extra SQL operators, features, predicates, information varieties, and file codecs so extra scans, filters, and aggregations might be pushed all the way down to AQUA. Pushdowns to AQUA stay clear so Amazon Redshift decides when to push queries all the way down to AQUA to benefit from {hardware} acceleration. And when queries don’t get pushed all the way down to AQUA, they proceed to run on Amazon Redshift as earlier than.

We invite you to check AQUA for your self and share the findings.

In regards to the Authors

Dinesh Kumar is a Database Engineer at AWS specializing in Amazon AQUA. He works with clients to construct extremely scalable information warehouse and excessive performant database options. Exterior work, he enjoys gardening and spending time together with his household.

Manan Goel is a Product Go-To-Market Chief for AWS Analytics Companies together with Amazon Redshift & AQUA at AWS. He has greater than 25 years of expertise and is nicely versed with databases, information warehousing, enterprise intelligence, and analytics. Manan holds a MBA from Duke College and a BS in Electronics & Communications engineering.