Use AQUA with Amazon Redshift RA3.xlplus nodes

Amazon Redshift RA3 is the most recent technology node kind that permits you to scale compute and storage on your knowledge warehouses independently. The RA3 node household contains RA3.16xlarge, RA3.4xlarge, and RA3.xlplus nodes for big, medium, and small workloads, respectively. RA3.xlplus, the most recent member of the RA3 node household, gives one third of the computing energy of RA3.4xlarge and prices one third of the worth. RA3.xlplus is the smallest node within the RA3 household, however it gives the identical superior functionalities. It has been broadly utilized in environments with mild computing demand corresponding to QA, knowledge analytics for small groups, or processing smaller datasets.

In 2021, Amazon Redshift launched AQUA (Superior Question Accelerator) for Amazon Redshift to spice up efficiency of analytical queries that scan, filter, and mixture massive datasets. AQUA makes use of AWS-designed processors with the AWS Nitro chip adapter to hurry up knowledge encryption and compression, and customized analytical processors carried out in FPGAs to speed up purposes requiring textual content search of a really massive dataset, corresponding to advertising and marketing and personalization.

Clients have requested us to help AQUA for RA3.xlplus, and we just lately launched AQUA for RA3.xlplus nodes. On this publish, we proceed to construct on the publish AQUA (Superior Question Accelerator) – A Pace Enhance for Your Amazon Redshift Queries and present that with AQUA help, RA3.xlplus offers the identical profit as the prevailing supported RA3 nodes within the following areas:

• Routinely boosting sure forms of queries
• Lowering the impression in your Amazon Redshift cluster by offloading sure queries that scan, filter, and mixture massive datasets to AQUA

Check atmosphere

To check AQUA for RA3.xlplus, we began by creating an RA3.xlplus cluster with the next particulars:

• Amazon Redshift cluster – 2-node RA3.xlplus
• Dataset – 3 TB TPC-DS, 3 TB TPC-H
• Question set – Pattern queries primarily based on the TPC-H and TPC-DS workload

Pattern queries

To check AQUA, we created six textual content search queries that scan, filter, and mixture the lineitem desk within the TPC-H dataset, which has 18 billion rows with a WHERE clause predicate in opposition to the l_comment column.

The next desk summarizes our desk definition.

We randomly generated a question set with queries of assorted complexity. The queries are designed to measure scan value, that are an space of focus for AQUA. Every question has a predicate with LIKE and OR. The variety of LIKE or OR predicates will get progressively greater to simulate advanced workloads.

For instance, Question 1 has one OR predicate:

SELECT COUNT(l_orderkey)
FROM lineitem
WHERE (l_comment LIKE '%throughout%') OR (l_comment LIKE '%courageous,%');

In distinction, Question 4 has 50 OR predicates:

SELECT COUNT(l_orderkey)
  FROM lineitem
  WHERE (l_comment LIKE '%outsi%') OR
  (l_comment LIKE '%uthless%') OR
  (l_comment LIKE '%capades%') OR
  (l_comment LIKE '%horses%') OR
  (l_comment LIKE '%ornis%' AND l_comment LIKE '%phins?%') OR
  (l_comment LIKE '%affix%') OR
  (l_comment LIKE '%integrat%') OR
....
  (l_comment LIKE '%ithin%' AND l_comment LIKE '%quiet%') OR
  (l_comment LIKE '%taphs%') OR
  (l_comment LIKE '%dugouts%' AND l_comment LIKE '%ches%') OR
  (l_comment LIKE '%telets%' AND l_comment LIKE '%detect!%') OR
  (l_comment LIKE '%develop%') OR
  (l_comment LIKE '%promise!%') OR
  (l_comment LIKE '%was%') OR
  (l_comment LIKE '%accounts%') OR
  (l_comment LIKE '%idly%' AND l_comment LIKE '%deposits%') OR
  (l_comment LIKE '%combine!%' AND l_comment LIKE '%rely%') OR
  (l_comment LIKE '%ins%' AND l_comment LIKE '%makes use of!%') OR
  (l_comment LIKE '%epitaphs!%' AND l_comment LIKE '%breac%') OR
  (l_comment LIKE '%pliers%' AND l_comment LIKE '%phins%') OR
  (l_comment LIKE '%hogs%' AND l_comment LIKE '%sentiments%') OR
  (l_comment LIKE '%ctions%' AND l_comment LIKE '%daringly%') OR
  (l_comment LIKE '%ies%' AND l_comment LIKE '%esias%');

The next desk summarizes the complexity of every question.

Scan efficiency enchancment with AQUA

We ran the 4 queries sequentially with out another workload on the system. With AQUA, the efficiency enhancements vary from roughly 7–13 instances sooner, as summarized within the following desk.

AQUA impression on a number of workloads

On this atmosphere, we simulated a multi-user workflow utilizing TPC-DS queries on the Amazon Redshift cluster. We recorded question runtime for 3 situations:

• Baseline – We measured the end-to-end runtime working all TPC-DS queries serially on the Amazon Redshift cluster. On this state of affairs, AQUA was off and no extra workload was run (a single consumer was on the cluster).
• Baseline with extra workload – This was the identical because the baseline state of affairs with an extra workload run in parallel. We simulated a consumer load by working textual content scan queries randomly chosen from Question 1, Question 2 and Question 3. These queries have comparatively quick runtimes. We had two variations of this state of affairs:
  • AQUA turned off
  • AQUA turned on

From the outcomes, we noticed the next:

• With AQUA turned on for all workloads, the impression of a textual content scan question on the baseline runtime was negligible.
• With out AQUA, the baseline runtime was impacted by the extra workload created with textual content scan queries. In our case, overhead was about 31%.

Single-node RA3.xlplus help

AQUA additionally helps the just lately launched Amazon Redshift single-node RA3.xlplus. In a single-node configuration, the useful resource is shared amongst all Amazon Redshift operations, that are historically dealt with individually by a frontrunner node and compute nodes. A single-node configuration is usually utilized in a private or small group atmosphere for knowledge exploration.

We ran the identical set of queries as earlier than utilizing Question 1, 2 and Question 3. The outcomes demonstrated that AQUA offers the same degree of accelerations for these queries in a single-node atmosphere.

Abstract

On this publish, we ran a set of simulated efficiency assessments on the Amazon Redshift RA3.xlplus platform with AQUA. With AQUA on, RA3.xlplus offers the identical profit as earlier supported platforms. It offers a question scan efficiency increase with AQUA-supported operators, which is able to develop over time. It could cut back the efficiency impression of your current workflow by offloading the scan to AQUA.

We invite you to share your feedback and use circumstances with the Amazon Redshift AQUA workforce.

For extra details about how AQUA accelerates Amazon Redshift, see AQUA (Superior Question Accelerator) for Amazon Redshift.

For extra details about queries accelerated by AQUA, see When does Amazon Redshift use AQUA to run queries?

In regards to the Authors

Quan Li is a Senior Database Engineer at Amazon Redshift. His focus is enabling prospects to ship most enterprise worth. Quan is keen about optimizing high-performance analytical databases. Throughout his spare time, he enjoys touring and experiencing various kinds of cuisines together with his household.

Steffen Rochel is a Sr. Software program Improvement Supervisor at AWS. He’s centered on knowledge analytics acceleration. He has experience in hardware-software design and operation of large-scale, high-performance distributed methods.