Redshift Materialized view auto-refresh failing when cluster workload is high

Question

I have a materialized view with auto-refresh ON. However, it's not getting refreshed often enough. If I query SVL_MV_REFRESH_STATUS I see a lot of "Auto refresh aborted due to conflicting user workload" errors. I wonder if there is a way to increase the priority of the view refresh.?

Accepted Answer

Redshift always prioritizes user workload over the automated system initiated queries such as MV AUTO REFRESH. And if the user workload is high then it may abort the system initiated tasks. So in such cases the only workaround is to initiate a manual MV refresh (which will be now a user initiated query) to get the latest materialized view without the high workload error.

For more information please refer to this link,
     https://repost.aws/knowledge-center/redshift-materialized-view-not-refresh

[+]. https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-refresh-sql-command.html

[+].https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-refresh.html#materialized-view-auto-refresh

Hope this helps!

Answer

All auto tasks fall under the Autonomics umbrella. These autonomics tasks are automatically performed by Amazon Redshift when it sees available free resources. User submitted queries always take precedence over the background autonomics tasks. And background tasks are paused or stopped when user queries come in. Since you have identified that auto-refresh is not occurring as expected then best option is to manually include a refresh step in your workflow. Then this becomes a user query and Redshift will take the query to completion.

Redshift Materialized view auto-refresh failing when cluster workload is high

Contenido relevante