Redshift Query Performance Monitoring

This is part 3 of a series on Amazon Redshift maintenance:

  1. Vacuuming for Table Performance
  2. Monitoring Disk Space
  3. Monitoring Query Performance
  4. 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.

Next Generation SQL Data Analysis Write SQL, visualize data, and share your results. Onboarding forward-thinking teams now:

No spam, ever! Unsubscribe any time. Learn more about the product.