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, a copy of the source cluster is needed. 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, there is no 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.
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.
Trust the experts with the best knowledge of AWS DMS and the Schema Conversion Tool (SCT) to help move your on-premises databases to AWS RDS, Amazon Aurora, and Amazon Redshift.
Do you like what you see? Please contact us today to see how we can get started!
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.
Check out our blog posts that showcase our deep experience with mastering AWS Schema Conversion Tool to optimize Amazon Redshift storage.
Optimizing your Redshift Storage with AWS Schema Conversion Tool
Using Amazon Redshift performance tuning tips to reduce query times by 800%
Extracting Data from Warehouses with Data Migration Agents in AWS Schema Conversion Tool
Using AWS Schema Conversion Tool Extension Pack for Data Warehouses
Using data extraction filters for smart database migration with AWS Schema Conversion Tool