Customize Your Redshift Workload Management System for Optimal Performance
Customize Workload Management
Workload management in Redshift means optimizing the queues. This includes the correct selection of queues, the assignment of queries, and the allocation of memory.
Although automated workload management and concurrency scaling have greatly improved workload management, workload management is a critical point in ensuring a scalable system (and one of the weak points of Redshift)
Since Redshift Workload Management is primarily based on queuing queries, very unstable runtimes can be expected if misconfigured. Consider switching from manual WLM to automatic WLM, in which queues and their queries can be prioritized.
Consider whether concurrency scaling (for read-only workload) or short query acceleration makes sense in your environment.
Use Column Encoding for compressionConsider compressing columns. This reduces the storage space and I/Os needed by queries.
Do not use Redshift for OLTP queriesRedshift is entirely unsuitable for this kind of workload. Not only because it is a column store but also because no indexes can be used (as in Teradata columnar).
Choose the proper method to distribute the data.Use the DISTKEY to help Amazon Redshift join large tables.
Use EVEN if no suitable DISTKEY exists to avoid skew.
Copy lookups to all slices.
Maintenance of your Amazon Redshift statisticsIf the statistics are correct, memory will be reserved for the query plan created in the correct size.
If too much memory is reserved, the other queries in the same queue are missing and delayed.
If too little memory is reserved, it is possible that the memory must be buffered. The query will then be much slower.
Execute the ANALYZE command regularly.
Release space regularlyRow space in Amazon Redshift is not automatically freed after a DELETE or UPDATE, so this must be done manually. This is done with the VACUUM FULL command. Space is released, and SORTKEY sorts the rows. This is regularly necessary to maintain performance.
Write better queriesHere are two examples:
Since we are dealing with a column store, you should always select only the required columns to minimize the I/Os.
Also, restrict your queries with a WHERE condition whenever possible to minimize the scans.
Access the Amazon Redshift Documentation here:
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.
https://aws.amazon.com/de/redshift/
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe for free and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.