(ETL engine in the above could be AWS Glue)
There are various ways to define performance and what that means. A simple way to be consistent with management is so improve how fast dashboards load. This gives the ‘use case’ where you can mprove SELECT query performance with aggregation and some filtering by data time and some other frequently used fields. Proper performance testing and baselining is important when you are looking at cloud datawarehouse compute and speed on large datasets.
Long story short, you can either: 1) increase the resources or 2) try to limit how many blocks your data warehouse needs to scan/read to find the data you’re looking for. Of course, there are other ways and techniques, like caching the most frequent results, using more performance SQL functions, etc., but the two things mentioned above are the core principles that will make a significant impact.
In the AWS Documentation there is a High-level overview of Redshift architecture.
The block size depends on the database, but Amazon Redshift uses a block size of 1 MB. That’s a lot bigger compared to typical databases, which use block sizes up to 32 KB. According to Amazon, this is more efficient and further reduces the number of I/O requests needed to perform any database loading or other operations that are part of query execution. In addition to its data, each block also stores some meta information. For instance, the minimum and maximum values of a column in the block. This helps Redshift better understand which blocks it should read and which ones it can skip.
AWS documentation explaining Columnar storage is a good read.
Techniques which help to significantly improve performance.
It may not be the same for all cases, but those are pretty general recommendations you should follow. Let’s review them one by one.
Choosing the appropriate SORT key can lead to faster query times as large sections of data can be skipped while processing the query. This is especially relevant for queries with range-restricted predicates because it can reduce disk I/O by improving zone map effectiveness
While SORT keys define how data is sorted, the DIST key defines the distribution of rows to compute nodes. Optimizing the distribution also can reduce query time by minimizing the impact of the query optimizer’s redistribution step.
Using column encoding can reduce your storage footprint and improve query performance. If you don’t use compression, then data eventually consumes additional storage space, and when querying it, Redshift needs to read more blocks. Applying compression to large columns can not only reduce the storage but also significantly improve the performance of your cluster.
Amazon Redshift also has an automated Advisor that reviews storage metadata associated with large uncompressed columns that aren’t used as sort keys. It can give you useful suggestions on which compressions you should use for which columns, in addition to some other recommendations to improve performance and decrease operating costs. Just take everything there with a grain of salt.
Although RedShift has an Automatic WLM and Query Priorities queue mechanism, it’s possible to control it manually too. This may add some additional overhead but allows to have fine-grained workload groups and control concurrency scaling manually.
Concurrency scaling allows to dynamically expand cluster capacity in response to the workload. The concurrent scaling function in Amazon Redshift can extend the throughput of the cluster up to 10 times. The maximum number you can currently set is 10, but it’s a soft limit that you could ask to increase.
Another common and effective way to increase query performance is to pre-aggregate your data in data marts or create materialized views.
You can use dbt for managing models, and it doesn’t support Redshift materialized views. So instead of materialized views, create data marts.
In 2019, Amazon introduced RA3 nodes for Redshift. They use very large and performant SSDs for local caching, automatic fine-grained data eviction, and some smart data pre-fetching. The bottom line is that with RA3 nodes, you can separate compute and storage. What is nice about that separation is that you can scale your clusters according to your computing needs, not by how much data you need to store. For instance, you could even have an automated script that increases node count during peak hours and reduces node count over the night, or something similar.
Additionally, with RA3 nodes, you can leverage AQUA (Advanced Query Accelerator). Which can speed up certain queries with the LIKE or SIMILAR TO expression that AQUA supports. You can find more information in the Working with AQUA (Advanced Query Accelerator) documentation section.
Vacuuming and analyzing tables might help improve performance too, or rather, not make it worse after some time. In Redshift, two commands will help you to do that — VACUUM and ANALYZE:
Vacuum as often as you need to maintain consistent query performance. If you delay vacuuming, the vacuum will take longer because more data has to be reorganized. So it makes sense to run it more often. For instance, we chose to run it every day.
You don’t necessarily need to run the ANALYZE when you create a table first. But if you make any modifications, you should not forget to run the ANALYZE command to update table statistics. For simplicity, we chose to run ANALYZE every day together with VACUUM.
=END
