Typical Amazon Redshift Performance Tuning Challenges

Amazon Redshift has made great leaps forward in reducing some of the challenges that DBAs experience data warehouse maintenance. For example, there is no INDEX command, however, there are certain storage definitions which can make a big difference in the performance of your queries.

We were an early adopter of Amazon Redshift technologies, fostering close connections to the development team. Here is a sample of the challenges that we’ve seen with Redshift deployments, and how Amazon Redshift Performance Tuning can help.

Queries run slower as data grows

As your database grows over time, changes in data no longer match original data distribution style or sort keys settings.

At DB Best, we worked closely with Amazon as they developed the Redshift Optimization feature as part of the AWS Schema Conversion Tool (SCT). We can help your team understand root causes of performance issues and show how SCT can optimize storage based on your new data.

Choosing the right strategy

The Redshift optimization project can be considered a regular AWS Schema Conversion Tool migration project with the source and target pointing to the Amazon Redshift clusters. Should you use a copy of the source cluster as a target, or start the optimization project from scratch?

Official Amazon documentation does not recommend using the same cluster for both source and target for your optimization project. Thus, before beginning the optimization process, you need to create a copy of the source cluster. The essence of this method focuses on the creation of a source cluster snapshot followed by its restoration in another cluster.

Running the optimization project, we can choose one of two migration strategies:

  • Migration to a copy
    This ensures the target cluster includes original data from the source cluster. Therefore, you do not need to worry about migrating data into the optimization project.
  • Migration to a clean slate
    Choosing this option may be favorable when reviewing new optimization suggestions. Should you decide to accept them and use the newly created cluster, you will need to create the DMS task to migrate the data or use the Data Migration Agents.

Other performance considerations

Sometimes, the Redshift Optimization tool doesn't always address the problem with specific queries. What other aspects should be considered for performance tuning?

Like most databases, it’s all about understanding how data is stored and how the query optimizer processes the data. Here is a sample of other things to look for that SCT doesn’t address.

  • Tables missing statistics
  • Incorrect column encoding
  • Tables with very large VARCHAR columns
  • Blocked queries
  • Inefficient use of Temporary Tables

Our team can help identify these issues and troubleshoot them to keep your system running with the performance you expect.

Learn more about Amazon Redshift Performance Tuning
Contact us

Getting Started Offers

We have a variety of entry level offers to help you tune the performance of your Amazon Redshift storage. Here’s how you can improve the key optimization metrics and increase the queries execution speed.

Optimizing Redshift performance with AWS SCT

In November 2016 Amazon introduced a the new Redshift Optimization Feature in SCT . You can now use AWS SCT to optimize your Amazon Redshift databases.

This feature looks very interesting and promises to be a very helpful aid to DBAs needing to tune their Redshift. The AWS SCT provides recommendations based on the selection of Distribution Keys and Style, as well as the best choice for the Sort Keys.

Essentially, the Redshift optimization project can be considered a regular AWS SCT migration project with the source and target pointing to Redshift clusters. Here you have to make an important decision: whether to use a copy of the source cluster as a target, or start the optimization project from scratch.

In the following video, we will demonstrate the essentials of using the Redshift Optimization to improve the query performance.

Optimization Rules Selection in AWS SCT

Let's get together to talk about Amazon Redshift Performance Tuning
Contact us today

Learn more

Check out our blog posts that showcase our deep experience with mastering AWS Schema Conversion Tool to optimize Amazon Redshift storage.

Blog posts

Optimizing your Redshift Storage with AWS Schema Conversion Tool
2 March 2017 Bill Ramos

Amazon introduced the new feature called Redshift Optimization for the Schema Conversion Tool (SCT) November 17, 2016 release. You can now use AWS SCT to optimize your Amazon Redshift ...

Using Amazon Redshift performance tuning tips to reduce query times by 800%
14 December 2017 Bill Ramos

Often database administrators experience decreases in query performance with Amazon Redshift as the size of data increases. The problem becomes even more significant when we start talk...

Extracting Data from Warehouses with Data Migration Agents in AWS Schema Conversion Tool
15 March 2017 Bill Ramos

Migrating data warehouses to Amazon Redshift represents a serious challenges when you consider what it takes to transfer huge amounts of data in parallel. On February 16, 2017, Amazon ...

Using AWS Schema Conversion Tool Extension Pack for Data Warehouses
28 April 2017 admin

When migrating to Amazon Redshift, you need a way to convert functions and other capabilities that Oracle, Teradata, Greenplum, Netezza support that Redshift does not natively support....

Using data extraction filters for smart database migration with AWS Schema Conversion Tool
29 June 2017 Andrei Ivanov

Amazon introduced the data extraction agents in the previous release of AWS Schema Conversion Tool (SCT). They allowed for migrating all the data from your on-premises data warehouse p...

Let us help you with your project!
Contact us for a FREE quote today.
Request a quote