By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Redshift ANALYZE is not being executed automatically, even though threshold_percent is met

0

Good day,

I have a Redshift table with 13M rows and according to svv_table_info, it's 100% statsoff. I read the Redshift documentation, and as I understand analyze should be run automatically in the background. However, I can't find any information about if this needs to be enabled somehow (or if it can be accidentally disabled).

According to the STL_ANALYZE table, it seems that ANALYZE was executed once (maybe when I copied the original data?), but since then, never. In STL_ANALYZE it tells that analyze threshold is 90% (I also didn't find if this can be lowered somewhere), but since statoff is 100%, a high threshold shouldn't be a problem.

I know I can run analyze manually, but my question is, do I need to enable it somewhere for it to run automatically?

Thank you

Edit: auto_analyze in the parameter group is set to true

Topics
Analytics
Tags
Amazon Redshift
Language
English
Peter
asked 2 years ago528 views
2 Answers
2
Accepted Answer

There is a delay between the time in which a table needs to be analyzed and the time in which the auto analyze background process identifies this and executes the analyze command. This is because auto analyze is a background process.

Additionally, auto analyze doesn't compete with user activity so if your Redshift is busy, it will wait until there are enough free resources to execute in order to prevent interfering with users' activities.

If you are loading a lot of data with an INSERT statement in an ETL pipeline, then you may want to consider adding the ANALYZE command there. Users will get up to date statistics and when auto analyze does run, it will skip tables that don't need analyze to run.

More information here: https://docs.aws.amazon.com/redshift/latest/dg/t_Analyzing_tables.html

profile pictureAWS
EXPERT
Jon Roberts
answered 2 years ago
  • Peter
    2 years ago

    Thank you, as you advised I added running it manually at the end of the ETL pipeline.

1

You should check SVL_AUTO_WORKER_ACTION to see if you find status reported for this table.

profile pictureAWS
EXPERT
MilindOke
answered 2 years ago
  • Peter
    2 years ago

    Thanks, this shows that the cluster is indeed busy. In combination with the previous answer, my issue should be solved. Thank you both!

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

Relevant content