Wednesday, November 1, 2023
HomeBig DataAmazon Redshift: Cheaper price, increased efficiency

Amazon Redshift: Cheaper price, increased efficiency

Like nearly all clients, you wish to spend as little as doable whereas getting the absolute best efficiency. This implies you should take note of price-performance. With Amazon Redshift, you’ll be able to have your cake and eat it too! Amazon Redshift delivers as much as 4.9 occasions decrease price per person and as much as 7.9 occasions higher price-performance than different cloud information warehouses on real-world workloads utilizing superior methods like concurrency scaling to assist a whole lot of concurrent customers, enhanced string encoding for quicker question efficiency, and Amazon Redshift Serverless efficiency enhancements. Learn on to know why price-performance issues and the way Amazon Redshift price-performance is a measure of how a lot it prices to get a specific stage of workload efficiency, particularly efficiency ROI (return on funding).

As a result of each worth and efficiency enter into the price-performance calculation, there are two methods to consider price-performance. The primary method is to carry worth fixed: when you have $1 to spend, how a lot efficiency do you get out of your information warehouse? A database with higher price-performance will ship higher efficiency for every $1 spent. Subsequently, when holding worth fixed when evaluating two information warehouses that price the identical, the database with higher price-performance will run your queries quicker. The second method to take a look at price-performance is to carry efficiency fixed: if you happen to want your workload to complete in 10 minutes, what’s going to it price? A database with higher price-performance will run your workload in 10 minutes at a decrease price. Subsequently, when holding efficiency fixed when evaluating two information warehouses which might be sized to ship the identical efficiency, the database with higher price-performance will price much less and prevent cash.

Lastly, one other necessary facet of price-performance is predictability. Figuring out how a lot your information warehouse goes to price because the variety of information warehouse customers grows is essential for planning. It mustn’t solely ship the perfect price-performance right this moment, but additionally scale predictably and ship the perfect price-performance as extra customers and workloads are added. An excellent information warehouse ought to have linear scale—scaling your information warehouse to ship twice the question throughput ought to ideally price twice as a lot (or much less).

On this submit, we share efficiency outcomes as an example how Amazon Redshift delivers considerably higher price-performance in comparison with main various cloud information warehouses. Which means if you happen to spend the identical quantity on Amazon Redshift as you’d on one in every of these different information warehouses, you’re going to get higher efficiency with Amazon Redshift. Alternatively, if you happen to measurement your Redshift cluster to ship the identical efficiency, you will notice decrease prices in comparison with these options.

Value-performance for real-world workloads

You need to use Amazon Redshift to energy a really extensive variety of workloads, from batch-processing of advanced extract, remodel, and cargo (ETL)-based experiences, and real-time streaming analytics to low-latency enterprise intelligence (BI) dashboards that have to serve a whole lot and even 1000’s of customers on the similar time with subsecond response occasions, and every little thing in between. One of many methods we regularly enhance price-performance for our clients is to always evaluate the software program and {hardware} efficiency telemetry from the Redshift fleet, searching for alternatives and buyer use circumstances the place we are able to additional enhance Amazon Redshift efficiency.

Some latest examples of efficiency optimizations pushed by fleet telemetry embody:

  • String question optimizations – By analyzing how Amazon Redshift processed totally different information sorts within the Redshift fleet, we discovered that optimizing string-heavy queries would convey vital profit to our clients’ workloads. (We focus on this in additional element later on this submit.)
  • Automated materialized views – We discovered that Amazon Redshift clients usually run many queries which have widespread subquery patterns. For instance, a number of totally different queries could be a part of the identical three tables utilizing the identical be a part of situation. Amazon Redshift is now capable of mechanically create and keep materialized views after which transparently rewrite queries to make use of the materialized views utilizing the machine-learned automated materialized view autonomics characteristic in Amazon Redshift. When enabled, automated materialized views can transparently improve question efficiency for repetitive queries with none person intervention. (Observe that automated materialized views weren’t utilized in any of the benchmark outcomes mentioned on this submit).
  • Excessive-concurrency workloads – A rising use case we see is utilizing Amazon Redshift to serve dashboard-like workloads. These workloads are characterised by desired question response occasions of single-digit seconds or much less, with tens or a whole lot of concurrent customers operating queries concurrently with a spiky and sometimes unpredictable utilization sample. The prototypical instance of that is an Amazon Redshift-backed BI dashboard that has a spike in visitors Monday mornings when numerous customers begin their week.

Excessive-concurrency workloads specifically have very broad applicability: most information warehouse workloads function at concurrency, and it’s not unusual for a whole lot and even 1000’s of customers to run queries on Amazon Redshift on the similar time. Amazon Redshift was designed to maintain question response occasions predictable and quick. Redshift Serverless does this mechanically for you by including and eradicating compute as wanted to maintain question response occasions quick and predictable. This implies a Redshift Serverless-backed dashboard that masses rapidly when it’s being accessed by one or two customers will proceed to load rapidly even when many customers are loading it on the similar time.

To simulate the sort of workload, we used a benchmark derived from TPC-DS with a 100 GB information set. TPC-DS is an industry-standard benchmark that features a wide range of typical information warehouse queries. At this comparatively small scale of 100 GB, queries on this benchmark run on Redshift Serverless in a median of some seconds, which is consultant of what customers loading an interactive BI dashboard would count on. We ran between 1–200 concurrent exams of this benchmark, simulating between 1–200 customers attempting to load a dashboard on the similar time. We additionally repeated the check in opposition to a number of common various cloud information warehouses that additionally assist scaling out mechanically (if you happen to’re conversant in the submit Amazon Redshift continues its price-performance management, we didn’t embody Competitor A as a result of it doesn’t assist mechanically scaling up). We measured common question response time, that means how lengthy a person would wait for his or her queries to complete (or their dashboard to load). The outcomes are proven within the following chart.

Competitor B scales effectively till round 64 concurrent queries, at which level it’s unable to supply further compute and queries start to queue, resulting in elevated question response occasions. Though Competitor C is ready to scale mechanically, it scales to decrease question throughput than each Amazon Redshift and Competitor B and isn’t capable of preserve question runtimes low. As well as, it doesn’t assist queueing queries when it runs out of compute, which prevents it from scaling past round 128 concurrent customers. Submitting further queries past this are rejected by the system.

Right here, Redshift Serverless is ready to preserve the question response time comparatively constant at round 5 seconds even when a whole lot of customers are operating queries on the similar time. The typical question response occasions for Opponents B and C improve steadily as load on the warehouses will increase, which ends up in customers having to attend longer (as much as 16 seconds) for his or her queries to return when the information warehouse is busy. Which means if a person is attempting to refresh a dashboard (which can even submit a number of concurrent queries when reloaded), Amazon Redshift would be capable to preserve dashboard load occasions much more constant even when the dashboard is being loaded by tens or a whole lot of different customers on the similar time.

As a result of Amazon Redshift is ready to ship very excessive question throughput for brief queries (as we wrote about in Amazon Redshift continues its price-performance management), it’s additionally capable of deal with these increased concurrencies when scaling out extra effectively and due to this fact at a considerably decrease price. To quantify this, we have a look at the price-performance utilizing printed on-demand pricing for every of the warehouses within the previous check, proven within the following chart. It’s value noting that utilizing Reserved Situations (RIs), particularly 3-year RIs bought with the all upfront fee choice, has the bottom price to run Amazon Redshift on Provisioned clusters, leading to the perfect relative price-performance in comparison with on-demand or different RI choices.

So not solely is Amazon Redshift capable of ship higher efficiency at increased concurrencies, it’s ready to take action at considerably decrease price. Every information level within the price-performance chart is equal to the price to run the benchmark on the specified concurrency. As a result of the price-performance is linear, we are able to divide the price to run the benchmark at any concurrency by the concurrency (variety of Concurrent Customers on this chart) to inform us how a lot including every new person prices for this explicit benchmark.

The previous outcomes are easy to copy. All queries used within the benchmark can be found in our GitHub repository and efficiency is measured by launching an information warehouse, enabling Concurrency Scaling on Amazon Redshift (or the corresponding auto scaling characteristic on different warehouses), loading the information out of the field (no guide tuning or database-specific setup), after which operating a concurrent stream of queries at concurrencies from 1–200 in steps of 32 on every information warehouse. The identical GitHub repo references pregenerated (and unmodified) TPC-DS information in Amazon Easy Storage Service (Amazon S3) at varied scales utilizing the official TPC-DS information era package.

Optimizing string-heavy workloads

As talked about earlier, the Amazon Redshift crew is repeatedly searching for new alternatives to ship even higher price-performance for our clients. One enchancment we not too long ago launched that considerably improved efficiency is an optimization that accelerates the efficiency of queries over string information. For instance, you would possibly wish to discover the entire income generated from retail shops situated in New York Metropolis with a question like SELECT sum(worth) FROM gross sales WHERE metropolis = ‘New York’. This question is making use of a predicate over string information (metropolis = ‘New York’). As you’ll be able to think about, string information processing is ubiquitous in information warehouse purposes.

To quantify how usually clients’ workloads entry strings, we carried out an in depth evaluation of string information kind utilization utilizing fleet telemetry of tens of 1000’s of buyer clusters managed by Amazon Redshift. Our evaluation signifies that in 90% of the clusters, string columns represent no less than 30% of all of the columns, and in 50% of the clusters, string columns represent no less than 50% of all of the columns. Furthermore, a majority of all queries run on the Amazon Redshift cloud information warehouse platform entry no less than one string column. One other necessary issue is that string information may be very usually low cardinality, that means the columns include a comparatively small set of distinctive values. For instance, though an orders desk representing gross sales information could include billions of rows, an order_status column inside that desk would possibly include just a few distinctive values throughout these billions of rows, corresponding to pending, in course of, and accomplished.

As of this writing, most string columns in Amazon Redshift are compressed with LZO or ZSTD algorithms. These are good general-purpose compression algorithms, however they aren’t designed to benefit from low-cardinality string information. Particularly, they require that information be decompressed earlier than being operated on, and are much less environment friendly of their use of {hardware} reminiscence bandwidth. For low-cardinality information, there may be one other kind of encoding that may be extra optimum: BYTEDICT. This encoding makes use of a dictionary-encoding scheme that permits the database engine to function straight over compressed information with out the necessity to decompress it first.

To additional enhance price-performance for string-heavy workloads, Amazon Redshift is now introducing further efficiency enhancements that velocity up scans and predicate evaluations, over low-cardinality string columns which might be encoded as BYTEDICT, between 5–63 occasions quicker (see ends in the subsequent part) in comparison with various compression encodings corresponding to LZO or ZSTD. Amazon Redshift achieves this efficiency enchancment by vectorizing scans over light-weight, CPU-efficient, BYTEDICT-encoded, low-cardinality string columns. These string-processing optimizations make efficient use of reminiscence bandwidth afforded by fashionable {hardware}, enabling real-time analytics over string information. These newly launched efficiency capabilities are optimum for low-cardinality string columns (up to a couple hundred distinctive string values).

You’ll be able to mechanically profit from this new excessive efficiency string enhancement by enabling computerized desk optimization in your Amazon Redshift information warehouse. In case you don’t have computerized desk optimization enabled in your tables, you’ll be able to obtain suggestions from the Amazon Redshift Advisor within the Amazon Redshift console on a string column’s suitability for BYTEDICT encoding. You can too outline new tables which have low-cardinality string columns with BYTEDICT encoding. String enhancements in Amazon Redshift at the moment are obtainable in all AWS Areas the place Amazon Redshift is offered.

Efficiency outcomes

To measure the efficiency impression of our string enhancements, we generated a 10TB (Tera Byte) dataset that consisted of low-cardinality string information. We generated three variations of the information utilizing quick, medium, and lengthy strings, equivalent to the twenty fifth, fiftieth, and seventy fifth percentile of string lengths from Amazon Redshift fleet telemetry. We loaded this information into Amazon Redshift twice, encoding it in a single case utilizing LZO compression and in one other utilizing BYTEDICT compression. Lastly, we measured the efficiency of scan-heavy queries that return many rows (90% of the desk), a medium variety of rows (50% of the desk), and some rows (1% of the desk) over these low-cardinality string datasets. The efficiency outcomes are summarized within the following chart.

Queries with predicates that match a excessive share of rows noticed enhancements of 5–30 occasions with the brand new vectorized BYTEDICT encoding in comparison with LZO, whereas queries with predicates that match a low share of rows noticed enhancements of 10–63 occasions on this inside benchmark.

Redshift Serverless price-performance

Along with the high-concurrency efficiency outcomes introduced on this submit, we additionally used the TPC-DS-derived Cloud Knowledge Warehouse benchmark to check the price-performance of Redshift Serverless to different information warehouses utilizing a bigger 3TB dataset. We selected information warehouses that had been priced equally, on this case inside 10% of $32 per hour utilizing publicly obtainable on-demand pricing. These outcomes present that, like Amazon Redshift RA3 cases, Redshift Serverless delivers higher price-performance in comparison with different main cloud information warehouses. As all the time, these outcomes will be replicated by utilizing our SQL scripts in our GitHub repository.

We encourage you to strive Amazon Redshift utilizing your personal proof of idea workloads as one of the best ways to see how Amazon Redshift can meet your information analytics wants.

Discover the perfect price-performance to your workloads

The benchmarks used on this submit are derived from the industry-standard TPC-DS benchmark, and have the next traits:

  • The schema and information are used unmodified from TPC-DS.
  • The queries are generated utilizing the official TPC-DS package with question parameters generated utilizing the default random seed of the TPC-DS package. TPC-approved question variants are used for a warehouse if the warehouse doesn’t assist the SQL dialect of the default TPC-DS question.
  • The check consists of the 99 TPC-DS SELECT queries. It doesn’t embody upkeep and throughput steps.
  • For the only 3TB concurrency check, three energy runs had been run, and the perfect run is taken for every information warehouse.
  • Value-performance for the TPC-DS queries is calculated as price per hour (USD) occasions the benchmark runtime in hours, which is equal to the price to run the benchmark. The most recent printed on-demand pricing is used for all information warehouses and never Reserved Occasion pricing as famous earlier.

We name this the Cloud Knowledge Warehouse benchmark, and you’ll simply reproduce the previous benchmark outcomes utilizing the scripts, queries, and information obtainable in our GitHub repository. It’s derived from the TPC-DS benchmarks as described on this submit, and as such shouldn’t be similar to printed TPC-DS outcomes, as a result of the outcomes of our exams don’t adjust to the official specification.


Amazon Redshift is dedicated to delivering the {industry}’s finest price-performance for the widest number of workloads. Redshift Serverless scales linearly with the perfect (lowest) price-performance, supporting a whole lot of concurrent customers whereas sustaining constant question response occasions. Primarily based on check outcomes mentioned on this submit, Amazon Redshift has as much as 2.6 occasions higher price-performance on the similar stage of concurrency in comparison with the closest competitor (Competitor B). As talked about earlier, utilizing Reserved Situations with the 3-year all upfront choice provides you the bottom price to run Amazon Redshift, leading to even higher relative price-performance in comparison with on-demand occasion pricing that we used on this submit. Our method to steady efficiency enchancment entails a singular mixture of buyer obsession to know buyer use circumstances and their related scalability bottlenecks coupled with steady fleet information evaluation to determine alternatives to make vital efficiency optimizations.

Every workload has distinctive traits, so if you happen to’re simply getting began, a proof of idea is one of the best ways to know how Amazon Redshift can decrease your prices whereas delivering higher efficiency. When operating your personal proof of idea, it’s necessary to deal with the fitting metrics—question throughput (variety of queries per hour), response time, and price-performance. You may make a data-driven determination by operating a proof of idea by yourself or with help from AWS or a system integration and consulting associate.

To remain updated with the most recent developments in Amazon Redshift, comply with the What’s New in Amazon Redshift feed.

In regards to the authors

Stefan Gromoll is a Senior Efficiency Engineer with Amazon Redshift crew the place he’s answerable for measuring and bettering Redshift efficiency. In his spare time, he enjoys cooking, taking part in along with his three boys, and chopping firewood.

Ravi Animi is a Senior Product Administration chief within the Amazon Redshift crew and manages a number of purposeful areas of the Amazon Redshift cloud information warehouse service together with efficiency, spatial analytics, streaming ingestion and migration methods. He has expertise with relational databases, multi-dimensional databases, IoT applied sciences, storage and compute infrastructure companies and extra not too long ago as a startup founder utilizing AI/deep studying, laptop imaginative and prescient, and robotics.

Aamer Shah is a Senior Engineer within the Amazon Redshift Service crew.

Sanket Hase is a Software program Growth Supervisor within the Amazon Redshift Service crew.

Orestis Polychroniou is a Principal Engineer within the Amazon Redshift Service crew.



Please enter your comment!
Please enter your name here

Most Popular

Recent Comments