awslabs / awslabs/amazon-redshift-utils
Vacuum ReIndex is running repeatedly for certain tables
- Dominant language
- Python
- Stars
- 2.8k
- Forks
- 1.2k
- PR merge metrics
- No merged PRs in 30d
Description
I have used the script that was made available under the path
**amazon-redshift-utils/src/AnalyzeVacuumUtility/lib/analyze_vacuum.py**
As per my understanding we are mainly depending on interleaved_skew value from **svv_interleaved_columns** which is the ratio of skew present in interleaved sort columns(> 1.4)
I have run the script once and some **30 tables** in the schema have gone through **vacuum** **ReIndex** consuming 3 hours of time and that was good as it was for the first time I have run the vacuum ReIndex, But it was very unobvious and unfortunate that even when I ran the script for 2nd time with no new inserts or updates on the tables the script again picked the **same** **tables** and ran the vacuum ReIndex for 3 more hours..
I have gone through the log and found that the **interleaved_skew** value didn't go down after 1st vacuum ReIndex and so as the interleaved_skew is > 1.4 they are being picked again and vacuum ReIndex ran against them.
Is this an expected behavior or are we fooling ourselves by using interleaved_skew as the value of consideration?
Do we have any other stat that we could rely on to decide if a table should go through vacuum ReIndex other than **interleaved_skew**?
And I have tried to know about **svv_interleaved_counts** and found probably nothing..
It would help if there is little explanation on it.
Contributor guide
Research direction
Start with amazon-redshift-utils/src/AnalyzeVacuumUtility/lib/analyze_vacuum.py and trace how svv_interleaved_columns and interleaved_skew select tables for VACUUM REINDEX. Compare the first and second runs described in the issue and review svv_interleaved_counts; done should be a clear explanation of the repeated selection and any supported decision metric.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, sql
- Domain
- databases
- Issue type
- Documentation
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100