Redshift Query Performance Monitoring
This is part 3 of a series on Amazon Redshift maintenance:
- Vacuuming for Table Performance
- Monitoring Disk Space
- Monitoring Query Performance
- Column Compression
While the AWS Console can give you a high-level view of your Redshift Cluster's performance, it's sometimes necessary to jump into the system tables provided by Redshift to understand and debug the performance of your queries.
Here are the most important system tables you can query.
STL_ALERT_EVENT_LOG
The STL_ALERT_EVENT_LOG table records an alert when the Redshift query optimizer identifies performance issues with your queries.
Alerts include missing statistics, too many ghost (deleted) rows, or large distribution or broadcasts. The Redshift documentation on `STL_ALERT_EVENT_LOG goes into more details.
You will usually run either a vacuum operation or an analyze operation to help fix issues with excessive ghost rows or missing statistics.
SVV_TABLE_INFO
The SVV_TABLE_INFO summarizes information from a variety of Redshift system tables and presents it as a view. Along with STL_ALERT_EVENT_LOG this view can help you understand why your queries have degraded performance either due to the wrong compression encoding, distribution keys or sort styles.
Some sample usecases:
| Column | Description | Possible action |
|---|---|---|
unsorted |
Percent of unsorted rows in the table. | Your table might need a vaccum full or a vacuum sort. |
stats_off |
Number that indicates how stale the table's statistics are; 0 is current, 100 is out of date. | Table statistics are a key input to the query planner, and if there are stale your query plans might not be optimum anymore. Run analyze to recompute statistics. |
Onboarding forward-thinking teams now:
No spam, ever! Unsubscribe any time. Learn more about the product.