Performance Implications of High Storage Utilization in Redshift

When a Redshift cluster reaches 95% storage capacity, several performance issues can arise:

Potential Performance Impacts

1. Vacuum Operations: Redshift requires temporary space for vacuum operations. At high utilization, vacuums may:

   • Run less efficiently
   • Take longer to complete
   • Fail to reclaim space effectively

2. Query Performance: Limited free space can impact:

   • Sort operations that require temp space
   • Complex joins and aggregations
   • Concurrent query performance

3. Data Loading: ETL processes may slow down or fail due to insufficient space for staging

Key Metrics to Monitor

To determine if storage utilization is affecting performance, monitor these metrics:

1. Vacuum-Related Metrics

• SVV_VACUUM_PROGRESS: Check duration and effectiveness of vacuum operations
• SVV_VACUUM_SUMMARY: Review historical vacuum performance
• STL_VACUUM: Examine detailed vacuum logs for failures or extended runtimes

2. Performance Metrics

• PercentageDiskSpaceUsed: Track this CloudWatch metric over time
• QueryDuration: Monitor if query performance is degrading
• WLM Queue Wait Times: Check if queries are waiting longer

3. System Table Queries

sql -- Check for vacuum effectiveness SELECT table_name, unsorted_rows, vacuum_sort_benefit FROM svv_table_info ORDER BY vacuum_sort_benefit DESC;

-- Monitor tables with high deleted space SELECT "table", size, tbl_rows, unsorted_rows, deleted_rows, (deleted_rows::float / tbl_rows::float) 100 as pctdeleted FROM svv_table_info WHERE deleted_rows > 0 ORDER BY pct_deleted DESC;

-- Check for long-running vacuums SELECT FROM stlvacuum ORDER BY xid DESC LIMIT 10;

4. CloudWatch Metrics

• CPUUtilization: Higher values may indicate system strain
• ReadIOPS/WriteIOPS: Increased I/O can indicate vacuum overhead
• ReadLatency/WriteLatency: Higher latencies suggest storage pressure

Recommendations

If metrics indicate performance issues but increasing capacity isn't an option:

1. Optimize Vacuum Strategy:

   • Schedule vacuums during low-usage periods
   • Use targeted vacuums on critical tables
   • Consider VACUUM BOOST for one-time space recovery

2. Data Management:

   • Archive historical data to S3 using Redshift Spectrum
   • Implement more aggressive data retention policies
   • Use column encoding to improve compression

3. Table Optimization:

   • Review and adjust distribution and sort keys
   • Consider table partitioning for large tables
   • Analyze skew in data distribution

Documentation Sources

These recommendations are supported by AWS documentation:

1. Vacuum Operations and Disk Space:

   • VACUUM Command Documentation states: "VACUUM DELETE temporarily blocks update and delete operations" and "By default, VACUUM skips the sort phase for any table where more than 95 percent of the table's rows are already sorted."

2. Storage Utilization Impact:

   • Vacuuming Tables notes: "Maintenance operations like VACUUM and COPY use intermediate temporary storage space for their sort operations, so a spike in disk usage is expected."

3. Performance Monitoring:

   • SVV_TABLE_INFO provides the vacuum_sort_benefit column which shows "The estimated maximum percentage improvement of scan query performance when you run vacuum sort."
   • Monitoring Cluster Performance recommends monitoring "Percentage disk space used" to track disk utilization.

4. Vacuum Optimization:

   • Minimizing Vacuum Times advises: "Run vacuum operations on a regular schedule" and "If you need to load a new table with multiple COPY operations, run the largest load first."