DWHPro Insights

Customize Your Redshift Workload Management System for Optimal Performance

By Roland Wenzlofsky ·

Customize Your Redshift Workload Management System for Optimal Performance

The golden rules for Amazon Redshift optimization

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 eligible read and write workloads) or short query acceleration makes sense in your environment.

Use Column Encoding for compression

Consider compressing columns. This reduces the storage space and I/Os needed by queries.

Do not use Redshift for OLTP queries

Redshift 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.

Redshift KEY, EVEN, ALL and AUTO distribution have different trade-offs. DISTSTYLE ALL copies a table per compute node, not per slice; compare storage, load and join costs.

Maintenance of your Amazon Redshift statistics

If 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 regularly

Redshift performs automatic vacuum deletion and sorting; manual VACUUM can still help particular workloads. Automatic statistics, encoding and table optimization should also be considered.

Write better queries

Here 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:

https://aws.amazon.com/de/redshift/

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Practical data engineering insights, regularly

Join more than 3,500 data professionals: a regular newsletter with practical insights on data engineering, new data platforms (Snowflake, Databricks, Teradata) and AI.

Follow DWHPro in Google to see our articles more often in Search.

1 thought on “Customize Your Redshift Workload Management System for Optimal Performance”

  1. The advice in this post is substantially out of date.
    1. Use AutoWLM with query priorities
    2. Compression is applied automatically
    3. {correct!}
    4. Use DISTKEY if possible, use DISTSTYLE AUTO otherwise
    5. Redshift automatically runs ANALYZE in the background
    6. Redshift automatically runs VACUUM DELETE.
    7. {correct!}

    Reply

Leave a Comment

Get more practical data engineering advice

Join the DWHPro newsletter for practical data engineering tips, troubleshooting examples and data platform insights.

Protected by Security by CleanTalk and CleanTalk Anti-Spam