Long Running VACUUM DELETE Redshift

0

On a table with size ~30 TB, vacuum delete with ~ 70 millions rows deletion took 6 hours.

Is it normal? What can be done to decrease vacuum delete time ?

Thank you.

AWS
asked a year ago525 views
1 Answer
3

Hello,

Redshift automatically performs vacuum sort and vacuum delete operations on tables in the background implicitly. These background vacuums run during periods of reduced loads and are paused during periods of high load and that vacuum is not prioritize in comparison to other cluster workload. That is to say when there are high workload on the cluster the vacuum operation will pause to prioritize other workloads. Thus, it's common to see long running vacuum operation during cluster peak hours. So, I feel your case might be normal considering these factors.

Automatic vacuum operations pause if any of the following conditions are met:

  • A user runs a data definition language (DDL) operation, such as ALTER TABLE, that requires an exclusive lock on a table that automatic vacuum is currently working on.
  • A user triggers VACUUM on any table in the cluster (only one VACUUM can run at a time).
  • A period of high cluster load.

Furthermore, below factors may affect the performance of Redshift Auto Vacuum:

  • High percentage of unsorted data
  • Large table with too many columns
  • Interleaved sort key usage
  • Irregular or infrequent use of VACUUM
  • Concurrent tables, cluster queries, DDL statements, or ETL jobs

You can also override the WLM slot count when you have occasional queries that take a lot of resources in the cluster, such as when you perform a VACUUM operation in the database. see here for ref : https://docs.aws.amazon.com/redshift/latest/dg/tutorial-wlm-query-slot-count.html

To troubleshoot performance issues with VACUUM in Amazon Redshift please see our post: https://repost.aws/knowledge-center/redshift-vacuum-performance

profile pictureAWS
SUPPORT ENGINEER
answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions